数据库—pymysql模块和数据备份
一、pymysql模块
模块功能:帮助我们在代码中执行mysql语句,实现对数据库的操作。
安装:pip install PyMySQL
1.pymsql操作数据库
import pymysql db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接 cursor = db.cursor() # 获得一个操作游标 cursor.execute("SELECT VERSION()") # 使用 execute() 方法执行 SQL 查询 data = cursor.fetchone() # 使用 fetchone() 方法获取单条数据 print ("Database version : %s " % data) db.close()
更多参数:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import pymysql conn = pymysql.connect( host='localhost', user='root', password="root", database='db', port=3306, charset='utf-8', ) cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
使用实例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
'''查询操作''' import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123", "day040_homework") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: cursor.execute(sql) # 执行SQL语句 results = cursor.fetchall() # 获取所有记录列表 for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income)) except: print("Error: unable to fetch data") # 关闭数据库连接 db.close()
2.创建表操作
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL,如果表存在则删除 cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # 使用预处理语句创建表 sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql) # 关闭数据库连接 db.close()
3.数据相关操作
1.插入数据commit
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: cursor.execute(sql) # 执行sql语句 db.commit() # 提交到数据库执行 except: db.rollback() # 如果发生错误则回滚 # 关闭数据库连接 db.close()
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES (%s, %s, %s, %s, %s )" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: cursor.execute(sql) # 执行sql语句 db.commit() # 执行sql语句 except: db.rollback() # 发生错误时回滚 # 关闭数据库连接 db.close()
2.查询操作
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
- fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
- fetchall(): 接收全部的返回结果行.
- rowcount: 这是一个只读属性,并返回执行execute()方法后影响的行数。
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: num = cursor.execute(sql)# 执行SQL语句,这里num是查询数据符合的条数。 results = cursor.fetchall()# 获取所有记录列表 for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income )) except: print ("Error: unable to fetch data") # 关闭数据库连接 db.close()
3.更新操作commit
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 更新语句 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: cursor.execute(sql) # 执行SQL语句 db.commit() # 提交到数据库执行 except db.rollback() # 发生错误时回滚 # 关闭数据库连接 db.close()
4.删除操作commit
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try cursor.execute(sql) # 执行SQL语句 db.commit() # 提交修改 except db.rollback() # 发生错误时回滚# 关闭连接 db.close()
4.pymysql执行sql语句的注意事项
sql语句拼接的问题
pymysql库在执行sql语句时,如果我们在sql语句中用到了字符串拼接,需要给字符串的拼接符单独加上引号。
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() name,age,gender,weight = "alex",25,"male",75 # SQL 插入语句 sql = """insert into staff_info('%s',%s,'%s',%s)"""%(name,age,gender,weight) # 注意这里字符串类型数据的拼接需要加引号。 # 执行sql语句 cursor.excute(sql) db.commit() # 提交到数据库 db.close()
另一种执行方式
针对上述问题,pymysql中提供了另一种执行sql语句的方式,这种方式不仅无需自己拼接字符串,还能避免mysql中的sql语句注入问题
import pymysql # 打开数据库连接 db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() name,age,gender,weight = "alex",25,"male",75 info_list = [name,age,gender,weight] # 执行sql语句 cursor.excute("insert into staff_info(%s, %s, %s, %s)", info_list) # 注意这里的字符串拼接符无需额外添加引号 db.commit() # 提交到数据库 db.close()
二、数据备份
1. 物理备份: 直接复制数据库文件,适用于大型数据库环境。但不能恢复到异构系统中如Windows。
2. 逻辑备份: 备份的是建表、建库、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。
3. 导出表: 将表导入到文本文件中
1.使用mysqldump实现逻辑备份
#语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例: #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
2.恢复逻辑备份
#方法一: [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二: mysql> use db1; mysql> SET SQL_LOG_BIN=0; mysql> source /root/db1.sql #注:如果备份/恢复单个库时,可以修改sql文件 DROP database if exists school; create database school; use school;
3.备份/恢复案例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#数据库备份/恢复实验一:数据库损坏 备份: 1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql 2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog 3. 插入数据 //模拟服务器正常运行 4. mysql> set sql_log_bin=0; //模拟服务器损坏 mysql> drop database db; 恢复: 1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log 2. mysql> set sql_log_bin=0; mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 mysql> source /backup/last_bin.log //恢复最后个binlog文件 #数据库备份/恢复实验二:如果有误删除 备份: 1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql 2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog 3. 插入数据 //模拟服务器正常运行 4. drop table db1.t1 //模拟误删除 5. 插入数据 //模拟服务器正常运行 恢复: 1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql # mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 2. mysql> set sql_log_bin=0; mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 mysql> source /tmp/1.log //恢复最后个binlog文件 mysql> source /tmp/2.log //恢复最后个binlog文件 注意事项: 1. 完全恢复到一个干净的环境(例如新的数据库或删除原有的数据库) 2. 恢复期间所有SQL语句不应该记录到binlog中
1.创建新的数据库
# 进入数据库 mysql -u root -p123 # 创建一个新的数据库 mysql>CREATE DATABASE newdb DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
2.复制数据库
使用mysqldump及mysql的命令组合,一次性完成复制
-
在同一台MySQL服务器上复制数据库的方法。
# 复制db1中的表到newdb数据库中 mysqldump db1 -u root -p123 --add-drop-table | mysql newdb -u root -p123 注意-p123参数的写法:-p后面直接跟密码,中间没有空格)
如果要复制到远程另一台MySQL服务器上,可以使用mysql的“ -h 主机名/ip”参数。前提是mysql允许远程连接,且远程复制的传输效率和时间可以接受。
-
数据库不在同一个mysql服务器上。
mysqldump db1 -uroot -p123 --add-drop-table | mysql -h 192.168.1.22 newdb -u root -p123
3.数据库迁移
# 务必保证在相同版本之间迁移 mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456
5.实现自动化备份
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
备份计划: 1. 什么时间 2:00 2. 对哪些数据库备份 3. 备份文件放的位置 备份脚本: [root@egon ~]# vim /mysql_back.sql #!/bin/bash back_dir=/backup back_file=`date +%F`_all.sql user=root pass=123 if [ ! -d /backup ];then mkdir -p /backup fi # 备份并截断日志 mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file} mysql -u${user} -p${pass} -e 'flush logs' # 只保留最近一周的备份 cd $back_dir find . -mtime +7 -exec rm -rf {} \; 手动测试: [root@egon ~]# chmod a+x /mysql_back.sql [root@egon ~]# chattr +i /mysql_back.sql [root@egon ~]# /mysql_back.sql 配置cron: [root@egon ~]# crontab -l * * * /mysql_back.sql
6.表的到出和导入
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT... INTO OUTFILE 导出文本文件 示例: mysql> SELECT * FROM school.student1 INTO OUTFILE 'student1.txt' FIELDS TERMINATED BY ',' //定义字段分隔符 OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来 LINES TERMINATED BY '\n' ; //定义换行符 mysql 命令导出文本文件 示例: # mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt # mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml # mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html LOAD DATA INFILE 导入文本文件 mysql> DELETE FROM student1; mysql> LOAD DATA INFILE '/tmp/student1.txt' INTO TABLE school.student1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '”' LINES TERMINATED BY '\n';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#可能会报错 mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n'; ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable #数据库最关键的是数据,一旦数据库权限泄露,那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走,因而mysql对此作了限制,只能将文件导出到指定目录 在配置文件中 [mysqld] secure_file_priv='C:\\' #只能将数据导出到C:\\下 重启mysql 重新执行上述语句
7.事务和锁
begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值,for update添加行锁; update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务
衣带渐宽终不悔,为伊消得人憔悴!