pymysql操作mysql详解

前言

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

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

这里便不再赘述 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 中所有的有关更新数据(insertupdatedelete)的操作都需要 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('也可以扫下方二维码哦~')
猿狮的单身日常
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容