python pymysql的安装

方法1：适用于网络很好的情况下

python3 -m pip install PyMySQL

python -m pip install pymysql

前言
在之前几天的日记中，各位可以看到我在保存数据时用的基本上都是 MySQL 数据库，而我用的也是 pymysql 来操作 MySQL，pymysql 则是一个纯 Python 实现的 MySQL 客户端操作库

今天呢就来讲讲 pymysql 的使用，事实上我觉得看起来用 pymysql 来操作 MySQL 的代码会显得比较繁杂，之后也会讲讲 Python 操作 MongoDB、Redis 等数据库的库

这里便不再赘述 MySQL 有关的详细的知识点，有需要的话可以看看这里
MySQL 教程
《深入浅出mysql》学习笔记

正文
安装...就不用多说了8

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

输出

{'password': 'very-secret', 'id': 1}
数据库连接
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
这里除了 host、user、password 等还有很多参数可以选择 详见

参数    解释
host    数据库服务器地址，默认 localhost
user    用户名，默认为当前程序运行用户
password    登录密码，默认为空字符串
database    默认操作的数据库
port    数据库端口，默认为 3306
bind_address    当客户端有多个网络接口时，指定连接到主机的接口。参数可以是主机名或IP地址。
unix_socket    unix 套接字地址，区别于 host 连接
read_timeout    读取数据超时时间，单位秒，默认无限制
write_timeout    写入数据超时时间，单位秒，默认无限制
charset    数据库编码
sql_mode    指定默认的 SQL_MODE
read_default_file    Specifies my.cnf file to read these parameters from under the [client] section.
conv    Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types.
use_unicode    Whether or not to default to unicode strings. This option defaults to true for Py3k.
client_flag    Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass    设置默认的游标类型
init_command    当连接建立完成之后执行的初始化 SQL 语句
connect_timeout    连接超时时间，默认 10，最小 1，最大 31536000
ssl    A dict of arguments similar to mysql_ssl_set()’s parameters. For now the capath and cipher arguments are not supported.
read_default_group    Group to read from in the configuration file.
compress    Not supported
named_pipe    Not supported
autocommit    是否自动提交，默认不自动提交，参数值为 None 表示以服务器为准
local_infile    Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet    发送给服务器的最大数据量，默认为 16MB
defer_connect    是否惰性连接，默认为立即连接
auth_plugin_map    A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
server_public_key    SHA256 authenticaiton plugin public key value. (default: None)
db    参数 database 的别名
passwd    参数 password 的别名
binary_prefix    Add _binary prefix on bytes and bytearray. (default: False)
游标
连接完数据库，接着就该获取游标，之后才能进行执行、提交等操作
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'
最后
最后再来看看之前写的插入数据库的代码，是不是更加清晰了

    def insertmysql(info_id, info_url, price, refprice, name, comment_num, comment_types):
        conn_insert = pymysql.connect(host='localhost', port=3306, user='', password='', db='jingdong')
        cursor_ins = conn_insert.cursor()

        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)
        try:
            select_sql = "select `pid` from `jd_huawei` where `pid`='%s'" % info_id
            response = cursor_ins.execute(select_sql)
            conn_insert.commit()
            if response == 1:
                print(u'该手机已存在...')
            else:
                try:
                    cursor_ins.execute(insert_sql)
                    conn_insert.commit()
                    print(u'更新成功...')
                except Exception as e:
                    print(u'更新错误...', e)
                    conn_insert.rollback()
        except Exception as e:
            print(u'查询错误...', e)
            conn_insert.rollback()
        finally:
            cursor_ins.close()
            conn_insert.close()
print('微信公众号搜索 "猿狮的单身日常" ，Java技术升级、虫师修炼，我们 不见不散!')
print('也可以扫下方二维码哦~')

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