使用PyMySQL操作MySQL数据库

此博客是在学习崔庆才《Python3 网络爬虫开发实战》中的笔记总结。在开始学习之前，请确保安装好了MySQL数据库并且保证数据库能够正常运行，并且需要安装好PyMySQL库

目录

1 连接数据库

 2 创建表

3 插入数据

4 更新数据 

5 查询数据

6 删除数据

---

1 连接数据库

• connect() 方法声明一个MySQL连接对象db,此时需要传入MySQL运行的host（即IP）。由于MySQL在本地运行，所以传入的是localhost。如果MySQL在远程运行，则传入其公网IP地址。后续的参数user即用户名，password即密码，port即端口（默认3306）
• cursor() 方法获得MySQL的操作游标，利用游标来执行SQL语句。
• execute() 方法将字符串内容当做命令来执行，作用是动态创建和执行SQL语句。
• fetchone() 方法获得第一条数据

连接数据库 假设MySQL运行在本地，用户名为root，密码是root，运行端口为3306，用PyMySQL连接MySQL，打印MySQL数据库的版本信息，然后创建一个新的数据库test

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306)
cursor=db.cursor()
# 获取MySQL版本
cursor.execute('SELECT VERSION()')
data=cursor.fetchone()
print('Database version:',data)
# 创建数据库Test,默认编码utf-8
cursor.execute('CREATE DATABASE test DEFAULT CHARACTER SET utf8')
db.close()

运行结果如下：

 2 创建表

创建数据库后，在数据库中创建数据表连接时需要额外指定一个参数db

新创建一个数据表User结构如下，执行创建表的的SQL语句即可

字段名	含义	类型
id	编号	varchar
name	姓名	varchar
age	年龄	int

创建该表的代码如下：

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
sql='CREATE TABLE IF NOT EXISTS user' \
    '(id VARCHAR(255) NOT NULL,name VARCHAR(255) NOT NULL,age INT NOT NULL,' \
    'PRIMARY KEY (id))'
cursor.execute(sql)
db.close()

运行之后，便创建了一个名为user的数据表

3 插入数据

向数据库中插入一个用户信息，编号为‘1’，名字为‘林先生‘，年龄20，代码如下：

• commit()：需要执行db对象的commit()方法才可实现数据插入，这个方法才是真正将SQL语句提交到数据库执行的方法。对于数据的增删改查操作，都需要调用该方法才能生效。
• rollback()：如果执行失败，调用rollback()方法执行数据回滚，相当于什么都没发生过。

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
id='1'
name='林先生'
age=20
sql='INSERT INTO user(id,name,age) values (%s,%s,%s)'
try:
    if cursor.execute(sql,(id,name,age)):
        print('插入数据成功')
        db.commit()
except:
    print('插入数据失败')
    db.rollback()
db.close()

上面的数据插入的操作是通过构造SQL语句实现，但是有一个极其不方便的地方，如果突然增加了性别字段gender，此时SQL语句就需要改成：

INSERT INTO user (id, name,age, gender) values (%s, %s, %s, %s)

相应的元组参数则需要改成：(id, name, age, gender)

这显然有点麻烦，我们要达到的效果是插入数据方法无需改动，做成一个通用的方法，只需要传入一个动态变化的字典就好了。

{
    'id':'1',
    'name':'林先生',
    'age':20
}

然后SQL语句会根据字典动态创造，元组也动态创造，实现通用的插入方法，这里我们修改一下插入方法。

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
data={
    'id':'2',
    'name':'李先生',
    'age':20
}
table='user'
keys=', '.join(data.keys())
values=', '.join(['%s']*len(data))
sql='INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table,keys=keys,values=values)
try:
    if cursor.execute(sql,tuple(data.values())):
        print('插入数据成功')
        db.commit()
except:
    print('插入数据失败')
    db.rollback()
db.close()

这里要传入的数据data是一个字典变量，变量table指表名，接下来构建一个动态的SQL语句。

首先，需要插入的字段是id, name, age.这里将data的键名取出，然后用逗号分隔，所以keys=', '.join(data.keys())的结果就是keys='id, name, age' 。然后构建多个占位符%s，有几个字段构建几个占位符。values=', '.join(['%s']*len(data))结果等于values='%s, %s, %s'。最后利用format()方法将表名、字段名和占位符构造出来。最终的SQL语句如下：

INSERT INTO user(id,name,age) values (%s,%s,%s)

• execute()  第一个参数传入SQL变量，第二个参数传入data的键值构造的元组

4 更新数据 

数据更新操作实际上也是执行SQL语句，这里同样用占位符的方法构造SQL，然后执行execute()方法，插入元组形式的参数，同样执行commit()方法执行操作。

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
sql='UPDATE user SET age = %s WHERE NAME = %s'
try:
    if cursor.execute(sql,(25,'林先生')):
        print('更新数据成功')
        db.commit()
except:
    print('更新数据失败')
    db.rollback()
db.close()

在实际操作中，有时可能会出现重复的数据，如果出现了，我们希望更新数据而不是重复保存一次。所以这里可以再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。另外，这种做法支持灵活的字典传值。

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
data={
    'id':'1',
    'name':'林先生',
    'age':22
}
table='user'
keys=', '.join(data.keys())
values=', '.join(['%s'] * len(data))
sql='INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table,keys=keys,values=values)
update=','.join([" {key} = %s".format(key=key) for key in data])
sql+=update
try:
    if cursor.execute(sql,tuple(data.values())*2):
        print('更新数据成功')
        db.commit()
except:
    print('更新数据失败')
    db.rollback()
db.close()

这里构建的SQL语句其实是插入语句，但是在后面加了ON DUPLICATE KEY UPDATE。这行代码的意思是如果主键已经存在，就执行更新操作。比如我们传入的id依然是‘1’，但是年龄有变化,由25变成了22，此时这条数据不会被插入，而是更新id为‘1’的数据。

完整的SQL语句如下

INSERT INTO user (id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s

 这里有六个%s占位符。所以在后面的execute()方法的第二个参数就需要乘以2变成原来的2倍。这样我们就实现主键不存在便插入数据，存在则更新数据的功能了。

5 查询数据

查询会用到SELECT语句，这里我们构建一个SQL语句，将年龄20及以上的用户查询出来。源代码如下

• rowcount属性获取查询结果的条数
• fetchone()方法获取结果的第一条数据，返回结果是元组形式
• fetchall()方法可以得到结果的所有数据，它是一个二重元组

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
sql='SELECT * FROM user WHERE age >= 20'
try:
    cursor.execute(sql)
    print('Count:',cursor.rowcount)
    one=cursor.fetchone()
    print('One',one)
    result=cursor.fetchall()
    print('Result',result)
    print('Result Type:',type(result))
    for row in result:
        print(row)
except:
    print('查收数据失败')

 运行结果如图

数据库user中的数据

这里有一个问题：fetchall() 方法显示的是3条数据而不是4条，fetchall() 方法不是获取所有的数据吗？为什么会这样？原因是它的内部实现有一个偏移指针来指向查询结果，最开始偏移指针指向第一条数据，取一次后，指针偏移到下一条数据，这样再取的话，就会取到下一条数据了。我们最初调用了 一次fetchone() 方法，这样结果的偏移指针就指向下一条数据，fetchall() 方法返回的是偏移指针指向的数据一直到结束的所有数据，所以该方法在这里获取的结果就只剩3个。

我们还可以用while循环加fetchone()方法来获取所有数据，而不是用fetchall()全部一起获取出来。fetchall()方法会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。因此，推荐如下方法来逐条取数据,每循环一次，指针偏移一条数据

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
sql='SELECT * FROM user WHERE age >= 20'
try:
    cursor.execute(sql)
    print('Count',cursor.rowcount)
    row=cursor.fetchone()
    while row:
        print('Row:',row)
        row=cursor.fetchone()
except:
    print('查询数据失败')

6 删除数据

删除操作相对简单，使用DELETE语句即可，需要指定要删除的目标表名和删除条件，而且仍然需要使用db对象的commit() 方法才能生效。删除操作不可逆，操作需谨慎。

删除年龄大于21的用户数据，代码如下：

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='test')
cursor=db.cursor()
table='user'
condition='age>21'
sql='DELETE FROM {table} WHERE {condition}'.format(table=table,condition=condition)
try:
    if cursor.execute(sql):
        print('删除数据成功')
        db.commit()
except:
    print('删除数据失败')
    db.rollback()
db.close()

运行结果

执行删除操作后数据库中剩余的数据