In different projects, we need to use different interfaces and adapters, and these "accessories" are different, and there are still many types of choices. Today, let's share with you some related contents in the python project!

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's implementation.

Why use Python Database Connector?

Python's standard database interface is the Python DB-API. This interface only uses the MySQL LDB module for MySQL. This module is independent of any other database engine, so we need to write Python scripts to access any other database engine. However, this is not compatible with Python 3. Therefore, python provides us with a python database connector.

List of the first 5 Python SQL connectors

The following is a list of the first five Python SQL database connectors that are useful to most Python programmers:

1. pymysql
2. database
3. QTSQL
4. Psycopg2
5. Super SQLite

1. pymysql

MySQL is a leading open source database management system. It is a multi-user, multi-threaded database management system. And MySQL is particularly popular in Web development.

Installation and use

To install PyMySQL on your PC, run the following command:

bash 
pip install pymysql 

After installation, we can now test our database connector by running the following python code:

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()

advantage

• Most public API s are compatible with mysqlclient and MySQL dB.
• Supports Python 2 and 3.
• MySQL and MariaDB servers are supported.

shortcoming

• Not supported_ mysql provides low-level API s, such as data_seek,store_result and use_result.

two   MySQL database

MySQL DB is a thread compatible interface of popular MySQL database server and provides Python database API.

Installation and use

To install the MySQL DB 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 following Python code:

from MySQLdb import _mysql

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

advantage

• Built in C, so it runs fast.
• Pure SQL.
• MySQL is supported.

shortcoming

• Python 3 is not supported.
• You need to write SQL code.
• You need to manage cursors without any caching, parameterization, etc.
• If you do not rewrite all the database code, you cannot switch to a different database backend.

3. QTSQL

QTSQL is a database connector used to integrate databases with PYQT5 applications. It should be noted that QTSQL is mainly used for UI applications (after all, QT is a GUI Toolkit).

Installation and use

PYQT5 is pre installed in QTSQL.

To import a module, use the following Python code:

from PyQt5 import QtSql

To connect to the database:

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

QSqlDatabase.addDatabase the first parameter in the above code is used to add drivers (for example, QPSQL, qmmysql, QOCI, QODBC, QSQLITE, etc.). The next four commands, setHostName(), setDatabaseName(), setUserName(), and setPassword(), initialize the database connection. QSqlDatabase.open() is called to open the database and access it after initialization.

advantage

• Use Qt library only.
• It returns Qt objects, so it will integrate with Qt's standard widgets.
• You can use any database backend supported by Qt (MySQL, SQLite).

shortcoming

• You still need to write SQL.

4. Psycopg2

Psycopg is the most popular PostgreSQL database adapter in Python programming language. Its main feature is the complete implementation of Python DB API 2.0 specification and thread safety (multiple threads can share the same connection). It is designed for a large number of multithreaded applications that create and destroy a large number of cursors and generate a large number of concurrent INSERTs or UPDATEs.

Installation and guidance

To install, run the following command:

bash 
pip install psycopg2 

After installation, run the following Python code 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""")

advantage

• Fast and efficient.
• Multiple connections and connection objects are supported.
• Support asynchronous query.

shortcoming

• Lack of documentation.

5. Super SQLite

SQLite libraries and drivers for Python. The library replaces the built-in SQLite package with a newer version of SQLite precompiled locally for each platform and a locally precompiled SQLite extension.

Installation and guidance

To install, run the following command:

bash 
pip install supersqlite

After installation, run the following Python code to use:

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

advantage

• Fast and efficient.
• Remote streaming over HTTP.
• Full text search.

shortcoming

• There are no known shortcomings.