Python+MySQL数据库操作

朴灿烈づ我的快乐病毒、 2022-06-13 04:12 256阅读 0赞

Python的数据库接口标准是Python DB-API。大多数Python数据库接口遵循这个标准。  
可以为应用程序选择正确的数据库。Python数据库API支持广泛的数据库服务器，如 -

*  GadFly
 *  mSQL
 *  [MySQL][]
 *  [PostgreSQL][]
 *  Microsoft SQL Server 2000
 *  Informix
 *  Interbase
 *  [Oracle][]
 *  Sybase
 *  [SQLite][]

以下是可用的Python数据库接口 - [Python数据库接口和API][Python_API]的列表。必须为需要访问的每个数据库下载一个单独的DB API模块。 例如，如果需要访问Oracle数据库和MySQL数据库，则必须同时下载Oracle和MySQL数据库模块。

DB API为尽可能使用Python结构和语法处理数据库提供了最低标准。API包括以下内容：

*  导入API模块。
 *  获取与数据库的连接。
 *  发出SQL语句和存储过程。
 *  关闭连接

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

## 1.什么是PyMySQL？ ##

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

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

## 2.如何安装PyMySQL？ ##

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

#!/usr/bin/python3
    
    import PyMySQL

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

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

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

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

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

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

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

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

## 3.数据库连接 ##

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

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

创建表`employee`的语句为：

CREATE TABLE `employee` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `first_name` char(20) NOT NULL,
      `last_name` char(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `sex` char(1) DEFAULT NULL,
      `income` float DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**实例**

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

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

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

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

Database version : 5.7.14-log

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

## 4.创建数据库表 ##

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

**示例**

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

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

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

Created table Successfull.

## 5.插入操作 ##

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

**示例**

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

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    # Open database connection
    db = pymysql.connect("localhost","root","123456","test" )
    
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    
    # Prepare SQL query to INSERT a record into the database.
    sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
       LAST_NAME, AGE, SEX, INCOME)
       VALUES ('Mac', 'Su', 20, 'M', 5000)"""
    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()
    
    ## 再次插入一条记录
    # Prepare SQL query to INSERT a record into the database.
    sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
       LAST_NAME, AGE, SEX, INCOME)
       VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()
    print (sql)
    print('Yes, Insert Successfull.')
    # disconnect from server
    db.close()

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

Yes, Insert Successfull.

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

**示例**

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

..................................
    user_id = "test123"
    password = "password"
    
    con.execute('insert into Login values("%s", "%s")' % \
                 (user_id, password))
    ..................................

## 6.读取操作 ##

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

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

*  `fetchone()` \- 它获取查询结果集的下一行。 结果集是当使用游标对象来查询表时返回的对象。
 *  `fetchall()` \- 它获取结果集中的所有行。 如果已经从结果集中提取了一些行，则从结果集中检索剩余的行。
 *  `rowcount` \- 这是一个只读属性，并返回受`execute()`方法影响的行数。

**示例**

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

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    # Open database connection
    db = pymysql.connect("localhost","root","123456","test" )
    
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    # 按字典返回 
    # cursor = db.cursor(pymysql.cursors.DictCursor)
    
    # Prepare SQL query to select a record from the table.
    sql = "SELECT * FROM EMPLOYEE \
           WHERE INCOME > %d" % (1000)
    #print (sql)
    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Fetch all the rows in a list of lists.
       results = cursor.fetchall()
       for row in results:
          #print (row)
          fname = row[1]
          lname = row[2]
          age = row[3]
          sex = row[4]
          income = row[5]
          # Now print fetched result
          print ("name = %s %s,age = %s,sex = %s,income = %s" % \
                 (fname, lname, age, sex, income ))
    except:
       import traceback
       traceback.print_exc()
    
       print ("Error: unable to fetch data")
    
    # disconnect from server
    db.close()

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

## 7.更新操作 ##

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

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

#!/usr/bin/python3
    #coding=utf-8
    
    import pymysql
    
    # Open database connection
    db = pymysql.connect("localhost","root","123456","test" )
    
    # prepare a cursor object using cursor() method
    #cursor = db.cursor()
    cursor = db.cursor(pymysql.cursors.DictCursor)
    # prepare a cursor object using cursor() method
    cursor = db.cursor()
    
    # Prepare SQL query to UPDATE required records
    sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
                              WHERE SEX = '%c'" % ('M')
    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()
    
    # disconnect from server
    db.close()

## 8.删除操作 ##

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

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

## 9.执行事务 ##

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

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

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

**示例**

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

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

### 9.1.COMMIT操作 ###

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

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

db.commit()

### 9.2.回滚操作 ###

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

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

db.rollback()

## 10.断开数据库连接 ##

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

db.close()

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

## 11.处理错误 ##

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

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

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

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

[MySQL]: http://www.yiibai.com/mysql
[PostgreSQL]: http://www.yiibai.com/postgresql
[Oracle]: http://www.yiibai.com/oracle
[SQLite]: http://www.yiibai.com/sqlite
[Python_API]: http://wiki.python.org/moin/DatabaseInterfaces
[PyMySQL]: http://github.com/PyMySQL/PyMySQL/
[http_pymysql.readthedocs.io]: http://pymysql.readthedocs.io/