Python操作MySQL

 

安装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)
except Exception 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 = 0
try:
    count = cursor.execute(sql)
except Exception 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 = 0
try:
    count = cursor.execute(sql)
except Exception 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 = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    print(e)

data = cursor.fetchall()

print(f'查询到的数据行数是:{count}')

for row in data:
    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 = 0
try:
    count = cursor.execute(sql)
except Exception as e:
    print(e)

print(f'查询到的数据行数是:{count}')

for i in range(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参数,指定获取几条数据,个人觉得是小概率用法。

 

posted @ 2020-08-04 11:02  不该相遇在秋天  阅读(454)  评论(0编辑  收藏  举报