Python SQLite CRUD 操作

在本教程中，您将学习如何使用 SQLite 数据库在 Python 中执行 CRUD 操作。Python 以 sqlite3 模块的形式内置了对 SQLite 的支持。该模块包含对 SQLite 数据库执行持久 CRUD 操作的函数。

Sqlite 数据库

SQLite 是一个独立的事务关系数据库引擎，不需要服务器配置，如 Oracle、MySQL 等。它是由 D. Richard Hipp 于 2000 年 8 月开发的开源和进程内库。整个 SQLite 数据库包含在一个文件中，该文件可以放在计算机文件系统中的任何位置。

SQLite 被广泛用作移动设备、Web 浏览器和其他独立应用程序中的嵌入式数据库。尽管体积很小，但它是一个完全符合 ACID 的数据库，符合 ANSI SQL 标准。

SQLite 可从官方网站https://www.sqlite.org/download.html免费下载。此页面包含所有主要操作系统的预编译二进制文件。一组命令行工具包含用于管理 SQLite 数据库文件的命令行 shell 和其他实用程序。

我们将下载最新版本的 SQLite（版本 3.25.1）以及命令行工具并解压缩存档。

要创建新的 SQLite 数据库，请从命令提示符导航到解压缩存档的文件夹并输入以下命令：

Sqlite3 命令

现在可以执行任何 SQL 查询。以下语句创建一个新表。（确保语句以分号结尾）

sqlite> create table student(name text, age int, marks real);

在上表中添加一条记录。

sqlite> insert into student values('Ramesh', 21, 55.50);

要检索记录，请使用 SELECT 查询，如下所示：

sqlite> select * from student;
Ramesh|21|55.5

Python 数据库 API

Python 数据库 API是一个特殊兴趣小组为数据库模块标准化推荐的一组标准。提供与所有主要数据库产品的数据库接口功能的 Python 模块必须遵守此标准。DB-API 标准被另一个Python 增强提案(PEP-249)进一步修改为 DB-API 2.0 。

标准 Python 发行版具有对 SQLite 数据库连接的内置支持。它包含遵循 DB-API 2.0 并由 Gerhard Haring 编写的 sqlite3 模块。其他 RDBMS 产品也具有 DB-API 兼容模块：

• MySQL：PyMySql 模块
• Oracle：Cx-Oracle 模块
• SQL Server：PyMsSql 模块
• PostGreSQL：psycopg2 模块
• ODBC：pyodbc 模块

按照规定的标准，该过程的第一步是获取与代表数据库的对象的连接。为了与SQLite数据库建立连接，需要导入sqlite3模块并执行connect()函数。

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

该connect()函数返回一个引用现有数据库的连接对象，如果它不存在，则返回一个新数据库。

在连接类中定义了以下方法：

方法	描述
cursor()	返回一个使用此连接的 Cursor 对象。
commit()	将任何挂起的事务显式提交到数据库。如果底层数据库不支持事务，则该方法应为空操作。
rollback()	此可选方法导致事务回滚到起点。它可能不会在所有地方实施。
close()	永久关闭与数据库的连接。在调用此方法后尝试使用连接将引发 DB-API 错误。

游标是一个 Python 对象，它使您能够使用数据库。它充当给定 SQL 查询的句柄；它允许检索一行或多行结果。因此，使用以下语句从连接中获取游标对象以执行 SQL 查询：

cur=db.cursor()

游标对象的以下方法很有用。

方法	描述
execute()	在字符串参数中执行 SQL 查询
executemany()	使用元组列表中的一组参数执行 SQL 查询
fetchone()	从查询结果集中获取下一行。
fetchall()	从查询结果集中获取所有剩余的行。
callproc()	调用存储过程。
close()	关闭游标对象。

连接类 的commit()和rollback()方法确保事务控制。execute()游标的方法接收一个包含 SQL 查询的字符串。具有不正确 SQL 查询的字符串会引发异常，应该正确处理。这就是为什么该execute()方法被放置在 try 块中，并且使用该commit()方法持久保存 SQL 查询的效果。但是，如果 SQL 查询失败，则产生的异常由 except 块处理，并使用该rollback()方法撤消挂起的事务。

该execute()方法的典型用途如下：

例子：

try:
    cur=db.cursor()
    cur.execute("Query")
    db.commit()
    print ("success message")
except:
    print ("error")
    db.rollback()
db.close()

 

创建新表

包含 CREATE TABLE 查询的字符串作为参数传递给execute()游标对象的方法。以下代码在 test.db 数据库中创建学生表。

示例：在 Sqlite 中创建一个新表

import sqlite3
db=sqlite3.connect('test.db')
try:        
    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')
except:
    print ('error in operation')
    db.rollback()
db.close()

 

这可以使用sqlite shell 中的.tables命令进行验证。

E:\SQLite>sqlite3 test.db
SQLite version 3.25.1 2018-09-18 20:20:44
Enter ".help" for usage hints.
sqlite> .tables
student

插入记录

再一次，execute()应该使用表示 INSERT 查询语法的字符串参数调用游标对象的方法。我们创建了一个包含三个字段的学生表：姓名、年龄和分数。包含 INSERT 查询的字符串定义为：

qry="INSERT INTO student (name, age, marks) VALUES ('Rajeev',20,50);"

我们必须将它用作execute()方法的参数。为了解决可能的异常，execute()如前所述，该语句被放置在 try 块中。插入操作的完整代码如下：

示例：在 Sqlite 中插入一条记录

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

 

您可以使用 Sqlite shell 中的 SELECT 查询来检查结果。

sqlite> select * from student;
1|Rajeev|20|50.0

在查询中使用参数

通常，在 SQL 操作中需要使用 Python 变量的值。一种方法是使用 Python 的 stringformat()函数将 Python 数据放入字符串中。但是，这可能会导致对您的程序进行 SQL 注入攻击。相反，使用 Python DB-API 中推荐的参数替换。这 ？字符用作查询字符串中的占位符，并在execute()方法中以元组的形式提供值。以下示例使用参数替换方法插入记录：

例子：

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

 

该executemany()方法用于一次添加多条记录。要添加的数据应在元组列表中给出，每个元组包含一个记录。列表对象（包含元组）是该executemany()方法的参数，以及查询字符串。

例子：

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values(?,?,?);"
students=[('Amar', 18, 70), ('Deepak', 25, 87)]
try:
    cur=db.cursor()
    cur.executemany(qry, students)
    db.commit()
    print ("records added successfully")
except:
    print ("error in operation")
    db.rollback()
db.close()

 

检索记录

当查询字符串包含 SELECT 查询时，该execute()方法形成一个包含返回记录的结果集对象。Python DB-API 定义了两种获取记录的方法：

1. fetchone()：从结果集中获取下一条可用记录。它是一个元组，由获取记录的每一列的值组成。
2. fetchall()：以元组列表的形式获取所有剩余的记录。每个元组对应一条记录并包含表中每一列的值。

使用该fetchone()方法时，使用循环遍历结果集，如下：

示例：获取记录

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

 

执行时，Python shell 中会显示以下输出：

结果：

 

(1, 'Rajeev', 20, 50.0)
(2, 'Vijaya', 16, 75.0)
(3, 'Amar', 18, 70.0)
(4, 'Deepak', 25, 87.0)

该fetchall()方法返回一个元组列表，每个元组是一个记录。

例子：

import sqlite3
db=sqlite3.connect('test.db')
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
students=cur.fetchall()
for rec in students:
    print (rec)
db.close()

 

更新记录

方法中的查询字符串execute()应包含 UPDATE 查询语法。要将 'Amar' 的 'age' 值更新为 17，请定义如下字符串：

qry="update student set age=17 where name='Amar';"

您还可以使用替换技术将参数传递给 UPDATE 查询。

示例：更新记录

import sqlite3
db=sqlite3.connect('test.db')
qry="update student set age=? where name=?;"
try:
    cur=db.cursor()
    cur.execute(qry, (19,'Deepak'))
    db.commit()
    print("record updated successfully")
except:
    print("error in operation")
    db.rollback()
db.close()

 

删除记录

查询字符串应包含 DELETE 查询语法。例如，下面的代码用于从学生表中删除“Bill”。

qry="DELETE from student where name='Bill';"

您可以使用 ? 参数替换的字符。

示例：删除记录

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