Python+Sqlite3数据库简单示例教程
本篇文章分享了Python如何和Sqlite3一起工作，包含python连接sqlite3数据库，python在sqlite3中搜索数据以及使用python删除sqlite3的数据库代码示例。
软件开发人员必须处理数据。通常情况下，您使用的数据需要同时可供多个开发人员和多个用户使用。这种情况的典型解决方案是使用数据库。数据库以表格格式保存数据，这意味着它们标记了数据的列和行。
大多数数据库软件要求您在本地计算机或您有权访问的服务器上安装复杂的软件。流行的数据库软件包括 Microsoft SQL Server、PostgreSQL 和 MySQL 等。出于本文的目的，您将关注一个非常简单的SQLite。您将使用 SQLite 的原因是它是一个包含在 Python 中的基于文件的数据库系统。您无需进行任何配置或额外安装。这使您可以专注于数据库是什么及其运作方式的基本要素，同时避免在安装和设置细节中迷失的危险。
在本文中，您将了解以下内容：
- 创建 SQLite 数据库
- 将数据添加到您的数据库
- 搜索您的数据库
- 编辑数据库中的数据
- 从数据库中删除数据
现在让我们开始学习如何在数据库中使用 Python！
Python连接 SQLite 数据库
有第三方方 SQL 连接器包可帮助您将 Python 代码连接到所有主要数据库。Python 标准库已经sqlite3内置了一个库，您将使用它。这意味着您无需安装任何额外的东西即可完成本文。您可以在sqlite3此处阅读该库的文档：
要开始使用数据库，您需要连接到预先存在的数据库或创建一个新数据库。出于本文的目的，您将创建一个数据库。但是，您将在本文中学到足够多的知识，如果您愿意，还可以加载预先存在的数据库并与之交互。
SQLite 支持以下类型的数据：
NULLINTEGERREALTEXTBLOB
这些是您可以存储在此类数据库中的数据类型。如果您想了解有关 Python 数据类型如何转换为 SQLite 数据类型以及反之亦然的更多信息，请参阅以下链接：
现在是您创建数据库的时候了！以下是使用 Python 创建 SQLite 数据库的方法：
import sqlite3
sqlite3.connect("library.db")首先，导入sqlite3然后使用该connect()函数，该函数将数据库文件的路径作为参数。如果文件不存在，sqlite3模块将创建一个空数据库。创建数据库文件后，您需要添加一个表才能使用它。用于执行此操作的基本 SQL 命令如下：
CREATE TABLE table_name
(column_one TEXT, column_two TEXT, column_three TEXT)SQL 中的关键字不区分大小写——因此 CREATE == Create == create。但是，标识符可能区分大小写——这取决于所使用的 SQL 引擎以及该引擎或数据库可能使用的配置设置。如果使用预先存在的数据库，请检查其文档或仅使用与表和字段名称相同的大小写。
您将遵循大写 KEYWORDS 和混合或小写标识符的约定。
该CREATE TABLE命令将使用指定的名称创建一个表。您可以使用每列的名称以及列类型来遵循该命令。也可以将列视为字段，将列类型视为字段类型。上面的 SQL 代码片段创建了一个三列表，其中所有列都包含文本。如果调用此命令并且该表已存在于数据库中，则会收到错误消息。
您可以创建数据库允许的任意数量的表。行数和列数可能会受到数据库软件的限制，但大多数情况下您不会遇到此限制。
如果你结合你在最后两个例子中学到的信息，你可以创建一个数据库来存储有关书籍的信息。创建一个名为的新文件create_database.py并输入以下代码：
# create_database.py
import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
# create a table
cursor.execute("""CREATE TABLE books
(title TEXT, author TEXT, release_date TEXT,
publisher TEXT, book_type TEXT)
""")要使用 SQLite 数据库，您需要connect()访问它，然后cursor()从该连接创建一个对象。这cursor是您用来通过其execute()功能将 SQL 命令发送到您的数据库的内容。上面的最后一行代码将使用您之前看到的 SQL 语法创建一个books包含五个字段的表：
title- 书名作为文本author- 这本书的作者作为文本release_date- 这本书作为文本发布的日期publisher- 作为文本的书的出版商book_type- 书籍类型（印刷版、epub、PDF 等）
现在您有一个可以使用的数据库，但它没有数据。您将在下一节中了解如何向表中添加数据！
Python将数据添加到您的Sqlite3数据库
将数据添加到数据库是使用INSERT INTOSQL 命令完成的。将此命令与要插入数据的表的名称结合使用。通过查看一些代码，这个过程会变得更加清晰，所以继续创建一个名为add_data.py. 然后在其中添加以下代码：
# add_data.py
import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
# insert a record into the books table in the library database
cursor.execute("""INSERT INTO books
VALUES ('Python 101', 'Mike Driscoll', '9/01/2020',
'Mouse Vs Python', 'epub')"""
)
# save data
conn.commit()
# insert multiple records using the more secure "?" method
books = [('Python Interviews', 'Mike Driscoll',
'2/1/2018', 'Packt Publishing', 'softcover'),
('Automate the Boring Stuff with Python',
'Al Sweigart', '', 'No Starch Press', 'PDF'),
('The Well-Grounded Python Developer',
'Doug Farrell', '2020', 'Manning', 'Kindle')]
cursor.executemany("INSERT INTO books VALUES (?,?,?,?,?)", books)
conn.commit()前六行显示了如何连接到数据库并像以前一样创建游标。然后您使用execute()调用INSERT INTO并传递一系列 5 VALUES。要将该记录保存到数据库表中，您需要调用commit().
最后几行代码展示了如何使用executemany(). 您传递executemany()一条 SQL 语句和要与该 SQL 语句一起使用的项目列表。虽然还有其他插入数据的方法，使用“？” 上面示例中的语法是将值传递给游标的首选方式，因为它可以防止 SQL 注入攻击。
如果您想了解有关 SQL 注入的更多信息，可以从 Wikipedia 开始：
现在您的表中有数据，但您无法实际查看该数据。接下来您将了解如何做到这一点！
Python使用Sqlite3数据库搜索数据
从数据库中提取数据主要与完成SELECT，FROM和WHERE关键字。您会发现这些命令并不太难使用。您应该创建一个名为的新文件queries.py并在其中输入以下代码：
import sqlite3
def get_cursor():
conn = sqlite3.connect("library.db")
return conn.cursor()
def select_all_records_by_author(cursor, author):
sql = "SELECT * FROM books WHERE author=?"
cursor.execute(sql, [author])
print(cursor.fetchall()) # or use fetchone()
print("\nHere is a listing of the rows in the table\n")
for row in cursor.execute("SELECT rowid, * FROM books ORDER BY author"):
print(row)
def select_using_like(cursor, text):
print("\nLIKE query results:\n")
sql = f"""
SELECT * FROM books
WHERE title LIKE '{text}%'"""
cursor.execute(sql)
print(cursor.fetchall())
if __name__ == '__main__':
cursor = get_cursor()
select_all_records_by_author(cursor,
author='Mike Driscoll')
select_using_like(cursor, text='Python')这段代码有点长，所以我们将分别介绍每个函数。这是第一部分代码：
import sqlite3
def get_cursor():
conn = sqlite3.connect("library.db")
return conn.cursor()该get_cursor()函数是连接数据库并返回cursor对象的有用函数。您可以通过将您希望打开的数据库的名称传递给它来使其更通用。
下一个函数将向您展示如何获取数据库表中特定作者的所有记录：
def select_all_records_by_author(cursor, author):
sql = "SELECT * FROM books WHERE author=?"
cursor.execute(sql, [author])
print(cursor.fetchall()) # or use fetchone()
print("\nHere is a listing of the rows in the table\n")
for row in cursor.execute("SELECT rowid, * FROM books ORDER BY author"):
print(row)要从数据库中获取所有记录，您可以使用以下 SQL 命令：SELECT * FROM books. SELECT，默认情况下，从数据库表中的每条记录返回请求的字段。星号是通配符，表示“我想要所有字段”。So SELECTand *combine 将返回当前表中的所有数据。你通常不想这样做！表可能会变得非常大，并且试图一次从中提取所有内容可能会对您的数据库或计算机的性能产生不利影响。相反，您可以使用该WHERE子句来过滤SELECT更具体的内容，和/或仅选择您感兴趣的字段。
在此示例中，您将 过滤SELECT为特定的author。您仍在选择所有记录，但单个作者不太可能对太多行产生负面影响。然后将光标告诉fetchall()，它将从SELECT您所做的调用中获取所有结果。您可以使用fetchone()仅从SELECT.
最后两行代码获取表中的所有条目books及其rowids，并按作者姓名对结果进行排序。此函数的输出如下所示：
Here is a listing of the rows in the table
(3, 'Automate the Boring Stuff with Python', 'Al Sweigart', '', 'No Starch Press', 'PDF')
(4, 'The Well-Grounded Python Developer', 'Doug Farrell', '2020', 'Manning', 'Kindle')
(1, 'Python 101', 'Mike Driscoll', '9/01/2020', 'Mouse Vs Python', 'epub')
(2, 'Python Interviews', 'Mike Driscoll', '2/1/2018', 'Packt Publishing', 'softcover')您可以看到，当您 sort by 时author，它使用整个字符串而不是姓氏进行排序。如果您正在寻找挑战，您可以尝试弄清楚如何存储数据以使其能够按姓氏排序。或者，您可以编写更复杂的 SQL 查询或在 Python 中处理结果以更好的方式对其进行排序。
要查看的最后一个函数是select_using_like()：
def select_using_like(cursor, text):
print("\nLIKE query results:\n")
sql = f"""
SELECT * FROM books
WHERE title LIKE '{text}%'"""
cursor.execute(sql)
print(cursor.fetchall())此函数演示了如何使用 SQL 命令LIKE，这是一种过滤通配符搜索。在此示例中，您告诉它查找后面带有百分号的特定字符串。百分号是通配符，因此它将查找标题以传入字符串开头的任何记录。
当您在text设置为“Python”的情况下运行此函数时，您将看到以下输出：
此函数演示了如何使用 SQL 命令LIKE，这是一种过滤通配符搜索。在此示例中，您告诉它查找后面带有百分号的特定字符串。百分号是通配符，因此它将查找标题以传入字符串开头的任何记录。
当您在text设置为“Python”的情况下运行此函数时，您将看到以下输出：此函数演示了如何使用 SQL 命令LIKE，这是一种过滤通配符搜索。在此示例中，您告诉它查找后面带有百分号的特定字符串。百分号是通配符，因此它将查找标题以传入字符串开头的任何记录。
当您在text设置为“Python”的情况下运行此函数时，您将看到以下输出：
此函数演示了如何使用 SQL 命令LIKE，这是一种过滤通配符搜索。在此示例中，您告诉它查找后面带有百分号的特定字符串。百分号是通配符，因此它将查找标题以传入字符串开头的任何记录。
当您在text设置为“Python”的情况下运行此函数时，您将看到以下输出：此函数演示了如何使用 SQL 命令LIKE，这是一种过滤通配符搜索。在此示例中，您告诉它查找后面带有百分号的特定字符串。百分号是通配符，因此它将查找标题以传入字符串开头的任何记录。
当您在text设置为“Python”的情况下运行此函数时，您将看到以下输出：
# update_record.py
import sqlite3
def update_author(old_name, new_name):
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
sql = f"""
UPDATE books
SET author = '{new_name}'
WHERE author = '{old_name}'
"""
cursor.execute(sql)
conn.commit()
if __name__ == '__main__':
update_author(
old_name='Mike Driscoll',
new_name='Michael Driscoll',
)在此示例中，您创建了update_author()一个接受要查找的旧作者姓名和要更改为的新作者姓名。然后连接到数据库并创建游标，就像在前面的示例中一样。此处的 SQL 代码告诉您的数据库您要更新books表并将author字段设置为新名称，其中author名称当前等于旧名称。最后，你execute()和commit()变化。
要测试此代码是否有效，您可以重新运行上一节中的查询代码并检查输出。
现在您已准备好学习如何从数据库中删除数据！
Python从Sqlite3数据库中删除数据
有时必须从数据库中删除数据。例如，如果您决定不再是银行的客户，您会希望他们在一段时间过后从他们的数据库中清除您的信息。要从数据库中删除，可以使用该DELETE命令。
继续创建一个名为的新文件delete_record.py并添加以下代码以查看删除数据的工作原理：
# delete_record.py
import sqlite3
def delete_author(author):
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
sql = f"""
DELETE FROM books
WHERE author = '{author}'
"""
cursor.execute(sql)
conn.commit()
if __name__ == '__main__':
delete_author(author='Al Sweigart')在这里，您创建delete_author()一个以您希望从数据库中删除的作者的姓名。除了 SQL 语句本身之外，此示例中的代码与前一个示例几乎相同。在 SQL 查询中，您DELETE FROM用来告诉数据库要从哪个表中删除数据。然后使用该WHERE子句告诉它使用哪个字段来选择目标记录。在这种情况下，您告诉数据库从books表中删除与author名称匹配的所有记录。
您可以使用本文前面的 SQL 查询代码验证此代码是否有效。
总结
使用数据库可能需要大量工作。本文仅介绍了使用数据库的基础知识。在这里，您学习了如何执行以下操作：
- 创建 SQLite 数据库
- 将数据添加到您的数据库
- 搜索您的数据库
- 编辑数据库中的数据
- 从数据库中删除数据
如果您发现 SQL 代码有点难以理解，您可能需要查看“对象关系映射器”包，例如SQLAlchemy或SQLObject。对象关系映射器 (ORM) 将 Python 语句转换为 SQL 代码，以便您只编写 Python 代码。有时您可能仍然需要使用裸 SQL 来从数据库中获得所需的效率，但这些 ORM 可以帮助加快开发速度并使事情变得更容易。
以下是这两个项目的一些链接：
- SQLALchemy - https://www.sqlalchemy.org/
- SQLObject - http://sqlobject.org/
{{ nComment.author.nickname }}
{{ nComment.time }}