Python - 数据库访问

用于数据库接口的Python标准是Python DB-API。大多数Python数据库接口都遵守该标准。

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

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

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

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

# 使用fetchone()方法获取单行。
data=cursor.fetchone()
print "Database version : %s " % data

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

运行此脚本时，它将在无涯教程的Linux机器上产生以下输出。

Database version : 5.0.45

创建数据库表

创建数据库连接后，准备使用创建的游标的 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()

插入操作

要在数据库表中创建记录时需要它。

以下示例执行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))
..................................

读取操作

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

创建无涯教程的数据库连接后，您就可以对这个数据库进行查询了。您可以使用 fetchone()方法来获取单个记录，也可以使用 fetchall()方法来从数据库表中获取多个值。

• fetchone()  - 提取查询输出集的下一行。输出集是当使用游标对象查询表时返回的对象。

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

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

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

#!/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: unable to fecth data"

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

这将产生以下输出-

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

更新操作

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

以下过程更新所有SEX为'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()

删除操作

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

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

执行事务

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

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

• 一致性 - 事务必须以一致的状态开始并使系统保持一致的状态。

• 隔离性 - 交易的中间输出在当前交易之外不可见。

• 持久性 - 提交事务后，即使在系统出现故障后，影响仍然持久。

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

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

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

提交操作

Commit是一项操作，它向数据库发出绿色信号以完成更改，并且在执行此操作后，将无法还原任何更改。

这是调用 commit 方法的简单示例。

db.commit()

回滚操作

如果您对一项或多项更改不满意，并且想要完全还原这些更改，请使用 rollback()方法。

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

db.rollback()

断开数据库

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

db.close()

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

处理错误

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

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