Python 简明教程
Python - Database Access
Database Access in Python
在 Python 中使用数据库访问用于与数据库进行交互，允许应用程序以一致的方式存储、检索、更新和管理数据。各种关系数据库管理系统 (RDBMS) 都支持这些任务，每个系统都需要用于连接的特定 Python 包 -
-
GadFly
-
MySQL
-
PostgreSQL
-
Microsoft SQL Server
-
Informix
-
Oracle
-
Sybase
-
SQLite
-
and many more…
在程序执行期间输入和生成的数据存储在 RAM 中。如果要永久存储它，则需要将它存储在数据库表中。
关系数据库使用 SQL（结构化查询语言）对数据库表执行插入/删除/更新操作。但是，SQL 的实现因一种数据库类型而异。这会产生不兼容问题。一个数据库的 SQL 指令与另一个不匹配。
DB-API (Database API)
为了解决此兼容性问题，Python Enhancement Proposal (PEP) 249 引入了称为 DB-API 的标准化界面。此接口为数据库驱动程序提供了一个一致的框架，确保在不同的数据库系统中保持统一的行为。它通过建立一组通用的规则和方法简化了在各种数据库之间转换的过程。
Using SQLite with Python
Python 的标准库包括 sqlite3 模块，这是 SQLite3 数据库的与 DB_API 兼容的驱动程序。它用作 DB-API 的参考实现。对于其他类型的数据库，你必须安装相关的 Python 包 -
Working with SQLite
借助内置的 sqlite3 模块，使用 SQLite 搭配 Python 非常容易。流程包括以下步骤：
-
Connection Establishment − 使用 sqlite3.connect() 创建一个连接对象，并提供必要的连接信息，例如服务器名称、端口、用户名和密码。
-
Transaction Management − 连接对象管理数据库操作，包括打开、关闭及事务控制（提交或回滚事务）。
-
Cursor Object − 从连接处获取一个游标对象执行 SQL 查询。游标充当 CRUD（创建、读取、更新、删除）操作在数据库中的网关。
在本文档中，我们将学习如何使用 Python 访问数据库、如何在 SQLite 数据库中存储 Python 对象数据，以及如何从 SQLite 数据库检索数据并使用 Python 程序对其进行处理。
The sqlite3 Module
SQLite 是一个无服务器的基于文件的轻量级事务关系数据库。它不需要任何安装，而且无需任何用户名或密码等凭据即可访问数据库。
Python 的 sqlite3 模块包含了适用于 SQLite 数据库的 DB-API 实现。它由 Gerhard Häring 所撰写。我们将学习如何使用 sqlite3 模块来实现 Python 数据库访问。
我们先从导入 sqlite3 并检查其版本开始。
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.39.4'The Connection Object
连接对象由 sqlite3 模块中的 connect() 函数设置。此函数的第一个参数是一个字符串，用于表示 SQLite 数据库文件的路径（相对或绝对）。该函数返回一个引用数据库的连接对象。
>>> conn=sqlite3.connect('testdb.sqlite3')
>>> type(conn)
<class 'sqlite3.Connection'>连接类中定义了不同的方法。其中一个方法是 cursor() 方法，该方法返回一个游标对象，我们将在下一部分中了解它。事务控制是由连接对象的 commit() 和 rollback() 方法实现的。连接类具有重要的方法，可定义要在 SQL 查询中使用的自定义功能和聚合。
The Cursor Object
接下来，我们需要从连接对象获取游标对象。它是您在数据库上执行任何 CRUD 操作时对数据库的控制。连接对象的 cursor() 方法返回游标对象。
>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>现在，我们可以借助于 cursor 对象中可用的 execute() 方法执行所有 SQL 查询操作。该方法需要一个字符串参数，该字符串必须是有效的 SQL 语句。
Creating a Database Table
现在，我们将 Employee 表添加到新创建的“testdb.sqlite3”数据库中。在以下脚本中，我们调用 cursor 对象的 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
EmployeeINSERT Operation
当您想将记录创建到数据库表中时，需要使用 INSERT 操作。
Example
以下示例执行 SQL INSERT 语句以在 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()您还可以使用参数替换技术执行 INSERT 查询，如下所示：
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()READ Operation
在任何数据库上进行 READ 操作意味着从数据库中获取一些有用的信息。
建立数据库连接后，便可针对此数据库进行查询。你可以使用 fetchone() 方法来获取单一记录，或使用 fetchall() 方法来获取数据库表格中的多个值。
-
fetchone() − 从查询结果集中获取下一列。当使用光标对象对表格进行查询时，会返回结果集对象。
-
fetchall() − 从结果集中获取所有列。如果已从结果集中提取一些列，它会从结果集中检索剩余的列。
-
rowcount − 这是只读属性，并返回受 execute() 方法影响的列数。
Example
在以下代码中，光标对象执行 SELECT * FROM EMPLOYEE 查询。通过 fetchall() 方法获取结果集。我们以 for 循环打印出结果集中的所有记录。
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="SELECT * FROM EMPLOYEE"
try:
# Execute the SQL command
cur.execute(qry)
# Fetch all the rows in a list of lists.
results = cur.fetchall()
for row in results:
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
# Now print fetched result
print ("fname={},lname={},age={},sex={},income={}".format(fname, lname, age, sex, income ))
except Exception as e:
print (e)
print ("Error: unable to fecth data")
conn.close()它将生成以下 output −
fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0Update Operation
对任何数据库进行 UPDATE 操作表示更新数据库中已有的一个或多个记录。
以下过程更新了 income=2000 的所有记录。此处，我们将 income 增加 1000。
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="UPDATE EMPLOYEE SET INCOME = INCOME+1000 WHERE INCOME=?"
try:
# Execute the SQL command
cur.execute(qry, (1000,))
# Fetch all the rows in a list of lists.
conn.commit()
print ("Records updated")
except Exception as e:
print ("Error: unable to update data")
conn.close()DELETE Operation
当你想从数据库中删除一些记录时，需要 DELETE 操作。以下过程用于删除 EMPLOYEE 中 INCOME 小于 2000 的所有记录。
import sqlite3
conn=sqlite3.connect('testdb.sqlite3')
cur=conn.cursor()
qry="DELETE FROM EMPLOYEE WHERE INCOME<?"
try:
# Execute the SQL command
cur.execute(qry, (2000,))
# Fetch all the rows in a list of lists.
conn.commit()
print ("Records deleted")
except Exception as e:
print ("Error: unable to delete data")
conn.close()Performing Transactions
事务是一种确保数据一致性的机制。事务具有以下四个属性−
-
Atomicity − 事务要么完成，要么根本不会发生。
-
Consistency − 事务必须从一致的状态开始，并以一致的状态离开系统。
-
Isolation − 事务的中间结果在当前事务外不可见。
-
Durability − 一旦提交事务，其效果将持久，即使在系统故障后也是如此。
Python DB API 2.0 提供了两种方法来提交或回滚事务。
ROLLBACK Operation
如果你不满意其中的一个或多个更改，并且想要完全撤消那些更改，请使用 rollback() 方法。
此处是调用 rollback() 方法的一个简单示例。
db.rollback()The PyMySQL Module
PyMySQL 是一个接口，用于从 Python 连接到 MySQL 数据库服务器。它实现了 Python 数据库 API v2.0 并包含一个纯 Python MySQL 客户端库。PyMySQL 的目标是成为 MySQLdb 的直接替代品。
Installing 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 PyMySQLNote − 确保您具有安装上述模块的根权限。