1. 树叶云首页
  2. 技术资讯

太全了！用Python操作MySQL的使用教程集锦！

运维••技术资讯

一. python操作数据库介绍

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

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

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

不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和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.1 PyMySQL模块

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

2.1.1 安装PyMySQL

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

pipinstallPyMySQL

2.2 基本使用

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
importpymysql
# 创建连接
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 获取最新创建的数据自增ID

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
importpymysql
# 创建连接
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 查询操作

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
importpymysql
# 创建连接
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注入

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
importpymysql
# 创建连接
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()

这样，SQL操作就更安全了。如果需要更详细的文档参考PyMySQL文档吧。不过好像这些SQL数据库的实现还不太一样，PyMySQL的参数占位符使用%s这样的C格式化符，而Python自带的sqlite3模块的占位符好像是问号(?)。因此在使用其他数据库的时候还是仔细阅读文档吧。Welcome to PyMySQL’s documentation

三. 数据库连接池

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

3.1 DBUtils模块

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

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

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

3.2 模式一

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
fromDBUtils.PersistentDBimportPersistentDB
importpymysql
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',
)
deffunc():
conn=POOL.connection(shareable=False)
cursor=conn.cursor()
cursor.execute('select * from USER')
result=cursor.fetchall()
cursor.close()
conn.close()
returnresult
result=func()
print(result)

3.2 模式二

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
importtime
importpymysql
importthreading
fromDBUtils.PooledDBimportPooledDB, 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'
)
deffunc():
# 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报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()
returnresult
result=func()
print(result)

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

3.3 加锁

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

3.4 无锁(报错)

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

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

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

# cat sql_helper.py
importpymysql
importthreading
fromDBUtils.PooledDBimportPooledDB, 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'
)
defconnect():
# 创建连接
# 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)
returnconn,cursor
defclose(conn,cursor):
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
deffetch_one(sql,args):
conn,cursor=connect()
# 执行SQL，并返回收影响行数
effect_row=cursor.execute(sql,args)
result=cursor.fetchone()
close(conn,cursor)
returnresult
deffetch_all(sql,args):
conn, cursor=connect()
# 执行SQL，并返回收影响行数
cursor.execute(sql,args)
result=cursor.fetchall()
close(conn, cursor)
returnresult
definsert(sql,args):
"""
   创建数据
   :param sql: 含有占位符的SQL
   :return:
   """
conn, cursor=connect()
# 执行SQL，并返回收影响行数
effect_row=cursor.execute(sql,args)
conn.commit()
close(conn, cursor)
defdelete(sql,args):
"""
   创建数据
   :param sql: 含有占位符的SQL
   :return:
   """
conn, cursor=connect()
# 执行SQL，并返回收影响行数
effect_row=cursor.execute(sql,args)
conn.commit()
close(conn, cursor)
returneffect_row
defupdate(sql,args):
conn, cursor=connect()
# 执行SQL，并返回收影响行数
effect_row=cursor.execute(sql, args)
conn.commit()
close(conn, cursor)
returneffect_row

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

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/282693.html<

赞 (0)
运维的头像运维
00
上一篇2025-05-13 14:15
下一篇 2025-05-13 14:17

相关推荐

  • 个人主题怎么制作？技术资讯

    个人主题怎么制作？

    制作个人主题是一个将个人风格、兴趣或专业领域转化为视觉化或结构化内容的过程，无论是用于个人博客、作品集、社交媒体账号还是品牌形象，核心都是围绕“个人特色”展开，以下从定位、内容规划、视觉设计、技术实现四个维度,详细拆解制作个人主题的完整流程，明确主题定位：找到个人特色的核心主题定位是所有工作的起点，需要先回答……

    运维的头像运维
    2025-11-20
    0
  • 社群营销管理关键是什么？技术资讯

    社群营销管理关键是什么？

    社群营销的核心在于通过建立有温度、有价值、有归属感的社群，实现用户留存、转化和品牌传播，其管理需贯穿“目标定位-内容运营-用户互动-数据驱动-风险控制”全流程,以下从五个维度展开详细说明：明确社群定位与目标社群管理的首要任务是精准定位，需明确社群的核心价值（如行业交流、产品使用指导、兴趣分享等）、目标用户画像……

    运维的头像运维
    2025-11-20
    0
  • 香港公司网站备案需要什么材料？技术资讯

    香港公司网站备案需要什么材料？

    香港公司进行网站备案是一个涉及多部门协调、流程相对严谨的过程，尤其需兼顾中国内地与香港两地的监管要求，由于香港公司注册地与中国内地不同，其网站若主要服务内地用户或使用内地服务器，需根据服务器位置、网站内容性质等，选择对应的备案路径（如工信部ICP备案或公安备案），以下从备案主体资格、流程步骤、材料准备、注意事项……

    运维的头像运维
    2025-11-20
    0
  • 如何企业上云推广技术资讯

    如何企业上云推广

    企业上云已成为数字化转型的核心战略,但推广过程中需结合行业特性、企业痛点与市场需求，构建系统性、多维度的推广体系，以下从市场定位、策略设计、执行落地及效果优化四个维度，详细拆解企业上云推广的实践路径，精准定位：明确目标企业与核心价值企业上云并非“一刀切”的方案，需先锁定目标客户群体，提炼差异化价值主张，客户分层……

    运维的头像运维
    2025-11-20
    0
  • PS设计搜索框的实用技巧有哪些？技术资讯

    PS设计搜索框的实用技巧有哪些？

    在PS中设计一个美观且功能性的搜索框需要结合创意构思、视觉设计和用户体验考量，以下从设计思路、制作步骤、细节优化及交互预览等方面详细说明，帮助打造符合需求的搜索框，设计前的规划明确使用场景：根据网站或APP的整体风格确定搜索框的调性，例如极简风适合细线条和纯色，科技感适合渐变和发光效果，电商类则可能需要突出搜索……

    运维的头像运维
    2025-11-20
    0

发表回复

您的邮箱地址不会被公开。必填项已用 * 标注