Python-数据库访问

---

程序执行期间输入和生成的数据存储在 RAM 中。如果要持久化存储，就需要存储在数据库表中。有多种可用的关系数据库管理系统（RDBMS）。

• 虻
• MySQL
• PostgreSQL
• 微软SQL服务器
• 信息系统
• 甲骨文
• 赛贝斯
• SQLite
• 还有很多...

在本章中，我们将学习如何使用Python访问数据库，如何将Python对象的数据存储在SQLite数据库中，以及如何从SQLite数据库中检索数据并使用Python程序处理它。

关系数据库使用 SQL（结构化查询语言）对数据库表执行 INSERT/DELETE/UPDATE 操作。然而，SQL 的实现因一种数据库类型而异。这会引发不兼容问题。一个数据库的 SQL 指令与其他数据库不匹配。

为了克服这种不兼容性，PEP（Python增强提案）249中提出了一个通用接口。该提案称为DB-API，并要求用于与Python交互的数据库驱动程序应符合DB-API。

Python的标准库包含sqlite3模块，它是SQLite3数据库的DB_API兼容驱动程序，也是DB-API的参考实现。

由于所需的 DB-API 接口是内置的，我们可以轻松地将 SQLite 数据库与 Python 应用程序一起使用。对于其他类型的数据库，您必须安装相关的 Python 包。

数据库	Python包
甲骨文	CX_Oracle、pyodbc
SQL服务器	pymssql、pyodbc
PostgreSQL	心理咨询师2
MySQL	MySQL 连接器/Python、pymysql

DB-API 模块（例如 sqlite3）包含连接和游标类。通过提供所需的连接凭据（例如服务器名称和端口号以及用户名和密码（如果适用）），使用 connect() 方法获取连接对象。连接对象处理打开和关闭数据库以及提交或回滚事务的事务控制机制。

从连接对象获取的游标对象在执行所有 CRUD 操作时充当数据库的句柄。

sqlite3 模块

SQLite 是一种无服务器、基于文件的轻量级事务关系数据库。它不需要任何安装，也不需要用户名和密码等凭据来访问数据库。

Python 的 sqlite3 模块包含 SQLite 数据库的 DB-API 实现。它的作者是格哈德·哈林。让我们学习如何使用 sqlite3 模块通过 Python 访问数据库。

让我们首先导入 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()方法来实现的。Connection 类具有定义在 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

插入操作

当您想要将记录创建到数据库表中时，需要执行 INSERT 操作。

例子

以下示例执行 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 对任何数据库的操作意味着从数据库中获取一些有用的信息。

建立数据库连接后，您就可以对该数据库进行查询了。您可以使用 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:
   # 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()

它将产生以下输出-

fname=Mac,lname=Mohan,age=20,sex=M,income=2000.0
fname=Makrand,lname=Mohan,age=21,sex=M,income=5000.0

更新操作

UPDATE 对任何数据库的操作都意味着更新数据库中已有的一条或多条记录。

以下过程更新所有收入 = 2000 的记录。在这里，我们将收入增加了 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 操作。以下是删除 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()

执行交易

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

• Atomics性- 事务要么完成，要么什么也没有发生。

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

• 隔离- 事务的中间结果在当前事务之外不可见。

• 持久性- 一旦事务被提交，即使在系统发生故障之后，其影响也是持久的。

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

例子

您已经知道如何实施交易。这是一个类似的例子 -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > ?"
try:
   # Execute the SQL command
   cursor.execute(sql, (20,))
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

提交操作

提交是一个操作，它向数据库发出绿色信号以完成更改，并且在此操作之后，任何更改都无法恢复。

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

db.commit()

回滚操作

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

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

db.rollback()

PyMySQL 模块

PyMySQL 是一个用于从 Python 连接到 MySQL 数据库服务器的接口。它实现了 Python 数据库 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

注意- 确保您具有安装上述模块的 root 权限。

MySQL 数据库连接

在连接到 MySQL 数据库之前，请确保以下几点 -

• 您已经创建了一个数据库 TESTDB。

• 您已在 TESTDB 中创建了一个表 EMPLOYEE。

• 该表包含字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。

• 设置用户ID“testuser”和密码“test123”来访问TESTDB。

• Python 模块 PyMySQL 已正确安装在您的计算机上。

• 您已完成 MySQL 教程以了解 MySQL 基础知识。

例子

要在前面的示例中使用 MySQL 数据库而不是 SQLite 数据库，我们需要更改 connect() 函数，如下所示 -

import PyMySQL
# Open database connection
db = PyMySQL.connect("localhost","testuser","test123","TESTDB" )

除了这个改变之外，每个数据库操作都可以毫无困难地执行。

处理错误

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

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

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