Python信息采集器使用关系型数据库MySQL

1，引言
之前有一篇文章：Python信息采集器使用轻量级关系型数据库SQLite，介绍了通过sqlite3库来对SQLite进行各种操作。
本文讲一下用Python来操作Mysql数据库。
MySQL是最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

Python库pymysql3提供python3访问MySQL数据库的接口。
Python3下安装该库的方法:   easy_install pymysql3

2，Python对MySQL进行操作示例


以下的代码将创建一个简单的关系型数据库，为一个书店存储书的分类和价格。数据库中包含两个表：category用于记录分类，book用于记录某本书的信息。一本书归属于某一个分类，因此book有一个外键(foreign key)，指向catogory表的主键id。

2.1 创建数据库

首先，创建数据库，以及数据库中的表。在使用connect()连接数据库后，就可以通过定位指针cursor，来执行SQL命令：

1. import pymysql3
   
2. 
   
3. # 连接和操作的数据库名为test
   
4. conn = pymysql.connect(host="localhost",user="root",passwd="root", db='test', charset="utf8")
   
5. 
   
6. c = conn.cursor()
   
7. 
   
8. # create tables
   
9. c.execute('''CREATE TABLE category
   
10.       (id int primary key, sort int, name text)''')
    
11. c.execute('''CREATE TABLE book
    
12.       (id int primary key,
    
13.        sort int,
    
14.        name text,
    
15.        price real,
    
16.        category int,
    
17.        FOREIGN KEY (category) REFERENCES category(id))''')
    
18. 
    
19. # save the changes
    
20. conn.commit()
    
21. 
    
22. # close the connection with the database
    
23. conn.close()

复制代码

利用execute()命令，执行了两个SQL命令，创建数据库中的两个表。创建完成后，保存并断开数据库连接。

2.2 插入数据

上面创建了数据库和表，确立了数据库的抽象结构。下面将在同一数据库中插入数据：

1. import pymysql3
   
2. 
   
3. # 连接和操作的数据库名为test
   
4. conn = pymysql.connect(host="localhost",user="root",passwd="root", db='test', charset="utf8")
   
5. c  = conn.cursor()
   
6. 
   
7. books = [(1, 1, 'Cook Recipe', 3.12, 1),
   
8.             (2, 3, 'Python Intro', 17.5, 2),
   
9.             (3, 2, 'OS Intro', 13.6, 2),
   
10.            ]
    
11. 
    
12. # execute "INSERT"
    
13. c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")
    
14. 
    
15. # using the placeholder
    
16. c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])
    
17. 
    
18. # execute multiple commands
    
19. c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)
    
20. 
    
21. conn.commit()
    
22. conn.close()

复制代码

插入数据同样可以使用execute()来执行完整的SQL语句。SQL语句中的参数，使用"?"作为替代符号，并在后面的参数中给出具体值。这里不能用Python的格式化字符串，如"%s"，因为这一用法容易受到SQL注入攻击。

也可以用executemany()的方法来执行多次插入，增加多个记录。每个记录是表中的一个元素，如上面的books表中的元素。

2.3 查询

在执行查询语句后，Python将返回一个循环器，包含有查询获得的多个记录。循环读取，也可以使用sqlite3提供的fetchone()和fetchall()方法读取记录：

1. import pymysql3
   
2. 
   
3. # 连接和操作的数据库名为test
   
4. conn = pymysql.connect(host="localhost",user="root",passwd="root", db='test', charset="utf8")
   
5. c  = conn.cursor()
   
6. 
   
7. # retrieve one record
   
8. c.execute('SELECT name FROM category ORDER BY sort')
   
9. print(c.fetchone())
   
10. print(c.fetchone())
    
11. 
    
12. # retrieve all records as a list
    
13. c.execute('SELECT * FROM book WHERE book.category=1')
    
14. print(c.fetchall())
    
15. 
    
16. # iterate through the records
    
17. for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    
18.     print(row)
    

复制代码

2.4 更新与删除

可以更新某个记录，或者删除记录：

1. import pymysql3
   
2. 
   
3. # 连接和操作的数据库名为test
   
4. conn = pymysql.connect(host="localhost",user="root",passwd="root", db='test', charset="utf8")
   
5. c  = conn.cursor()
   
6. 
   
7. c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
   
8. c.execute('DELETE FROM book WHERE id=2')
   
9. 
   
10. conn.commit()
    
11. conn.close()

复制代码

也可以直接删除整张表：

1. c.execute('DROP TABLE book')

复制代码

3，总结

pymysql3是一个MySQL的接口。想要熟练的使用MySQL数据库，需要学习关系型数据库的知识。在一些场景下，Python网络爬虫可以使用MySQL存储采集到的网页信息。
GooSeeker集搜客网络爬虫提供的数据云存储功能，使用了包括MySQL，MongoDB在内的多种数据库引擎。

4，文档修改历史
2016-09-09：V1.0，首次发布

有同学问python访问mysql的问题，这篇把SQLite存取方法的那篇文章改写了一下