Python 之数据库操作（Python Database Operations）

💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。

推荐:Linux运维老纪的首页,持续学习,不断总结,共同进步,活到老学到老
导航剑指大厂系列:全面总结运维核心技术:系统基础、数据库、网路技术、系统安全、自动化运维、容器技术、监控工具、脚本编程、云服务等。
常用运维工具系列:常用的运维开发工具, zabbix、nagios、docker、k8s、puppet、ansible等
数据库系列:详细总结了常用数据库 mysql、Redis、MongoDB、oracle 技术点,以及工作中遇到的 mysql 问题等
懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

Python 之数据库操作

技能目标：

- 掌握使用 pymysql 模块操作数据库的方法

- 掌握使用 sqlalchemy 模块操作数据库的方法

- 掌握使用 Python 脚本备份还原数据库的方法

7.1 Python 操作数据库简介

不过使用文件方式时不容易管理，同时还容易丢失，会带来许多问题。目前主流的方法都是

采用数据库软件，通过数据库软件来组织和存放数据，不管是读还是存都非常方便，速度也

有保障。本篇介绍 python 操作 MySQL 数据库软件的方法。

数据库（Database）是按照数据结构来组织、存储和管理建立在计算机存储设备上的

数据仓库。MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发。MySQL 是

一个可移植的数据库，几乎能在当前所有的操作系统上运行，如 Unix/Linux、Windows、

Mac 和 Solaris。MySQL 采用标准的 SQL 语言（结构化查询语言），SQL 是一种数据库查

询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

虽然 Python 可以支持数据的本地存储，但前提是先从数据库获取才能保存下来，同

时还无法存储过多的数据，因为数据释放和使用会占用较大的内存，数据库负责把磁盘数据

组织好放回内存, Python 负责在内存中操作数据。

7.2 PyMySQL

PyMySQL 是 Python 中操作 MySQL 的模块，其使用方法和 MySQLdb 几乎相同。但

目前 PyMySQL 支持 Python3.x，而 MySQLdb 不支持 3.x 版本。

7.2.1 安装 pymysql 模块

通过 pip 安装 PyMySQL 的命令：

[root@localhost ~] # pip3 install pymysql

7.2.2 pymysql 使用

官方文档：https://pypi.org/project/PyMySQL/#documentation

在前面的课程中，已经详细的介绍过了 MySQL 的安装及配置过程，在本章将不做过多

阐述。

假设本机已安装 MySQL，现在登录 MySQL 实例，创建一个 test 库，并在其中创建一

张 users 表，root 帐户密码为“123456”，代码如下。

# mysql -uroot –p

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test;

Database changed

mysql> CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`email` varchar(255) COLLATE utf8_bin NOT NULL,

`password` varchar(255) COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

AUTO_INCREMENT=1 ;

Query OK, 0 rows affected (0.11 sec)

示例 1：使用 PyMySQL 模块连接 MySQL 数据库向 user 表插入一条记录并查询这条

记录：

[root@localhost ~] # cat pymysql-1.py

#!/usr/local/bin/python3.6

import pymysql

connection = pymysql.connect(host='localhost',

user='root',

password='123456',

db='test',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

try:

with connection.cursor() as cursor:

# Create a new record

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

# connection is not autocommit by default. So you must commit to save

# your changes.

connection.commit()

with connection.cursor() as cursor:

# Read a single record

sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"

cursor.execute(sql, ('webmaster@python.org',))

result = cursor.fetchone()

print(result)

finally:

connection.close()

[root@localhost ~] # python3 pymysql-1.py

{'id': 1, 'password': 'very-secret'}

执行成功，进 MySQL 看下是否插入成功。

[root@localhost ~]# mysql -uroot -p

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from users;

+----+----------------------+-------------+

| id | email

| password

+----+----------------------+-------------+

| 1 | webmaster@python.org | very-secret |

+----+----------------------+-------------+

1 row in set (0.00 sec)

从代码的执行结果中，可以看到数据库表中已经成功插入了一条记录。

代码说明：先创建一个连接对象，下面是一个异常处理。如果连接成功，执行 INSERT

语句并提交，再执行 SELECT 语句，获取查询的第一条记录并打印；finally 是最终执行关

闭连接。在上面示例中用到了 pymysql.connect()函数，还有游标对象 cursor，下面具体看

下它们的用法。

语法：

pymysql.connect()

表 7-1 是 pymysql.connect()函数的常用参数。

表 7-1 pymysql.connect()函数的常用参数

参数	描述
host	数据库主机地址
user	数据库账户
passwd	账户密码
db	使用的数据库
port	数据库主机端口，默认 3306
connect_timeout	连接超时时间，默认 10，单位秒
charset	使用的字符集
cursorclass	自定义游标使用的类。上面示例用的是字典类，以字典形式返回结果，默认是元组形式

表 7-2 是 pymysql 连接对象常用方法。

表 7-2 pymysql 连接对象常用方法

方法	描述
commit()	提交事务。对支持事务的数据库和表，如果提交修改操作，不适用这个方法，则不会写到数据库中
rollback()	事务回滚。对支持事务的数据库和表，如果执行此方法，则回滚当前事务，放弃之前的操作。
cursor([cursorclass])	创建一个游标对象。所有的 sql 语句的执行都要在游标对象下进行

表 7-3 是游标对象常用方法。

方法	描述
close()	关闭游标
excutemany(sql)	执行多条 sql 语句
fetchone()	从执行结果中取第一条记录
fetchmany(n)	从执行结果中取 n 条记录
execute(sql)	执行 sql 语句
fetchall()	从执行结果中取所有记录

7.2.3 数据库增删改查

了解了 pymysql 基本用法，现在使用上面的这些方法实现数据库的增、删、改、查功

能。

1. 增加记录

示例 2：在 user 表里再添加一条记录。

>>> import pymysql

>>> connection = pymysql.connect(host='localhost',

...

user='root',

...

password='123456',

...

db='test',

...

charset='utf8mb4',

...

cursorclass=pymysql.cursors.DictCursor)

>>> cursor = connection.cursor()

>>> sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

>>> cursor.execute(sql, ('user1@python.org', '123456'))

# 影响的行数

>>> connection.commit()

# 提交事务，写到数据库

>>> sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"

>>> cursor.execute(sql, ('user1@python.org',))

>>> result = cursor.fetchone()

>>> print(result)

{'id': 2, 'password': '123456'}

下面是一次插入多条记录的代码。

>>> sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

>>>

args

[('user2@python.org','123456'),

('user3@python.org','123456'),('user4@python.org','123456')]

>>> cursor.executemany(sql, args)

>>> connection.commit()

>>> sql = "SELECT `id`, `email`, `password` FROM `users`"

>>> cursor.execute(sql)

>>> result = cursor.fetchall()

>>> print(result)

[{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}, {'id': 2, 'email':

'user1@python.org', 'password': '123456'}, {'id': 3, 'email': 'user2@python.org', 'password':

'123456'}, {'id': 4, 'email': 'user3@python.org', 'password': '123456'}, {'id': 5, 'email':

'user4@python.org', 'password': '123456'}]

args 变量是一个包含多元组的列表，每个元组对应着每条记录。当插入多条记录时，

使用此方法，可有效提高插入效率。

2. 查询记录

示例 3：查询 users 表记录

>>> sql = "SELECT `id`, `email`, `password` FROM `users`"

>>> cursor.execute(sql)

>>> cursor.fetchone()

# 获取第一条记录

{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}

>>> cursor.execute(sql)

>>> cursor.fetchmany(2)

# 获取前两条记录

[{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}, {'id': 2, 'email':

'user1@python.org', 'password': '123456'}]

>>> cursor.fetchall()

# 获取所有记录

[{'id': 3, 'email': 'user2@python.org', 'password': '123456'}, {'id': 4, 'email': 'user3@python.org',

'password': '123456'}, {'id': 5, 'email': 'user4@python.org', 'password': '123456'}]

3. 修改记录

示例 4：将 user1@python.org 密码修改为 456789

>>> sql = "UPDATE users SET `password`='456789' WHERE `email`='user1@python.org'"

>>> cursor.execute(sql)

>>> connection.commit()

>>> sql = "SELECT `id`, `email`, `password` FROM `users`"

>>> cursor.execute(sql)

>>> cursor.fetchmany(2)[{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}, 'id': 2, 'email':

'user1@python.org', 'password': '456789'}}]

4. 删除记录

示例 5：删除 email 是 webmaster@python.org 的记录

>>> sql = 'DELETE FROM `users` WHERE email="webmaster@python.org"'

>>> cursor.execute(sql)

>>> connection.commit()

>>> sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"

>>> cursor.execute(sql, ('webmaster@python.org',))

>>> sql = "SELECT `id`, `email`, `password` FROM `users`"

>>> cursor.execute(sql)

>>> cursor.fetchall()

[{'id': 2, 'email': 'user1@python.org', 'password': '123456'}, {'id': 3, 'email': 'user2', 'password':

'123456'}, {'id': 4, 'email': 'user3', 'password': '123456'}, {'id': 5, 'email': 'user4', 'password':

'123456'}]

7.2.4 遍历查询结果

示例 6：查询 user 表所有 email 与密码

[root@localhost ~]# cat pymysql-2.py

#!/usr/local/bin/python3.6

import pymysql

connection = pymysql.connect(host='localhost',

user='root',

password='123456',

db='test',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

try:

with connection.cursor() as cursor:

sql = "SELECT `id`, `email`, `password` FROM `users`"

cursor.execute(sql)

result = cursor.fetchall()

for dict in result:

print("email:%s, password:%s" %(dict['email'],dict['password']))

finally:

connection.close()

[root@localhost ~]# python3 pymysql-2.py

email:user1@python.org, password:456789

email:user2@python.org, password:123456

email:user3@python.org, password:123456

email:user4@python.org, password:123456

pymysql 模块主要是提供连接数据库和获取 SQL 语句执行结果的方法。

7.3 SQLAlchemy

官方文档：http://docs.sqlalchemy.org/en/latest/contents.html

在 Python 中，最有名的 ORM 框架实现是 SQLAlchemy 模块。

ORM（Object-Relational Mapping，对象关系映射）：是一种程序技术，用于将关系

数据库的表结构映射到对象上。ORM 提供了概念性、易于理解的模型化的方法。使用 ORM

技术，可以避免写复杂的 SQL 语句。

7.3.1 安装 SQLAlchemy 模块

通过 pip 命令安装 SQLAlchemy 模块。

# pip3 install sqlalchemy

7.3.2 基本使用

SQLAlchemy 模块的用法如下。

1. 创建 MySQL 连接

导入 SQLAlchemy 模块指定类，并创建 MySQL 连接。

>>> from sqlalchemy import create_engine

连接 MySQL 数据库，使用 create_engine()方法如下：

>>> engine =create_engine('mysql+pymysql://root:123456@localhost:3306/test')

参数格式：

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

此时，engine 变量是一个实例，它表示数据库的核心接口。

创建一个数据库连接时，create_engine()方法尚未打开连接。当第一次调用时，才会由

Engine 维护的连接池建立一个连接，并保留它，直到提交所有更改或关闭会话对象。

2. 声明映射

当使用 ORM 时，首选描述我们要处理的数据库表，通过定义自己的类到这些表。

映射表是通过一个基类来定义的，所以要先导入它。

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

现在有了一个基类，我们可以根据它定义任意数量的映射类。例如定义数据库表名为

users，该类中定义了表的详细信息，主要是表名，以及列的名称和数据类型。

>>> from sqlalchemy import Column, Integer, String

>>> class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

email = Column(String(255))

password = Column(String(255))

一个映射类至少需要一个__tablename__属性，并且至少有一个 Column 是主键。

如果多个表，就继续定义其他表的类，跟上面代码一样。

3. 创建会话

现在开始与数据库交互，先创建一个会话。

>>> from sqlalchemy.orm import sessionmaker

>>> Session = sessionmaker(bind=engine)

>>> Session = sessionmaker()

>>> Session.configure(bind=engine)

实例化一个 Session。

>>> session = Session()

4. 数据库基本操作

添加对象，有了 ORM，向数据库表中添加一行记录，可以视为添加一个 User 对象：

>>> add_user = User(email='user5@python.org', password='123456')

>>> session.add(add_user)

>>> session.commit()

创建 Query 查询，filter 是 where 条件，最后调用返回唯一行。如果调用 all()则返回所

有行，并且以列表类型返回：

>>> user = session.query(User).filter(User.email=='user5@python.org').one()

>>> user.email

'user5@python.org'

>>> user.password

'123456'

可以给 User 一次添加多个对象，使用 add_all()：

>>> session.add_all([

User(email='user6@python.org', password='123456'),

User(email='user7@python.org', password='123456'),

User(email='user8@python.org', password='123456')])

有三个新 User 对象正在等待处理。

>>> session.new

IdentitySet([<__main__.User

object

0x7fa80bd71ba8>,

<__main__.User

object

0x7fa80bd71908>, <__main__.User object at 0x7fa80bd716d8>])

将更改刷新到数据库，并提交事务。

>>> session.commit()

修改 user5@python.org 密码：

>>>session.query(User).filter(User.email=='user5@python.org').update({"password":456789})

>>> user = session.query(User).filter(User.email=='user5@python.org').one()

>>> user.password

456789

>>> session.commit()

现在，对实现步骤进行总结。

[root@localhost ~]# cat pymysql-3.py

#!/usr/local/bin/python3.6

from sqlalchemy import create_engine

# 导入初始化数据库连接类

from sqlalchemy.ext.declarative import declarative_base # 导入声明映射用的基类from sqlalchemy import Column, Integer, String # 导入字段类型

from sqlalchemy.orm import sessionmaker # 导入会话的类

# 创建对象的基类

Base = declarative_base()

# 定义 User 对象

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

email = Column(String(255))

password = Column(String(255))

# 初始化数据库连接

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')

# 创建 Session 类型

Session = sessionmaker(bind=engine)

Session = sessionmaker()

Session.configure(bind=engine)

session = Session()

# 查询所有记录

user = session.query(User).all()

for i in user:

print("email: %s, password: %s" %(i.email, i.password))

[root@localhost ~]# python3 pymysql-3.py

email: user1@python.org, password: 456789

email: user2@python.org, password: 123456

email: user3@python.org, password: 123456

email: user4@python.org, password: 123456

email: user5@python.org, password: 456789

email: user6@python.org, password: 123456

email: user7@python.org, password: 123456

email: user8@python.org, password: 123456

当查询一个 User 对象时，该对象返回一个包含若干个对象的列表。

ORM 框架的作用就是把数据库表的一行记录与一个对象相互做自动转换。

7.4 Python 脚本备份还原 MySQL 数据库

如果要对当前服务器上的数据库进行备份，从 MySQL 导出数据，需要用到 mysqldump

工具，备份全部数据库数据到一个 sql 文件，包括 mysql、sys、information_schema、

performance_schema 等内置数据库。无论是否使用 Python 脚本执行数据库备份，都需要

使用 mysqldump 工具。

本例服务器的 MySQL 连接信息如下：

host: 192.168.9.167

port: 3306

user: root

password: 123456

7.4.1 使用 mysqldump 命令一键备份 mysql 数据库

执行命令如下：

[root@localhost ~]# mysqldump -h 192.168.9.167 -uroot -p 123456 -A > mysql_backup.sql

不用编写 Python 脚本即可一条命令备份 mysql 数据库，那么编写 Python 脚本备份数

据库不是多此一举么？如果是备份全部数据，直接使用命令确实很方便，但是在企业生产环

境中，备份的方式要灵活很多。例如只需要把每个数据库单独备份成一个 sql 文件，并都保

存在同一个目录下面。就需要手动执行很多条命令，此时使用 Python 脚本实现就非常简单

方便了。

7.4.2 编写 Python 脚本文件 mysql_backup.py

使用 Python 编写数据库备份脚本 mysql_backup.py 文件，代码如下：

import logging

import os

import subprocess

import pymysql

logging.basicConfig(format='%(asctime)s

%(pathname)s[line:%(lineno)d]

- %(levelname)s: %(message)s',level=logging.INFO)

# 设置日志输出格式

MYSQL_USERNAME = 'root'

# MySQL 数据库用户名

MYSQL_PASSWORD = '123456'

# 数据库密码

MYSQL_HOST = '192.168.9.167'

# 数据库主机地址

MYSQL_PORT = 3306

# 数据库端口

BACKUP_PATH = 'backup'

# 备份文件存放路径

DISABLED_DATABASES = {'information_schema', 'mysql', 'performance_schema', 'sys'}

# 排

除不需要备份操作的数据库名称集合

def mkdir_if_not_exists(path):

#判断给定目录是否存在，不存在则创建它

if not os.path.exists(path):

os.mkdir(path)

def create_mysql_conn(db='mysql'):

#创建并返回一个 mysql 数据库连接

conn

pymysql.connect(host=MYSQL_HOST,

port=MYSQL_PORT,

user=MYSQL_USERNAME, password=MYSQL_PASSWORD, db='mysql')

return conn

def read_all_databases():

#从数据库中读取全部数据库名称

logging.info('读取全部数据库名称...')

conn = create_mysql_conn()

cursor = conn.cursor()

cursor.execute('show databases')

# 查询服务器上有哪些数据库

res = cursor.fetchall()

databases = {item[0] for item in res}

databases = list(databases - DISABLED_DATABASES)

# 排除不备份的数据库

cursor.close()

conn.close()

logging.info('读取完毕，数据库列表如下：{}'.format(databases))

return databases

def backup_database(database):

#备份指定数据库的数据和表结构

logging.info('开始备份数据库 {}...'.format(database))

command = 'mysqldump -h192.168.9.167 -uroot -p123456 --add-drop-database --databases

{database} > {backup_path}/{database}.sql'.format(

database=database,

backup_path=BACKUP_PATH)

exit_code = subprocess.call(command, shell=True)

if exit_code != 0:

# 判断命令是否正常执行，异常则直接抛出

raise Exception('在备份数据库的过程中出错，请检查！')

logging.info('数据库 {} 备份完毕！'.format(database))

def backup():

#读取全部数据库名称，并对这些数据库的数据和结构进行备份

mkdir_if_not_exists(BACKUP_PATH)

# 检查备份路径是否存在，不存在则进行创建

databases = read_all_databases()

# 读取全部待备份数据库名称

for database in databases:

# 逐个对数据库进行备份

backup_database(database)

if __name__ == '__main__':

backup()

7.4.3 执行 Python 脚本并查看备份后的 sql 文件

编写完成后执行脚本完成备份，执行命令及结果如下：

[root@localhost ~]# python3 mysql_backup.py

2020-07-10 09:52:46,425 - mysql_backup.py[line:55] - INFO: 读取全部数据库名称...

2020-07-10 09:52:46,429 - mysql_backup.py[line:66] - INFO: 读取完毕，数据库列表如下：['test']

2020-07-10 09:52:46,430 - mysql_backup.py[line:77] - INFO: 开始备份数据库 test...

mysqldump: [Warning] Using a password on the command line interface can be insecure.

2020-07-10 09:52:46,765 - mysql_backup.py[line:86] - INFO: 数据库 test 备份完毕！

[root@localhost ~]# ls backup

test.sql

注意：warning 警告的意思是在命令行界面上使用密码可能不安全。所以在实际生产环

境中可以利用常量来代替明文输入密码的这一步，或者在配置文件中填写用户名密码等方式

保障安全。

7.4.4 编写 Python 脚本文件 mysql_restore.py

备份脚本已经编写执行完成，此时还需再编写一个还原数据库使用的脚本

mysql_restore.py，用于还原恢复 mysql 数据库。代码如下：

import logging

import os

import subprocess

import pymysql

logging.basicConfig(format='%(asctime)s

%(pathname)s[line:%(lineno)d]

- %(levelname)s: %(message)s',level=logging.INFO)

# 设置日志输出格式

MYSQL_USERNAME = 'root'

# MySQL 数据库用户名

MYSQL_PASSWORD = '123456'

# 数据库密码

MYSQL_HOST = '127.0.0.1'

# 数据库主机地址

MYSQL_PORT = 3306

# 数据库端口

BACKUP_PATH = 'backup'

# 备份文件存放路径

logging.info('开始获取需要恢复的数据库文件...')

files = list(os.listdir(BACKUP_PATH))

logging.info('文件列表：{}'.format(files))

for file in files:

# 开始逐个恢复数据库

logging.info('开始恢复数据库 {}...'.format(file.split('.')[0]))

command

'mysql

-h{host}

-f

-u{user}

-p{password}

-P{port}

{path}/{file}'.format(host=MYSQL_HOST,user=MYSQL_USERNAME,password=MYSQL_PA

SSWORD,port=MYSQL_PORT, file=file,path=BACKUP_PATH)

subprocess.call(command, shell=True)

logging.info('完毕！')

7.4.5 执行还原数据库的 Python 脚本

编写完成后执行脚本完成数据库恢复，执行命令及结果如下：[root@localhost ~]# python3 mysql_restore.py

2020-07-10 10:20:42,963 - mysql_restore.py[line:11] - INFO: 开始获取需要恢复的数据库文件...

2020-07-10 10:20:42,964 - mysql_restore.py[line:13] - INFO: 文件列表：['test.sql']

2020-07-10 10:20:42,964 - mysql_restore.py[line:15] - INFO: 开始恢复数据库 test...

mysql: [Warning] Using a password on the command line interface can be insecure.

2020-07-10 10:20:43,181 - mysql_restore.py[line:18] - INFO: 完毕！

此时应注意，备份和恢复的数据库服务器可以不是同一台服务器，所以此脚本也可用于

数据库迁移的场景。当目标数据库中已经有同名的数据库了，会先删除同名数据库，再重新

创建并恢复数据。所以在实际生产环境中，请确认此还原逻辑是否符合需求。对于执行数据

库还原脚本时一定要谨慎操作。