pymysql模块

1 PyMySQL介绍

　　PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

　　Django中也可以使用PyMySQL连接MySQL数据库。

2 PyMySQL安装

　　pip3 install pymysql

3 连接数据库

　　3.1 注意事项

• 有一个MySQL数据库，并且已经启动。
• 有可以连接该数据库的用户名和密码
• 有一个有权限操作的database

　　3.2 基本使用

　　注意：charset=“utf8”，编码不要写成"utf-8"

    # 导入pymysql模块
import pymysql
    # 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
    # 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
    # 定义要执行的SQL语句
sql = """
CREATE TABLE USER1 (
id INT auto_increment PRIMARY KEY ,
name CHAR(10) NOT NULL UNIQUE,
age TINYINT NOT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;
"""
    # 执行SQL语句
cursor.execute(sql)
     # 使用 fetchone() 方法获取单条数据
data = cursor.fetchone()       
print(data)

    # 关闭光标对象
cursor.close()
    # 关闭数据库连接
conn.close()

4 增删改查操作

　　4.1 增

import pymysql

conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
cursor = conn.cursor()

sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
username = "Alex"
age = 18

try:
    cursor.execute(sql, [username, age])
    # 提交事务
    conn.commit()
    # 提交之后，获取刚插入的数据的ID
    last_id = cursor.lastrowid

except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"
data = [("Alex", 18), ("Egon", 20), ("Yuan", 21)]
try:
    #### 批量执行多条插入SQL语句
    cursor.executemany(sql, data)
    # 提交事务
    conn.commit()
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

　　4.2 删

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "DELETE FROM USER1 WHERE id=%s;"
try:
    cursor.execute(sql, [4])
    # 提交事务
    conn.commit()
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()

cursor.close()
conn.close()

　　4.3 改

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 修改数据的SQL语句
sql = "UPDATE USER1 SET age=%s WHERE name=%s;"
username = "Alex"
age = 80
try:
    # 执行SQL语句
    cursor.execute(sql, [age, username])
    # 提交事务
    conn.commit()
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

　　4.4 查（不需要commit）

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT id,name,age from USER1 WHERE id=1;"
# 执行SQL语句
cursor.execute(sql)
# 获取单条查询数据
ret = cursor.fetchone()
cursor.close()
conn.close()
# 打印下查询结果
print(ret)

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT id,name,age from USER1;"
# 执行SQL语句
cursor.execute(sql)
# 获取多条查询数据
ret = cursor.fetchall()
cursor.close()
conn.close()
# 打印下查询结果
print(ret)

####### sql注入问题
sql = "select * from userinfo where username ='%s' and password = '%s' "%(user,pwd)       # 会引起sql注入，如输入 alex' or 1=1 --

    # sql注入原因： 
        执行时输入ehwuf' or 1=1 -- ，程序执行过程如下：
        username ='ehwuf' or 1=1 -- '   ：1=1肯定成立，语句成立；-- 后边的给注释掉了

#######解决sql注入问题
    # sql = "select * from userinfo where username ='%s' and password = '%s' "
    sql = "select * from userinfo where username ='%(u)s' and password = '%(p)s' "   

    # 匹配字符串
        # cursor.execute(sql,user,pwd)             # 执行，防止sql注入
        # cursor.execute(sql,[user,pwd])           # 列表按顺序自动分配给每一项    

5 进阶用法

# 可以获取指定数量的数据
cursor.fetchmany(3)

# 光标按绝对位置移动1
cursor.scroll(1, mode="absolute")

# 光标按照相对位置(当前位置)移动1
cursor.scroll(1, mode="relative")

6 数据备份

　　6.1 数据库的逻辑备份

#语法：
# 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 

　　6.2 数据恢复

#方法一：
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql