原文：
towardsdatascience.com/get-started-with-sqlite3-in-python-creating-tables-fetching-rows-a52bdf41e52a
由 Sunder Muthukumaran 在 Unsplash 上拍摄的照片
概述
-
简介 – 什么是 SQLite 和 SQLite3？
-
创建我们的第一个 SQLite 数据库
-
连接器和游标
-
创建表
-
向数据库中插入行
-
从数据库中检索行
-
总结
简介 – 什么是 SQLite 和 SQLite3？
对于大多数现代 IT 专业人员来说，结构化查询语言 (SQL) 是一项核心技能。这是一种声明性语言，用于与关系数据库交互。数据工程师和分析人员经常使用 SQL 来运行数据管道并调查数据中的有用关系。
当你没有任何 SQL 经验时，直接进入常见的 数据库管理系统 (DBMS) 如 PostgreSQL 或 MySQL 可能会有些令人畏惧。幸运的是，SQLite 是学习 SQL 基础的一个很好的选择。由于它没有单独的服务器进程，因此设置简单且易于管理。所以尽管数据工程师和数据分析师通常会使用与 SQLite 不同的数据库管理系统，但它是一个学习 SQL 的好地方。事实上，SQLite 是世界上使用最广泛的 DBMS 之一！
此外，Python 库 sqlite3 是与 SQLite 交互的简单接口。在这篇博客文章中，我们将使用 SQLite 和 sqlite3 库来学习两个主要概念：
-
一些使用 SQL 最基本和最有用命令（如
CREATE TABLE、INSERT INTO和SELECT - FROM）的基本方法。 -
如何使用编程语言（在我们的情况下是 Python）与关系数据库交互。
我们将设置一个 SQLite 数据库，使用 sqlite3 从 Python 创建数据库连接，并在数据库中插入/检索一些行。目标不是让你成为 SQL 专家，而是让你看到它如何被使用，并学习一些基本命令以开始学习。如果你想了解更多，我在 YouTube 上有 8 个免费的视频，它们与这篇博客文章开始的方式相同，但会深入得多：
创建我们的第一个 SQLite 数据库
在 SQLite 的官方网页上，你可以找到有关下载 SQLite 的信息。然而，对于我们大多数人来说，这并不是必要的，因为 SQLite 已经包含在大多数机器上。你还需要 Python 中的 sqlite3 库，但这个库是标准库的一部分，因此包含在大多数 Python 发行版中。所以，很可能不需要安装任何东西 😃
要检查是否已经安装了所有内容，请打开一个新的 Python 文件并写入单个命令：
import sqlite3
如果上述文件运行正常，那么 SQLite 和 Python 库 sqlite3 都已安装。我们现在可以开始了！
在导入步骤之后，我们需要创建与数据库的连接。这是通过使用 sqlite3 库中的 connect() 函数来完成的：
# Create a connection to the database
connection = sqlite3.connect("music.db")
传递给 connect() 函数的参数将是数据库名称。由于我们还没有数据库，这将简单地为我们创建一个新的数据库。如果你现在运行 Python 文件，那么在你的工作目录中会出现一个名为 music.db 的新文件。这就是我们的数据库！
关系型数据库由各种表组成。如果你是新手，那么你可以将此视为 Excel 工作表的集合。这低估了关系型数据库的强大功能，但这是一个很好的初始心理模型。
在创建连接对象之后，我们需要创建一个游标。游标可以执行针对数据库的 SQL 命令。为了创建这个游标，我们在连接对象上使用 .cursor() 方法，如下所示：
# Create a cursor
cursor = connection.cursor()
变量 cursor 现在包含了一个游标对象，我们可以使用它来向数据库中插入和检索数据。到目前为止，你应该有以下代码：
import sqlite3
# Create a connection to the database
connection = sqlite3.connect("music.db")
# Create a cursor
cursor = connection.cursor()
创建表
首先，我们需要在数据库中创建一个表。我们将处理代表 80 年代歌曲的数据。在游标对象上，我们可以调用 execute() 方法来执行 SQL 语句。我们将学习的第一个语句是 CREATE TABLE 语句：
# Create a table
cursor.execute("CREATE TABLE IF NOT EXISTS songs(name, artist, album, year, duration)")
如您从上面的命令中看到的，我们创建了一个名为songs的表，它有五个列：name、artist、album、year和duration。可选部分IF NOT EXISTS确保只有当表不存在时才创建表。如果它已经存在，则命令不执行任何操作。
尽管我们的表目前是空的，但模式是清晰的。我们正在设置一个记录关于歌曲的相关信息的表。我们想要跟踪的每首歌曲的信息是名称、艺术家、专辑、年份和时长。我们很快将用代表各种歌曲的行来填充这个表。
运行 Python 文件后，你可能会立即想到在当前目录中打开数据库文件music.db来调查发生了什么。然而，music.db中的信息并不是为了以这种方式访问的。你将只会看到混乱的信息，因为这不是用于这种查看的格式。我们将不得不编写更多的 SQL 命令来读取数据库中的信息。
恭喜你学会了你的第一个 SQL 命令！请确保在你的脑海中区分什么是 SQL 命令，什么是 Python 库 sqlite3。只有句子CREATE TABLE ...才是 SQL 命令。连接和游标是用于与数据库交互的 Python 对象。
将行插入数据库
我们现在有一个只有一个表的数据库。但表是空的！为了使数据库有用，我们需要在其中添加一些数据。现在让我们看看我们如何使用 SQL 关键字INSERT INTO将数据插入到我们数据库的表中。我们首先创建一个歌曲列表，其中每首歌曲都表示为包含信息的元组：
# Rows for the songs table
songs = [
("I Wanna Dance with Somebody (Who Loves Me)", "Whitney Houston", "Whitney", 1987, 291),
("Dancing in the Dark", "Bruce Springsteen", "Born In The U.S.A.", 1984, 241),
("Take On Me", "a-ha", "Hunting High and Low", 1985, 225),
("Africa", "TOTO", "Toto IV", 1982, 295),
("Never Gonna Give You Up", "Rick Astley", "Whenever You Need Somebody", 1987, 213)
]
如您所见，每个元组有五个部分，对应于songs表中的列。对于第一首歌，我们有：
-
name将是I Wanna Dance with Somebody (Who Loves Me)。 -
artist将是Whitney Houston。 -
album将是Born In The U.S.A.。 -
year将是1987。 -
duration（以秒为单位）将是291。
现在我们已经准备好了行，我们需要将它们插入到music.db数据库中的songs表中。
一种方法是一次向表中插入一行。以下代码将第一首歌插入到表中：
# Insert a single value into the database
cursor.execute("INSERT INTO songs VALUES(?, ?, ?, ?, ?)", songs[0])
connection.commit()
在这里，我们已从songs列表中挑选出第一首歌并将其插入到songs表中。我们使用 SQL 命令INSERT INTO table VALUES，其中table是我们想要插入的表。最后，我们使用.commit()方法确保事务完全完成。
通过在 Python 中使用 for 循环重复这种方法，我们可以编写以下代码将所有行插入到表中：
# Insert all the values into the table by looping
for song in songs:
cursor.execute("INSERT INTO songs VALUES(?, ?, ?, ?, ?)", song)
connection.commit()
这里没有新的 SQL 命令，只有一些 Python 逻辑来确保所有行都插入到songs表中。
上述方法的缺点是，当你有很多行要插入时，它会非常慢。在我们的例子中，由于我们只有几首歌曲，所以一切都很迅速。但是，关系数据库中的表可以有数百万甚至数十亿行。那么在 Python 中的循环可能会减慢行的插入速度。
解决这个问题的一个方法是一次性插入所有行，而不是逐行循环。我们可以通过在游标对象上使用 .executemany() 方法，而不是我们迄今为止使用的 .execute() 方法来实现这一点。以下代码在一个批次中插入所有行：
# Can insert all the values at the same time with a batch approach
cursor.executemany("INSERT INTO songs VALUES(?, ?, ?, ?, ?)", songs)
connection.commit()
现在我们已经在 music.db 数据库中有一个名为 songs 的表，并插入了一些行。我们迄今为止编写的代码（不包括注释）看起来像这样：
import sqlite3
songs = [
("I Wanna Dance with Somebody (Who Loves Me)", "Whitney Houston", "Whitney", 1987, 291),
("Dancing in the Dark", "Bruce Springsteen", "Born In The U.S.A.", 1984, 241),
("Take On Me", "a-ha", "Hunting High and Low", 1985, 225),
("Africa", "TOTO", "Toto IV", 1982, 295),
("Never Gonna Give You Up", "Rick Astley", "Whenever You Need Somebody", 1987, 213)
]
connection = sqlite3.connect("music.db")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS songs")
cursor.execute("CREATE TABLE IF NOT EXISTS songs(name, artist, album, year, duration)")
cursor.executemany("INSERT INTO songs VALUES(?, ?, ?, ?, ?)", songs)
connection.commit()
仔细观察，你会发现我在代码中偷偷加入了一行新代码。这是执行 SQL 命令 DROP TABLE IF EXISTS songs 的那一行。如果你运行上面的代码，它首先会删除如果存在的表，然后再次构建它。
这样可以避免我们做的实验导致结果不同。通过运行上面的 Python 文件，我们重置了数据库的状态，应该在下一节中得到相同的结果。在生产系统中，这样的操作会非常昂贵，因为每次插入行时都会重建整个表。然而，这对于实验来说是可以接受的，这正是我们在做的事情。
从数据库中检索行
由 Andy Powell 在 Unsplash 拍摄的照片
现在是时候从数据库中检索行。我们将使用 SQL 关键字 SELECT 和 FROM 来完成这个操作。让我们先从数据库中获取一首歌曲：
# Fetch a single song
single_song = cursor.execute("SELECT * FROM songs").fetchone()
print(single_song)
和往常一样，我们在游标对象上使用 .execute() 方法来执行 SQL 语句。语句 SELECT * FROM songs 从数据库中检索所有列和所有行。因此，这给我们提供了所有信息。然而，我们使用 sqlite3 中的 .fetchone() 方法只检索其中一行。通过这样做，当运行我们的 Python 文件时，我们只打印出一首歌曲。
我们使用了通配符符号 * 来检索所有列。如果你只需要一些列，你可以按照以下方式指定它们：
# Fetch only name and artist column of a single song
name_and_artist = cursor.execute("SELECT name, artist FROM songs").fetchone()
print(name_and_artist)
除了 .fetchone() 方法之外，你还可以使用 .fetchmany(number_of_rows) 和 .fetchall() 方法来获取更多行。以下代码使用 .fetchall() 方法选择所有歌曲：
# Getting all the rows and columns back
full_songs = cursor.execute("SELECT * FROM songs").fetchall()
print(full_songs)
一旦你将信息带回到 Python 中，你就可以使用标准的 Python 逻辑来获取有用的见解。以下代码通过找到我们数据库中所有歌曲的平均时长来举例说明这一点：
# Get the average duration with Python logic
average_duration = 0
for song in full_songs:
average_duration += song[-1]
average_duration = average_duration / len(full_songs)
print(f"The average 80s song is {int(average_duration // 60)} minutes and {int(average_duration % 60)} seconds long.")
你可能会认为我们有点来回跳跃。我们已经在 Python 脚本中有了原始的songs列表，那么为什么我们首先需要将其插入数据库，然后再检索它呢？这就是教程有点人为的地方。在实践中，插入和从数据库中检索数据的 Python 脚本并不相同。也可能有多个 Python 脚本（或其他接口）将数据插入数据库。因此，从数据库中检索数据并计算平均时长可能是我们获取此类信息的唯一选择。
最后，在结束之前，我们需要关闭数据库连接。我们之前使用connection()函数打开了数据库连接。如果我们不关闭它，它将保持打开状态，这可能会在更复杂的应用程序中引起性能和持久性问题。确保数据库连接始终关闭是一个好的做法。在 sqlite3 中，我们可以使用连接对象上的.close()方法来完成此操作：
# Close the connection
connection.close()
以下代码演示了我们所做的一切：
import sqlite3
songs = [
("I Wanna Dance with Somebody (Who Loves Me)", "Whitney Houston", "Whitney", 1987, 291),
("Dancing in the Dark", "Bruce Springsteen", "Born In The U.S.A.", 1984, 241),
("Take On Me", "a-ha", "Hunting High and Low", 1985, 225),
("Africa", "TOTO", "Toto IV", 1982, 295),
("Never Gonna Give You Up", "Rick Astley", "Whenever You Need Somebody", 1987, 213)
]
connection = sqlite3.connect("music.db")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS songs")
cursor.execute("CREATE TABLE IF NOT EXISTS songs(name, artist, album, year, duration)")
cursor.executemany("INSERT INTO songs VALUES(?, ?, ?, ?, ?)", songs)
connection.commit()
full_songs = cursor.execute("SELECT name, artist, album, year, duration FROM songs").fetchall()
average_duration = 0
for song in full_songs:
average_duration += song[-1]
average_duration = average_duration / len(full_songs)
print(f"The average 80s song is {int(average_duration // 60)} minutes and {int(average_duration % 60)} seconds long.")
connection.close()
总结
由Spencer Bergen在Unsplash上的照片
我希望这篇博客文章能帮助你理解 SQL 命令和 Python 中的 sqlite3 库。如果你对 AI、数据科学或数据工程感兴趣，那么请随意关注我或在LinkedIn上与我联系。
**喜欢我的写作吗？**查看我的一些其他帖子以获取更多内容：
扫一扫
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
