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模块是专门针对数据库类型的。

因此，它们之间缺乏兼容性。如果用户想换成不同的数据库产品，这将被证明是困难的。这个不兼容的问题通过提出 “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有以下方法

序号	方法和描述
1	cursor(): 返回一个使用该连接的游标对象。
2	commit(): 明确地将任何未决的事务提交给数据库。
3	rollback(): 这个可选的方法导致一个事务被回滚到起点。
4	close(): 永久地关闭与数据库的连接。

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

cur=db.cursor()

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

序号	方法和描述
1	execute() 在一个字符串参数中执行SQL查询。
2	executemany() 使用一组参数在图元列表中执行SQL查询。
3	fetchone() 从查询结果集中获取下一条记录。
4	fetchall() 从查询结果集中获取所有剩余的行。
5	callproc() 调用一个存储过程。
6	close() 关闭游标对象。

下面的代码在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中的学生表上执行了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操作是通过调用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()

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

下面的例子列出了学生表中的所有记录

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