Python Data Persistence 简明教程

Python Data Persistence - Sqlite3 Module

CSV、JSON、XML 等文件的一个主要缺点是，它们对随机访问和事务处理并不十分起作用，因为它们在很大程度上都是非结构化的。因此，对内容进行修改变得非常困难。

这些平面文件不适合于客户-服务器环境，因为它们缺乏异步处理能力。使用非结构化数据文件会导致数据冗余和不一致。

这些问题可以通过使用关系数据库来克服。数据库是一个有组织的数据集合，用于消除冗余和不一致，并维护数据完整性。关系数据库模型非常流行。

它的基本概念是按实体表（称作关系）排列数据。实体表结构提供了一个属性，其值为每行唯一。这样的属性称为 'primary key' 。

当一个表的主键显示在其他表的结构中时，这称为 'Foreign key' ，并且这形成了两个表之间关系的基础。基于此模型，目前有许多流行的关系型数据库管理系统产品 −

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase
SQLite

SQLite 是一款轻量级关系数据库，用于各种各样的应用程序。它是一款自包含的、无服务器的、零配置的、事务性的 SQL 数据库引擎。整个数据库是一个独立文件，可以放置在文件系统的任何地方。它是一款开源软件，占用空间非常小，并且无需任何配置。它通常用于嵌入式设备、物联网和移动应用程序。

所有关系数据库都使用 SQL 在表格中处理数据。然而，较早以前，每个此类数据库都使用特定于数据库类型的 Python 模块与 Python 应用程序相连接。

因此，它们之间缺乏兼容性。如果用户希望转向不同的数据库产品，这将被证明是困难的。通过提出“Python 增强提案 (PEP 248)”以推荐与称为 DB-API 的关系数据库相一致的接口，解决了这种不兼容性问题。最新的推荐称为 DB-API 2.0 版本。（PEP 249）

Python 的标准库包含 sqlite3 模块，这是一个 DB-API 兼容模块，可通过 Python 程序处理 SQLite 数据库。本章解释了 Python 与 SQLite 数据库的连接。

如前所述，Python 在 sqlite3 模块的形式中包含了对 SQLite 数据库的内置支持。对于其他数据库，必须使用 pip 实用工具安装相应的 DB-API 兼容 Python 模块。例如，要使用 MySQL 数据库，我们需要安装 PyMySQL 模块。

pip install pymysql

在 DB-API 中推荐以下步骤 −

使用 connect() 函数与数据库建立连接，并获取连接对象。
调用连接对象的 cursor() 方法获取光标对象。
形成一个由要执行的 SQL 语句组成的查询字符串。
通过调用 execute() 方法执行所需查询。
Close the connection.

import sqlite3
db=sqlite3.connect('test.db')

此处，db 是表示 test.db 的连接对象。请注意，如果数据库尚未存在，它将被创建。连接对象 db 具有以下方法 −

Sr.No.	Methods & Description
1	cursor(): 返回一个使用此连接的游标对象。
2	commit(): 显式提交数据库中所有挂起的交易。
3	rollback(): 此可选项方法导致交易回滚到起始点。
4	close(): 永久关闭与数据库的连接。

游标作为给定 SQL 查询的句柄，允许检索结果中一行或多行。从连接获取游标对象以使用以下语句执行 SQL 查询 −

cur=db.cursor()

游标对象具有以下定义的方法 −

Sr.No	Methods & Description
1	execute() 执行字符串参数中的 SQL 查询。
2	executemany() 使用元组列表中的参数集执行 SQL 查询。
3	fetchone() 从查询结果集中提取下一行。
4	fetchall() 从查询结果集中提取所有剩余行。
5	callproc() Calls a stored procedure.
6	close() Closes the cursor object.

以下代码在 test.db 中创建一个表：-

import sqlite3
db=sqlite3.connect('test.db')
cur =db.cursor()
cur.execute('''CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);''')
print ('table created successfully')
db.close()

数据库中所需的数据完整性通过连接对象的 commit() 和 rollback() 方法实现。SQL 查询字符串可能包含可能引发异常的错误 SQL 查询，该异常应得到妥善处理。为此，execute() 语句放置在 try 块中。如果成功，则使用 commit() 方法永久保存结果。如果查询失败，则使用 rollback() 方法撤消交易。

以下代码在 test.db 中的 student 表上执行 INSERT 查询。

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values('Abbas', 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

如果你希望 INSERT 查询中的值子句由用户输入动态提供，请使用 Python DB-API 中推荐的参数替换。？字符用作查询字符串中的占位符，并以元组形式提供 execute() 方法中的值。以下示例使用参数替换方法插入记录。将姓名、年龄和分数作为输入。

import sqlite3
db=sqlite3.connect('test.db')
nm=input('enter name')
a=int(input('enter age'))
m=int(input('enter marks'))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

sqlite3 模块定义了 executemany() 方法，该方法能够一次添加多条记录。要添加的数据应以元组列表提供，每个元组包含一条记录。列表对象是 executemany() 方法的参数，以及查询字符串。但是，某些其他模块不支持 executemany() 方法。

UPDATE 查询通常包含 WHERE 子句指定的逻辑表达式。execute() 方法中的查询字符串应包含 UPDATE 查询语法。若要将名称为“Anil”的“age”值更新为 23，请按如下方式定义字符串：

qry="update student set age=23 where name='Anil';"

为了使更新过程更具动态性，我们使用如上所述的参数替换方法。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

类似地，DELETE 操作是通过使用包含 SQL 的 DELETE 查询语法的字符串调用 execute() 方法执行的。顺便提一下， DELETE 查询通常也包含 WHERE 子句。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

对数据库表进行的重要操作之一是从中检索记录。SQL 为此提供 SELECT 查询。当将包含 SELECT 查询语法的字符串提供给 execute() 方法时，将返回结果集对象。使用游标对象有两个重要的方法，可以使用它们从结果集中检索一条或多条记录。

fetchone()

从结果集中提取下一个可用的记录。这是一个元组，由检索到的记录的每一列的值组成。

fetchall()

以元组列表的形式检索所有剩余的记录。每个元组对应一个记录，并包含表的每列的值。

以下示例列出了学生表中的所有记录

import sqlite3
db=sqlite3.connect('test.db')
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

如果您计划使用 MySQL 数据库而不是 SQLite 数据库，则需要按照上述说明安装 PyMySQL 模块。由于 MySQL 数据库安装在服务器上，因此数据库连接过程中的所有步骤都是相同的，connect() 函数需要 URL 和登录凭据。

import pymysql
con=pymysql.connect('localhost', 'root', '***')

可能与 SQLite 不同的唯一一件事是 MySQL 特定的数据类型。同样，通过安装 pyodbc 模块，任何 ODBC 兼容数据库都可以与 Python 一起使用。