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脚本中输入以下内容即可执行它 -

1.  
    
2.  
   #!/usr/bin/python3
3.  
    
4.  
   import pymysql
5.  
    

Python

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

1.  
   C:UsersAdministrator>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.  
   :UsersAdministrator> 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
8.  
    
9.  
   Installing collected packages: PyMySQL
10.  
    Successfully installed PyMySQL-0.7.11
11.  
     
12.  
    C:UsersAdministrator>

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的语句为：

1.  
    
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;
11.  
     

SQL

实例

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

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

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

Python

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

Database version : 5.7.14-log

Shell

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

4.创建数据库表

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

示例

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

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
10.  
    # prepare a cursor object using cursor() method
11.  
    cursor = db.cursor()
12.  
     
13.  
    # Drop table if it already exist using execute() method.
14.  
    cursor.execute("DROP TABLE IF EXISTS employee")
15.  
     
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;"""
26.  
     
27.  
    cursor.execute(sql)
28.  
    print("Created table Successfull.")
29.  
    # disconnect from server
30.  
    db.close()
31.  
     

Python

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

Created table Successfull.

Shell

5.插入操作

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

示例

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

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
10.  
    # prepare a cursor object using cursor() method
11.  
    cursor = db.cursor()
12.  
     
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()
25.  
     
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()
43.  
     

Python

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

Yes, Insert Successfull.

Shell

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

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
10.  
    # prepare a cursor object using cursor() method
11.  
    cursor = db.cursor()
12.  
     
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()
26.  
     
27.  
    # disconnect from server
28.  
    db.close()
29.  
     

Python

示例

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

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

Python

6.读取操作

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

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

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

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

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

示例

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

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
10.  
    # prepare a cursor object using cursor() method
11.  
    cursor = db.cursor()
12.  
    # 按字典返回
13.  
    # cursor = db.cursor(pymysql.cursors.DictCursor)
14.  
     
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()
37.  
     
38.  
    print ("Error: unable to fetch data")
39.  
     
40.  
    # disconnect from server
41.  
    db.close()
42.  
     

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字段)更新为增加一年。

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
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()
15.  
     
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()
27.  
     
28.  
    # disconnect from server
29.  
    db.close()
30.  
     

Python

8.删除操作

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

1.  
    
2.  
   #!/usr/bin/python3
3.  
   #coding=utf-8
4.  
    
5.  
   import pymysql
6.  
    
7.  
   # Open database connection
8.  
   db = pymysql.connect("localhost","root","123456","test" )
9.  
    
10.  
    # prepare a cursor object using cursor() method
11.  
    cursor = db.cursor()
12.  
     
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()
23.  
     
24.  
    # disconnect from server
25.  
    db.close()
26.  
     

Python

9.执行事务

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

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

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

示例

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

1.  
    
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()
12.  
     

Python

9.1.COMMIT操作

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

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

1.  
    
2.  
   db.commit()
3.  
    

Python

9.2.回滚操作

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

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

1.  
    
2.  
   db.rollback()
3.  
    

Python

10.断开数据库连接

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

1.  
    
2.  
   db.close()
3.  
    

Python

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

11.处理错误

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

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

编号	异常	描述
1	Warning	用于非致命问题，是StandardError的子类。
2	Error	错误的基类，是StandardError的子类。
3	InterfaceError	用于数据库模块中的错误，但不是数据库本身，是Error的子类。
4	DatabaseError	用于数据库中的错误，是Error的子类。
5	DataError	DatabaseError的子类引用数据中的错误。
6	OperationalError	DatabaseError的子类，涉及如丢失与数据库的连接等错误。 这些错误通常不在Python脚本程序的控制之内。
7	IntegrityError	DatabaseError 子类错误，可能会损害关系完整性，例如唯一性约束和外键。
8	InternalError	DatabaseError的子类，指的是数据库模块内部的错误，例如游标不再活动。
9	ProgrammingError	DatabaseError的子类，它引用错误，如错误的表名和其他安全。
10	NotSupportedError	DatabaseError的子类，用于尝试调用不支持的功能。

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