Python MySQL 数据库访问

---

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

你可以为你的应用程序选择正确的数据库。 Python 数据库 API 支持广泛的数据库服务器，例如：

• GadFly
• mSQL
• MySQL
• PostgreSQL
• 微软 SQL Server 2000
• Informix
• 基地间
• Oracle
• Sybase

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

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

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

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

什么是 MySQLdb？

---

MySQLdb 是一个用于从 Python 连接到 MySQL 数据库服务器的接口。它实现了 Python 数据库 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

注意 ： 确保你有安装以上模块的root权限。

数据库连接

---

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

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

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

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

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

• Python 模块 MySQLdb 已正确安装在你的机器上。

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

例子

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

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

Database version : 5.0.45

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

创建数据库表

---

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

例子

让我们创建数据库表 EMPLOYEE：

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

插入操作

---

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

例子

下面的例子，执行 SQL INSERT 在 EMPLOYEE 表中创建记录的语句：

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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/python

import MySQLdb

# Open database connection
db = MySQLdb.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))
..................................

读操作

---

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

一旦我们的数据库连接建立，你就可以对该数据库进行查询了。你可以使用 获取一个() 获取单个记录的方法或 获取所有() 从数据库表中提取多个值的方法。

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

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

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

例子

以下过程查询 EMPLOYEE 表中工资超过 1000 的所有记录：

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() 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 对任何数据库的操作意味着更新数据库中已经存在的一条或多条记录。

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

例子

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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 操作。以下是删除 EMPLOYEE 中 AGE 大于 20 的所有记录的过程：

例子

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

执行交易

---

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

• 原子性 ：要么交易完成，要么什么都没有发生。

• 一致性 ：一个事务必须以一致的状态开始，并让系统保持一致的状态。

• 隔离 ： 一个事务的中间结果在当前事务之外是不可见的。

• 耐用性 ：一旦事务被提交，效果是持久的，即使在系统故障之后。

Python DB API 2.0 提供了两种方法 commit or 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 method.

db.commit()

回滚操作

---

如果你对一项或多项更改不满意，并且希望完全还原这些更改，请使用 回滚() method.

这是一个简单的调用示例 回滚() method.

db.rollback()

断开数据库

---

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

db.close()

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

处理错误

---

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

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

序号.	例外与说明
1	Warning 用于非致命问题。必须继承 StandardError。
2	Error 错误的基类。必须继承 StandardError。
3	接口错误 用于数据库模块中的错误，而不是数据库本身。必须子类化错误。
4	数据库错误 用于数据库中的错误。必须子类化错误。
5	数据错误 DatabaseError 的子类，指数据中的错误。
6	操作错误 DatabaseError 的子类，它指的是诸如丢失与数据库的连接之类的错误。这些错误通常不受 Python 脚本编写者的控制。
7	完整性错误 DatabaseError 的子类，用于可能破坏关系完整性的情况，例如唯一性约束或外键。
8	内部错误 DatabaseError 的子类，它引用数据库模块内部的错误，例如游标不再处于活动状态。
9	编程错误 DatabaseError 的子类，它指的是错误的表名和其他可以安全归咎于你的错误。
10	不支持错误 DatabaseError 的子类，指尝试调用不受支持的功能。

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