Python 中的数据库 CRUD 操作

2023-09-17 22:15:34

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

Sqlite Database

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

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

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

我们将下载最新版本的SQLite(版本3.25.1)以及命令行工具并提取存档。

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

Sqlite3 命令

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

在上表中添加一条记录。

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

Python DB-API

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

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

• MySQL: PyMySql module
• 甲骨文:Cx-Oracle module
• SQL Server: PyMsSql module
• PostGreSQL: psycopg2 module
• ODBC:pyodbc module

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

connect() 函数返回引用现有数据库的连接对象或新数据库(如果不存在)。

连接类中定义了以下方法:

cursor是一个 Python 对象，可用于处理数据库。\t它充当给定 SQL 查询的句柄;它允许检索结果的一行或多行。 因此，使用以下语句从连接中获取cursor对象以执行 SQL 查询:

cursor对象的以下方法很有用。

连接类的commit()和rollback()方法保证事务控制。 cursor的 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 a New Table)

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

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 命令进行验证。

插入记录(Insert a Record)

同样，cursor对象的execute()方法应使用表示 INSERT 查询语法的字符串参数进行调用。 我们创建了一个包含三个字段的学生表:姓名、年龄和分数。保存 INSERT 查询的字符串定义为:

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

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 查询来检查结果。

在查询中使用参数

通常，Python 变量的值需要在 SQL 操作中使用。一种方法是使用 Python 的字符串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()

检索记录(Retrieve Records)

当查询字符串包含 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() 方法返回元组列表，每个元组是一条记录。

students=cur.fetchall()
for rec in students:
print (rec)

更新记录(Update a Record)

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

还可以使用替换技术将参数传递给 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 a Record)

查询字符串应包含 DELETE 查询语法。例如，下面的代码用于从学生表中删除"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()

本文内容总结：

• Sqlite Database
• Python DB-API
• 创建新表(Create a New Table)
• 插入记录(Insert a Record)
• 检索记录(Retrieve Records)
• 更新记录(Update a Record)
• 删除记录(Delete a Record)