当前位置：首页 > news >正文

Python数据库操作

news 2025/11/5 11:27:00

前面的章节中学习了使用 Python 读写文件的方法，大家可以用文件方式来存放数据，不过使用文件方式时不容易管理，同时还容易丢失，会带来许多问题。目前主流的方法都是采用数据库软件，通过数据库软件来组织和存放数据，不管是读还是存都非常方便，速度也有保障。本篇介绍 python 操作 MySQL 数据库软件的方法。

一、Python操作数据库简介

数据库(Database)是按照数据结构来组织、存储和管理建立在计算机存储设备上的数据仓库。MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发。MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行，如 Unix/Linux、windows、Mac 和 Solaris。MySOL 采用标准的 SOL 语言(结构化查询语言)，SQL 是一种数据库査询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
虽然 Python 可以支持数据的本地存储，但前提是先从数据库获取才能保存下来，同时还无法存储过多的数据，因为数据释放和使用会占用较大的内存，数据库负责把磁盘数据组织好放回内存，Python负责在内存中操作数据。

二、PyMySQL

PyMySQL 是 Python 中操作 MySQL 的模块，其使用方法和 MySQLdb 几乎相同。但目前 PyMySQL支持 Python3.x，而 MySQLdb 不支持 3.x 版本。
本章节我们来学习 PyMySQL 模块对数据库的操作，以及利用 python 脚本实现 mysql 的备份和还原。

1.设备清单

2.在 python 主机安装 pymysql 模块

通过 pip 安装 PyMySQL 的命令:

[root@localhost ~]# pip3 install pymysql

3.数据库主机安装 mariadb

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# yum -y install mariadb-server

[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysqladmin -uroot password '123456'
[root@localhost ~]# mysql -uroot -p123456
MariaDB [(none)]> grant all on *.* to root@'%' identified by '123456';

4.在 mariadb01 上创建数据库

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,email varchar(255), password varchar(255), PRIMARY KEY(id));

5.pymysql使用

在前面的课程中，已经详细的介绍过了 MySQL 的安装及配置过程，在本章将不做过多阐述。现在我们安装一个 Mariadb 数据库，并登录 Mariadb 实例，在 test 库中创建一张 users 表，root 帐户密码为“123456”，代码如下。

示例 1:使用 PyMySOL 模块连接 MySOL 数据库向 user 表插入一条记录并査询这条记录:

[root@localhost ~]# vim pymysql-1.py import pymysql
connection=pymysql.connect(host='192.168.10.102',
user='root',
password='123456',
db='test',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:with connection.cursor() as cursor:sql = "insert into users (email,password) values(%s,%s)"cursor.execute(sql,('zhangsan@qq.com','pwd123'))connection.commit()with connection.cursor() as cursor:sql = "select id,password from users where email=%s"cursor.execute(sql,('zhangsan@qq.com'))result = cursor.fetchone()print(result)
finally:connection.close()

[root@localhost ~]# python3 pymysql-1.py 
{'id': 1, 'password': 'pwd123'}

[root@localhost ~]# mysql -uroot -p123456
MariaDB [(none)]> use test;
MariaDB [test]> select * from users;
+----+-----------------+----------+
| id | email           | password |
+----+-----------------+----------+
|  1 | zhangsan@qq.com | pwd123   |
+----+-----------------+----------+
1 row in set (0.00 sec)

从代码的执行结果中，可以看到数据库表中已经成功插入了一条记录。
代码说明:先创建一个连接对象，下面是一个异常处理。如果连接成功，执行INSERT 语句并提交再执行 SELECT 语句，获取査询的第一条记录并打印;finally 是最终执行关闭连接。在上面示例中用到了 pymysql.connect()函数，还有游标对象 cursor，下面具体看下它们的用法。

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

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

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

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

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

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

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

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

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

6.数据库增删改查

了解了 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('lisi@gq.com','pwd123')"
>>>cursor.execute(sql)
>>>connection.commit()
>>>connection.close()

(2)查询记录

示例 3:查询 users 表记录

>>>import pymysql
>>>
connection=pymysql.connect(host='localhost',user='root',password='123456',db='test')
>>>cursor=connection.cursor()
>>>sql="SELECT id,email, password FROM users'
>>>cursor.execute(sq1)
5
>>>cursor.fetchone()# 获取第一条记录
>>>cursor.fetchmany(2)#获取前两条记录
>>>cursor.execute(sql)
>>>cursor.fetchall()#获取所有记录
>>>cursor.execute(sql)
>>>cursor.fetchall()[3]#查看第四条记录
>>>connection.close()

(3)修改记录

修改用户密码

>>>import pymysql
>>>
connection=pymysql.connect(host='localhost',user='root',password='123456',db='test')
>>>cursor=connection.cursor()
>>>sql ="UPDATE users SET password='456789'WHERE email='lisi@qq.com,'"
>>>cursor.execute(sql)
1
>>>connection.commit()
>>>sql="SELECT id,email,password FROM users`"
>>>cursor.execute(sq1)#执行sql语句
5
>>>cursor.fetchmany(2) #显示前两个记录

(4)删除记录

删除用户信息

>>>sql ='DELETE FROM users WHERE email="lisi@gq.com"
>>> cursor.execute(sql)
1
>>> connection.commit()
>>>sql="SELECT id, password FROM users WHERE email=%s"
>>>cursor.execute(sql,('zhangsan@qq.com',))
>>>cursor.execute(sql,('lisi@qq.com',))
0
>>>sq1 ="SELECT 'id`,email,`password FROM 'users'"
>>>cursor.execute(sql)
4
>>> cursor.fetchall()

7.遍历查询结果

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

[root@localhost ~]# cat pymysql-2.py#!/usr/local/bin/python3.6
import pymysq1
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

三、案例实战--Python 脚本周期性备份 MySQL 数据库

如果要对当前服务器上的数据库进行备份，从 MySQL 导出数据，需要用到 mysqldump 工具,备份全部数据库数据到一个 sq1 文件，包括 mysq1、sys、information schema.performance schema 等内置数据库。无论是否使用 Python 脚本执行数据库备份，都需要使用mysqldump 具。

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

host: 192.168.10.102
port:3306
user: root
password:123456

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

执行命令如下:

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

不用编写 Python 脚本即可一条命令备份 mysq1 数据库,那么编写 Python 脚本备份数据库不是多此一举么?如果是备份全部数据，直接使用命令确实很方便，但是在企业生产环境中，备份的方式要灵活很多。例如只需要把每个数据库单独备份成一个 sq1 文件，并都保存在同一个目录下面。就需要手动执行很多条命令，此时使用 Python 脚本实现就非常简单方便了。

2.写 Python 脚本文件 backup mysql.py

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

import datetime
import os
importsubprocess
import time
import logging
import pymysql
# Set log format
logging.basicconfig(format='%(asctime)s-%(pathname)s[line:%(lineno)d]
-%(levelname)s:%(message)s',level=logging.INFO)
# MySOL database details to which backup to be done. Make sure below user having enoughprivileges to take databases backup.
# 数据库主机地址
MYSOL HOST ='192.168.10.102
# MySQL 数据库用户名MYSOL USERNAME ='root'
井
MYSOL PORT = 3306
# 数据库密码
MYSOL PASSWORD=123456
# 需要备份的数据库名称
# DB NAME ='main'
#排除，不进行备份操作的数据库名称集合
DISABLED DATABASEs = {'information schema', 'mysql', 'performance_schema', 'sys'}
#备份文件存放路径
BACKUP PATH ='/backup/mysql/'
# Getting current datetime to create seprate backup folder.DATETIME = datetime.datetime.now().strftime("%%m%d%H%M%S")
TODAYBACKUPPATH = BACKUP PATH + MYSOL HOST + '/'+ DATETIME# 备份保留时长
DAY =3

defmkdir if not exists(path):"""判断备份目录是否存在，不存在则创建Args :path:带创建目录名称
"""
if not os.path.exists(path):os.makedirs(path)
logging.info("creating backup folder")
def create mysql conn(db="mysql"):"""
创建并返回一个 MySQL 数据库连接Args :db:要连接的数据库名称"""conn = pymysql.connect(host=MYSOL HOST, pOrt=MYSOL PORT, USer=MYSOL USERNAMEpassword=MYSOL PASSWORD,db='mysql')return conndef read all databases():"""从数据库中读取全部数据库名称:return:数据库名称 list"""logging.info('读取全部数据库名称')conn = create mysql conn()cursor =conn.cursor()#查询服务器上有哪些数据库cursor.execute('show databases')res = cursor.fetchall()databases =fitem[0lfor item in res}# 排除掉指定不备份的数据库databases=list(databases-DISABLED DATABASES)cursor.close()conn.close()logging.info('读取数据库完毕，数据库列表如下:{}'.format(databases))return databasesdef backup database(database):"""备份指定数据库的数据和结构Args :database:待备份的数据库名称"""logging.info('开始备份数据库{}...'.format(database))# 通过调用mysqldump 完成指定数据库的备份command='mysqldump -h%s -u%s-p%s%s>%s/%s.sq1'%(MYSOL HOST, MYSOL USERNAME, MYSOL PASSWORD, database, TODAYBACKUPPATH,database)exit code =subprocess.call(command,shell=True)# 判断命令是否正常执行，异常则直接抛出if exit code != 0:raise Exception('在备份数据库的过程中出错，请检査!')logging.info('数据库 {} 备份完毕!'.format(database))def del old backup(path):"""删除保留时长以外的数据备份"""old folder = subprocess.Popen("find {} -maxdepth 3 -mindepth 2 -mtime +{} -typed".format(path, DAY),stdout=subprocess.PIPE, stderr=subprocess.STDOUT,shell=True).communicate()logging.info('时间戳为{}的备份将被删除'.format(old folder))str old folder = old folder[0].decode("utf-8",errors="ignore")logging.info("{}".format(str old folder))list lid folder =str old folder.split('\n')for folder in list lid folder:if folder.strip():logging.info("删除{".format(folder))os.system("rm -rf {}".format(folder))
def backup():"""读取全部数据库名称，并对这些数据库的数据和结构进行备份"""#检查备份路径是否存在，不存在则进行创建mkdir if not exists(TODAYBACKUPPATH)#读取全部待备份数据库名称databases =read all databases()# 逐个对数据库进行备份database in databases:forbackup database(database)# 删除旧备份del old backup(BACKUP PATH)#打印备份文件放置的目录print("\n")command ='echo -e"\e[;31m 本次备份放置的位置是:"subprocess.call(command,shell=True)print(TODAYBACKUPPATH)command='echo -e"e[m"subprocess.call(command,shell=True)
if_name__==' main ':backup()

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

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

[root@localhost ~]# python3 backup_mysql.py 
2024-10-16 09:45:30,118 - /root/backup_mysql.py[line:42] - INFO:creating backup folder
2024-10-16 09:45:30,145 - /root/backup_mysql.py[line:61] - INFO:读取全部数据库名称
2024-10-16 09:45:30,174 - /root/backup_mysql.py[line:72] - INFO:读取数据库完毕，数据库列表如下：['test']
2024-10-16 09:45:30,175 - /root/backup_mysql.py[line:82] - INFO:开始备份数据库test...
2024-10-16 09:45:30,191 - /root/backup_mysql.py[line:90] - INFO:数据库 test 备份完毕！
2024-10-16 09:45:30,193 - /root/backup_mysql.py[line:98] - INFO:时间戳为(b'', None)的备份将被删除
2024-10-16 09:45:30,194 - /root/backup_mysql.py[line:100] - INFO:本次备份放置的位置是:
/backup/mysql/localhost/20241016094530

执行过备份脚本后，会将所有的数据库备份到同一个目录中，如果再次执行此脚本，就会创建一个新的目录，将所有的库的备份文件存放进去。

用户可以将此脚本写入到周期性计划任务中，实现自动化的周期性数据库备份

注意:warning 警告的意思是在命令行界面上使用密码可能不安全。所以在实际生产环境中可以利用常量来代替明文输入密码的这一步，或者在配置文件中填写用户名密码等方式保障安全。备份结束之后，可以在当前主机查看备份的结果。

4.编写导入数据库脚本restore_mysql.py

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

[root@localhost ~]# vim restore_mysql.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.10.103'   # 数据库主机地址
MYSQL_PORT = 3306       # 数据库端口
BACKUP_PATH = '/backup/mysql/192.168.10.102/20231216193331'     # 注意，要替换成你的备
份文件存放路径logging.info('开始获取需要恢复的数据库文件...')
files = list(os.listdir(BACKUP_PATH))
logging.info('文件列表：{}'.format(files))
for file in files:      # 开始逐个恢复数据库mydatabase=('{}'.format(file.split('.')[0]))print("\r")print("导入数据库："+mydatabase)command = 'mysql -h{host} -f -u{user} -p{password} -P{port} -e "drop database {mydatabase}"&>/dev/null'.format(host=MYSQL_HOST,user=MYSQL_USERNAME,password=MYSQL_PASSWORD,port=MYSQL_PORT,mydatabase=mydatabase)subprocess.call(command, shell=True)command = 'mysql -h{host} -f -u{user} -p{password} -P{port} -e "create database {mydatabase}"&>/dev/null'.format(host=MYSQL_HOST,user=MYSQL_USERNAME,password=MYSQL_PASSWORD,port=MYSQL_PORT,mydatabase=mydatabase)subprocess.call(command, shell=True)logging.info('开始恢复数据库 {}...'.format(file.split('.')[0]))command = 'mysql -h{host} -f -u{user} -p{password} -P{port} {mydatabase}<{path}/{file}'.format(host=MYSQL_HOST,user=MYSQL_USERNAME,password=MYSQL_PASSWORD,port=MYSQL_PORT,file=file,path=BACKUP_PATH,mydatabase=mydatabase)subprocess.call(command, shell=True)
logging.info('完毕！')

5.执行数据库导入操作

我们可以将数据库导入到任何一个 mysq1 服务器中，这样我们就可以方便的进行数据库的迁移。

[root@localhost ~]# python3 restore_mysql.py 
2024-10-16 11:26:15,423-/root/restore_mysql.py[line:13]-INFO: 开始获取需要恢复的数据库文件...
2024-10-16 11:26:15,423-/root/restore_mysql.py[line:15]-INFO: 文件列表：['test.sql']导入数据库：test
2024-10-16 11:26:15,440-/root/restore_mysql.py[line:28]-INFO: 开始恢复数据库 test...
2024-10-16 11:26:15,453-/root/restore_mysql.py[line:31]-INFO: 完毕！

备份和恢复的数据库服务器可以不是同一台服务器，所以此脚本也可用于数据库迁移的场景。当目标数据库中已经有同名的数据库了，会先删除同名数据库，再重新创建并恢复数据。所以在实际生产环境中，请确认此还原逻辑是否符合需求。对于执行数据库还原脚本时一定要谨慎操作。