Python爬虫之在MySQL中存储数据
作者:mmseoamin日期:2023-12-14

MySQL是一个开源的关系型数据库管理系统,被广泛应用于网站开发中的数据存储。在爬虫中,数据的存储是非常重要的一环。下面我们先简单介绍MySQL的基本知识,再讲一下在Python爬虫中如何使用MySQL进行数据存储。

MySQL基本概念

数据库

数据库是存储数据的容器。数据库可以被看做是一个文件夹,其中可以存放各种不同类型的文件,这些文件中包含着我们需要存储的数据。

表是数据库中最小的存储单位,可以看做是一个二维表格,其中包含了若干行和若干列。每一行代表一个数据记录,每一列代表数据记录中的一个属性。

字段

字段是表中最小的存储单元,代表数据记录中的一个属性。

主键

主键是表中用于唯一标识每个数据记录的一列或一组列。

在Python中使用MySQL存储数据

在Python中,可以使用第三方模块pymysql来操作MySQL数据库。具体的使用方法如下所示:

import pymysql
# 连接到数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='test'
)
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句
sql = "SELECT * FROM students;"
cursor.execute(sql)
# 获取查询结果
results = cursor.fetchall()
# 关闭游标和数据库连接
cursor.close()
conn.close()

上面的代码是一个简单的使用pymysql连接MySQL数据库、执行SQL语句、获取查询结果的例子,并没有实际的存储数据。接下来我们以一个爬取豆瓣电影数据的示例来讲解如何使用MySQL来存储数据。

import requests
from bs4 import BeautifulSoup
import pymysql
# 连接到数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='test'
)
# 创建游标对象
cursor = conn.cursor()
# 爬取数据
for i in range(0, 250, 25):
    url = f'https://movie.douban.com/top250?start={i}&filter='
    response = requests.get(url)
    soup = BeautifulSoup(response.text, 'html.parser')
    items = soup.select('.item')
    for item in items:
        # 获取电影名称、评分、导演等数据
        title = item.select_one('.title').text.strip()
        score = item.select_one('.rating_num').text.strip()
        directors = [d.text.strip() for d in item.select('.bd p')[0].select('span')[1].select('a')]
        actors = [a.text.strip() for a in item.select('.bd p')[0].select('span')[3].select('a')]
        time = item.select('.bd p')[0].text.strip().split('\n')[-1]
        
        # 将数据存储到数据库中
        sql = f"INSERT INTO movies (title, score, directors, actors, time) VALUES ('{title}', '{score}', '{','.join(directors)}', '{','.join(actors)}', '{time}');"
        cursor.execute(sql)
        conn.commit()
# 关闭游标和数据库连接
cursor.close()
conn.close()

上面的代码首先使用requests和BeautifulSoup爬取了豆瓣电影排行榜的数据,然后将爬取到的数据存储到MySQL数据库中。我们可以观察到存储数据的主要步骤如下:

  • 连接到MySQL数据库:使用pymysql模块中的connect函数连接到MySQL数据库。
  • 创建游标对象:使用cursor函数创建游标对象。
  • 执行SQL语句:使用游标对象的execute方法执行SQL语句。
  • 提交事务:使用commit方法提交事务。
  • 关闭游标和数据库连接:使用close方法关闭游标对象和数据库连接。

    至此,我们就完成了一个简单的Python爬虫从爬取数据到存储数据的完整过程。在实际开发中,我们还需要注意以下几个问题:

    数据库表的创建

    在存储数据之前,我们需要先在数据库中创建相应的数据表。以上述示例为例,我们需要在MySQL数据库中创建一个名为movies的数据表,并定义相应的字段。可以使用如下的SQL语句进行创建:

    CREATE TABLE movies (
        id INT(11) NOT NULL AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        score FLOAT NOT NULL,
        directors VARCHAR(255) NOT NULL,
        actors VARCHAR(255) NOT NULL,
        time VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    

    上面的代码定义了一个名为movies的数据表,其中包含了以下字段:

    • id:电影的唯一标识符,使用AUTO_INCREMENT关键字定义自增长。
    • title:电影的名称,使用VARCHAR类型定义长度为255的字符类型。
    • score:电影的评分,使用FLOAT类型定义浮点数类型。
    • directors:电影的导演,使用VARCHAR类型定义长度为255的字符类型。
    • actors:电影的主演,使用VARCHAR类型定义长度为255的字符类型。
    • time:电影的上映时间,使用VARCHAR类型定义长度为255的字符类型。
    • PRIMARY KEY:使用id字段作为主键。

      数据库连接的封装

      在实际开发中,我们可以将数据库连接的过程进行封装,以便更好地复用。具体地,可以将连接到数据库的相关代码抽象成一个函数,并将连接信息作为参数进行传递。例如,下面是一个简单的数据库连接函数:

      def connect_to_mysql(host, port, user, password, database):
          conn = pymysql.connect(
              host=host,
              port=port,
              user=user,
              password=password,
              database=database
          )
          return conn
      

      使用上述函数,我们可以以如下的方式连接到MySQL数据库:

      conn = connect_to_mysql('localhost', 3306, 'root', 'root', 'test')
      

      SQL注入的风险

      在使用SQL语句进行数据插入时,我们需要特别谨慎地处理输入数据,避免SQL注入的风险。SQL注入攻击的本质是通过修改SQL语句中的参数,使得SQL语句在执行时产生不良的影响。例如,通过在输入数据中加入特殊字符(如'、;等),攻击者就可以修改原本的SQL语句,从而可能导致数据泄露、损坏等问题。

      为避免SQL注入的风险,我们可以使用参数化查询,将输入数据作为参数绑定到SQL语句中。在使用参数化查询时,我们需要将SQL语句中占位符(如%s)替换为真实的参数,并使用元组或字典来传递参数。例如,下面是一个进行参数化查询的例子:

      # 使用元组传递参数
      sql = "INSERT INTO movies (title, score, directors, actors, time) VALUES (%s, %s, %s, %s, %s);"
      params = ('肖申克的救赎', 9.6, '弗兰克·德拉邦特', '蒂姆·罗宾斯,摩根·弗里曼', '1994年9月10日')
      cursor.execute(sql, params)
      conn.commit()
      # 使用字典传递参数
      sql = "INSERT INTO movies (title, score, directors, actors, time) VALUES (%(title)s, %(score)s, %(directors)s, %(actors)s, %(time)s);"
      params = {'title': '肖申克的救赎', 'score': 9.6, 'directors':'弗兰克·德拉邦特','actors':'蒂姆·罗宾斯,摩根·弗里曼','time':'1994年9月10日'}
      cursor.execute(sql, params)
      conn.commit()

      当然,在实际开发中,为了保证输入数据的合法性和安全性,我们还需要使用其他手段进行数据的校验和过滤。

      ### 数据库连接池的使用

      在多线程或多进程爬虫中,我们需要注意数据库连接的并发问题。具体地,由于MySQL数据库的连接数是有限制的,因此我们需要限制同时打开的连接数,并且在使用后及时关闭连接。

      为更好地管理数据库连接,我们可以使用连接池。连接池中包含了多个数据库连接,可以在需要时从中取出连接,并在使用完毕后将连接放回连接池中。常用的Python连接池模块包括`pymysqlpool`、`DBUtils`等。

      例如,下面是一个使用`DBUtils`模块实现连接池的例子:

      from dbutils.pooled_db import PooledDB
      pool = PooledDB(
          creator=pymysql,
          maxconnections=5,
          maxcached=3,
          host='localhost',
          port=3306,
          user='root',
          password='root',
          database='test'
      )
      conn = pool.connection()
      cursor = conn.cursor()
      # 执行SQL语句
      sql = "SELECT * FROM students;"
      cursor.execute(sql)
      # 获取查询结果
      results = cursor.fetchall()
      # 关闭游标和数据库连接
      cursor.close()
      conn.close()
      

      上面的代码使用DBUtils模块创建了一个最大连接数为5,最大缓存连接数为3的连接池,然后通过pool.connection()取得数据库连接。

      总结

      本文简要介绍了MySQL的基本概念、Python中使用MySQL存储数据的主要步骤,并讨论了一些实际开发中需要注意的问题,包括数据库表的创建、数据库连接的封装、SQL注入的风险和数据库连接池的使用。MySQL作为一款广泛应用的数据库管理系统,在爬虫中的应用也是非常重要的一环,相信通过学习本文,读者可以更好地掌握Python爬虫中如何使用MySQL进行数据存储。