Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Common relational databases include MySQL, Oracle, SQLServer, SQLite and PostgreSQL. There are roughly two ways to operate a database:

(1) Directly connect to the database interface. The relational database connection modules in Python include Pymysql, cx_Oracle, PyMSSQL, SQLITe3, and Psycopg2. Typically, these databases are connected to the database, execute SQL statements, commit things, and close the database connection.

(2) Operate the database through ORM framework. Common ORM framework modules are SQLObject, Stom, Django’s ORM, Peewee, and SQLAlchemy

1. Overview and installation of SQLAlchemy

SQLAlchemy can be installed directly using the PIP command

pip install SQLAlchemy
Copy the code

The installation process is as shown in the figure:

Use SQLAlchemy to connect to the database essentially through the database module connection, SQLAlchemy installation also need to install the database interface module, such as connect to MySQL need to install Pymysql module, connect to SQLite need SQLITe3 module, etc..

2. Connect to the database

Take the test database of the local MySQL database as an example. The user name root, password 123456, and default port 3306 are respectively used. The specific information of the database is shown in the figure below:

2.1 Connection Principles

Database connection pooling is a technique that stores database connections as objects in memory when the system is initialized. When a user needs to access a database, instead of creating a new connection, an existing connection object is removed from the pool. Instead of closing the connection, the user puts it back into the connection pool for the next access request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, the initial number of connections in the connection pool, the upper and lower limits of connections, and each connection can be controlled by setting connection pool parameters. The maximum number of times the connection is used and the maximum idle time. It can also monitor the number of database connections, usage, and so on through its own management mechanism

2.2 Connection Mode

2.2.1 Common Connection modes

from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test? charset=utf8', echo=True)Copy the code

2.2.2 Complete connection mode

from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test? charset=utf8', echo=True, pool_size=5, max_overflow=4, pool_recyle=7200, pool_timeout=30)Copy the code

2.3 Connection Parameters

mysql+pymysql://root:123456@localhost:3306/test? Charset = utf8: Mysql database system was types, pymysql is connected to the database interface module, the root is the user name, database system 123456 is a password database system, localhost: 3306 is the host address (the local database is because it is over, so localhost) and database service port, Test is the name of the database used

Echo =True: Displays the SQL statements executed by SQLAlchemy while operating on the database. In simple terms, if the value is True, the executed SQL statement is printed on the console; If the value is False, the SQL statement executed is not printed on the terminal

Pool_size: specifies the size of the connection pool. The default value is 5, which represents the maximum number of connections that can be created by the pool itself. It can be adjusted according to the actual situation.

Max_overflow: The maximum number of connections that can be created beyond the connection pool size. The default value is 10. When pool_size< number of connections <max_overflow, the portion that exceeds pool_size can be accessed normally. After use, the excess portion is not placed in the connection pool, but is actually closed. For example, if pool_size is 5 and max_overflow is 10, the number of connections should not be greater than 5+10=15. When the number of connections is 8, the disassembled 3 connections will be closed.

Pool_recyle: connection reset interval. The default value is -1. The recommended value is 7200. That is, if the connection has been idle for 7200 seconds, it is automatically reacquired to prevent the connection from being closed.

Pool_timeout: specifies the connection timeout duration. The default value is 30 seconds.

? Charset = UTf8: Sets the encoding of the database and can read and write Chinese characters to the database. If not, encoding errors will be prompted when data is added, modified, or updated.

2.4 Connection modes of Other Databases

Microsoft SQL Sever:

mssql+pymssql://username@password@ip:port//dbname
Copy the code

MySQL:

mysql+pymysql://username@password@ip:port//dbname
Copy the code

Oracle:

cx_Oracle://username@password@ip:port//dbname
Copy the code

PostgreSQL:

postgresql://username@password@ip:port//dbname
Copy the code

SQLite:

sqlite://file_path
Copy the code