pymysql指南
1 引言
mysql应该说是如今使用最为普遍的数据库了,没有之一,而python作为最为流行的语言之一,自然少不了与mysql打交道,pymysql就是使用最多的工具库了。
2 创建库、表
我们先从创建数据库、数据表说起,有了库表,后面的增删改查才有根据。
2.1 创建数据库
pymysql的所有对数据库的操作都必选先与数据库服务建立连接,然后创建游标为基础执行具体的sql语句。创建数据库方法如下:
# -*- coding: utf-8 -*- import pymysql conn = pymysql.connect( # 创建数据库连接 host='10.10.11.131', # 要连接的数据库所在主机ip user='chb', # 数据库登录用户名 password='123456!', # 登录用户密码 charset='utf8' # 编码,注意不能写成utf-8 ) cursor = con.cursor() cursor .execute("create database test_db character set utf8;") # 执行完之后别忘了关闭游标和数据库连接 cursor.close() conn.close()
上面代码执行完后,就创建了一个名为test_db的数据库:
2.2 创建数据表
# -*- coding: utf-8 -*- import pymysql conn = pymysql.connect( # 创建数据库连接 host='10.10.11.131', # 要连接的数据库所在主机ip user='chb', # 数据库登录用户名 password='123456!', # 登录用户密码 database='test_db', # 连接的数据库名,也可以后续通过cursor.execture('user test_db')指定 charset='utf8' # 编码,注意不能写成utf-8 ) cursor = conn.cursor() # 创建一个游标 # 需要执行的创建表的sql语句 sql = """ create table book( bookid int auto_increment primary key , bookname varchar(255) not null , authors varchar(255) not null , year_publication year not null ); """ cursor.execute(sql) # 使用游标执行sql # 执行完之后别忘了关闭游标和数据库连接 cursor.close() conn.close()
在这个创建数据表的例子中,在创建数据库服务连接时,我们通过 database='test_db'这行代码指定参数连接了目标数据库,如果不想在创建数据库服务连接时指定数据库,可以在后续通过cursor.execture('user test_db')这种方法指定数据库。
从上面创建数据库和数据表的例子可以看出,pymysql执行具体操作时都是先创建数据库服务连接,然后通过连接创建游标,以游标来执行具体的sql语句来完成具体的对数据库操作。不单单可以创建数据库、数据表,还可以创建索引、视图等等,方法也是一样的,这里不再介绍。
3 增删改查
3.1 插入
插入操作可以通过游标的execute和executemany两个方法来完成。注意:只要是对数据表有修改的操作(插入、更新、删除)在使用execute方法后,都需要再次调用commit方法对数据库的修改才会最终生效。
execute方法一次插入一条记录,executemany一次插入多条记录:
- execute:一次插入一条记录
cursor.execute('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);' % ('python从入门到放弃', '乔布斯', 2019)) conn.commit()
上面这种写法是先用字符串利用%s占位生成一条完整的sql语句,然后去执行。其实,pymysql的游标也自带这一功能,而且看起来更加方便,所以,我们还可以这样执行execute方法:
cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('python从入门到放弃', '乔布斯', 2019)) conn.commit()
这种方法的好处是sql语句中的%s不需要加引号(如果加了引号,引号也会被当做数据插入到数据表中),游标执行这一语句时,会根据数据类型来判断是否要加上引号。
- executemany:一次插入多条记录
data = [ ('21天完全入门java', '扎克伯格', 2018), ('linux学习手册', '李纳斯', 2017), ('mysql从删库到跑路', '比尔盖茨', 2018), ] cursor.executemany('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);', data) conn.commit()
3.2 更新
cursor.execute('update book set authors=%s where bookname=%s;', ["马云", "python从入门到放弃"]) conn.commit()
3.3 查询
查询是最最要但也是最复杂的一个操作了。我们分为fetch操作、游标两部分来说。
(1)fetch操作
插入、更新、删除操作必须再执行commit操作之后才会生效,而查询操作也只有在执行fetch操作之后才会生效。fetch操作包括3个方法,分别是fetchone()、fetchall()、fetchmany()。
- fetchall():查询指定的所有记录
cursor.execute('select * from book where bookid < %s;', [4]) books = cursor.fetchall() print(books)
输出结果为:
((1, 'python从入门到放弃', '马云', 2019), (2, 'python从入门到放弃', '马云', 2019), (3, '21天完全入门java', '扎克伯格', 2018))
输出结果是以元组的形式来保存,且每一条记录也是一个元素,这是由游标cursor决定的,在下文中还会说到。
- fetchmany(size):查询指定数量的记录
cursor.execute('select * from book where bookid < %s;', [4]) books = cursor.fetchmany(2) print(books)
输出结果为:
((1, 'python从入门到放弃', '马云', 2019), (2, 'python从入门到放弃', '马云', 2019))
可以看出,fetchmany(size)是取出符合查询条件的最前面的指定数量记录。这里的size指的就是想要取出的记录条数。
- fetchone():取出第一条记录
cursor.execute('select * from book where bookid < %s;', [4]) books = cursor.fetchone() print(books)
输出结果:
fetchone()相当于是fetchmany(1),取出第一条符合查询条件的记录。
(2)游标
我们之前使用游标都是采用默认的cursor类型,除此以外,pymysql还提供了dictcursor、sscursor、ssdictcursor这几类游标。
- dictcursor
之前使用的cursor返回的数据是以元组的方式保存,而dictcursor是以字典的形式保存。创建这种类型的游标方法也很简单,在conn.sursor()方法中传入dictcursor这个类即可:
cursor = conn.cursor(pymysql.cursors.dictcursor) # 创建一个字典游标 cursor.execute('select * from book where bookid < %s;', [3]) books = cursor.fetchall() print(books) cursor.execute('select * from book where bookid < %s;', [3]) book_one = cursor.fetchone() print(book_one)
输出结果如下:
- sscursor和ssdictcursor
sscursor和ssdictcursor被称为流式游标,这类游标不会像上面使用的cursor和dictcursor那样,一次性返回所有的数据,流式游标会陆陆续续一条一条得返回查询数据,所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。
流式游标的使用方法类似于迭代器,再循环中每取一条,生成一条:
cursor = conn.cursor(pymysql.cursors.sscursor) # 创建一个流式游标 cursor.execute('select * from book;') book = cursor.fetchone() while book: print(book) book = cursor.fetchone()
输出结果如下:
sscursor与ssdictcursor的区别就类似于cursor和dictcursor的区别,这里就不在介绍了。
3.4 删除
使用pymysql执行插入、更新、删除操作都是相似的,最后都需要commit提交:
cursor.execute('delete from book where bookid=%s;', [1]) conn.commit()
这时候,bookid为1的记录已经被删除,如下图所示:
也可以使用executemany()一次性删除多条:
cursor.executemany('delete from book where bookid=%s;', [[2], [4]]) conn.commit()
如下图所示,bookid为2和4的记录已经被删除: