Python数据持久化-Sqlite3模块

---

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

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

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

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

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

• 虻
• 数据库管理
• MySQL
• PostgreSQL
• 微软SQL服务器2000
• 信息系统
• 英特贝斯
• 甲骨文
• 赛贝斯
• 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 查询的值子句中的数据由用户输入动态提供，请使用 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 查询语法。要将 name='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()

从结果集中获取下一条可用记录。它是一个由所获取记录的每列值组成的元组。

获取全部()

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

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

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