PyMySQL初见

什么是 PyMySQL？

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

PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

安装：python3 -m pip install PyMySQL

官方例子：

# 表结构
CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,  # id 整型 不能为空 自动增长
    `email` varchar(255) COLLATE utf8_bin NOT NULL,  # 邮箱 可变字符串 区分大小写，不能为空
    `password` varchar(255) COLLATE utf8_bin NOT NULL,  # 密码 可变字符串 区分大小写，不能为空
    PRIMARY KEY (`id`)  # id 为主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;
# InnoDB 引擎 默认 utf-8 编码 区分大小写 自动增长从1开始

import pymysql.cursors

# 连接数据库
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # 创建一条新的记录
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # 连接完数据库并不会自动提交，所以需要手动 commit 你的改动
    connection.commit()

    with connection.cursor() as cursor:
        # 读取单条记录
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

这里注意连续用了两处 with 好处就在于 with 结束后会自动 close cursor 而免去了 cursor.close() 

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

游标

连接完数据库，接着就该获取游标，之后才能进行执行、提交等操作
cursor = connection.cursor()

查询时，默认返回的数据类型为元组，可以修改返回类型
几种常用游标类型：

• Cursor: 默认，元组类型
• DictCursor: 字典类型
• SSCursor: 无缓冲元组类型
• SSDictCursor: 无缓冲字典类型

无缓冲游标类型，适用于数据量很大，一次性返回太慢，或者服务端带宽较小

创建连接时，通过cursorclass 参数指定类型：

 

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='root',
                             db='db',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

也可以在创建游标时指定类型：

 

cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)

游标移动

所有的数据查询操作均基于游标，我们可以通过cursor.scroll(num, mode)控制游标的位置。

 

cursor.scroll(1, mode='relative') # 相对当前位置移动
cursor.scroll(2, mode='absolute') # 相对绝对位置移动

sql语句

我的习惯是先写好要操作的语句，如插入、更新、删除等，同时也要注意 pymysql 中所有的有关更新数据（insert，update，delete）的操作都需要 commit，否则无法将数据提交到数据库

 

# 插入语句
insert_sql = "insert into `jd_huawei` (`pid`, `url`, `price`, " \
                     "`refprice`, `name`, `comment_num`, `comment_type`)values('%s','%s','%s'," \
                     "'%s','%s','%s','%s')" % (info_id, info_url, price, refprice, name, comment_num, comment_types)
# 查询语句
select_sql = "select `pid` from `jd_huawei` where `pid`='%s'" % info_id

最后一条ID

cursor提供一个参数可以获取最新 insert 自增的 id ，也就是最后插入的一条数据ID，如果没有insert过，执行cursor.lastrowid会报错

执行sql语句

cursor.execute(sql, args)
这里的参数 args 可以是，tuple，list，dict，另外 execute 还能返回受影响的行数

 

influence_num = cursor.execute(sql, args)
print(type(influence_num))  # int

cursor.executemany(sql, args)
可以批量执行

查询获取数据

查询表中的全部数据
select * from table

取出全部的数据，可以返回一个结果集
fetchall()

取出一定数量的数据
fetchmany(size)

取出一条数据
fetchone()

事务处理

事务开始（这个我好像没怎么用）
connection.begin()

提交修改
connection.commit()

事务回滚
connection.rollback()

两个close

一般用于最后结束对数据库的操作，可在 finally 中写

关闭数据库连接
connection.close()

关闭游标
cursor.close()

还有防止 sql 注入

通过操作输入来修改后台SQL语句

 

#正常构造语句的情况
sql="select user,pass from tb7 where user='%s' and pass='%s'" % (user,passwd)
row_count=cursor.execute(sql)
#拼接语句被构造成下面这样，永真条件，此时就注入成功了。
select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'

要避免这种情况可以使用 pymysql 提供的参数化查询

 

#避免注入，使用pymysql提供的参数化语句
user="u1' or '1'-- "
passwd="u1pass"
#执行参数化查询
row_count=cursor.execute("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))

# 内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。
sql=cursor.mogrify("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
print(sql)
# 被转义的语句
select user,pass from tb7 where user='u1\' or \'1\'-- ' and pass='u1pass

作者：不存在的一角
链接：https://www.jianshu.com/p/1ba64df4fd15
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。