Python 数据持久化 - Sqlite3 模块

---

---

CSV、JSON、XML 等文件的**一个主要缺点**是它们对于随机访问和事务处理不是很有用，因为它们本质上是无结构的。因此，修改内容变得非常困难。

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

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

其基本概念是将数据排列在实体表（称为关系）中。实体表结构提供一个属性，其值对于每一行都是唯一的。此类属性称为**“主键”**。

当一个表的主键出现在其他表的结构中时，它被称为**“外键”**，这构成了这两个表之间关系的基础。基于此模型，目前有许多流行的关系数据库管理系统（RDBMS）产品可用 -

• 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()** 方法执行所需的查询。

• 关闭连接。

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

这里，db 是表示 test.db 的连接对象。请注意，如果数据库尚不存在，则会创建它。连接对象 db 具有以下方法 -

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

cur=db.cursor()

游标对象定义了以下方法 -

以下代码在 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 查询的 values 子句中的数据由用户输入动态提供，请使用 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 操作通过调用 execute() 方法来执行，该方法带有一个包含 SQL 的 DELETE 查询语法的字符串。顺便说一句，**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()

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

以下示例列出 student 表中的所有记录

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 一起使用。