Python 之 PyMySQL 安装和使用

Python具有内置的SQLite支持。 在本节中，我们将学习使用MySQL的相关概念和知识。 在早期Python版本一般都使用MySQLdb模块，但这个MySQL的流行接口与Python 3不兼容。因此，在教程中将使用PyMySQL模块。

1.什么是PyMySQL？

PyMySQL是从Python连接到MySQL数据库服务器的接口。 它实现了Python数据库API v2.0，并包含一个纯Python的MySQL客户端库。 PyMySQL的目标是成为MySQLdb的替代品。

PyMySQL参考文档：http://pymysql.readthedocs.io/

2.如何安装PyMySQL？

在使用PyMySQL之前，请确保您的机器上安装了PyMySQL。只需在Python脚本中输入以下内容即可执行它 -

2. #!/usr/bin/python3
4. import pymysql

Python

在 Windows 系统上，打开命令提示符 -

1. C:\Users\Administrator>python
2. Python 3.6.1 (v3.6.1:69c0db5, Mar 21 2017, 18:41:36) [MSC v.1900 64 bit (AMD64)] on win32
3. Type "help", "copyright", "credits" or "license" for more information.
4. >>> import PyMySQL
5. Traceback (most recent call last):
6. File "<stdin>", line 1, in <module>
7. ModuleNotFoundError: No module named 'PyMySQL'
8. >>>

Shell

如果产生如上结果，则表示 PyMySQL 模块尚未安装。

最后一个稳定版本可以在PyPI上使用，可以通过 pip 命令来安装-

1. :\Users\Administrator> pip install PyMySQL
2. Collecting PyMySQL
3. Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78kB)
4. 51% |████████████████▋ |
5. 40kB 109kB/s eta 0:0 64% |████████████████████▊
6. | 51kB 112kB/s eta 77% |█████████████████████████ | 61kB 135kB/s 90% |█████████████████████████████
7. | 71kB 152 100% |████████████████████████████████| 81kB 163kB/s
9. Installing collected packages: PyMySQL
10. Successfully installed PyMySQL-0.7.11
12. C:\Users\Administrator>

Shell

或者(例如，如果pip不可用)，可以从GitHub下载tarball，并按照以下方式安装：

1. $ # X.X is the desired PyMySQL version (e.g. 0.5 or 0.6).
2. $ curl -L http://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
3. $ cd PyMySQL*
4. $ python setup.py install
5. $ # The folder PyMySQL* can be safely removed now.

Shell

注意 - 确保具有root权限来安装上述模块。

3.数据库连接

在连接到MySQL数据库之前，请确保以下几点：

• 已经创建了一个数据库： test 。
• 已经在 test 中创建了一个表: employee 。
• employee 表格包含: fist_name ， last_name ， age ， sex 和 income 字段。
• MySQL用户“root”和密码“123456”可以访问： test 。
• Python模块PyMySQL已正确安装在您的计算机上。
• 已经通过MySQL教程了解MySQL基础知识。

创建表 employee 的语句为：

2. CREATE TABLE `employee` (
3. `id` int(10) NOT NULL AUTO_INCREMENT,
4. `first_name` char(20) NOT NULL,
5. `last_name` char(20) DEFAULT NULL,
6. `age` int(11) DEFAULT NULL,
7. `sex` char(1) DEFAULT NULL,
8. `income` float DEFAULT NULL,
9. PRIMARY KEY (`id`)
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL

实例

以下是Python通过PyMySQL模块接口连接MySQL数据库“ test ”的示例 -

注意：在 Windows 系统上， import PyMySQL 和 import pymysql 有区别。

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
13. # execute SQL query using execute() method.
14. cursor.execute("SELECT VERSION()")
16. # Fetch a single row using fetchone() method.
17. data = cursor.fetchone()
19. print ("Database version : %s " % data)
21. # disconnect from server
22. db.close()

Python

运行此脚本时，会产生以下结果 -

Database version : 5.7.14-log

Shell

如果使用数据源建立连接，则会返回连接对象并将其保存到 db 中以供进一步使用，否则将 db 设置为 None 。 接下来， db 对象用于创建一个游标对象，用于执行SQL查询。 最后，在结果打印出来之前，它确保数据库连接关闭并释放资源。

4.创建数据库表

建立数据库连接后，可以使用创建的游标的 execute 方法将数据库表或记录创建到数据库表中。

示例

下面演示如何在数据库： test 中创建一张数据库表： employee -

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
13. # Drop table if it already exist using execute() method.
14. cursor.execute("DROP TABLE IF EXISTS employee")
16. # Create table as per requirement
17. sql = """CREATE TABLE `employee` (
18. `id` int(10) NOT NULL AUTO_INCREMENT,
19. `first_name` char(20) NOT NULL,
20. `last_name` char(20) DEFAULT NULL,
21. `age` int(11) DEFAULT NULL,
22. `sex` char(1) DEFAULT NULL,
23. `income` float DEFAULT NULL,
24. PRIMARY KEY (`id`)
25. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
27. cursor.execute(sql)
28. print("Created table Successfull.")
29. # disconnect from server
30. db.close()

Python

运行此脚本时，会产生以下结果 -

Created table Successfull.

Shell

5.插入操作

当要将记录创建到数据库表中时，需要执行 INSERT 操作。

示例

以下示例执行SQL的 INSERT 语句以在 EMPLOYEE 表中创建一条(多条)记录 -

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
13. # Prepare SQL query to INSERT a record into the database.
14. sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
15. LAST_NAME, AGE, SEX, INCOME)
16. VALUES ('Mac', 'Su', 20, 'M', 5000)"""
17. try:
18. # Execute the SQL command
19. cursor.execute(sql)
20. # Commit your changes in the database
21. db.commit()
22. except:
23. # Rollback in case there is any error
24. db.rollback()
26. ## 再次插入一条记录
27. # Prepare SQL query to INSERT a record into the database.
28. sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
29. LAST_NAME, AGE, SEX, INCOME)
30. VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
31. try:
32. # Execute the SQL command
33. cursor.execute(sql)
34. # Commit your changes in the database
35. db.commit()
36. except:
37. # Rollback in case there is any error
38. db.rollback()
39. print (sql)
40. print('Yes, Insert Successfull.')
41. # disconnect from server
42. db.close()

Python

运行此脚本时，会产生以下结果 -

Yes, Insert Successfull.

Shell

上述插入示例可以写成如下动态创建SQL查询 -

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
13. # Prepare SQL query to INSERT a record into the database.
14. sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
15. LAST_NAME, AGE, SEX, INCOME) \
16. VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
17. ('Max', 'Su', 25, 'F', 2800)
18. try:
19. # Execute the SQL command
20. cursor.execute(sql)
21. # Commit your changes in the database
22. db.commit()
23. except:
24. # Rollback in case there is any error
25. db.rollback()
27. # disconnect from server
28. db.close()

Python

示例

以下代码段是另一种执行方式，可以直接传递参数 -

2. ..................................
3. user_id = "test123"
4. password = "password"
6. con.execute('insert into Login values("%s", "%s")' % \
7. (user_id, password))
8. ..................................

Python

6.读取操作

任何数据库上的读操作表示要从数据库中读取获取一些有用的信息。

在建立数据库连接后，就可以对此数据库进行查询了。 可以使用 fetchone() 方法获取单条记录或 fetchall() 方法从数据库表中获取多个值。

• fetchone() - 它获取查询结果集的下一行。 结果集是当使用游标对象来查询表时返回的对象。

• fetchall() - 它获取结果集中的所有行。 如果已经从结果集中提取了一些行，则从结果集中检索剩余的行。

• rowcount - 这是一个只读属性，并返回受 execute() 方法影响的行数。

示例

以下过程查询 EMPLOYEE 表中所有记录的工资超过 1000 员工记录信息 -

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
12. # 按字典返回
13. # cursor = db.cursor(pymysql.cursors.DictCursor)
15. # Prepare SQL query to select a record from the table.
16. sql = "SELECT * FROM EMPLOYEE \
17. WHERE INCOME > %d" % (1000)
18. #print (sql)
19. try:
20. # Execute the SQL command
21. cursor.execute(sql)
22. # Fetch all the rows in a list of lists.
23. results = cursor.fetchall()
24. for row in results:
25. #print (row)
26. fname = row[1]
27. lname = row[2]
28. age = row[3]
29. sex = row[4]
30. income = row[5]
31. # Now print fetched result
32. print ("name = %s %s,age = %s,sex = %s,income = %s" % \
33. (fname, lname, age, sex, income ))
34. except:
35. import traceback
36. traceback.print_exc()
38. print ("Error: unable to fetch data")
40. # disconnect from server
41. db.close()

Python

1. name = Mac Su,age = 20,sex = M,income = 5000.0
2. name = Kobe Bryant,age = 40,sex = M,income = 8000.0

Shell

7.更新操作

UPDATE语句可对任何数据库中的数据进行更新操作，它可用于更新数据库中已有的一个或多个记录。

以下程序将所有 SEX 字段的值为“ M ”的记录的年龄( age 字段)更新为增加一年。

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. #cursor = db.cursor()
12. cursor = db.cursor(pymysql.cursors.DictCursor)
13. # prepare a cursor object using cursor() method
14. cursor = db.cursor()
16. # Prepare SQL query to UPDATE required records
17. sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
18. WHERE SEX = '%c'" % ('M')
19. try:
20. # Execute the SQL command
21. cursor.execute(sql)
22. # Commit your changes in the database
23. db.commit()
24. except:
25. # Rollback in case there is any error
26. db.rollback()
28. # disconnect from server
29. db.close()

Python

8.删除操作

当要从数据库中删除一些记录时，那么可以执行 DELETE 操作。 以下是删除 EMPLOYEE 中 AGE 超过 40 的所有记录的程序 -

2. #!/usr/bin/python3
3. #coding=utf-8
5. import pymysql
7. # Open database connection
8. db = pymysql.connect("localhost","root","123456","test" )
10. # prepare a cursor object using cursor() method
11. cursor = db.cursor()
13. # Prepare SQL query to DELETE required records
14. sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (40)
15. try:
16. # Execute the SQL command
17. cursor.execute(sql)
18. # Commit your changes in the database
19. db.commit()
20. except:
21. # Rollback in case there is any error
22. db.rollback()
24. # disconnect from server
25. db.close()

Python

9.执行事务

事务是确保数据一致性的一种机制。事务具有以下四个属性 -

• 原子性 - 事务要么完成，要么完全没有发生。
• 一致性 - 事务必须以一致的状态开始，并使系统保持一致状态。
• 隔离性 - 事务的中间结果在当前事务外部不可见。
• 持久性 - 当提交了一个事务，即使系统出现故障，效果也是持久的。

Python DB API 2.0提供了两种提交或回滚事务的方法。

示例

已经知道如何执行事务。 这是一个类似的例子 -

2. # Prepare SQL query to DELETE required records
3. sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
4. try:
5. # Execute the SQL command
6. cursor.execute(sql)
7. # Commit your changes in the database
8. db.commit()
9. except:
10. # Rollback in case there is any error
11. db.rollback()

Python

9.1.COMMIT操作

提交是一种操作，它向数据库发出信号以完成更改，并且在此操作之后，不会更改任何更改。

下面是一个简单的例子演示如何调用 commit() 方法。

2. db.commit()

Python

9.2.回滚操作

如果您对一个或多个更改不满意，并且要完全还原这些更改，请使用 rollback() 方法。

下面是一个简单的例子演示如何调用 rollback() 方法。

2. db.rollback()

Python

10.断开数据库连接

要断开数据库连接，请使用 close() 方法。

2. db.close()

Python

如果用户使用 close() 方法关闭与数据库的连接，则任何未完成的事务都将被数据库回滚。 但是，您的应用程序不会依赖于数据级别的实现细节，而是明确地调用提交或回滚。

11.处理错误

错误有很多来源。一些示例是执行的SQL语句中的语法错误，连接失败或为已取消或已完成语句句柄调用 fetch 方法等等。

DB API定义了每个数据库模块中必须存在的许多错误。下表列出了这些异常和错误 -

Python脚本应该处理这些错误，但在使用任何上述异常之前，请确保您的PyMySQL支持该异常。 可以通过阅读DB API 2.0规范获得更多关于它们的信息。