文章目录
前言
在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文件
注意事项:
- 完全恢复到一个干净的环境(例如新的数据库或删除原有的数据库)
- 恢复期间所有SQL语句不应该记录到binlog中
4 实现自动化备份
备份计划:
- 什么时间
- 对哪些数据库备份
- 备份文件放的位置
备份脚本:
[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; # 提交事务