Python 中的数据库访问用于与数据库交互，允许应用程序一致地存储、检索、更新和管理数据。各种关系数据库管理系统 (RDBMS) 都支持这些任务，每种 RDBMS 都需要特定的 Python 包来进行连接 −
程序执行期间产生的数据通常存储在 RAM 中。如果需要持久化存储，则需要将数据存入数据库表中。
关系数据库使用 SQL（结构化查询语言）来执行对数据库表的操作，如插入、删除和更新。然而，SQL 的实现因数据库类型的不同而有所差异。这就引发了兼容性问题：适用于一种数据库的 SQL 指令可能并不适用于另一种数据库。
DB-API（数据库 API）
为了应对兼容性的问题，Python 改进提案 (PEP) 249 引入了一个标准化接口，即 DB-API。这个接口提供了一个一致的框架给数据库驱动程序，确保了跨不同数据库系统的统一行为。它简化了在各种数据库之间迁移的过程，通过建立一套通用的规则和方法。
使用 Python 进行数据库操作
Python 的标准库包含了 sqlite3 模块，这是一个与 DB-API 兼容的 SQLite3 数据库驱动器。它作为 DB-API 的参考实现。对于其他类型的数据库，你需要安装相应的 Python 包 −
-
Oracle: cx_oracle, pyodbc
-
SQL Server: pymssql, pyodbc
-
-
MySQL: MySQL Connector/Python, pymysql
使用 SQLite
由于内置的 sqlite3 模块，使用 Python 进行 SQLite 数据库的操作变得非常简单。过程包括 −
-
连接建立: 使用
sqlite3.connect() 创建一个连接对象，提供必要的连接凭证，如服务器名、端口、用户名和密码。
-
事务管理: 连接对象管理数据库操作，包括打开、关闭以及事务控制（提交或回滚事务）。
-
游标对象: 从连接中获取一个游标对象以执行 SQL 查询。游标作为执行数据库上的 CRUD（创建、读取、更新、删除）操作的入口。
让我们通过导入 sqlite3 来开始，并检查它的版本。
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'
连接对象
通过 sqlite3 模块中的 connect() 函数建立连接对象。此函数的第一个位置参数是一个字符串，表示 SQLite 数据库文件的路径（相对或绝对）。函数返回一个指向数据库的连接对象。
>>> conn = sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>
连接类定义了多种方法。其中之一是 cursor() 方法，它返回一个游标对象，我们将在下一节中了解。通过连接对象的 commit() 和 rollback() 方法可以实现事务控制。连接类还有重要的方法来定义用于 SQL 查询的自定义函数和聚合。
游标对象
接下来，我们需要从连接对象中获取游标对象。它是你在对数据库执行任何 CRUD 操作时的句柄。通过连接对象上的 cursor() 方法返回游标对象。
>>> cur = conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>
现在我们可以借助游标对象上的 execute() 方法执行所有 SQL 查询操作。此方法需要一个有效的 SQL 语句作为字符串参数。
创建数据库表
我们现在在新创建的 'testdb.sqlite3' 数据库中添加 Employee 表。在下面的脚本中，我们调用游标对象的 execute() 方法，并传入一个带有 CREATE TABLE 语句的字符串。
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = '''
CREATE TABLE Employee (
EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
FIRST_NAME TEXT (20),
LAST_NAME TEXT(20),
AGE INTEGER,
SEX TEXT(1),
INCOME FLOAT
);
'''
try:
cur.execute(qry)
print('Table created successfully')
except:
print('error in creating table')
conn.close()
当上述程序运行时，在当前工作目录中创建了一个包含 Employee 表的数据库。
我们可以通过列出 SQLite 控制台中的数据库表来验证。
sqlite> .open mydb.sqlite
sqlite> .tables
Employee
插入操作
当你要向数据库表中创建记录时，需要插入操作。
示例
下面的例子执行 SQL 插入语句以在 EMPLOYEE 表中创建一条记录。
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cur.execute(qry)
conn.commit()
print('Record inserted successfully')
except:
conn.rollback()
print('error in INSERT operation')
conn.close()
你也可以使用参数替换技术来执行插入查询，如下所示：
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES (?, ?, ?, ?, ?)"""
try:
cur.execute(qry, ('Makrand', 'Mohan', 21, 'M', 5000))
conn.commit()
print('Record inserted successfully')
except Exception as e:
conn.rollback()
print('error in INSERT operation')
conn.close()
读取操作
在任何数据库上的读取操作意味着从数据库中提取有用的信息。
一旦建立了数据库连接，你就可以准备好向数据库中发出查询。你可以使用 fetchone() 方法来提取单条记录或者使用 fetchall() 方法来从数据库表中提取多条值。
-
fetchone() − 它提取查询结果集中的下一行。结果集是使用游标对象查询表格时返回的对象。
-
fetchall() − 它提取结果集中的所有行。如果已经有部分行从结果集中抽取出来，则它提取剩余的行。
-
rowcount − 这是一个只读属性，返回由 execute() 方法影响的行数。
示例
在下面的代码中，游标对象执行 SELECT * FROM EMPLOYEE 查询。通过 fetchall() 方法获得结果集。我们使用一个 for 循环打印结果集中的所有记录。
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = "SELECT * FROM EMPLOYEE"
try:
cur.execute(qry)
results = cur.fetchall()
for row in results:
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
print("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income))
except Exception as e:
print(e)
print("Error: unable to fetch data")
conn.close()
它会产生以下输出 −
fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0
更新操作
在任何数据库上的更新操作意味着更新数据库中已经存在的一个或多个记录。
下面的过程更新所有收入为 2000 的记录。这里我们将收入增加 1000。
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = "UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"
try:
cur.execute(qry, (1000,))
conn.commit()
print("Records updated")
except Exception as e:
print("Error: unable to update data")
conn.close()
删除操作
当你要从数据库中删除一些记录时，需要删除操作。下面是删除所有收入小于 2000 的记录的过程。
import sqlite3
conn = sqlite3.connect('testdb.sqlite3')
cur = conn.cursor()
qry = "DELETE FROM EMPLOYEE WHERE INCOME<?"
try:
cur.execute(qry, (2000,))
conn.commit()
print("Records deleted")
except Exception as e:
print("Error: unable to delete data")
conn.close()
进行事务处理
事务是一种确保数据一致性的机制。事务具有以下四个特性 −
-
-
一致性 − 事务必须在一个一致的状态下开始，并且使系统处于一个一致的状态。
-
-
持久性 − 一旦事务被提交，其效果是持久的，即使是在系统故障之后。
进行事务处理
Python DB API 2.0 提供了两种方法来提交或回滚一个事务。
示例
你已经知道如何实现事务。以下是一个类似的例子 −
sql = "DELETE FROM EMPLOYEE WHERE AGE > ?"
try:
cursor.execute(sql, (20,))
db.commit()
except:
db.rollback()
COMMIT 操作
提交是一个操作，它向数据库发出信号以最终确定更改，在此操作后，更改无法撤销。
以下是一个简单的调用 commit 方法的例子。
db.commit()
ROLLBACK 操作
如果你不满意一个或多个更改，并希望完全恢复这些更改，那么使用 rollback() 方法。
以下是一个简单的调用 rollback() 方法的例子。
db.rollback()
PyMySQL 模块
PyMySQL 是一个用于从 Python 连接到 MySQL 数据库服务器的接口。它实现了 Python Database API v2.0 并且包含了一个纯 Python 的 MySQL 客户端库。PyMySQL 的目标是作为一个 MySQLdb 的直接替代品。
安装 PyMySQL
在继续之前，请确保你的机器上已安装了 PyMySQL。只需在你的 Python 脚本中输入以下内容并执行它 −
import PyMySQL
如果它产生了以下结果，那么就说明 MySQLdb 模块未安装 −
Traceback (most recent call last):
File "test.py", line 3, in <module>
Import PyMySQL
ImportError: No module named PyMySQL
最新的稳定版本可以在 PyPI 上找到，并且可以使用 pip 安装 −
pip install PyMySQL
注意 − 确保你有安装上述模块的管理员权限。
MySQL 数据库连接
在连接到 MySQL 数据库之前，请确保以下几点 −
-
-
你已经在 TESTDB 中创建了一个名为 EMPLOYEE 的表。
-
此表有字段 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
-
用户 ID "testuser" 和密码 "test123" 已设置为访问 TESTDB。
-
Python 模块 PyMySQL 已经正确安装在你的机器上。
-
你已经阅读了 MySQL 教程以理解 MySQL 的基础知识。
示例
为了在前面的例子中使用 MySQL 数据库而不是 SQLite 数据库，我们需要将 connect() 函数修改如下 −
import PyMySQL
db = PyMySQL.connect("localhost", "testuser", "test123", "TESTDB")
除了这个改变外，每个数据库操作都可以毫无困难地执行。
错误处理
有很多错误的来源。几个例子是一个执行 SQL 语句的语法错误、一个连接失败、或者对一个已经被取消或完成的语句句柄调用 fetch 方法。
DB API 定义了许多必须存在于每个数据库模块中的错误。以下表格列出了这些异常。
| 序号 |
异常 & 描述 |
| 1 |
Warning |
| 2 |
Error |
| 3 |
InterfaceError |
| 4 |
DatabaseError |
| 5 |
DataError |
| 6 |
OperationalError |
| 7 |
IntegrityError |
| 8 |
InternalError |
| 9 |
ProgrammingError |
| 10 |
NotSupportedError |