Python 3 – MySQL数据库访问

Python数据库接口的标准是Python DB-API。大多数Python数据库接口都遵循此标准。

您可以选择适合您的应用程序的正确数据库。Python Database API支持各种数据库服务器，例如 −

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

这是可用的Python数据库接口列表 − Python Database Interfaces and APIs 。您必须为每个需要访问的数据库下载单独的DB API模块。例如，如果您需要访问Oracle数据库以及MySQL数据库，则必须下载Oracle和MySQL数据库模块。

DB API提供了一个最小的标准，使用Python结构和语法尽可能地与数据库一起使用。该API包括以下内容 −

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

Python具有对SQLite的内置支持。在本节中，我们将学习使用MySQL的所有概念。MySQLdb模块是与Python不兼容的流行的与MySQL的接口。相反，我们将使用 PyMySQL 模块。

PyMySQL是什么？

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

如何安装PyMySQL？

在进一步进行之前，请确保在计算机上安装了PyMySQL。只需在Python脚本中键入以下内容并执行它即可 −

#!/usr/bin/python3

import pymysql

如果它产生以下结果，则意味着未安装MySQLdb模块−

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

最后的稳定版本可在PyPI上获得并且可以使用pip安装−

pip install pymysql

或者（例如，如果没有pip）可以从 GitHub 下载tarball，并在Setuptools中安装如下−

$ # X.X是所需的pymysql版本（例如0.5或0.6）。
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # 现在可以安全地删除文件夹PyMySQL*。

注意 −确保具有根特权以安装上述模块。

数据库连接

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

您已创建数据库TESTDB。
您已在TESTDB中创建表EMPLOYEE。
该表具有字段FIRST_NAME，LAST_NAME，AGE，SEX和INCOME。
用户ID“testuser”和密码“test123”设置为访问TESTDB。
计算机上正确安装的Python模块PyMySQL。

实例

下面是连接到MySQL数据库“TESTDB”的示例−

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print ("数据库版本 : %s " % data)

# 关闭数据库连接
db.close()

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

数据库版本 : 5.5.20-log

如果与数据源建立了连接，则返回 Connection 对象并将其保存到 db 中以供进一步使用，否则将 db 设置为 None。接下来，使用 db 对象创建一个 cursor 对象，然后使用该对象来执行 SQL 查询。最后，在退出之前，确保关闭数据库连接并释放资源。

创建数据库表

一旦建立数据库连接，我们就可以使用创建的游标的 execute 方法创建表或将记录插入数据库表中。

示例

让我们创建一个名为 EMPLOYEE 的数据库表 –

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 如果 EMPLOYEE 表已经存在，删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据库表，例如
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()

插入操作

插入操作用于将记录插入数据库表中。

示例

以下示例使用 SQL INSERT 语句向 EMPLOYEE 表中创建一条记录 –

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行 SQL 语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()

上面的示例可以使用以下方法动态创建 SQL 查询：

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 动态准备 SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行 SQL 语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()

示例

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

..................................
user_id = "test123"
password = "password"

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

读操作

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

建立数据库连接后，您就可以查询这个数据库了。您可以使用 fetchone() 方法来获取单个记录，也可以使用 fetchall() 方法从数据库表中获取多个值。

fetchone() - 它取出查询结果集的下一行。当光标对象用于查询表时，返回一个结果集对象。
fetchall() - 它获取结果集中的所有行。如果已经从结果集中提取了某些行，则会从结果集中检索剩余的行。
rowcount - 这是一个只读属性，返回由execute（）方法影响的行数。

示例

以下过程查询薪资超过1000美元的EMPLOYEE表中的所有记录-

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor（）方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以将记录插入数据库。
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 从列表中获取所有行。
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 现在打印获取的结果
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# 从服务器断开连接
db.close()

输出

这将产生以下结果 –

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000

更新操作

任何数据库上的UPDATE操作意味着更新一个或多个已经在数据库中的记录。

以下过程更新具有 ‘M’ 性别的所有记录。这里，我们将所有男性的年龄增加一岁。

示例

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以更新所需的记录
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 在数据库中提交更改
   db.commit()
except:
   # 如果有任何错误，则回滚
   db.rollback()

# 从服务器断开连接
db.close()

删除操作

当您想从数据库中删除一些记录时，需要使用DELETE操作。以下是从EMPLOYEE删除年龄大于20的所有记录的过程 –

示例

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor（）方法创建一个游标对象cursor
cursor = db.cursor()

# 准备SQL查询以删除所需的记录
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # 执行SQL命令
   cursor.execute(sql)
   # 在数据库中提交更改
   db.commit()
except:
   # 如果有任何错误，则回滚
   db.rollback()

# 从服务器断开连接
db.close()

执行事务

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

原子性 − 事务要么全部完成，要么就什么也不做。
一致性 − 事务必须在一致的状态下开始，并将系统留在一致的状态下。
隔离性 − 事务的中间结果在当前事务之外是不可见的。
持久性 − 一旦事务提交，其效果是持久的，即使系统发生故障。

Python DB API 2.0 提供了两个方法，即 commit 和 rollback 用于执行事务。

示例

您已经知道如何实现事务。以下是类似的示例 −

# 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()

COMMIT 操作

Commit 是一种操作，它向数据库发送信号以完成更改，在此操作之后，无法撤销任何更改。

以下是调用 commit 方法的简单示例。

db.commit()

ROLLBACK 操作

如果您对一个或多个更改不满意，并希望完全恢复这些更改，请使用 rollback() 方法。

以下是调用 rollback() 方法的简单示例。

db.rollback()

断开数据库连接

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

db.close()

如果用户使用 close() 方法关闭与数据库的连接，则由 DB 回滚所有未完成的事务。但是，与其依赖于任何数据库低级别实现的详细信息，您的应用程序最好明确调用 commit 或 rollback。

处理错误

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

DB API 定义了每个数据库模块必须存在的几个错误。以下表格列出了这些异常。

序号	异常及其说明
1	警告用于非致命问题。必须是StandardError的子类。
2	错误错误的基类。必须是StandardError的子类。
3	接口错误用于数据库模块中的错误，而不是数据库本身的错误。必须是Error的子类。
4	数据库错误用于数据库中的错误。必须是Error的子类。
5	数据错误指数据中的错误，是DatabaseError的子类。
6	操作错误指诸如与数据库的连接丢失之类的错误。这些错误通常不受Python脚本程序员的控制。
7	完整性错误对于可能损坏关系完整性的情况，如唯一性约束或外键，属于DatabaseError的子类。
8	内部错误指数据库模块内部的错误，如光标不再有效。属于DatabaseError的子类。
9	编程错误指如错误的表名等原因导致的错误和其它人为造成的错误。属于DatabaseError的子类。
10	不支持的错误指调用不支持的功能。属于DatabaseError的子类。