Database Access

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

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

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

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

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

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

我们将学习使用MySQL的所有概念，所以让我们来谈谈MySQLdb模块。

什么是MySQLdb？

MySQLdb是用于从Python连接到MySQL数据库服务器的接口。 它实现了Python Database API v2.0，并构建在MySQL C API之上。

如何安装MySQLdb？

在继续之前，请确保在您的计算机上安装了MySQLdb。 只需在Python脚本中键入以下内容并执行它 -

#!/usr/bin/python
import MySQLdb

如果它产生以下结果，那么它意味着没有安装MySQLdb模块 -

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

要安装MySQLdb模块，请使用以下命令 -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Note - 确保您具有root权限以安装上述模块。

数据库连接 (Database Connection)

在连接MySQL数据库之前，请确保以下内容 -

• 您已经创建了一个数据库TESTDB。

• 您已在TESTDB中创建了一个表EMPLOYEE。

• 此表包含FIRST_NAME，LAST_NAME，AGE，SEX和INCOME字段。

• 用户ID“testuser”和密码“test123”设置为访问TESTDB。

• Python模块MySQLdb已正确安装在您的计算机上。

• 您已经通过MySQL教程来了解MySQL基础知识。

例子 (Example)

以下是连接MySQL数据库“TESTDB”的示例

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> method
cursor = db.cursor()
# execute SQL query using <i>execute()</i> method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using <i>fetchone()</i> method.
data = cursor.fetchone()
print "Database version : %s " % data
# disconnect from server
db.close()

运行此脚本时，它在我的Linux机器上产生以下结果。

Database version : 5.0.45

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

创建数据库表

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

例子 (Example)

让我们创建数据库表EMPLOYEE -

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> method
cursor = db.cursor()
# Drop table if it already exist using <i>execute()</i> 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()

插入操作

当您想要将记录创建到数据库表中时，它是必需的。

例子 (Example)

以下示例，执行SQL INSERT语句以创建记录到EMPLOYEE表中 -

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> 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/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> 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()

例子 (Example)

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

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

读操作 (READ Operation)

READ对任何数据库的操作意味着从数据库中获取一些有用的信息。

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

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

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

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

例子 (Example)

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

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

这将产生以下结果 -

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

更新操作 (Update Operation)

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

以下过程将SEX的所有记录更新为'M' 。 在这里，我们将所有男性的年龄增加一年。

例子 (Example)

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> 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中删除所有记录的程序 -

例子 (Example)

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using <i>cursor()</i> 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()

执行事务 (Performing Transactions)

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

• Atomicity - 事务完成或根本没有任何事情发生。

• Consistency - 事务必须以一致状态启动，并使系统保持一致状态。

• Isolation - 在当前事务之外不可见事务的中间结果。

• Durability - 一旦提交了事务，即使系统出现故障，影响也会持续存在。

Python DB API 2.0提供了两种commit或rollback事务的方法。

例子 (Example)

您已经知道如何实现事务。 这是类似的例子 -

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

断开数据库 (Disconnecting Database)

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

db.close()

如果用户使用close（）方法关闭与数据库的连接，则DB将回滚所有未完成的事务。 但是，不依赖于任何DB较低级别的实现细节，您的应用程序最好明确地调用commit或rollback。

处理错误 (Handling Errors)

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

DB API定义了每个数据库模块中必须存在的许多错误。 下表列出了这些例外情况。

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