如何在Python中操作MySQL？

如何在Python中操作MySQL？

一. Python 操作数据库介绍

Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。Python 数据库接口支持非常多的数据库，你可以选择适合你项目的数据库：

GadFlymSQLMySQL***Microsoft *** 2000InformixInterbase***Sybase ...

你可以访问Python数据库接口及API查看详细的支持数据库列表。

不同的数据库你需要下载不同的DB API模块，例如你需要访问***数据库和Mysql数据，你需要下载***和MySQL数据库模块。

DB-API 是一个规范. 它定义了一系列必需的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。

Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

Python DB-API使用流程：

引入 API 模块。

获取与数据库的连接。

执行SQL语句和存储过程

关闭数据库连接。

二. Python操作MySQL模块

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

DB模块(原生SQL)

PyMySQL(支持python2.x/3.x)MySQLdb(目前仅支持python2.x)

ORM框架

SQLAchemy

2.1PyMySQL模块

本文主要介绍PyMySQL模块,MySQLdb使用方式类似

2.1.1 安装PyMySQL

PyMySQL是一个Python编写的MySQL驱动程序，让我们可以用Python语言操作MySQL数据库。

pip install PyMySQL

2.2 基本使用

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

2.4 查询操作

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "shuke"# Date: 2018/5/13import 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')  # 相对绝对位置移动

2.5 防止SQL注入

三. 数据库连接池

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

3.1 DBUtils模块

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

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

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

3.2 模式一

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "shuke"# Date: 2018/5/13from DBUtils.PersistentDB import PersistentDBimport pymysqlPOOL = 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 resultresult = func()print(result)

3.3 模式二

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "shuke"# Date: 2018/5/13import timeimport pymysqlimport threadingfrom DBUtils.PooledDB import PooledDB, SharedDBConnectionPOOL = 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 resultresult = func()print(result)

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

3.4 加锁

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "shuke"# Date: 2018/5/13import pymysqlimport threadingfrom threading import RLockLOCK = 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 无锁(报错)

#! /usr/bin/env python# -*- coding: utf-8 -*-# __author__ = "shuke"# Date: 2018/5/13import pymysqlimport threadingCONN = 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()

此时可以在数据库中查看连接情况: show status like 'Threads%';

四. 数据库连接池结合pymsql使用

PS: 可以利用静态方法封装到一个类中,方便使用。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。