Choose the Best SQL Adapter for Your Python Project

Introduction

This article will explain what a database connector is and cover the pros and cons of some popular python SQL connectors.

What is a Database Connector?

A database connector is a driver that works like an adapter that connects a software interface to a specific database vendor implementation.

Why Use Python Database Connectors?

Python’s standard database interface is Python DB-API. This interface uses the MySQLdb module for only MySQL. This module is independent of any other database engine, so we need to write Python scripts to access any other database engine. However, doing this isn’t compatible with Python 3. Therefore Python provides us with python database connectors.

List of The Top 5 Python SQL Connectors

Below is a list of the top five python SQL database connectors that will be useful to most Python programmers:

PyMySQL
MySQLdb
QTSQL
Psycopg2
SuperSQLite

PyMySQL

MySQL is a leading open-source database management system. It is a multiuser, multithreaded database management system. MySQL is especially popular for web development work.

Installation and Use

To install PyMySQL on your pc, run the command below:

bash
pip install pymysql

After installing, we can now test our database connector by running the python code below:

import pymysql
con = pymysql.connect('localhost', 'username',
    'password', 'db_name’')
with con.cursor() as cur:
    cur.execute('SELECT VERSION()')
    version = cur.fetchone()
    print(f'Database version: {version[0]}')

con.close()

Pros

Most public APIs are compatible with mysqlclient and MySQLdb
Supports both Python 2 and 3
Supports both MySQL and MariaDB server

Cons

Doesn't support low-level APIs _mysql provides like data_seek, store_result, and use_result

MySQLdb

MySQLdb is a thread-compatible interface to the popular MySQL database server that provides the Python database API.

Installation and Use

To install MySQLdb module, use the following command :

bash
# For Ubuntu, use the following command -
sudo apt-get install python-pip python-dev libmysqlclient-dev

# For Fedora, use the following command -
sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

#For Python command prompt, use the following command -
pip install MySQL-python

To use this connector, run the python code below:

from MySQLdb import _mysql

db=_mysql.connect()
db=_mysql.connect(host="localhost",user="username",
                  passwd="password",db="db_name")

Pros

Built with C, and therefore works fast
Pure SQL
Supports MySQL

Cons

Does not support Python 3
Requires you to write SQL code
Requires you to manage the cursor, doesn't do any caching, parameterization, etc.
Can't switch to a different database backend without rewriting all of your database code

QTSQL

QTSQL is a database connector used to integrate databases with PYQT5 applications. It is important to note that QTSQL is primarily intended for use with UI applications (QT is, after all, a GUI toolkit).

Installation and Use

QTSQL comes pre-installed with PYQT5.

To import the module, use the python code below:

from PyQt5 import QtSql

To connect to databases:

self.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("host_name")
self.db.setDatabaseName("database_name")
self.db.setUserName("username")
self.db.setPassword("password")

The first argument QSqlDatabase.addDatabase in the code above is used to add drivers(e.g., QPSQL, QMYSQL, QOCI, QODBC, QSQLITE, etc.). The next four commands setHostName(), setDatabaseName(), setUserName(), and setPassword() initializes the database connection. QSqlDatabase.open() is called to open the database and to access it once it is initialized.

Pros

Only uses Qt libraries
It returns Qt objects, so it will integrate with Qt's standard widgets
Can use any database backend that Qt supports(MySQL, SQLite)

Cons

Still requires you to write SQL

Psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and thread safety (several threads can share the same connection). It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent INSERTs or UPDATEs.

Installation and Guide

To install, run the command below:

bash
pip install psycopg2

After installing, run the python code below to use:

import psycopg2

try:
    conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
except:
    print "I am unable to connect to the database"

cur = conn.cursor()
cur.execute("""SELECT datname from pg_database""")

Pros

Fast and Efficient
Supports multiple connections and connection objects
Supports asynchronous queries

Cons

Lack of documentation

SuperSQLite

A supercharged SQLite library and driver for Python. This library replaces the built-in SQLite packages with a newer version of SQLite natively pre-compiled for every platform, along with natively pre-compiled SQLite extensions.

Installation and Guide

To install, run the command below:

bash
pip install supersqlite

After installing, run the python code below to use:

from supersqlite import sqlite3
conn = sqlite3.connect('databasefile.db')

Pros

Fast and efficient
Remote streaming over HTTP
Full-text search

Cons

No known cons

Conclusion

In this article, you learned what a database connector is, why to use database connectors in python, and the top 5 Python SQL database connectors to use. In addition, you learned the pros and cons of each connector and how easy it is to install and use them.
If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.