Python 数据持久化 - SQLite3 模块

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

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

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

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

当一个表的主键出现在其他表的结构中时，它被称为'外键'，这构成了两者之间关系的基础。 基于这种模型，目前有很多流行的RDBMS产品 −

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

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

所有关系数据库都使用 SQL 来处理表中的数据。 然而，早些时候，这些数据库中的每一个都曾经借助特定于数据库类型的 Python 模块与 Python 应用程序连接。

因此，它们之间缺乏兼容性。 如果一个用户想要改变到不同的数据库产品，那将是困难的。 通过提出"Python 增强提案 (PEP 248)"以推荐与称为 DB-API 的关系数据库的一致接口，解决了这个不兼容问题。 最新的建议称为 DB-API 2.0 版。 （第 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的连接对象。 请注意，如果该数据库尚不存在，则会创建该数据库。 连接对象数据库有以下方法 −

游标充当给定 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 查询的值子句中的数据由用户输入动态提供，请使用 Python DB-API 中推荐的参数替换。 ? 字符用作查询字符串中的占位符，并在 execute() 方法中以元组的形式提供值。 以下示例使用参数替换方法插入一条记录。 name、age 和 marks 作为输入。

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() 方法。

The UPDATE query usually contains a logical expression specified by WHERE clause The query string in the execute() method should contain an UPDATE query syntax. To update the value of 'age' to 23 for name='Anil', define the string as below:

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()

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

下面的例子列出了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 特定的数据类型。 同样，任何 ODBC 兼容的数据库都可以通过安装 pyodbc 模块与 Python 一起使用。