安装PyMySQL
python中连接mysql的客户端主要有mysqldb、mysql-connector、pymysql三种。虽说性能上面各有差别,但是主流市场还是以操作便捷、使用简单为选择条件。
这里我选择以纯python编写的mysql连接库pymysql为首。
安装
pip3 install PyMySQL
打开数据库连接
以下两种写法可以打开数据库连接,返回db对象。
db = pymysql.connect("127.0.0.1", "root", "132456", "test")
db= pymysql.connect(host="127.0.0.1",user="root",password="132456",database="test")
获取游标
我们在python中使用mysql接口操作数据库,对于mysql来说,python就是客户端,在python客户端中,执行SQL统一要用到游标对象,游标是数据库中一个很重要的概念,字面意思就是游动的标记。
我们正常查询数据,会用变量来接收SQL语句查询返回的结果集,假设说我们的查询量非常巨大,达到几千万上亿行数据,那么我们的服务器内存会直线飙升,引起程序崩溃。如果查出来的数据还要遍历进行处理的话,那么程序崩溃*2
但若是使用游标对象来查询,查出的结果集不会立刻返回给客户端,而是会存储在mysql服务器上,可以理解成是mysql为用户查询操作开设的一个缓冲区,用来存储查询结果,然后客户端从这个缓冲区中获取数据内容。
从db对象中获取游标
cursor = db.cursor()
创建表
sql = '''CREATE TABLE user(
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
username VARCHAR(16) NOT NULL COMMENT '姓名',
sex INT(1) UNSIGNED NOT NULL COMMENT '性别 1男2女',
age INT(11) UNSIGNED NOT NULL COMMENT '年龄',
PRIMARY KEY (id)
)
ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;'''cursor.execute(sql)
cursor.close()
db.close()
增删改
如果你的数据库支持事务,那么pymysql在你建立游标的时候就自动开启了事务,所以我们想要让SQL执行生效,就必须要做提交事务的动作。
sql = '''INSERT INTO user(username,sex,age) VALUES ('小明',1,18);'''
try:
cursor.execute(sql)exceptException as e:
db.rollback()print(e)else:
db.commit()print('新增数据行的id是:{}'.format(cursor.lastrowid))
cursor.close()
db.close()
打印结果:
新增数据行的id是:1
修改操作和新增操作是一样的,当然修改操作取不到自增id,就算取结果也是0,但是它有一个受影响行数,执行execute方法会返回受影响行数,从游标中也一样可以取到。
sql = '''UPDATE user SET age = 19 WHERE id = 1'''count=try:
count=cursor.execute(sql)exceptException as e:
db.rollback()print(e)else:
db.commit()print(f'受影响行数是:{count}')print(f'从游标中也可以取到受影响行数:{cursor.rowcount}')
cursor.close()
db.close()
打印结果:
受影响行数是:1
从游标中也可以取到受影响行数:1
删除和修改就完全是换条SQL而已,这里就不做多余的演示了。
查询
查询数据之后,我们要从游标中取结果,有两种取法,一次性全取和一行一行的取。
一次性全取 fetchall()
sql = '''SELECT * FROM user'''count=try:
count=cursor.execute(sql)exceptException as e:print(e)print(f'查询到的数据行数是:{count}')
data=cursor.fetchall()print(data)
cursor.close()
db.close()
打印结果:
查询到的数据行数是:3
((1, '小明', 1, 19), (2, '小强', 1, 18), (3, '小花', 2, 17))
可以看到从游标中取到的结果集被装在了元组里,我们可以进一步遍历元组获取每一行:
sql = '''SELECT * FROM user'''count=try:
count=cursor.execute(sql)exceptException as e:print(e)
data=cursor.fetchall()print(f'查询到的数据行数是:{count}')for row indata:if row[2] == 1:
sex= '男'
elif row[2] == 2:
sex= '女'
else:
sex= '未知'
print(f'ID号{row[0]}:姓名是{row[1]},性别是{sex},今年{row[3]}岁。')
cursor.close()
db.close()
打印结果:
查询到的数据行数是:3
ID号1:姓名是小明,性别是男,今年19岁。
ID号2:姓名是小强,性别是男,今年18岁。
ID号3:姓名是小花,性别是女,今年17岁。
一行一行取 fetchone()
sql = '''SELECT * FROM user'''count=try:
count=cursor.execute(sql)exceptException as e:print(e)print(f'查询到的数据行数是:{count}')for i inrange(count):
row=cursor.fetchone()if row[2] == 1:
sex= '男'
elif row[2] == 2:
sex= '女'
else:
sex= '未知'
print(f'ID号{row[0]}:姓名是{row[1]},性别是{sex},今年{row[3]}岁。')
cursor.close()
db.close()
打印结果:
查询到的数据行数是:3
ID号1:姓名是小明,性别是男,今年19岁。
ID号2:姓名是小强,性别是男,今年18岁。
ID号3:姓名是小花,性别是女,今年17岁。
如果要对查出来的大量数据进行遍历处理,最好就是用游标一行一行的读出来处理,在上文获取游标小节中讲了,对变量进行遍历和从游标中取数据是两个概念,变量是存在于我们服务器内存中的,游标是数据库的资源句柄。
游标还有一个fetchmany()方法,传入一个int参数,指定获取几条数据,个人觉得是小概率用法。