Python3数据存储之MySQL存储

惯例，开篇一张图，内容全靠编！！

通常我们在运行Python爬虫脚本时经常需要将爬取到的数据信息保存到本地做持久化存储，常见的存储方式有直接保存到文本文件和存储到数据库这两种。比如说我们写个爬虫脚本去爬取猫眼电影上的前100部影片的信息，我们可以将爬取到的信息保存为csv文件，这种文件可以直接通过excel软件打开查看，还可以将爬取的信息格式化成json格式数据直接保存到TXT文本文件中，这样爬取的数据就轻松实现了本地持久化存储的目的。今天我们来聊聊python3是如何利用MySQL数据库来存储爬取到的数据的。

MySQL是一种关系型数据库，同样属关系型数据库的还有SQLite、Oracle、SQL Server、DB2等。(后期我们会讲另一种非关系型数据的存储，如MongoDB)至于为何选择python3而不是python2来讲，是因为Python2实在是太TMD难用了，同时在Python 2中，连接MySQL的库大多是使用MySQLdb，但是此库的官方并不支持Python 3，所以这里推荐使用的库是PyMySQL。好了，废话就不多啰嗦了，开始正文！

---

1. 安装MySQL数据库
首先请移玉步至https://dev.mysql.com/downloads/mysql/页面下

MySQL下载界面
下载MySQL免安装版本。将下载得到的压缩包解压到你钟意的目录下，解压后的文件如下图所示：

我这里的data和my.ini文件是后期生成的，不要怀疑你下错安装包了，解压的时候是没有这两个文件的。
接下来我们启动下MySQL数据库。以管理员的身份打开cmd命令行工具，有些小伙伴可能直接右键选择以管理员权限运行bin目录下的mysql.exe可执行程序，结果终端会一闪而过就关闭了。Why？？
对，就是因为你的打开方式不对！直接到C:\Windows\System32目录下点击cmd.exe就OK了！！额，好像又扯远了....

• 打开终端后切换目录：
  cd D:\mysql-8.0.11-winx64\bin
• 再输入安装命令：
  mysqld install
• 初始化data目录，执行下面这句命令你就会在你的mysql安装目录下看到data目录了：
  mysqld --initialize-insecure
• 启动mysql：
  net start mysql
  好，估计这会你的终端上显示mysql启动成功了。接下来在安装目录下新建个my.ini配置文件，内容如下：

[mysqld]
character-set-server=utf8mb4
#绑定IPv4和3306端口
bind-address = 0.0.0.0
port = 3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=1000M
innodb_log_file_size=50M
# 设置mysql的安装目录
# basedir=D:\mysql-8.0.11-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-8.0.11-winx64\data
# 允许最大连接数
max_connections=200
# skip_grant_tables
[mysql]
default-character-set=utf8mb4
[mysql.server]
default-character-set=utf8mb4
[mysql_safe]
default-character-set=utf8mb4
[client]
port = 3306
plugin-dir=D:\mysql-8.0.11-winx64\lib\plugin

将里面目录替换成你自己的安装目录，这个应该不要我强调的吧！！

---

2. 连接MySQL
上文提到过python3连接mysql要用到中间组件pymysql，我们先下载这个组件：
pip install pymysql
安装完这个组件后我们就可以测试是否能正常连接到mysql数据库。测试代码如下：

import pymysql

def create_db():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306)
    cusor = db.cursor()
    cusor.execute('select version()')
    data = cusor.fetchone()
    print('Database version:', data)
    cusor.execute('create database test DEFAULT CHARACTER SET utf8mb4')
    db.close()

• 由于当前的MySQL运行在本地，所以host为localhost,否则就设置成你要连接的公网ip地址；
• 用户名为root；
• 由于我们在安装mysql的时候初始化时执行了mysqld --initialize-insecure这句命令，这句命令执行后mysql的密码就缺省为``；
• 运行端口为之前my.ini文件中配置的port数值，默认是3306。
• 创建一个新的数据库，名字叫作test。

连接成功后，我们需要再调用 cursor()方法获得MySQL的操作游标，利用游标来执行SQL语句。

• cusor.execute('select version()')用于获得当前安装在本机的MySQL版本;
• data = cusor.fetchone()调用 fetchone()方法获得第一条数据，也就得到了版本号。
• cusor.execute('create database test DEFAULT CHARACTER SET utf8mb4')执行创建数据库的操作，数据库名叫作test，默认编码为utf8mb4，这个编码规则支持中文显示。

执行后就成功创建了数据库test。接下来我们就可以利用这个数据库进行后续的操作了。运行结果如下：
Database version: ('8.0.11',)
一般创建数据库的操作只需要执行一遍就OK了。当然，你也可以直接在cmd命令行下直接创建数据库，只要you like!!

---

3. 创建表
接下来，我们新创建一个数据表books，这里指定4个字段，结构如下所示。

字段名	含义	类型
id	编号	varchar
title	书名	varchar
author	作者	varchar
date	出版日期	varchar

吐槽下：用Markdown建表格的操作真麻烦，草！！！

创建该表的参考代码如下：

import pymysql
def create_table():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cusor = db.cursor()
    sql = 'create table if not exists books(id VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL,author VARCHAR(255) NOT NULL, date VARCHAR(255), PRIMARY KEY (id))'
    cusor.execute(sql)
    db.close()

执行代码后就在test数据库中创建了一个名为books的数据表了。在cmd命令下执行desc books，结果如下：

现在数据库及数据表均已创建成功，下面我们就来谈谈操作数据库的四大操作：增、删、改、查。

• 增加数据
  增加数据即通常所说的插入数据。现在我们要向数据库中插入一条book信息，{'id':'201801','title':'python','author':'Sam','date':NOW()}，该如何插入呢？参考代码如下：

def gener_book_insert():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cursor = db.cursor()
    data = {
        'id': '201801',
        'title': 'Python',
        'author': 'Sam',
        'date': '2016/05'
    }
    table = 'books'
    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('insert successful')
            db.commit()
    except Exception as e:
        print("insert failed!", e)
        db.rollback()
    db.close()

可能小伙伴们已经发现了上面的代码使用了字典来作为变量，这样的好处是我们不必去更改我们的插入方法，通俗点说就是这个插入方法我们已经做成了一个通用的方法，这样我们只需传入一个变化的字典就行了。
首先，需要构造插入的字段 id 、 title 、 author和date。这里只需要将data 的键名拿过来，然后用逗号分隔即可。所以 ', '.join(data.keys()) 的结果就是 id, title, author, date，然后需要构造多个 %s当作占位符，有几个字段构造几个即可。如，这里有4个字段，就需要构造%s, %s, %s，%s 。这里首先定义了长度为1的数组 ['%s'] ，然后用乘法将其扩充为 ['%s', '%s', '%s', '%s'] ，再调用 join() 方法，最终变成 %s, %s, %s, %s 。最后，我们再利用字符串的 format() 方法将表名、字段名和占位符构造出来。最终的SQL语句就被动态构造成了：
insert into books(id, title, author, date) values(%s, %s, %s, %s)
接下来执行execute()方法的第一个参数传入 sql 变量，第二个参数传入 data 的键值构造的元组，就可以成功插入数据了。执行完后结果如下：

插入数据成功
这时有小伙伴就要问了，你这只是插入一条数据而已，要是插入多条数据？你这还能正常运行不？
能！！前提是我们要对上面的方法进行一下稍微的改动，参考代码如下：

def gener_book_insert():
    data = [
        {
            'id': '201801',
            'title': 'Python',
            'author': 'Tom',
            'date': '2016/05'
        },
        {
            'id': '201802',
            'title': 'Android',
            'author': 'Tom',
            'date': '2016/08'
        },
        {
            'id': '201803',
            'title': 'PHP',
            'author': 'Sam',
            'date': '2018/05'
        }
    ]
    table = 'books'

    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cursor = db.cursor()
    for item in data:
        keys = ','.join(item.keys())
        values = ','.join(['%s'] * len(item))
        sql = 'insert into {table}({keys}) VALUES({values})'.format(table=table, keys=keys, values=values)
        try:
            if cursor.execute(sql, tuple(item.values())):
                print('insert successful')
                db.commit()
        except Exception as e:
            print("insert failed!", e)
            db.rollback()
    db.close()

注意到没，data已经不是之前的字典了，而是一个list列表了，列表中包含有3个字典，同时我们将插入的部分代码也进行了微调，使用迭代对list中的数据进行遍历取出所需数据进行逐条插入到数据库。执行后的结果如下：

多条数据插入
是不是解决了多条数据插入的问题？？！！

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

try:
  cursor.execute(sql)
  db.commit()
except Exception as e:
  print('Failed...', e)
  db.rollback()

需要执行db对象的commit()方法才能将数据插入，这个方法才是真正将语句提交到数据库执行的方法。对于数据插入、更新、删除操作，都需要调用该方法才能生效。如果插入失败则执行rollback()将数据回滚，就相当于啥都没发生。

• 更新数据
  通常在实际的数据抓取过程中，大部分情况下需要插入数据，但是有时候我们会发现抓取的数据出现重复数据。这时我们就希望更新数据而不是重复保存一次。这里可以再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。另外，这种做法支持灵活的字典传值。参考代码如下：

def update_book_data():
    data = {
        'id': '201801',
        'title': 'Python',
        'author': 'Tom',
        'date': '2016/05'
    }
    table = 'books'

    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cursor = db.cursor()
    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("successful!")
            db.commit()
    except Exception as e:
        print('failed!', e)
        db.rollback()
    db.close()

这里的SQL语句其实是一个插入拼接语句，但是我们在后面加了ON DUPLICATE KEY UPDATE。这行代码的意思是如果主键已经存在，就执行更新操作。如现在我们将上条插入的数据的author改为Tom,此时这条数据是不会插入的，而是直接根据id来更新数据。拼接后的SQL语句相当于：
INSERT INTO books(id, title, author, date) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, title= %s, author= %s, date=%s
注意到上面的sql语句里面出现了8个%s了没？这就是为什么在上面的代码中有if cursor.execute(sql, tuple(data.values()) * 2)这句中的*2的原因。
上面的代码就实现了主键不存在便出入新数据，否则就更新数据的功能，轻松实现数据去重的功能，是不是so easy??
执行结果如下：

更新数据

• 删除数据
  删除操作相对简单，直接使用 DELETE 语句即可，只是需要指定要删除的目标表名和删除条件，而且仍然需要使用db的commit()方法才能生效。参考代码如下：

def delete_data():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cursor = db.cursor()
    table = 'books'
    condition = 'author="Tom"'
    sql = 'delete from {table} where {condition}'.format(table=table, condition=condition)
    try:
        cursor.execute(sql)
        db.commit()
        print('delete data successful!')
    except Exception as e:
        print('delete data failed!')
        db.rollback()
    db.close()

上面的代码中直接将条件当作字符串来传递，轻松实现删除表中数据的目的。当然，删除的条件还有好多种，比如有大于、小于、LIKE、AND、OR等等，这里就不逐个示例了，有兴趣的又有时间的可以试试！！

• 查询数据
  讲了那么多终于到最后一个了，查询操作就很简单了，直接贴参考代码吧！代码如下：

def query_data():
    db = pymysql.connect(host='localhost', user='root', password='', port=3306, db='test')
    cursor = db.cursor()
    table = 'books'
    condition = 'author="Tom"'
    sql = 'select * from {table} where {condition}'.format(table=table, condition=condition)

    try:
        cursor.execute(sql)
        print('count:', cursor.rowcount)
        row = cursor.fetchone()
        while row:
            print("One:", row)
            row = cursor.fetchone()
        results = cursor.fetchall()
        print('result:', results)
        print('result type:', type(results))
        for row in results:
            print(row)
    except Exception as e:
        print('query failed!', e)
    db.close()

这里要注意的是fetchone()方法，这个方法可以获取结果的第一条数据，返回结果是元组形式，元组的元素顺序跟字段一一对应，即第一个元素就是第一个字段id ，第二个元素就是第二个字段 title ，以此类推。而fetchall()方法，它可以得到结果的所有数据，结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。然后将其结果和类型打印出来，它是二重元组，每个元素都是一条记录。
用while循环加fetchone()方法来获取所有数据，而不是用fetchall()全部一起获取出来。 fetchall()会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。执行的结果如下：

查询数据

• 好了，讲了这么多废话终于把这个讲完了，写文章真的很费时间，比撸代码还难，因为你得确保你讲的东西别人能看的懂，能理解，最好还能复现！！！

  
  good good study, day day up!!