Python SQLite CRUD 操作

朱雀 2023-10-03 08:44 112阅读 0赞

在本教程中，您将学习如何使用 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][https_www.sqlite.org_download.html]免费下载。此页面包含所有主要操作系统的预编译二进制文件。一组命令行工具包含用于管理 SQLite 数据库文件的命令行 shell 和其他实用程序。

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

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

[![b9b2e82928d2cae2573a08de0947abbf.gif][]][b9b2e82928d2cae2573a08de0947abbf.gif 1]

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

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

*  MySQL：[PyMySql 模块][PyMySql]
 *  Oracle：[Cx-Oracle 模块][Cx-Oracle]
 *  SQL Server：[PyMsSql 模块][PyMsSql]
 *  PostGreSQL：[psycopg2 模块][psycopg2]
 *  ODBC：[pyodbc 模块][pyodbc]

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

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

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

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

<table> 
 <thead> 
 <tr> 
 <th style="border-color:#dfdfdf #dfdfdf #dee2e6;text-align:inherit;vertical-align:bottom;">方法</th> 
 <th style="border-color:#dfdfdf #dfdfdf #dee2e6;text-align:inherit;vertical-align:bottom;">描述</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">cursor()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">返回一个使用此连接的 Cursor 对象。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">commit()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">将任何挂起的事务显式提交到数据库。如果底层数据库不支持事务，则该方法应为空操作。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">rollback()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">此可选方法导致事务回滚到起点。它可能不会在所有地方实施。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">close()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">永久关闭与数据库的连接。在调用此方法后尝试使用连接将引发 DB-API 错误。</td> 
 </tr> 
 </tbody> 
</table>

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

cur=db.cursor()

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

<table> 
 <thead> 
 <tr> 
 <th style="border-color:#dfdfdf #dfdfdf #dee2e6;text-align:inherit;vertical-align:bottom;">方法</th> 
 <th style="border-color:#dfdfdf #dfdfdf #dee2e6;text-align:inherit;vertical-align:bottom;">描述</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">execute()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">在字符串参数中执行 SQL 查询</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">executemany()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">使用元组列表中的一组参数执行 SQL 查询</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">fetchone()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">从查询结果集中获取下一行。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">fetchall()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">从查询结果集中获取所有剩余的行。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">callproc()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">调用存储过程。</td> 
 </tr> 
 <tr> 
 <td style="border-color:#dfdfdf;vertical-align:top;">close()</td> 
 <td style="border-color:#dfdfdf;vertical-align:top;">关闭游标对象。</td> 
 </tr> 
 </tbody> 
</table>

连接类 的`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 的 string`format()`函数将 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()

[https_www.sqlite.org_download.html]: https://www.sqlite.org/download.html
[b9b2e82928d2cae2573a08de0947abbf.gif]: https://img-blog.csdnimg.cn/img_convert/b9b2e82928d2cae2573a08de0947abbf.gif
[b9b2e82928d2cae2573a08de0947abbf.gif 1]: https://www.tutorialsteacher.com/Content/images/python/python-sqlite3.gif
[Python _ API]: https://www.python.org/dev/peps/pep-0249/
[Python]: https://www.python.org/dev/peps/
[PyMySql]: https://pypi.org/project/PyMySQL/
[Cx-Oracle]: https://oracle.github.io/python-cx_Oracle/
[PyMsSql]: https://pypi.org/project/pymssql/
[psycopg2]: https://pypi.org/project/psycopg2/
[pyodbc]: https://pypi.org/project/pyodbc/