Python操作数据库

本文主要介绍如何使用pymysql库以及DBUtils库来连接MySQL数据库。

一 准备

1 几个概念

2 几个概念间关系

二 安装模块

​ Python 程序想要操作数据库，首先需要安装 模块 来进行操作，下面使用pip命令安装PyMSQL模块

pip3 install pymysql

如果没有pip3命令那么需要确认环境变量是否有添加，安装完毕后测试是否安装完毕。

>>> import pymysql
>>>
# 如果没有报错，则表示安装成功

三 Pymysql 基本使用

连接数据库并执行sql语句的一般流程是：

1. 建立连接
2. 获取游标(创建)
3. 执行SQL语句
4. 提交事务
5. 释放资源

对应到代码上的逻辑为：

1. 导入相应的Python模块
2. 使用connect函数连接数据库，并返回一个Connection对象
3. 通过Connection对象的cursor方法，返回一个Cursor对象
4. 通过Cursor对象的execute方法执行SQL语句
5. 如果执行的是查询语句，通过Cursor对象的fetchall语句获取返回结果
6. 调用Cursor对象的close关闭Cursor
7. 调用Connection对象的close方法关闭数据库连接

connection 对象（生成对象的方法参数、对象方法）

cursor 对象（对象方法、fetch 方法）

3.1 实现

连接数据库

import pymysql

def connect_mysql():
    conn = pymysql.connect(host='127.0.0.1'，
                       port=3306，
                       user='root'，
                       password='123'，
                       database='pooldb'，
                       charset='utf8')
   return conn

# conn.begin：开始事务
# conn.commit：提交事务
# conn.rollback：回滚事务
# conn.cursor：返回一个Cursor对象
# conn.autocommit：设置事务是否自动提交
# conn.set_character_set：设置字符集编码
# conn.get_server_info：获取数据库版本信息
# conn.ping(reconnect=True): 测试数据库是否活着，reconnect表示断开与服务器连接后是否重连，连接关闭时抛出异常（一般用来测通断）

PS: 在实际的编程过程中，一般不会直接调用begin、commit和rollback函数，而是通过上下文管理器实现事务的提交与回滚操作。

利用游标操作数据库

查询类SQL

conn = connect_mysql()
cursor = conn.cursor()    # 创建游标
sql = 'select * from test.student where id = %s'
cursor.execute(sql,args=(2,))	# 参数化查询
result = cursor.fetchall()  # 获取游标执行的所有结果
cursor.close()
conn.close()

非查询类SQL

try:
    conn = connect_mysql()
    cursor = conn.cursor()  # 创建游标
    sql = r"insert into test.student (id,name,age) VALUES (%s,%s,%s)"
    cursor.execute(sql, args=(6,'dahl', 23,))
    conn.commit()    # 提交事务
except:
    conn.rollback()   # 当SQL语句执行失败时，回滚
finally:
    if cursor:   
        cursor.close()    # 关闭游标
    if conn:
        conn.close()   # 关闭连接

PS: 使用pymysql来连接数据库时，单线程应用完全没有问题，但如果涉及到多线程应用那么就需要加锁，一旦加锁那么连接势必就会排队等待，当请求比较多时，性能就会降低了。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

四 数据库连接池DBUtils

DBUtils 是一套用于管理数据库连接池的包，并允许对非线程安全的数据库接口进行线程安全包装。它能为高频度高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。

连接池对性能的提升：

1. 使用连接池可以进行长连接，直接从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度

2. 关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接

PS：如果不执行conn.close() 会导致线程不断创建连接，超过了连接池能容纳的最大连接数而报错: pymysql.err.OperationalError，1040， 'Too many connections'

DBUtils提供两种外部接口：

• PersistentDB: 提供线程专用的数据库连接，并自动管理连接。（为每一个线程创建一个）
• PooledDB: 提供线程间可共享的数据库连接，并自动管理连接。（常用）

PooledDB 和 PersistentDB 都通过回收数据库连接，且即使数据库连接中断也能保持稳定性，从而达到提升数据库访问性能的目的。那么在现实场景中应该如何选择呢？

PooledDB 常用于多线程的情况。如果你的程序频繁地启动和关闭线程，最好使用这个;

PersistentDB 常用于单线程的情况。PersistentDB 会为每个线程创建一个，资源消耗太大。如果你的程序只是在单个线程上进行频繁的数据库连接，最好使这个;

4.1 安装

pip install DBUtils
pip install pymysql

4.2 使用

连接池对象只初始化一次，一般可以作为模块级代码来确保。

PersistentDB

实现了稳定，线程仿射(thread-affine)，持久化的数据库连接。某个线程第一次开启一个数据库连接时，该连接将用于此特定线程。即使在线程中关闭连接，连接也会保持打开状态，以便同一个线程的下一次连接请求直接使用。线程结束时该连接会自动关闭。图解地址

from DBUtils.PersistentDB import PersistentDB
import pymysql

POOL = PersistentDB(
    creator=pymysql,
    ping=0,
    closeable=False,
    threadlocal=None,
    host='10.1.210.33',
    port=3306,
    user='root',
    password='1234qwer',
    database='devops',
    charset='utf8'
)

def query():
    conn = POOL.connection()
    cursor = conn.cursor()
    cursor.execute('select * from XXX')
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()
    
if __name__=='__main__':
    query()

NOTE:需要在连接上调用begin()方法明确开启事务。这可以确保a.只在事务完成时才重新打开连接b.连接被同一个线程重用时回滚。

PooledDB

DBUtils.PooledDB 实现了稳定、线程安全的缓存连接池。图解地址

相关配置参数说明：

PooledDB 参数	参数说明
mincached	初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached	连接池中允许的闲置的最多连接数量(缺省值 0和None不限制)
maxshared	允许的最大共享连接数(0或None表示所有连接都是专用的)
maxconnections	连接池允许的最大连接数，0和None表示不限制连接数
blocking	连接池中如果没有可用连接后，是否阻塞等待。True：等待；False：抛出异常
maxusage	单个连接的最大允许复用次数(缺省值 0 或 None 代表不限制的复用).当达到最大数时，连接会自动重新连接(关闭和重新打开)
setsession	开始会话前执行的命令列表
ping	如果ping()方法可用，该值表示何时使用ping()方法检查连接(`0 = None = never)
closeable	如果设置为True，将允许手动close()连接，默认为False，忽略关闭连接的操作，只在线程终止时自动关闭

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
import pymysql
from DBUtils.PooledDB import PooledDB， SharedDBConnection

POOL = PooledDB(
    creator=pymysql，
    host=self.host，
    port=self.port，
    user=self.user，
    password=self.password，
    db=self.db，
    charset="utf8"，
    mincached=2，
    maxcached=20，
    maxconnections=0，
    blocking=True
)

def query():
    try:
        conn = POOL.connection()
        
        # 必须先调用begin来开启一个事务
        conn.begin()
        with conn.cursor() as cursor:
            cursor.execute(sql, values)
            res = cursor.fetchall()
        
        # 这里commit之后才会真正提交给数据库
        conn.commit()
        conn.close()
    except Exception as e:
    	conn.rollback()
        raise Exception("连接或执行失败: " + str(e))
    finally:
        cursor.close()
        conn.close()

NOTE: 对于不再使用的连接，调用close()方法回收到连接池。

NOTE: 需要在连接上调用begin()方法明确开启事务。这可以确保：a.只在事务完成时才重新打开连接；b.连接被同一个线程重用时回滚；c.连接不会被其他线程共享

4.3 遇到的问题

1. 正常情况下，mysql的设置的timeout是8个小时(28800秒)，也就是说，如果一个连接8个小时都没有操作，那么mysql会主动的断开连接，当这个连接再次尝试查询的时候就会报个”2013, 'Lost connection to MySQL server during query'错误”

解决办法:
https://www.yangyanxing.com/article/connect_short_problem.html

1. 解决的方法有两种，既然这里的超时是由于在规定时间内没有任何操作导致mysql主动的将链接关闭，pymysql的connection对象有一个ping()方法，可以检查连接是否有效，在每次执行查询操作之前先执行一下ping()方法,该方法默认的有个reconnect参数，默认是True，如果失去连接了会重连。

2. 还有一种方法是使用连接池，连接池中保持着指定数量的可用连接，每次重新获取一个有效的连接进行查询操作,pymysql本身不具有连接池功能，需要借住DBUtils

1. 很多使用DBUtils.PooledDB模块建立连接池，使用cursor()来建立多线程连接，在执行SQL查询的时候会报错：2014， “Commands out of sync; you can’t run this command now”

究其原因是：
1、查询结果未释放，然又执行查询；
2、两次查询之间没有存储结果应该重复使用connection()，而不是cursor()；

更多请参考官网：https://cito.github.io/DBUtils/UsersGuide.html。 

4.4 思维导图总结

模式一

参考

DBUtils-数据库连接池：https://www.jianshu.com/p/f94bc9ce0842

数据库连接池DBUtils使用 https://cloud.tencent.com/developer/article/1578187

Python DBUtils 连接池对象 (PooledDB) https://my.oschina.net/u/4357988/blog/3347902

《python DbUtils 使用教程》https://cloud.tencent.com/developer/article/1568031

《python数据库连接工具DBUtils》https://segmentfault.com/a/1190000017952033

https://www.pythonf.cn/read/56464

一个简单易懂的博客https://www.cnblogs.com/zhuminghui/p/10930846.html