Comment faire fonctionner MySQL en Python ?

1. Introduction à la base de données d'exploitation Python

L'interface de base de données standard de Python est Python DB-API, qui fournit aux développeurs une interface de programmation d'applications de base de données. L'interface de base de données Python prend en charge de nombreuses bases de données, vous pouvez choisir la base de données qui convient à votre projet :

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

Vous pouvez visiter l'interface et l'API de la base de données Python pour afficher une liste détaillée des bases de données prises en charge.

Vous devez télécharger différents modules API DB pour différentes bases de données. Par exemple, si vous devez accéder à la base de données Oracle et aux données Mysql, vous devez télécharger les modules de base de données Oracle et MySQL.

DB-API est une spécification. Elle définit une série d'objets et de méthodes d'accès aux bases de données nécessaires pour fournir des interfaces d'accès cohérentes pour divers systèmes de bases de données sous-jacents et divers programmes d'interface de bases de données.

La DB-API de Python implémente des interfaces pour la plupart des bases de données. Après l'avoir utilisée pour vous connecter à chaque base de données, vous pouvez utiliser chaque base de données de la même manière.

Processus d'utilisation de Python DB-API :

Introduction du module API.

Obtenez la connexion à la base de données.

Exécutez des instructions SQL et des procédures stockées

Fermez la connexion à la base de données.

2. Python exploite le module MySQL

Python utilise principalement deux méthodes pour faire fonctionner MySQL :

Module DB (SQL natif)

• PyMySQL (prend en charge python2.x/3.x)
• MySQLdb (actuellement ne prend en charge que python2. x)

Cadre ORM

• SQLAchemy

2.1 Module PyMySQL

Cet article présente principalement le module PyMySQL L'utilisation de MySQLdb est similaire

2.1.1 Installer PyMySQL

PyMySQL. est un MySQL écrit en Pilote Python, utilisons le langage Python pour faire fonctionner la base de données MySQL.

pip install PyMySQL

2.2 Utilisation de base

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
# cursor = conn.cursor()
# 创建游标(查询数据返回为字典格式)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 1. 执行SQL,返回受影响的行数
effect_row1 = cursor.execute("select * from USER")
# 2. 执行SQL,返回受影响的行数,一次插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [("jack"), ("boom"), ("lucy")])# 3
# 查询所有数据,返回数据为元组格式
result = cursor.fetchall()
# 增/删/改均需要进行commit提交,进行保存
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
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 Obtenez les données nouvellement créées et incrémentez l'ID

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
cursor = conn.cursor()
# 获取新创建数据自增ID
effect_row = cursor.executemany("insert into USER (NAME)values(%s)", [("eric")])
# 增删改均需要进行commit提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
new_id = cursor.lastrowid
print(new_id)
"""
8
"""

2.4 Opération de requête

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
cursor.execute("select * from USER")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
row_2 = cursor.fetchmany(3)
#
# # 获取所有数据
row_3 = cursor.fetchall()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(row_1)
print(row_2)
print(row_3)

⚠️ Lors de la récupération des données, procédez dans l'ordre. Vous pouvez utiliser curseur.scroll(num, mode) pour vous déplacer. la position du curseur, tel que :

• cursor.scroll(1,mode='relative') #Déplacement par rapport à la position actuelle
• cursor.scroll(2,mode='absolute') #Déplacement par rapport à la position absolue

2.5 Empêcher l'injection SQL

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
# 存在sql注入情况(不要用格式化字符串的方式拼接SQL)
sql = "insert into USER (NAME) values('%s')" % ('zhangsan',)
effect_row = cursor.execute(sql)
# 正确方式一
# execute函数接受一个元组/列表作为SQL参数,元素个数只能有1个
sql = "insert into USER (NAME) values(%s)"
effect_row1 = cursor.execute(sql, ['wang6'])
effect_row2 = cursor.execute(sql, ('wang7',))
# 正确方式二
sql = "insert into USER (NAME) values(%(name)s)"
effect_row1 = cursor.execute(sql, {'name': 'wudalang'})
# 写入插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [('ermazi'), ('dianxiaoer')])
# 提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

De cette façon, les opérations SQL seront plus sûres. Si vous avez besoin d'une documentation plus détaillée, veuillez vous référer à la documentation PyMySQL. Cependant, il semble que l'implémentation de ces bases de données SQL ne soit pas la même. L'espace réservé aux paramètres de PyMySQL utilise le formateur C tel que %s, tandis que l'espace réservé du module sqlite3 fourni avec Python semble être un point d'interrogation (?) . Il est donc préférable de lire attentivement la documentation lors de l'utilisation d'autres bases de données. Bienvenue dans la documentation de PyMySQL

3. Pool de connexions à la base de données

Il y a un problème avec la méthode ci-dessus, qui peut être résolu dans un cas monothread. Le programme doit créer et libérer fréquemment des connexions pour terminer le fonctionnement de la base de données. Ensuite, notre programme/script est dans Quels problèmes vont survenir en multi-threading ? À ce stade, nous devons utiliser un pool de connexions à une base de données pour résoudre ce problème

3.1 Module DBUtils

DBUtils est un module en Python utilisé pour implémenter ! pools de connexions à la base de données.

Ce pool de connexions a deux modes de connexion :

• Créer une connexion pour chaque thread Même si le thread appelle la méthode close, il ne sera pas fermé. Il remettra simplement la connexion dans le pool de connexions pour son propre thread. à utiliser à nouveau. Lorsque le thread se termine, la connexion sera automatiquement fermée
• Créez un lot de connexions au pool de connexions pour tous les threads à partager (recommandé)

3.2 Mode un

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
from DBUtils.PersistentDB import PersistentDB
import pymysql
POOL = PersistentDB(
 creator=pymysql,# 使用链接数据库的模块
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：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,
 # 如果为False时， conn.close() 实际上被忽略，供下次使用，在线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
 threadlocal=None,# 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
 host='127.0.0.1',
 port=3306,
 user='zff',
 password='zff123',
 database='zff',
 charset='utf8',
)
def func():
 conn = POOL.connection(shareable=False)
 cursor = conn.cursor()
 cursor.execute('select * from USER')
 result = cursor.fetchall()
 cursor.close()
 conn.close()
 return result
result = func()
print(result)

3.3 Mode deux

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import time
import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
POOL = PooledDB(
 creator=pymysql,# 使用链接数据库的模块
 maxconnections=6,# 连接池允许的最大连接数，0和None表示不限制连接数
 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
 maxcached=5,# 链接池中最多闲置的链接，0和None不限制
 maxshared=3,
 # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：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():
 # 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常
 # 否则
 # 则优先去初始化时创建的链接中获取链接 SteadyDBConnection。
 # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。
 # 如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。
 # 一旦关闭链接后，连接就返回到连接池让后续线程继续使用。
 conn = POOL.connection()
 # print('连接被拿走了', conn._con)
 # print('池子里目前有', POOL._idle_cache, 'rn')
 cursor = conn.cursor()
 cursor.execute('select * from USER')
 result = cursor.fetchall()
 conn.close()
 return result
result = func()
print(result)

⚠️ Grâce à pymysql, MySQLdb, etc. La valeur threadsafety est 1, donc les threads du pool de connexion dans ce mode seront partagés par tous les threads, il est donc thread-safe. S'il n'y a pas de pool de connexions, lors de l'utilisation de pymysql pour se connecter à la base de données, il n'y a aucun problème avec les applications monothread. Cependant, si des applications multithreads sont impliquées, un verrouillage est requis. Une fois verrouillées, les connexions seront inévitablement mises en file d'attente. . Lorsqu'il y a de nombreuses demandes, les performances se détériorent.

3.4 Verrouillé

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
import threading
from threading import RLock
LOCK = RLock()
CONN = pymysql.connect(host='127.0.0.1',
port=3306,
user='zff',
password='zff123',
database='zff',
charset='utf8')
def task(arg):
 with LOCK:
 cursor = CONN.cursor()
 cursor.execute('select * from USER ')
 result = cursor.fetchall()
 cursor.close()
 print(result)
for i in range(10):
 t = threading.Thread(target=task, args=(i,))
 t.start()

3.5 Pas de verrouillage (rapport d'erreur)

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
import threading
CONN = pymysql.connect(host='127.0.0.1',
port=3306,
user='zff',
password='zff123',
database='zff',
charset='utf8')
def task(arg):
 cursor = CONN.cursor()
 cursor.execute('select * from USER ')
 # cursor.execute('select sleep(10)')
 result = cursor.fetchall()
 cursor.close()
 print(result)
for i in range(10):
 t = threading.Thread(target=task, args=(i,))
 t.start()

Vous pouvez vérifier l'état de la connexion dans la base de données à ce moment : afficher l'état comme "Threads%" ;

4 Le pool de connexion à la base de données est utilisé en conjonction avec. pymsql

# cat sql_helper.py
import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
POOL = PooledDB(
 creator=pymysql,# 使用链接数据库的模块
 maxconnections=20,# 连接池允许的最大连接数，0和None表示不限制连接数
 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
 maxcached=5,# 链接池中最多闲置的链接，0和None不限制
 #maxshared=3,# 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：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():
 # 创建连接
 # conn = pymysql.connect(host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db')
 conn = POOL.connection()
 # 创建游标
 cursor = conn.cursor(pymysql.cursors.DictCursor)
 return conn,cursor
def close(conn,cursor):
 # 关闭游标
 cursor.close()
 # 关闭连接
 conn.close()
def fetch_one(sql,args):
 conn,cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 result = cursor.fetchone()
 close(conn,cursor)
 return result
def fetch_all(sql,args):
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 cursor.execute(sql,args)
 result = cursor.fetchall()
 close(conn, cursor)
 return result
def insert(sql,args):
 """
 创建数据
 :param sql: 含有占位符的SQL
 :return:
 """
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 conn.commit()
 close(conn, cursor)
def delete(sql,args):
 """
 创建数据
 :param sql: 含有占位符的SQL
 :return:
 """
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 conn.commit()
 close(conn, cursor)
 return effect_row
def update(sql,args):
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql, args)
 conn.commit()
 close(conn, cursor)
 return effect_row

PS : Vous pouvez utiliser des méthodes statiques pour les encapsuler dans une classe pour une utilisation facile.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!