上一节:
下一节:

  Python 3 - MySQL 数据库访问

  • 简述

    数据库接口的 Python 标准是 Python DB-API。大多数 Python 数据库接口都遵循这个标准。
    您可以为您的应用程序选择合适的数据库。Python 数据库 API 支持广泛的数据库服务器，例如 -
    • GadFly
    • mSQL
    • MySQL
    • PostgreSQL
    • 微软 SQL Server 2000
    • Informix
    • Interbase
    • Oracle
    • Sybase
    • SQLite
    这是可用的 Python 数据库接口列表 - Python 数据库接口和 API。您必须为每个需要访问的数据库下载单独的 DB API 模块。例如，如果您需要访问 Oracle 数据库和 MySQL 数据库，则必须同时下载 Oracle 和 MySQL 数据库模块。
    DB API 为尽可能使用 Python 结构和语法处理数据库提供了最低标准。该 API 包括以下内容 -
    • 导入 API 模块。
    • 获取与数据库的连接。
    • 发出 SQL 语句和存储过程。
    • 关闭连接
    Python 内置了对 SQLite 的支持。在本节中，我们将学习使用 MySQL 的所有概念。MySQLdb 模块，一个流行的 MySQL 接口与 Python 3 不兼容。相反，我们将使用PyMySQL模块。

  • 什么是 PyMySQL？

    PyMySQL 是一个用于从 Python 连接到 MySQL 数据库服务器的接口。它实现了 Python 数据库 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 is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.
    Note− 确保您具有安装上述模块的root 权限。

  • 数据库连接

    在连接到 MySQL 数据库之前，请确保以下几点 -
    • 您已经创建了一个数据库 TESTDB。
    • 您已经在 TESTDB 中创建了一个表 EMPLOYEE。
    • 该表包含 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME 字段。
    • 设置用户ID“testuser”和密码“test123”访问TESTDB。
    • Python 模块 PyMySQL 已正确安装在您的机器上。
    • 您已经完成了 MySQL 教程以了解MySQL 基础知识。

    例子

    以下是连接 MySQL 数据库“TESTDB”的示例 - 
    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 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.5.20-log
    如果与数据源建立了连接，则返回一个连接对象并保存到db供进一步使用，否则db设置为无。下一个，db对象用于创建一个cursor对象，它又用于执行 SQL 查询。最后，在出来之前，它确保关闭数据库连接并释放资源。

  • 创建数据库表

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

    例子

    让我们创建一个数据库表 EMPLOYEE - 
    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 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 (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""
cursor.execute(sql)
# disconnect from server
db.close()

  • 插入操作

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

    例子

    以下示例执行 SQL INSERT语句以在 EMPLOYEE 表中创建一条记录 - 
    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 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', 'Mohan', 20, 'M', 2000)"""
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()
    上面的例子可以写成如下动态创建 SQL 查询 - 
    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 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 ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
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()

    例子

    以下代码段是另一种执行形式，您可以在其中直接传递参数 - 
    
..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

  • 读操作

    READ 对任何数据库的操作都意味着从数据库中获取一些有用的信息。
    建立数据库连接后，您就可以查询该数据库了。您可以使用fetchone()获取单个记录的方法或fetchall()从数据库表中获取多个值的方法。
    • fetchone()− 它获取查询结果集的下一行。结果集是使用游标对象查询表时返回的对象。
    • fetchall()− 它获取结果集中的所有行。如果已经从结果集中提取了一些行，则它会从结果集中检索剩余的行。
    • rowcount− 这是一个只读属性，返回受 execute() 方法影响的行数。

    例子

    以下过程查询 EMPLOYEE 表中薪水超过 1000 的所有记录 - 
    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
# disconnect from server
db.close()

    输出

    这将产生以下结果 - 
    
fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000

  • 更新操作

    UPDATE 对任何数据库的操作意味着更新一条或多条记录，这些记录已经存在于数据库中。
    以下过程将所有具有 SEX 的记录更新为'M'. 在这里，我们将所有男性的 AGE 增加一岁。

    例子

    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 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()

  • 删除操作

    当您要从数据库中删除某些记录时，需要执行 DELETE 操作。以下是从 AGE 超过 20 岁的 EMPLOYEE 中删除所有记录的程序 -

    例子

    
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# 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()
# disconnect from server
db.close()

  • 执行事务

    事务是一种确保数据一致性的机制。事务具有以下四个属性 -
    • Atomicity− 要么事务完成，要么什么都没有发生。
    • Consistency− 事务必须以一致状态开始并使系统保持一致状态。
    • Isolation− 事务的中间结果在当前事务之外是不可见的。
    • Durability− 一旦事务被提交，其影响是持久的，即使在系统故障之后也是如此。
    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()

  • 提交操作

    Commit 是一个操作，它向数据库发出绿色信号以完成更改，并且在此操作之后，无法还原任何更改。
    这是一个简单的例子来调用commit方法。 
    
db.commit()

  • 回滚操作

    如果您对一项或多项更改不满意，并且想完全还原这些更改，请使用rollback()方法。
    这是一个简单的例子来调用rollback()方法。 
    
db.rollback()

  • 断开数据库

    要断开数据库连接，请使用 close() 方法。 
    
db.close()
    如果用户使用 close() 方法关闭与数据库的连接，则数据库将回滚任何未完成的事务。但是，与其依赖于任何数据库较低级别的实现细节，您的应用程序最好显式调用提交或回滚。

  • 处理错误

    错误的来源有很多。一些示例是执行的 SQL 语句中的语法错误、连接失败或为已取消或已完成的语句句柄调用 fetch 方法。
    DB API 定义了每个数据库模块中必须存在的一些错误。下表列出了这些例外情况。
    序号 异常与说明
    1
    Warning
    用于非致命问题。必须继承 StandardError。
    2
    Error
    错误的基类。必须继承 StandardError。
    3
    InterfaceError
    用于数据库模块中的错误，而不是数据库本身。必须子类错误。
    4
    DatabaseError
    用于数据库中的错误。必须子类错误。
    5
    DataError
    DatabaseError 的子类，指的是数据中的错误。
    6
    OperationalError
    DatabaseError 的子类，指的是诸如与数据库的连接丢失之类的错误。这些错误通常不在 Python 脚本编写者的控制范围内。
    7
    IntegrityError
    DatabaseError 的子类，用于会破坏关系完整性的情况，例如唯一性约束或外键。
    8
    InternalError
    DatabaseError 的子类，指的是数据库模块内部的错误，例如游标不再处于活动状态。
    9
    ProgrammingError
    DatabaseError 的子类，指的是错误的表名和其他可以安全地归咎于您的事情。
    10
    NotSupportedError
    DatabaseError 的子类，指的是尝试调用不受支持的功能。
    您的 Python 脚本应该处理这些错误，但在使用上述任何异常之前，请确保您的 MySQLdb 支持该异常。您可以通过阅读 DB API 2.0 规范获得有关它们的更多信息。
上一节:
下一节: