Five SQL connectors suitable for Python project
- 2021-11-30 00:55:25
- OfStack
[51CTO. com] As a driver, database connector (
database connector
) works in a similar way that we connect a software interface to a specific database to implement the basic functionality of an adapter. Nowadays, Python has become a widely used programming language in the world. Therefore, it is necessary for us to understand the database connectors related to Python.
Usually, we can pass the Python standard database interface
Python DB-API
, will
MySQLdb
Module is applied to
MySQL
Above. However, since this module is independent of any other database engine, we need to additionally write Python scripts to achieve access to other database engines. Moreover, the API is not compatible with Python 3, so we need to use various Python type database connectors. Next, let's look at the first five Python SQL database connectors that are suitable for most Python programmers, and the various advantages and disadvantages of their installation
1. PyMySQL(https://pypi.org/project/PyMySQL/)
As you know, MySQL is an industry-leading, multi-user, multi-threaded open source database management system. It is very popular in the development of Web project.
Installation and use:
We can install PyMySQL on our own PC by running the following command:
bash
pip install pymysql
After installation, we can test the 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()
Advantages:
mysqlclient
And
MySQLdb
Compatible.
Simultaneous support
Python 2
And
3
.
Ability to support MySQL and MariaDB servers.
Disadvantages:
The low-level API provided by MySQL is not supported, including:
data_seek
,
store_result
And
Python DB-API0
Wait.
2. MySQLdb(https://mysqlclient.readthedocs.io/user_guide.html)
As an MySQL database server, compatible threads (
thread-compatible)
MySQLdb provides an API for the Python database.
Installation and use:
You can install the MySQLdb module on your own PC by running 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 installPythonpython-devel mysql-devel redhat-rpm-config gcc
#ForPythoncommand prompt, use the following command -
pip install MySQL-python
You can also use this connector by running the following Python code:
from MySQLdb import _mysql
db=_mysql.connect()
db=_mysql.connect(host="localhost",user="username",
passwd="password",db="db_name")
Advantages:
Disadvantages:
Python 3 is not supported. You need to write your own SQL code. Not only do you need to manage pointers by yourself, but you can't do any caching or parameterization. If you don't rewrite all the database code, you can't switch to a different database backend.
3. QTSQL(https://doc.qt.io/qt-5/qtsql-index.html)
As another database connector, QTSQL can be used to connect databases with various
PYQT5
Application integration. It is worth noting that since QT is an GUI toolkit,
QTSQL
It is mainly used for UI class applications.
Installation and use:
Since PYQT5 is pre-installed on QTSQL, you can import the corresponding module with the following Python code.
from PyQt5 import QtSql
And connect to the database through the following code snippet:
self.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("host_name")
self.db.setDatabaseName("database_name")
self.db.setUserName("username")
self.db.setPassword("password")
In the above code,
QSqlDatabase.addDatabase
Which can be used to add drivers, including QPSQL, QMYSQL, QOCI, QODBC, QSQLITE, and so on. The next four lines of command
：setHostName
(),
setDatabaseName
(),
setUserName
() and
setPassword()，
Can be used to initialize database connections. In addition, after initialization is complete, you can also call the
QSqlDatabase.open
() to open and access the database.
Advantages:
Disadvantages:
4. Psycopg2(https://pypi.org/project/psycopg2/)
Psycopg
Yes
Python
The most popular programming language
PostgreSQL
Database adapter. Its main feature is that it completely realizes
PythonDB API 2.0
And thread security (that is, multiple threads can share the same connection). Because it is designed for applications with a large number of multithreads, such applications will not only create and destroy a large number of pointers, but also generate a large number of concurrent
INSERT
Or
UPDATE
.
Installation and guide:
You can install psycopg2 on your own PC by running the following command:
bash
pip install psycopg2
When the installation is complete, run the following Python code:
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""")
Advantages:
Disadvantages:
5. SuperSQLite(https://github.com/plasticityai/supersqlite)
As a target for
Python
Super of
SQLite
Libraries and drivers,
SuperSQLite
Will the original built-in
SQLite
Package, replaced by native, precompiled (
MySQL0
)
SQLite
, and
SQLite
Expand.
Installation and guide:
You can install it on your own PC by running the following command
SuperSQLite：
bash
pip install supersqlite
After the installation is complete, run the following Python code to use it:
from supersqlite import sqlite3
conn = sqlite3.connect('databasefile.db')
Advantages:
No significant shortcomings have been found yet.
Summary:
To sum up, we learned what a database connector is, why we use a database connector in Python, and the five most commonly used Python SQL database connectors. In addition, we also discussed the installation steps of each connector and their respective advantages and disadvantages.