Python与MySQL的交互

爬虫俱乐部 · 公众号 · · 2018-08-01 09:22

正文

学习之路，永无止境

暑期Stata培训班报名倒计时咯！！！接力线上的网课培训，我们在今夏又开始新一轮的线下培训啦！ 8月4日至12日，爬虫俱乐部期待与您的相遇！培训具体内容详见推文《暑期Stata编程技术定制培训班》。

有问题，不要怕！点击推文底部“ 阅读原文 ”下载爬虫俱乐部用户问题登记表并按要求填写后发送至邮箱 [email protected] ,我们会及时为您解答哟~

喜大普奔~爬虫俱乐部的github主站正式上线了！我们的网站地址是： https://stata-club.github.io ，粉丝们可以通过该网站访问过去的推文哟~

好消息：爬虫俱乐部隆重推出数据定制及处理业务啦，您有任何网页数据获取及处理方面的难题，请发邮件至我们邮箱[email protected]，届时会有俱乐部资深高级会员为您排忧解难！

在实际工作过程中，我们往往需要将在Python中得到的数据保存到SQL里，如果我先将Python中的数据保存到本地，再打开MySQL，将本地文件上传上去的话，就显得麻烦了不少。因此，本次小编将给大家介绍的就是使用Python中的pymysql模块操作MySQL数据的方法。

首先，我们需要下载相关的模块pymysql，小编使用的是 pycharm编辑器，可以简单地从 file->project interpreter->available packages 中查找pymysql并安装，如果你们使用的是非Pycharm的Python IDE，可以在终端键入 pip install pymysql 。

连接MySQL并创建新的数据库

首先我们可以打开 MySQL ，双击用户，选中用户右键，选择“编辑用户”，我们看到：

这说明当前的MySQL运行在本地，用户名为‘root’，密码为“*********” (我们假设密码为‘123456’)，运行端口号默认为3306。

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306)

连接成功之后，我们需要调用 cursor() 方法获得MySQL的操作游标，这样我们就可以访问和操作数据库中的数据了，接下来我们创建一个叫 “spiders” 的数据库，用 cursor.execute() 来执行，最后关闭数据库连接。

cursor = db.cursor()
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
db.close()

经过以上操作之后，我们打开MySQL就会发现成功创建了spiders数据库。

创建新表

比方说我们要创建一个叫 “sample” 的数据表，里面有三个变量：id，name，age。

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

值得注意的是，创建新表的时候必须指定数据库，所以在连接的时候必须再额外指定一个参数 db='spiders' 。打开MySQL，我们发现sample表确实被成功创建了：

插入一条数据

假设我们现在有这样一条数据 data ，需要将它保存到sample这个表中

data=['0001','Amy',24]
sql = 'replace into sample(id,name,age) values(%s,%s,%s) '
cursor.execute(sql,data)
db.commit()
db.close()

注意：%s表示的是占位符，包含字符串、数字或者其他类型，%s不能加引号。 sql = 'replace into sample(id,name,age) values(%s,%s,%s)' 所起到的作用等价于 sql= "replace into sample(id,name,age) values('0001','Amy',24)"

另外，插入、更新和删除操作都是对数据库进行更改的操作，而更改操作都必须为一个事务，所以这些操作的标准写法就是：

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()  #如果执行失败，就执行数据回滚

因此，最后我们修改程序为：

data=['0001','Amy',24]
sql = 'insert into sample(id,name,age) values(%s,%s,%s) '
try:
    cursor.execute(sql,data)
    db.commit()
    print('成功')
except:
    db.rollback()
    print('失败')
db.close()

插入多条数据

如果有多条数据，我们使用上述方法一条一条录入，这显然是不可能的，在这里就使用到了 cursor.executemany() ：

data=[('0003','Cindy',21),('0004','Bob',32)]
sql = 'insert into sample(id,name,age) values(%s,%s,%s) '
try:
    cursor.executemany(sql,data)
    db.commit()
    print('成功')
except:
    db.rollback()
    print('失败')
db.close()

得到：

如何去重，只添加新增的数据

在某些情况下，我们爬取到的数据含有重复值，如果我们只想要添加新的数据到表中，而不再添加重复数据，我们可以修改sql中的“insert”为“replace”:

可以发现，我们需要录入的数据data中有一条id为0004的数据是重复的，而id为0005的是需要添加到表中的新数据。

data=[('0005','Eve',22),('0004','Bob',32)]
sql = 'replace into sample(id,name,age) values(%s,%s,%s) '
try:
    cursor.executemany(sql,data)
    db.commit()
    print('成功')
except:
    db.rollback()
    print('失败')
db.close()