The most complete tutorial on using Python to operate MySQL

1. Introduction to Python operation database

The Python standard database interface is Python DB-API, which provides developers with a database application programming interface. The Python database interface supports a lot of databases, and you can choose the database that suits your project:

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase …

You can access the Python database interface and API to view a detailed list of supported databases.

For different databases, you need to download different DB API modules. For example, you need to access Oracle database and Mysql data, you need to download Oracle and MySQL database modules.

DB-API is a specification. It defines a series of necessary objects and database access methods to provide a consistent access interface for a wide variety of underlying database systems and a variety of database interface programs.

Python's DB-API implements an interface for most databases. After using it to connect to each database, you can operate each database in the same way.

Python DB-API usage process:

Introduce API modules.
Get the connection to the database.
Execute SQL statements and stored procedures.
Close the database connection.

2. Python operates MySQL module

Python uses two main ways to operate MySQL:

Module (native SQL)

PyMySQL (support/)

MySQLdb (currently only supported)

frame

SQLAchemy

2.1 PyMySQL module

This article mainly introduces the PyMySQL module, and the usage of MySQLdb is similar.

Install PyMySQL

PyMySQL is a MySQL driver written in Python, allowing us to operate MySQL databases in Python.

pip install PyMySQL

2.2 Basic use

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql

# Create a connectionconn = (host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')

# Create a cursor (query data returns to tuple format)# cursor = ()

# Create a cursor (query data back to dictionary format)cursor = ()

# 1. Execute SQL and return the number of affected rowseffect_row1 = ("select * from USER")

# 2. Execute SQL, return the number of affected rows, insert multiple rows of data at onceeffect_row2 = ("insert into USER (NAME) values(%s)", [("jack"), ("boom"), ("lucy")])  # 3

# Query all data, return data in tuple formatresult = ()

# Add/delete/modify all require commit submission and save()

# Close cursor()

# Close the connection()

print(result)
"""
[{'id': 6, 'name': 'boom'}, {'id': 5, 'name': 'jack'}, {'id': 7, 'name': 'lucy'}, {'id': 4, 'name': 'tome'}, {'id': 3, 'name': 'zff'}, {'id': 1, 'name': 'zhaofengfeng'}, {'id': 2, 'name': 'zhaofengfeng02'}]
"""

2.3 Obtain the latest created data self-increase ID

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql

# Create a connectionconn = (host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')

# Create a cursor (query data returns to tuple format)cursor = ()

# Get the newly created data self-increasing IDeffect_row = ("insert into USER (NAME)values(%s)", [("eric")])

# Both additions, deletions and modifications require commit submission()

# Close cursor()

# Close the connection()

new_id = 
print(new_id)
"""
8
""

2.4 Query operation

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql

# Create a connectionconn = (host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')

# Create a cursorcursor = ()

("select * from USER")

# Get the first line of datarow_1 = ()

# Get the first n rows of datarow_2 = (3)
#
# # Get all datarow_3 = ()

# Close cursor()

# Close the connection()
print(row_1)
print(row_2)
print(row_3)

⚠️ When fetching the data, you can use (num, mode) to move the cursor position, such as:

(1,mode='relative') # Move relative to the current position
(2,mode='absolute') # Move relative to absolute position

2.5 Prevent SQL injection

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql

# Create a connectionconn = (host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')

# Create a cursorcursor = ()

# There is a SQL injection situation (do not splice SQL by formatting strings)sql = "insert into USER (NAME) values('%s')" % ('zhangsan',)
effect_row = (sql)

# The correct way# execute function accepts a tuple/list as SQL parameter, and only 1 element number can be 1sql = "insert into USER (NAME) values(%s)"
effect_row1 = (sql, ['wang6'])
effect_row2 = (sql, ('wang7',))

# Correct way twosql = "insert into USER (NAME) values(%(name)s)"
effect_row1 = (sql, {'name': 'wudalang'})

# Write and insert multiple rows of dataeffect_row2 = ("insert into USER (NAME) values(%s)", [('ermazi'), ('dianxiaoer')])

# submit()
# Close cursor()
# Close the connection()

In this way, SQL operations are safer. If you need more detailed documentation, please refer to the PyMySQL documentation. However, it seems that the implementation of these SQL databases is not quite the same. The parameter placeholders of PyMySQL use C formatters such as %s, while the placeholders of the sqlite3 module that Python comes with seem to be a question mark (?). Therefore, when using other databases, please read the documentation carefully. Welcome to PyMySQL’s documentation

3. Database connection pool

There is a problem with the above method. It can be met in a single threaded situation. The program needs to frequently create and release connections to complete the operation of the database. So, what problems will our program/script cause in a multi-threaded situation? At this time, we need to use the database connection pool to solve this problem!

3.1 DBUtils module

DBUtils is a module in Python for implementing database connection pooling.

There are two connection modes for this connection pool:

Create a connection for each thread. Even if the thread calls the close method, it will not close. It just puts the connection back into the connection pool for its own thread to use again. The connection will be automatically closed when the thread terminates

Create a batch of connections to the connection pool for sharing with all threads (recommended)

3.2 Mode One

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

from  import PersistentDB
import pymysql

POOL = PersistentDB(
    creator=pymysql,  # Use modules that link databases    maxusage=None,  # The maximum number of times a link is reused, None means unlimited    setsession=[],  # List of commands executed before starting the session.  For example: ["set datestyle to...", "set time zone..."]    ping=0,
    # ping MySQL server to check whether the service is available.  # For example: 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always    closeable=False,
    # If False, () is actually ignored and will automatically close the link the next time you use it and then the thread is closed.  If True, () closes the link, then an error will be reported when calling again, because the connection has been really closed (pool.steady_connection() can get a new link)    threadlocal=None,  # This thread has exclusive value objects, used to save link objects, if the link object is reset    host='127.0.0.1',
    port=3306,
    user='zff',
    password='zff123',
    database='zff',
    charset='utf8',
)

def func():
    conn = (shareable=False)
    cursor = ()
    ('select * from USER')
    result = ()
    ()
    ()
    return result


result = func()
print(result)

3.2 Mode 2

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import time
import pymysql
import threading
from  import PooledDB, SharedDBConnection

POOL = PooledDB(
    creator=pymysql,  # Use modules that link databases    maxconnections=6,  # The maximum number of connections allowed by the connection pool, 0 and None means no limit on the number of connections    mincached=2,  # When initializing, at least an idle link created in the link pool, 0 means that no    maxcached=5,  # The most idle links in the link pool, no restrictions on 0 and None    maxshared=3,
    # The maximum number of links shared in the link pool, 0 and None means all shared.  PS: Useless, because the threadsafety of modules such as pymysql and MySQLdb is 1, and no matter how many values are set to, _maxcached is always 0, so all links are always shared.    blocking=True,  # Whether to block and wait after no connection is available in the connection pool.  True, wait; False, don't wait and then report an error    maxusage=None,  # The maximum number of times a link is reused, None means unlimited    setsession=[],  # List of commands executed before starting the session.  For example: ["set datestyle to...", "set time zone..."]    ping=0,
    # ping MySQL server to check whether the service is available.  # For example: 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always    host='127.0.0.1',
    port=3306,
    user='zff',
    password='zff123',
    database='zff',
    charset='utf8'
)

def func():
    # Check whether the number of currently running connections is less than the maximum number of links. If not, then: Wait or report a raise TooManyConnections exception    # Otherwise    # Then, you will give priority to getting the link from the link created during initialization. SteadyDBConnection.    # Then encapsulate the SteadyDBConnection object into the PooledDedicatedDBConnection and return.    # If the link created at the beginning has no link, create a SteadyDBConnection object, then encapsulate it into the PooledDedicatedDBConnection and return.    # Once the link is closed, the connection returns to the connection pool for subsequent threads to continue to use.    conn = ()

    # print('Connection taken', conn._con)    # print('There is currently in the pool', POOL._idle_cache, '\r\n')
    cursor = ()
    ('select * from USER')
    result = ()
    ()
    return result


result = func()
print(result)

⚠️ Since the threadsafety value of pymysql, MySQLdb, etc. is 1, the threads in this mode connection pool will be shared by all threads, so it is thread-safe. If there is no connection pool, when using pymysql to connect to the database, there is no problem with single-threaded applications, but if it involves multi-threaded applications, then locking is required. Once locking is added, the connection will inevitably be queued. When there are many requests, the performance will be degraded.

3.3 Lock

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql
import threading
from threading import RLock

LOCK = RLock()
CONN = (host='127.0.0.1',
                       port=3306,
                       user='zff',
                       password='zff123',
                       database='zff',
                       charset='utf8')

def task(arg):
    with LOCK:
        cursor = ()
        ('select * from USER ')
        result = ()
        ()

        print(result)

for i in range(10):
    t = (target=task, args=(i,))
    ()

3.4 No lock (Error report)

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13

import pymysql
import threading

CONN = (host='127.0.0.1',
                       port=3306,
                       user='zff',
                       password='zff123',
                       database='zff',
                       charset='utf8')

def task(arg):
    cursor = ()
    ('select * from USER ')
    # ('select sleep(10)')
    result = ()
    ()
    print(result)

for i in range(10):
    t = (target=task, args=(i,))
    ()

At this time, you can view the connection status in the database: show status like ‘Threads%’;

IV. Database connection pool is used in combination with pymsql

# cat sql_helper.py

import pymysql
import threading
from  import PooledDB, SharedDBConnection
POOL = PooledDB(
    creator=pymysql,  # Use modules that link databases    maxconnections=20,  # The maximum number of connections allowed by the connection pool, 0 and None means no limit on the number of connections    mincached=2,  # When initializing, at least an idle link created in the link pool, 0 means that no    maxcached=5,  # The most idle links in the link pool, no restrictions on 0 and None    #maxshared=3, # The maximum number of links shared in the link pool, 0 and None means all shared.  PS: Useless, because the threadsafety of modules such as pymysql and MySQLdb is 1, and no matter how many values are set to, _maxcached is always 0, so all links are always shared.    blocking=True,  # Whether to block and wait after no connection is available in the connection pool.  True, wait; False, don't wait and then report an error    maxusage=None,  # The maximum number of times a link is reused, None means unlimited    setsession=[],  # List of commands executed before starting the session.  For example: ["set datestyle to...", "set time zone..."]    ping=0,
    # ping MySQL server to check whether the service is available.  # For example: 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always    host='192.168.11.38',
    port=3306,
    user='root',
    passwd='apNXgF6RDitFtDQx',
    db='m2day03db',
    charset='utf8'
)

def connect():
    # Create a connection    # conn = (host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db')
    conn = ()
    # Create a cursor    cursor = ()

    return conn,cursor

def close(conn,cursor):
    # Close cursor    ()
    # Close the connection    ()

def fetch_one(sql,args):
    conn,cursor = connect()
    # Execute SQL and return the number of rows affected    effect_row = (sql,args)
    result = ()
    close(conn,cursor)

    return result

def fetch_all(sql,args):
    conn, cursor = connect()

    # Execute SQL and return the number of rows affected    (sql,args)
    result = ()

    close(conn, cursor)
    return result

def insert(sql,args):
    """
     Create data
     :param sql: SQL with placeholders
     :return:
     """
    conn, cursor = connect()

    # Execute SQL and return the number of rows affected    effect_row = (sql,args)
    ()

    close(conn, cursor)

def delete(sql,args):
    """
     Create data
     :param sql: SQL with placeholders
     :return:
     """
    conn, cursor = connect()

    # Execute SQL and return the number of rows affected    effect_row = (sql,args)

    ()

    close(conn, cursor)

    return effect_row

def update(sql,args):
    conn, cursor = connect()

    # Execute SQL and return the number of rows affected    effect_row = (sql, args)

    ()

    close(conn, cursor)

    return effect_row

PS: It can be encapsulated into a class using static methods, which is convenient to use

The above is the detailed content of the most comprehensive tutorial on using Python to operate MySQL. For more information about Python to operate MySQL, please follow my other related articles!