[425]pymysql的基本使用

pymysql的基本使用

pymysql是从Python连接到MySQL数据库服务器的接口，其官方文档为：https://pymysql.readthedocs.io/en/latest/

以下是pymysql经常被用到的一些方法

方法	描述
pymysql.connect()	connect()方法返回要给数据库连接对象，参数可以传入很多，常用的参数有：host、port、user、password、database、charset，connect()创建了连接对象，执行完sql操作后，必须使用close()关闭
close()	数据库连接对象的一个方法，用于关闭数据库连接
cursor()	数据库连接对象的一个方法，用于获取游标对象，游标对象的execute(sql语句)方法可以执行sql语句
execute(sql)	游标对象的一个方法，可以执行sql语句
commit()	提交到数据库，数据库连接对象的一个方法，如果对表数据有修改的时候，就需要将修改提交到数据库，否则修改没有生效
rollback()	回滚已提交的内容，，数据库连接对象的一个方法，依据事务的原子性 ，提交要么全部生效，要么全不生效，如果遇到异常，需要对已提交的内容进行回滚

python操作MySQL模块

Python操作MySQL主要使用两种方式：

1、DB模块(原生SQL)

• PyMySQL
• MySQLdb

2、ORM框架

• SQLAchemy

安装

pip install pymysql 

基本使用

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.1 插入单条数据
sql = 'insert into student (name,age) values (%s,%s);'
cursor.execute(sql, ['wuli', 10])

# 2.2 执行SQL,返回受影响的行数,一次插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [("jack"), ("boom"), ("lucy")])  

# 3. 查询所有数据,返回数据为元组格式
result = cursor.fetchall()

# 4. 更新数据
sql = "update student set age=%s where name=%s;"
# 拼接并执行SQL语句
cursor.execute(sql, [28,"ni"])

# 5. 删除数据
sql = "delete from student where name=%s;"
name = "wuli"
# 拼接并执行SQL语句
cursor.execute(sql, [name])


# 增/删/改均需要进行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'}]
"""

获取最新创建的数据自增ID

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
"""

查询操作

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)

在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode=‘relative’) # 相对当前位置移动

cursor.scroll(2,mode=‘absolute’) # 相对绝对位置移动

防止SQL注入

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()

数据库连接池

上文中的方式存在一个问题,单线程情况下可以满足,程序需要频繁的创建释放连接来完成对数据库的操作,那么,我们的程序/脚本在多线程情况下会引发什么问题呢?此时,我们就需要使用数据库连接池来解决这个问题!

DBUtils模块

DBUtils是Python的一个用于实现数据库连接池的模块。

此连接池有两种连接模式：

• 为每个线程创建一个连接，线程即使调用了close方法，也不会关闭，只是把连接重新放到连接池，供自己线程再次使用。当线程终止时，连接才会自动关闭
• 创建一批连接到连接池，供所有线程共享使用(推荐使用)

模式一

# -*- coding: utf-8 -*-
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)

模式二

# -*- coding: utf-8 -*-
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, '\r\n')

    cursor = conn.cursor()
    cursor.execute('select * from USER')
    result = cursor.fetchall()
    conn.close()
    return result


result = func()
print(result)

⚠️ 由于pymysql、MySQLdb等threadsafety值为1，所以该模式连接池中的线程会被所有线程共享,因此是线程安全的。如果没有连接池，使用pymysql来连接数据库时，单线程应用完全没有问题，但如果涉及到多线程应用那么就需要加锁，一旦加锁那么连接势必就会排队等待，当请求比较多时，性能就会降低了。

参考：https://blog.csdn.net/xx16755498979/article/details/131863947
http://blog.csdn.net/wenxiaoba/article/details/126903858
https://blog.csdn.net/qq_39208536/article/details/125348755

---

pymysql的SSCursor踩的生成器的坑

在Python3连接mysql要用到pymysql模块。一般用的是普通游标，执行select等语句fetchall时是直接存入内存，有内存不够的风险。这时可以用SSCursor，貌似中文叫做流式游标？连接时需要这样：

conn = pymysql.connect(dbhost, dbuser, dbpass, dbname, charset='utf8')
cur = conn.cursor(pymysql.cursors.SSCursor)
# 也可以cur = pymysql.cursors.SSCursor(conn)

总之，在执行select等sql语句后

cur.execute(sql)

可以通过for循环遍历cur，此时cur相当于生成器，不会直接存储所有数据，而是在循环时一条一条生成数据。

    for i in cur:
        print(i)

或者存储需要的数据

    l = (i[0] for i in cur)
    # 数据量不大时也可以用列表生成式l = [i[0] for i in cur]

即使只select一种数据，出现的结果也是元组，类似于(1,)，而一般需要的数据只是元组里的这个1，若使用

    l = (i for i in cur)

由于没有解包元组，得不到想要的结果。

生成器的介绍还是看官方文档比较好。
类似于

def func():
    for i in range(3):
        print(i)

func() # 0, 1, 2

要改写成生成器，只需要把print()变成yield，这个函数本身就可以循环了。

def func():
    for i in range(3):
        yield i

for i in func():
    print(i) # 0, 1, 2

生成器只能循环一次。之后再度对它循环不会生成值。

    cur.execute(sql)
    l = (i[0] for i in cur)
    r = (i[1] for i in cur)

尽管r不生成任何值，但上面这段代码不会报错。
如果是这样

    cur.execute(sql)
    l = [i[0] for i in cur]
    r = [i[1] for i in cur]

调试时会发现r是一个空列表。
实际上要达成目的应该老老实实的这样写

    cur.execute(sql)
    l, r = [], []
    for i in cur:
        l.append(i[0])
        r.append(i[1])

由于cur本身就是一个生成器，实在不想用上面的写法的话，就在for循环里直接处理i[1]这样的数据吧。
相对于list而言，生成器没有长度的概念，无法使用len()判断长度，因此也不能判空。

def func():
    for i in range(0):
        yield i

if func():
    print(1) # 1

l = (i for i in range(0))
if l:
    print(1) # 1

因此，在执行一条select语句后，想知道实际有没有结果，不能用以下方式判断

    cur.execute(sql)
    if cur:
        for i in cur:
            pass

为了写入csv等，需要完整对齐的数据的话，可以这样

    cur.execute(sql)
    for i in cur:
        if i:
            l.append(i)
            break
    else:
        l.append('')

很奇怪的for...else...语法，感觉自己在瞎写了，应该有其他方式来实现，而不是非要用这种逻辑。不过，能抓到老鼠就是好猫。

要插入几十万以上数据想节省时间的话，可以这样

    # datas是一个list，里面又包含几十万个list
    bigN = 50000 # 一次插5万条，设置的太高mysql也不让插那么多
    for i in range(len(datas)//bigN):
        l, r = i * bigN, (i + 1) * bigN
        sql = "insert ... values %s"
        sql = sql % ','.join(datas[l:r])
        cur.execute(sql)
        if r + bigN > len(datas):
            sql = "insert ... values %s"
            sql = sql % ','.join(datas[r:]) # 边界条件，保证尾部元素都能插入
            cur.execute(sql)

不过以上代码没有检查datas长度小于bigN的情况，这个时候由于len(datas)//bigN为0，是不会进行循环的，这点也要注意。

参考：https://www.jianshu.com/p/56aae1da21c8