python访问MySQL数据库及其操作详解 – Python高级开发教程

上一章Python教程请查看：python CGI编程和Web开发

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

你可以为你的应用程序选择正确的数据库，Python数据库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模块。

1、MySQLdb是什么？

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

2、如何安装MySQLdb？

在继续之前，请确保你的计算机上安装了MySQLdb，只需在Python脚本中输入以下内容并执行即可：

#!/usr/bin/python

import MySQLdb

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

对于Ubuntu，使用下面的命令-
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
对于Fedora，使用以下命令-$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
对于Python命令提示符，使用以下命令-
pip install MySQL-python

注意——确保你有安装上述模块的根权限。

3、数据库连接

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

• 你已经创建了一个数据库TESTDB。
• 你已经在TESTDB中创建了一个表EMPLOYEE。
• 这个表有字段FIRST_NAME、LAST_NAME、年龄、性别和收入。
• 将用户ID“testuser”和密码“test123”设置为访问TESTDB。
• Python模块MySQLdb已正确安装在你的机器上。
• 你已经通过MySQL教程了解了MySQL的基础知识。

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

#!/usr/bin/python

import MySQLdb

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

# 使用cursor()方法准备游标对象
cursor = db.cursor()

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

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

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

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

4、创建数据库表

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

让我们创建数据库表EMPLOYEE –

#!/usr/bin/python

import MySQLdb

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

# 使用cursor()方法准备游标对象
cursor = db.cursor()

# 使用execute()方法删除已经存在的表
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()

5、插入操作

当你想要将记录创建到数据库表中时，就需要使用它。

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

#!/usr/bin/python

import MySQLdb

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

# 使用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/python

import MySQLdb

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

# 使用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))
..................................

6、读取或查找操作

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

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

• fetchone()——它获取查询结果集的下一行，结果集是一个对象，当游标对象用于查询一个表时返回该对象。
• 它获取结果集中所有的行，如果已经从结果集中提取了一些行，那么它从结果集中检索其余的行。
• rowcount——这是一个只读属性，返回受execute()方法影响的行数。

下面的过程从工资超过1000的雇员表中查询所有记录：

#!/usr/bin/python

import MySQLdb

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

# 使用cursor()方法准备游标对象
cursor = db.cursor()

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: 无法获取数据"

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

7、更新操作

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

下面的程序将更新所有以“M”开头的性行为记录，这里我们把所有男性的年龄增加了一年。

#!/usr/bin/python

import MySQLdb

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

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

8、删除操作

当你想要从数据库中删除一些记录时，需要执行DELETE操作，以下是删除所有年龄超过20岁的雇员的记录的程序

#!/usr/bin/python

import MySQLdb

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

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

9、执行事务

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

• 原子性——要么完成一个事务，要么什么也不发生。
• 一致性——事务必须以一致的状态开始，而系统必须保持一致的状态。
• 事务的隔离中间结果在当前事务之外是不可见的。
• 持久性——一旦提交了事务，即使在系统故障之后，其影响也是持久的。

Python DB API 2.0提供了两种方法来提交或回滚事务。

你已经知道如何实现事务，这里再举一个类似的例子：

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

10、提交操作

Commit提交是一种操作，它向数据库发出绿色信号以完成更改，在此操作之后，不能恢复任何更改。

下面是一个调用commit方法的简单示例。

db.commit ()

11、回滚操作

如果你对一个或多个更改不满意，并且希望完全还原这些更改，那么可以使用rollback()方法。

下面是一个调用rollback()方法的简单示例。

db.rollback ()

12、断开数据库连接

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

db.close ()

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

13、处理错误

错误的来源有很多，例如在执行的SQL语句中出现语法错误、连接失败，或者为已经取消或完成的语句句柄调用fetch方法。

DB API定义了许多必须存在于每个数据库模块中的错误，下表列出了这些异常。

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