如何在Python 3中使用sqlite3模块

The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.

作者选择了COVID-19救济基金来接受捐赠，这是Write for DOnations计划的一部分。

介绍 (Introduction)

SQLite is a self-contained, file-based SQL database. SQLite comes bundled with Python and can be used in any of your Python applications without having to install any additional software.

SQLite是一个独立的，基于文件SQL数据库。 SQLite与Python捆绑在一起，可在您的任何Python应用程序中使用，而无需安装任何其他软件。

In this tutorial, we’ll go through the sqlite3 module in Python 3. We’ll create a connection to a SQLite database, add a table to that database, insert data into that table, and read and modify data in that table.

在本教程中，我们将介绍Python 3中的sqlite3模块 。 我们将创建一个与SQLite数据库的连接，向该数据库添加一个表，将数据插入该表，以及读取和修改该表中的数据。

For this tutorial, we’ll be working primarily with an inventory of fish that we need to modify as fish are added to or removed from a fictional aquarium.

在本教程中，我们将主要处理鱼类的清单，当将鱼类添加到虚拟水族馆或从中删除时，需要对其进行修改。

先决条件 (Prerequisites)

To get the most out of this tutorial, it is recommended to have some familiarity with programming in Python and some basic background with SQL.

为了充分利用本教程，建议您熟悉Python编程和SQL的基本知识。

You can review these tutorials for the necessary background information:

您可以查看这些教程以获取必要的背景信息：

• How to Code in Python3

  如何在Python3中编码

• An Introduction to Queries in MySQL

  MySQL查询简介

第1步-创建与SQLite数据库的连接 (Step 1 — Creating a Connection to a SQLite Database)

When we connect to a SQLite database, we are accessing data that ultimately resides in a file on our computer. SQLite databases are fully featured SQL engines that can be used for many purposes. For now, we’ll consider a database that tracks the inventory of fish at a fictional aquarium.

当我们连接到SQLite数据库时，我们将访问最终驻留在计算机文件中的数据。 SQLite数据库是功能齐全SQL引擎，可以用于许多目的。 现在，我们将考虑一个跟踪虚构水族馆中鱼存量的数据库。

We can connect to a SQLite database using the Python sqlite3 module:

我们可以使用Python sqlite3模块连接到SQLite数据库：

import sqlite3

connection = sqlite3.connect("aquarium.db")

import sqlite3 gives our Python program access to the sqlite3 module. The sqlite3.connect() function returns a Connection object that we will use to interact with the SQLite database held in the file aquarium.db. The aquarium.db file is created automatically by sqlite3.connect() if aquarium.db does not already exist on our computer.

import sqlite3使我们的Python程序可以访问sqlite3模块。 sqlite3.connect()函数返回一个Connection对象，我们将使用该对象与文件aquarium.db保存SQLite数据库进行交互。 如果aquarium.db在我们的计算机上尚不存在，则sqlite3.connect()将自动创建aquarium.db文件。

We can verify we successfully created our connection object by running:

我们可以通过运行以下命令来验证是否成功创建了connection对象：

print(connection.total_changes)

If we run this Python code, we will see output like:

如果运行此Python代码，我们将看到类似以下的输出：

   
   

    
    Output
   
   
0

connection.total_changes is the total number of database rows that have been changed by connection. Since we have not executed any SQL commands yet, 0 total_changes is correct.

connection.total_changes是已通过connection更改的数据库行的总数。 由于我们尚未执行任何SQL命令，因此0 total_changes是正确的。

If, at any time, we find we want to start this tutorial again, we can delete the aquarium.db file from our computer.

如果在任何时候发现我们想重新开始本教程，则可以从计算机中删除aquarium.db文件。

Note: It is also possible to connect to a SQLite database that resides strictly in memory (and not in a file) by passing the special string ":memory:" into sqlite3.connect(). For example, sqlite3.connect(":memory:"). A ":memory:" SQLite database will disappear as soon as your Python program exits. This might be convenient if you want a temporary sandbox to try something out in SQLite, and don’t need to persist any data after your program exits.

注意：通过将特殊字符串":memory:"传递给sqlite3.connect()还可以连接到严格位于内存(而不是文件)中SQLite数据库。 例如， sqlite3.connect(":memory:") 。 Python程序退出后， ":memory:" SQLite数据库将消失。 如果您希望临时沙箱在SQLite中尝试某些操作，并且在程序退出后无需保留任何数据，则这可能会很方便。

第2步-将数据添加到SQLite数据库 (Step 2 — Adding Data to the SQLite Database)

Now that we have connected to the aquarium.db SQLite database, we can start inserting and reading data from it.

现在我们已经连接到aquarium.db SQLite数据库，我们可以开始从中插入和读取数据了。

In a SQL database, data is stored in tables. Tables define a set of columns, and contain 0 or more rows with data for each of the defined columns.

在SQL数据库中，数据存储在表中。 表定义了一组列，并包含0或更多行，其中包含每个定义的列的数据。

We will create a table named fish that tracks the following data:

我们将创建一个名为fish的表，该表将跟踪以下数据：

The fish table will track a value for name, species, and tank_number for each fish at the aquarium. Two example fish rows are listed: one row for a shark named Sammy, and one row for a cuttlefish named Jamie.

fish表将跟踪水族馆中每种鱼的name ， species和tank_number的值。 列出了两个示例fish行：一行是名为Sammy的shark ，另一行是名为Jamie的cuttlefish 。

We can create this fish table in SQLite using the connection we made in Step 1:

我们可以使用在步骤1中connection在SQLite中创建此fish表：

cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")

connection.cursor() returns a Cursor object. Cursor objects allow us to send SQL statements to a SQLite database using cursor.execute(). The "CREATE TABLE fish ..." string is a SQL statement that creates a table named fish with the three columns described earlier: name of type TEXT, species of type TEXT, and tank_number of type INTEGER.

connection.cursor()返回一个Cursor对象 。 Cursor对象允许我们使用cursor.execute()将SQL语句发送到SQLite数据库。 在"CREATE TABLE fish ..."字符串是创建一个名为表中SQL语句fish与三列前面描述： name类型的TEXT ，类型的种类TEXT和tank_number类型的INTEGER 。

Now that we have created a table, we can insert rows of data into it:

现在我们已经创建了一个表，可以在其中插入数据行：

cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")

We call cursor.execute() two times: once to insert a row for the shark Sammy in tank 1, and once to insert a row for the cuttlefish Jamie in tank 7. "INSERT INTO fish VALUES ..." is a SQL statement that allows us to add rows to a table.

我们两次调用cursor.execute() ：一次在罐1为鲨鱼Sammy插入一行，一次在罐7为墨鱼Jamie插入一行。 "INSERT INTO fish VALUES ..."是一条SQL语句，它允许我们向表中添加行。

In the next section, we will use a SQL SELECT statement to inspect the rows we just inserted into our fish table.

在下一节中，我们将使用SQL SELECT语句检查刚插入到fish表中的行。

步骤3 —从SQLite数据库读取数据 (Step 3 — Reading Data from the SQLite Database)

In Step 2, we added two rows to a SQLite table named fish. We can retrieve those rows using a SELECT SQL statement:

在步骤2中，我们在名为fishSQLite表中添加了两行。 我们可以使用SELECT SQL语句检索这些行：

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

If we run this code, we will see output like the following:

如果运行此代码，我们将看到类似以下的输出：

   
   

    
    Output
   
   
[('Sammy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]

The cursor.execute() function runs a SELECT statement to retrieve values for the name, species, and tank_number columns in the fish table. fetchall() retrieves all the results of the SELECT statement. When we print(rows) we see a list of two tuples. Each tuple has three entries; one entry for each column we selected from the fish table. The two tuples have the data we inserted in Step 2: one tuple for Sammy the shark, and one tuple for Jamie the cuttlefish.

该cursor.execute()函数运行一个SELECT语句来检索值的name ， species ，以及tank_number列中的fish表。 fetchall()检索SELECT语句的所有结果。 当我们print(rows)我们看到两个元组的列表。 每个元组有三个条目； 我们从fish表中选择的每一列都有一个条目。 这两个元组具有我们在步骤2中插入的数据：一个元组用于shark Sammy ，一个元组用于cuttlefish Jamie 。

If we wanted to retrieve rows in the fish table that match a specific set of criteria, we can use a WHERE clause:

如果我们想要在fish表中检索与一组特定条件匹配的行，则可以使用WHERE子句：

target_fish_name = "Jamie"
rows = cursor.execute(
    "SELECT name, species, tank_number FROM fish WHERE name = ?",
    (target_fish_name,),
).fetchall()
print(rows)

If we run this, we will see output like the following:

如果运行此命令，将看到类似以下的输出：

   
   

    
    Output
   
   
[('Jamie', 'cuttlefish', 7)]

As with the previous example, cursor.execute(<SQL statment>).fetchall() allows us to fetch all the results of a SELECT statement. The WHERE clause in the SELECT statement filters for rows where the value of name is target_fish_name. Notice that we use ? to substitute our target_fish_name variable into the SELECT statement. We expect to only match one row, and indeed we only see the row for Jamie the cuttlefish returned.

与前面的示例一样， cursor.execute(<SQL statment>).fetchall()允许我们获取SELECT语句的所有结果。 SELECT语句中的WHERE子句过滤name为target_fish_name 。 注意我们使用? 将我们的target_fish_name变量替换为SELECT语句。 我们希望只匹配一行，实际上我们只看到cuttlefish Jamie返回的cuttlefish行。

Warning: Never use Python string operations to dynamically create a SQL statement string. Using Python string operations to assemble a SQL statement string leaves you vulnerable to SQL injection attacks. SQL injection attacks can be used to steal, alter, or otherwise modify data stored in your database. Always use the ? placeholder in your SQL statements to dynamically substitute values from your Python program. Pass a tuple of values as the second argument to Cursor.execute() to bind your values to the SQL statement. This substitution pattern is demonstrated here and in other parts of this tutorial as well.

警告：切勿使用Python字符串操作来动态创建SQL语句字符串。 使用Python字符串操作组装SQL语句字符串会使您容易受到SQL注入攻击的攻击 。 SQL注入攻击可用于窃取，更改或以其他方式修改数据库中存储的数据。 始终使用? SQL语句中的占位符，以动态替换Python程序中的值。 将值的元组作为第二个参数传递给Cursor.execute()以将值绑定到SQL语句。 此替换模式已在此处以及本教程的其他部分中进行了演示。

步骤4 —修改SQLite数据库中的数据 (Step 4 — Modifying Data in the SQLite Database)

Rows in a SQLite database can be modified using UPDATE and DELETE SQL statements.

可以使用UPDATE和DELETE SQL语句修改SQLite数据库中的行。

Let’s say, for example, that Sammy the shark was moved to tank number 2. We can change Sammy’s row in the fish table to reflect this change:

举例来说，假设鲨鱼Sammy被移至2号水箱。我们可以更改fish表中Sammy的行以反映此更改：

new_tank_number = 2
moved_fish_name = "Sammy"
cursor.execute(
    "UPDATE fish SET tank_number = ? WHERE name = ?",
    (new_tank_number, moved_fish_name)
)

We issue an UPDATE SQL statement to change the tank_number of Sammy to its new value of 2. The WHERE clause in the UPDATE statement ensures we only change the value of tank_number if a row has name = "Sammy".

我们发出UPDATE SQL语句，将Sammy的tank_number更改为其新值2 。 UPDATE语句中的WHERE子句可确保仅在行name = "Sammy"才更改tank_number的值。

If we run the following SELECT statement, we can confirm our update was made correctly:

如果运行以下SELECT语句，则可以确认更新正确：

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

If we run this, we will see output like the following:

如果运行此命令，将看到类似以下的输出：

   
   

    
    Output
   
   
[('Sammy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]

Notice that the row for Sammy now has the value of 2 for its tank_number column.

注意， Sammy的行的tank_number列的值现在为2 。

Let’s say that Sammy the shark was released into the wild and no longer held by the aquarium. Since Sammy no longer lives at the aquarium, it would make sense to remove the Sammy row from the fish table.

假设鲨鱼萨米(Sammy)被放逐到野外，不再被水族馆饲养。 由于Sammy不再生活在水族馆中，因此有必要从fish桌上移开Sammy排。

Issue a DELETE SQL statement to remove a row:

发出DELETE SQL语句以删除行：

released_fish_name = "Sammy"
cursor.execute(
    "DELETE FROM fish WHERE name = ?",
    (released_fish_name,)
)

We issue a DELETE SQL statement to remove the row for Sammy the shark. The WHERE clause in the DELETE statement ensures we only delete a row if that row has name = "Sammy".

我们发出DELETE SQL语句以删除Sammy the shark的行。 DELETE语句中的WHERE子句可确保仅删除name = "Sammy" 。

If we run the following SELECT statement, we can confirm our deletion was made correctly:

如果运行以下SELECT语句，则可以确认删除正确：

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

If we run this code, we will see output like the following:

如果运行此代码，我们将看到类似以下的输出：

   
   

    
    Output
   
   
[('Jamie', 'cuttlefish', 7)]

Notice that the row for Sammy the shark is now gone, and only Jamie the cuttlefish remains.

请注意， shark Sammy的行现在不见了，只剩下cuttlefish Jamie 。

步骤5 — with语句一起使用with进行自动清除 (Step 5 — Using with Statements For Automatic Cleanup)

In this tutorial, we’ve used two primary objects to interact with the "aquarium.db" SQLite database: a Connection object named connection, and a Cursor object named cursor.

在本教程中，我们使用了两个主要对象与"aquarium.db" SQLite数据库进行交互：一个名为connection的Connection对象和一个名为cursor的Cursor对象 。

In the same way that Python files should be closed when we are done working with them, Connection and Cursor objects should also be closed when they are no longer needed.

就像在处理完Python文件后应该关闭它们一样，当不再需要Connection和Cursor对象时，也应该关闭它们。

We can use a with statement to help us automatically close Connection and Cursor objects:

我们可以使用with语句来帮助我们自动关闭Connection和Cursor对象：

from contextlib import closing

with closing(sqlite3.connect("aquarium.db")) as connection:
    with closing(connection.cursor()) as cursor:
        rows = cursor.execute("SELECT 1").fetchall()
        print(rows)

closing is a convenience function provided by the contextlib module. When a with statement exits, closing ensures that close() is called on whatever object is passed to it. The closing function is used twice in this example. Once to ensure that the Connection object returned by sqlite3.connect() is automatically closed, and a second time to ensure that the Cursor object returned by connection.cursor() is automatically closed.

closing是contextlib模块提供的便利功能。 当with语句退出时， closing可确保在传递给它的任何对象上调用close() 。 在此示例中， closing功能使用了两次。 一次以确保自动关闭由sqlite3.connect()返回的Connection对象，第二次以确保自动关闭由connection.cursor()返回的Cursor对象。

If we run this code, we will see output like the following:

如果运行此代码，我们将看到类似以下的输出：

   
   

    
    Output
   
   
[(1,)]

Since "SELECT 1" is a SQL statement that always returns a single row with a single column with a value of 1, it makes sense to see a single tuple with 1 as its only value returned by our code.

由于"SELECT 1"是一条SQL语句，始终返回单行和单列，其值均为1 ，因此将单个元组视为1是我们的代码返回的唯一值是有意义的。

结论 (Conclusion)

The sqlite3 module is a powerful part of the Python standard library; it lets us work with a fully featured on-disk SQL database without installing any additional software.

sqlite3模块是Python标准库的强大功能； 它使我们可以使用功能全面的磁盘SQL数据库，而无需安装任何其他软件。

In this tutorial, we learned how to use the sqlite3 module to connect to a SQLite database, add data to that database, as well as read and modify data in that database. Along the way, we also learned about the risks of SQL injection attacks and how to use contextlib.closing to automatically call close() on Python objects in with statements.

在本教程中，我们学习了如何使用sqlite3模块连接到SQLite数据库，向该数据库添加数据以及读取和修改该数据库中的数据。 在此过程中，我们还了解了SQL注入攻击的风险以及如何使用contextlib.closing在with语句中对Python对象自动调用close() 。

From here we can learn more about SQL databases in SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

从这里我们可以了解有关SQLite，MySQL和PostgreSQL中的 SQL数据库的更多信息：关系数据库管理系统的比较 。

翻译自: https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3