Python操作MySQL数据库的关键步骤及方法
一.python操作数据库介绍Python标准数据库接口为PythonDB-API，PythonDB-API为开发人员提供了数据库应用编程接口。Python数据库接口支持非常多的数据库，你可以选择适合你项目的数据库：GadFlymSQLMySQLPostgreSQLMicrosoftSQLServer2000InformixInterbaseOracleSybase…你可以访问Python数据库接口及API查看详细的支持数据库列表。不同的数据库你需要下载不同的DBAPI模块，例如你需要访问Oracle数据
一. 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数据库。
pip install PyMySQL
2.2 基本使用
#! /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 获取最新创建的数据自增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 查询操作
#! /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)⚠️ 在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
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()这样，SQL操作就更安全了。如果需要更详细的文档参考PyMySQL文档吧。不过好像这些SQL数据库的实现还不太一样，PyMySQL的参数占位符使用%s这样的C格式化符，而Python自带的sqlite3模块的占位符好像是问号(?)。因此在使用其他数据库的时候还是仔细阅读文档吧。
三. 数据库连接池
上文中的方式存在一个问题,单线程情况下可以满足,程序需要频繁的创建释放连接来完成对数据库的操作,那么,我们的程序/脚本在多线程情况下会引发什么问题呢?此时,我们就需要使用数据库连接池来解决这个问题!
3.1 DBUtils模块
DBUtils是Python的一个用于实现数据库连接池的模块。
此连接池有两种连接模式：
为每个线程创建一个连接，线程即使调用了close方法，也不会关闭，只是把连接重新放到连接池，供自己线程再次使用。当线程终止时，连接才会自动关闭
创建一批连接到连接池，供所有线程共享使用(推荐使用)
3.2 模式一
#! /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.2 模式二
#! /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)⚠️ 由于pymysql、MySQLdb等threadsafety值为1，所以该模式连接池中的线程会被所有线程共享,因此是线程安全的。如果没有连接池，使用pymysql来连接数据库时，单线程应用完全没有问题，但如果涉及到多线程应用那么就需要加锁，一旦加锁那么连接势必就会排队等待，当请求比较多时，性能就会降低了。
3.3 加锁
#! /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.4 无锁(报错)
#! /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()此时可以在数据库中查看连接情况: show status like 'Threads%';
四. 数据库连接池结合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: 可以利用静态方法封装到一个类中,方便使用
Postman是一套面向API全生命周期的开发与测试平台，可在Mac上发送和调试多种协议请求，管理集合、环境及认证信息，并完成脚本测试、模拟服务、监控、文档发布和团队协作。它适合开发、测试及运维人员使用，提供个人免费方案。
Docker Desktop 是 Docker 官方为 Mac 提供的容器开发环境，集成 Docker Engine、命令行工具、Compose、Build、Kubernetes 与图形化管理界面。
Android Studio 是 Google 面向安卓应用开发提供的官方集成开发环境，基于 IntelliJ IDEA 构建。它整合智能代码编辑、Gradle 构建、界面预览、设备模拟、性能分析、调试测试及 APK 分析等工具。
Xcode 是苹果面向 Mac 提供的官方集成开发环境，将代码编辑、项目构建、SwiftUI 实时预览、设备模拟、自动化测试、断点调试、性能分析和应用分发整合在同一套工作流中，适合开发与维护 iPhone、iPad、Mac、Apple Wa
JetBrains WebStorm 是面向 JavaScript、TypeScript 和现代 Web 技术的专业集成开发环境，可智能理解项目结构，提供代码补全、重构、错误检查、运行调试、测试及 Git 工具。
PhpStorm 是 JetBrains 推出的专业 PHP 集成开发环境，可在 Mac 上完成代码编写、智能检查、重构、调试、测试和数据库管理。它支持主流 PHP 框架、Composer、Git、Docker 与远程解释器。
JetBrains PyCharm 是面向 Python 开发者的集成开发环境，提供智能补全、代码检查、重构、调试、测试、版本控制及虚拟环境管理功能。Mac 版兼容 Intel 与 Apple 芯片，核心功能可免费使用。
IntelliJ IDEA 是 JetBrains 推出的专业集成开发环境，重点服务于 Java、Kotlin 与 JVM 项目开发。软件集成代码补全、静态检查、重构、调试、测试、版本控制及构建工具，并可借助插件扩展语言和框架支持。
Visual Studio Code 是微软提供的免费代码编辑器，支持 macOS，并为 Intel 与 Apple 芯片 Mac 提供适配版本。软件集代码编辑、调试、Git 源代码管理、集成终端和扩展市场于一体。
REAPER是Cockos开发的数字音频工作站，提供多轨音频与MIDI录制、剪辑、处理、混音和母带制作工具。Mac版兼容Intel与Apple芯片，支持AU、VST、VST3、CLAP等插件格式，并提供高度可定制的工作流程。
Adobe Audition 是面向播客、影视后期、音乐制作及内容创作者的专业音频工作站，提供波形精修、多轨录制与混音、频谱编辑、降噪修复、响度匹配和效果处理工具，并可与 Adobe Premiere 协同完成视频声音制作。
Pro Tools 是 Avid 推出的专业数字音频工作站，覆盖多轨录音、无损剪辑、MIDI 编曲、插件处理、自动化混音及声音与画面同步等工作。它拥有 Intro、Artist、Studio 和 Ultimate 等方案。
PixPix 是面向品牌、卖家和内容团队的在线 AI 图片视频创作平台，提供图视频 Agent、无限画布、图片生成、视频生成、自然语言编辑及商品套图、产品精修和带货视频等电商工具。
Postman 是面向 API 全生命周期的开发与协作平台。Windows 桌面版可用于构建和发送 HTTP、GraphQL、gRPC、WebSocket、MQTT 等请求，并提供环境变量、集合、脚本测试、模拟服务、监控、文档及团队工作区等
Docker Desktop 为 Windows 开发者提供集成式容器环境，内置 Docker Engine、命令行工具、Compose、Build 与 Kubernetes 等组件，可在本机完成镜像构建、容器运行、日志查看和资源管理。
Android Studio 是 Google 面向 Android 应用开发提供的官方集成开发环境，以 IntelliJ IDEA 为基础，集成智能代码编辑、Gradle 构建、设备模拟、界面预览、调试测试及性能分析工具，
WebStorm 是 JetBrains 面向 JavaScript、TypeScript 和现代 Web 技术打造的集成开发环境。它提供项目级代码分析、智能补全、安全重构、导航搜索，并集成调试、测试、终端、Git 与常用前端工具，
PhpStorm 是 JetBrains 面向 PHP 与现代 Web 开发打造的专业集成开发环境，提供代码补全、静态检查、重构、调试、测试、数据库、版本控制及前端语言支持。Windows 用户可直接下载安装程序，
PyCharm 是 JetBrains 推出的 Python 集成开发环境，提供智能代码补全、错误检查、重构、调试、测试、Git、终端及 Jupyter Notebook 支持。统一版安装后可体验 30 天 Pro 功能，
JetBrains IntelliJ IDEA 是以 Java、Kotlin 和 JVM 项目为核心的专业集成开发环境，提供智能补全、代码检查、安全重构、调试、测试、版本控制及构建工具集成。统一发行版的核心功能可免费用于商业和非商业项目，
Visual Studio Code 是微软推出的免费代码编辑器，Windows 版集成智能补全、代码导航、调试、终端和版本控制，并可通过扩展添加语言、格式化及远程开发能力。它启动较快、配置灵活，
REAPER 是 Cockos 推出的数字音频工作站，适用于录音、MIDI 编曲、音频编辑、混音、母带制作及声音设计。软件安装包小巧，支持多轨录制、灵活路由、自动化、第三方插件和脚本扩展，并提供完整功能的 60 天免费评估期。
Adobe Audition 是面向播客、影视后期、广播与音乐制作的专业音频工作站，提供波形编辑、多轨混音、频谱分析、录音、降噪修复和效果处理等功能。Windows 版通过 Adobe Creative Cloud 安装和管理，
Pro Tools 是 Avid 推出的专业数字音频工作站，提供多轨录音、音频剪辑、MIDI编曲、插件处理、自动化混音及影视声音制作能力。它适合音乐人、录音师、制作人和音频后期团队使用，并提供免费入门版、订阅方案及永久授权选择。