Python操作MySQL

---

前言

在python中提供了pymysql模块来连接与操作数据库

---

一、数据库的安装与连接

pymsql的安装：

pip install pymysql

python连接数据库：

import pymysql

db = pymysql.connect("数据库ip","用户","密码","数据库" ) 
# 打开数据库连接
cursor.execute("SELECT VERSION()")	
# 使用 execute() 方法执行 SQL 查询
data = cursor.fetchone()	
# 使用 fetchone() 方法获取单条数据
print ("Database version : %s " % data)
db.close()	
# 关闭数据库连接

其他参数：

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)

---

二、创建表操作

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()

---

三、操作数据

1 插入操作

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()

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:
   
   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()

3 删除操作

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 更新操作

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()

---

四、数据备份

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@psych 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 备份/恢复案例

• 数据库备份/恢复实验一：数据库损坏

备份：

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中

4 实现自动化备份

备份计划：

1. 什么时间
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
2 * * * /mysql_back.sql

5 表的导出和导入

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';

可能会报错

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
重新执行上述语句

6 数据库迁移

务必保证在相同版本之间迁移

mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

---

五、事务与锁

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务

---