Python - MySQL 数据库访问

上一页下一页

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

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

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• 基地间
• 甲骨文
• 赛贝斯

以下是可用 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

注意 − 确保你有安装以上模块的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 对象用于创建光标 对象，该对象又用于执行 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))
..................................

读操作

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

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

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

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

• 行数 − 这是一个只读属性，返回受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 或回滚 一笔交易。

示例

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

# 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() 方法关闭了与数据库的连接，则数据库将回滚任何未完成的事务。但是，您的应用程序最好显式调用提交或回滚，而不是依赖于任何 DB 较低级别的实现细节。

处理错误

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

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

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

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