> Let's learn all you need to get started with SQLite3 in Python! Source: Nik Piepenbreier

对于进行认真的数据分析的任何人来说，SQL和Python都已迅速成为典型技能！ 该Python SQLite教程是您在Python中启动和运行SQLite所需的唯一指南。 在这篇文章中，我们将介绍以下内容：

· 加载库

· 创建并连接到数据库

· 创建数据库表

· 新增资料

· 查询数据

· 删除资料

· 还有更多！

SQLite3（我们简称为SQLite）是标准Python 3程序包的一部分，因此您无需安装任何程序。 如果您没有运行Python 3，请查看此链接以开始使用。

您将创建什么

学习完本教程之后，您将使用Python在SQLite中创建一个数据库。 具体来说，本文将指导您完成创建数据库的所有步骤，该数据库涵盖下表，包括所有关系：

> An overview of the database we'll create. Source: Nik Piepenbreier

SQLite for Python中可用的数据类型

与其他SQL实现相比，SQLite for Python提供的数据类型更少。 这可能会有所限制。 但是，您将看到，SQLite使许多其他事情变得更加容易。 让我们快速浏览可用的数据类型：

· NULL —包含NULL值

· INTEGER —包含一个整数

· REAL —包含浮点（十进制）值

· TEXT。 —包括文字

· BLOB。 —包括一个完全作为输入存储的二进制大对象

在此列表中，您可能会注意到许多缺少的数据类型，例如日期。 不幸的是，使用SQLite时，您只能使用这些数据类型。

Python SQLite入门

> Ready? Let's get started! Source: Nik Piepenbreier

让我们通过加载库来开始本教程。 我们可以使用以下命令来做到这一点：

import sqlite3

让我们进入实际创建数据库的过程。

在Python中创建SQLite数据库

在Python SQLite教程的这一部分中，我们将探讨使用SQLite在Python中创建数据库的不同方法。 为此，我们将创建一个表示数据库的Connection对象。 该对象是使用SQLite的connect（）函数创建的。

首先创建一个.db文件，因为这是实际维护SQLite数据库的非常标准的方法。 我们将使用名为conn的变量表示连接。 我们将创建一个名为orders.db的文件。

conn = sqlite3.connect('orders.db')

通过这一行代码，我们在您工作的目录中创建了一个新的连接对象以及一个名为orders.db的新文件。 如果要指定特定目录，可以编写：

conn = sqlite3.connect(r'PATH-TO-YOUR-DIRECTORY/orders.db')

如果文件已经存在，则connect函数将仅连接到该文件。

注意：请注意，我们在包含此路径的字符串之前包括字母" r"。 这使Python知道我们正在使用原始字符串，这意味着" /"将不用于转义字符。 您可以通过查看此链接来了解有关原始字符串的更多信息。

connect函数创建与SQLite数据库的连接，并返回一个表示它的对象。

内存数据库

在Python中使用SQLite生成数据库的另一种方法是在内存中创建数据库。 这是生成可用于测试目的的数据库的好方法，因为它们仅存在于RAM中。

conn = sqlite3.connect(:memory:)

但是，出于本教程的目的以及您会遇到的大多数使用情况，您将使用我们前面介绍的方法。

创建一个游标对象

现在我们已经创建了一个数据库连接对象，接下来的任务是创建一个游标对象。 简而言之，游标对象使我们能够对数据库执行SQL查询。 我们将创建一个变量cur来保存光标对象：

cur = conn.cursor()

> Cursors are critical to SQLite3 in Python. Source: Nik Piepenbreier

现在我们有了一个游标对象，我们可以使用它以以下样式运行SQL查询：

cur.execute("YOUR-SQL-QUERY-HERE;")

请注意，我们将SQL查询用引号引起来-这很重要。 我们使用单引号，双引号还是三引号都没关系。 对于较长的查询，通常最好使用三引号，因为它们使我们能够编写多行查询。

在SQLite for Python中创建表

现在，在Python SQLite教程中，让我们在Python中使用SQLite创建第一个表！ 现在我们有了一个连接对象（conn）和一个游标对象（cur），我们可以创建第一个表。 按照我们之前显示的数据库模式：

> A quick reminder of what our database looks like. Source: Nik Piepenbreier

我们将从用户表开始。

cur.execute("""CREATE TABLE IF NOT EXISTS users( userid INT PRIMARY KEY, fname TEXT, lname TEXT, gender TEXT);""")

conn.commit()

在上面的代码中，我们正在做很多事情：

· 使用游标对象上的execute函数执行SQL查询

· 使用SQL生成名为用户的表

· IF NOT EXISTS在重新连接数据库时会为我们提供帮助。 该查询将使我们能够检查表是否存在，如果存在，则什么都没有改变。

· 我们创建四列：userid，fname，lname和gender。 userid被分配为主键。

· 我们通过在连接对象上使用commit函数来提交更改。

> Ready for some tables? Source: Nik Piepenbreier

要创建另一个表，我们可以遵循类似的模式并编写以下命令：

cur.execute("""CREATE TABLE IF NOT EXISTS orders( orderid INT PRIMARY KEY, date TEXT, userid TEXT, total TEXT);""")

conn.commit()

执行完这两个脚本后，您的数据库将具有两个表。 现在我们准备开始添加数据！

在Python中使用SQLite添加数据

让我们看一下如何使用Python中的SQLite向刚刚创建的数据库添加数据。 与表生成查询类似，添加数据的查询使用游标对象执行查询。

cur.execute("""INSERT INTO users(userid, fname, lname, gender) VALUES('00001', 'Nik', 'Piepenbreier', 'male');""")

conn.commit()

通常，当我们在Python中工作时，我们会拥有一些变量来保存我们的值。 例如，我们可能有一个元组，其中包含有关用户的信息，看起来可能像这样：

user = ('00002', 'Lois', 'Lane', 'Female')

如果我们想将此数据加载到我们的数据库中，我们将使用不同的约定：

cur.execute("INSERT INTO users VALUES(?, ?, ?, ?);", user)

conn.commit()

我们在这里所做的是用问号替换所有值，并添加一个包含我们希望添加的值的附加参数。

请务必注意，SQLite希望这些值采用元组格式。 但是，只要列表项是元组，变量就可以包含一个列表。 例如，我们可以使用变量添加更多用户：

more_users = [('00003', 'Peter', 'Parker', 'Male'), ('00004', 'Bruce', 'Wayne', 'male')]

在这种情况下，我们不使用execute函数，而是要使用executemany函数：

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", more_users)

conn.commit()

如果我们在这里在光标对象上使用了execute函数，则该函数将假定我们将两个项目直接传递到表中（两个元组），而不是将两组每个都传递四个项目！ 值得庆幸的是，该函数在这种情况下会失败，但是请小心使用哪个函数！

SQLite和防止注入攻击

顺便说一句，使用上面提到的（？，？，…）方法也有助于防止SQL注入攻击。 因此，建议在以前提到的方法上使用此方法。 输入也更容易，这是双赢！

一些脚本来加载更多数据

如果您自己按照Python SQLite教程进行操作，请加载更多数据以使以下各节更有意义。 您可以复制和粘贴以下脚本，以将一些示例数据插入两个表中：

customers = [('00005', 'Stephanie', 'Stewart', 'female'), ('00006', 'Sincere', 'Sherman', 'female'), ('00007', 'Sidney', 'Horn', 'male'), ('00008', 'Litzy', 'Yates', 'female'), ('00009', 'Jaxon', 'Mills', 'male'), ('00010', 'Paul', 'Richard', 'male'), ('00011', 'Kamari', 'Holden', 'female'), ('00012', 'Gaige', 'Summers', 'female'), ('00013', 'Andrea', 'Snow', 'female'), ('00014', 'Angelica', 'Barnes', 'female'), ('00015', 'Leah', 'Pitts', 'female'), ('00016', 'Dillan', 'Olsen', 'male'), ('00017', 'Joe', 'Walsh', 'male'), ('00018', 'Reagan', 'Cooper', 'male'), ('00019', 'Aubree', 'Hogan', 'female'), ('00020', 'Avery', 'Floyd', 'male'), ('00021', 'Elianna', 'Simmons', 'female'), ('00022', 'Rodney', 'Stout', 'male'), ('00023', 'Elaine', 'Mcintosh', 'female'), ('00024', 'Myla', 'Mckenzie', 'female'), ('00025', 'Alijah', 'Horn', 'female'), ('00026', 'Rohan', 'Peterson', 'male'), ('00027', 'Irene', 'Walters', 'female'), ('00028', 'Lilia', 'Sellers', 'female'), ('00029', 'Perla', 'Jefferson', 'female'), ('00030', 'Ashley', 'Klein', 'female')]orders = [('00001', '2020-01-01', '00025', '178'), ('00002', '2020-01-03', '00025', '39'), ('00003', '2020-01-07', '00016', '153'), ('00004', '2020-01-10', '00015', '110'), ('00005', '2020-01-11', '00024', '219'), ('00006', '2020-01-12', '00029', '37'), ('00007', '2020-01-14', '00028', '227'), ('00008', '2020-01-18', '00010', '232'), ('00009', '2020-01-22', '00016', '236'), ('00010', '2020-01-26', '00017', '116'), ('00011', '2020-01-28', '00028', '221'), ('00012', '2020-01-31', '00021', '238'), ('00013', '2020-02-02', '00015', '177'), ('00014', '2020-02-05', '00025', '76'), ('00015', '2020-02-08', '00022', '245'), ('00016', '2020-02-12', '00008', '180'), ('00017', '2020-02-14', '00020', '190'), ('00018', '2020-02-18', '00030', '166'), ('00019', '2020-02-22', '00002', '168'), ('00020', '2020-02-26', '00021', '174'), ('00021', '2020-02-29', '00017', '126'), ('00022', '2020-03-02', '00019', '211'), ('00023', '2020-03-05', '00030', '144'), ('00024', '2020-03-09', '00012', '112'), ('00025', '2020-03-10', '00006', '45'), ('00026', '2020-03-11', '00004', '200'), ('00027', '2020-03-14', '00015', '226'), ('00028', '2020-03-17', '00030', '189'), ('00029', '2020-03-20', '00004', '152'), ('00030', '2020-03-22', '00026', '239'), ('00031', '2020-03-23', '00012', '135'), ('00032', '2020-03-24', '00013', '211'), ('00033', '2020-03-27', '00030', '226'), ('00034', '2020-03-28', '00007', '173'), ('00035', '2020-03-30', '00010', '144'), ('00036', '2020-04-01', '00017', '185'), ('00037', '2020-04-03', '00009', '95'), ('00038', '2020-04-06', '00009', '138'), ('00039', '2020-04-10', '00025', '223'), ('00040', '2020-04-12', '00019', '118'), ('00041', '2020-04-15', '00024', '132'), ('00042', '2020-04-18', '00008', '238'), ('00043', '2020-04-21', '00003', '50'), ('00044', '2020-04-25', '00019', '98'), ('00045', '2020-04-26', '00017', '167'), ('00046', '2020-04-28', '00009', '215'), ('00047', '2020-05-01', '00014', '142'), ('00048', '2020-05-05', '00022', '173'), ('00049', '2020-05-06', '00015', '80'), ('00050', '2020-05-07', '00017', '37'), ('00051', '2020-05-08', '00002', '36'), ('00052', '2020-05-10', '00022', '65'), ('00053', '2020-05-14', '00019', '110'), ('00054', '2020-05-18', '00017', '36'), ('00055', '2020-05-21', '00008', '163'), ('00056', '2020-05-24', '00024', '91'), ('00057', '2020-05-26', '00028', '154'), ('00058', '2020-05-30', '00022', '130'), ('00059', '2020-05-31', '00017', '119'), ('00060', '2020-06-01', '00024', '137'), ('00061', '2020-06-03', '00017', '206'), ('00062', '2020-06-04', '00013', '100'), ('00063', '2020-06-05', '00021', '187'), ('00064', '2020-06-09', '00025', '170'), ('00065', '2020-06-11', '00011', '149'), ('00066', '2020-06-12', '00007', '195'), ('00067', '2020-06-14', '00015', '30'), ('00068', '2020-06-16', '00002', '246'), ('00069', '2020-06-20', '00028', '163'), ('00070', '2020-06-22', '00005', '184'), ('00071', '2020-06-23', '00022', '68'), ('00072', '2020-06-27', '00013', '92'), ('00073', '2020-06-30', '00022', '149'), ('00074', '2020-07-04', '00002', '65'), ('00075', '2020-07-05', '00017', '88'), ('00076', '2020-07-09', '00007', '156'), ('00077', '2020-07-13', '00010', '26'), ('00078', '2020-07-16', '00008', '55'), ('00079', '2020-07-20', '00019', '81'), ('00080', '2020-07-22', '00011', '78'), ('00081', '2020-07-23', '00026', '166'), ('00082', '2020-07-27', '00014', '65'), ('00083', '2020-07-30', '00021', '205'), ('00084', '2020-08-01', '00026', '140'), ('00085', '2020-08-05', '00006', '236'), ('00086', '2020-08-06', '00021', '208'), ('00087', '2020-08-07', '00021', '169'), ('00088', '2020-08-08', '00004', '157'), ('00089', '2020-08-11', '00017', '71'), ('00090', '2020-08-13', '00025', '89'), ('00091', '2020-08-16', '00014', '249'), ('00092', '2020-08-18', '00012', '59'), ('00093', '2020-08-19', '00013', '121'), ('00094', '2020-08-20', '00025', '179'), ('00095', '2020-08-22', '00017', '208'), ('00096', '2020-08-26', '00024', '217'), ('00097', '2020-08-28', '00004', '206'), ('00098', '2020-08-30', '00017', '114'), ('00099', '2020-08-31', '00017', '169'), ('00100', '2020-09-02', '00022', '226')]

您可以使用以下查询加载该数据：

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)

cur.executemany("INSERT INTO orders VALUES(?, ?, ?, ?);", orders)

conn.commit()

使用Python在SQLite中选择数据

在本Python SQLite教程的下一步中，我们将看一下如何在Python中使用SQLite选择数据！ 我们将采用与执行上面的查询类似的结构，但也会向其中添加另一个元素。

> There are many ways to return data. Source: Nik Piepenbreier

在Python中使用SQLite中的fetchone（）

让我们从使用fetchone（）函数开始。 我们创建一个变量one_result以仅提取结果

cur.execute("SELECT * FROM users;")one_result = cur.fetchone()print(one_result)

返回：

[(1, 'Nik', 'Piepenbreier', 'male')]

在Python与SQLite中使用fetchmany（）

假设我们要返回的结果不止一个，可以使用fetchmany（）函数。 让我们运行一个不同的脚本来生成3个结果：

cur.execute("SELECT * FROM users;")

three_results = cur.fetchmany(3)

print(three_results)

这将返回以下内容：

[(1, 'Nik', 'Piepenbreier', 'male'), (2, 'Lois', 'Lane', 'Female'), (3, 'Peter', 'Parker', 'Male')]

在SQLite和Python中使用fetchall（）

同样，我们可以使用fetchall（）函数返回所有结果。 如果运行以下命令，将返回所有结果：

cur.execute("SELECT * FROM users;")all_results = cur.fetchall()print(all_results)

使用Python在SQLite中删除数据

> Let's learn how to delete data as well! Source: Nik Piepenbreier

现在，我们来看看如何在Python中使用SQLite删除数据。 我们可以使用与上面类似的结构来完成此操作。 假设我们要删除姓氏为" Parker"的任何用户，我们可以这样写：

cur.execute("DELETE FROM users WHERE lname='Parker';")

conn.commit()

然后，当我们运行以下查询时：

cur.execute("select * from users where lname='Parker'")

print(cur.fetchall())

这将打印出一个空列表，确认记录已被删除。

在Python中使用SQLite连接表

最后，让我们看一下如何将数据与更复杂的查询结合在一起。 假设我们要生成一个查询，其中包含每个订单上每个客户的名字和姓氏。

为此，我们编写以下代码：

cur.execute("""SELECT *, users.fname, users.lname FROM orders LEFT JOIN users ON users.userid=orders.userid;""")

print(cur.fetchall())

同样，您可以应用其他一些SQL技巧。 我将介绍许多这样的方法，例如对数据进行排序，有条件地选择数据以及更深入地研究联接表。

结论：Python SQLite教程

在此Python SQLite教程中，我们探索了在Python中开始使用SQLite所需了解的一切。 我们从如何加载库开始，探讨了如何创建数据库和表，如何添加数据，如何查询表以及如何删除数据。

> Thanks so much for taking the time! You're really, really awesome! Source: Nik Piepenbreier

(本文翻译自Nik Piepenbreier的文章《Python SQLite Tutorial — The Ultimate Guide》，参考：

https://towardsdatascience.com/python-sqlite-tutorial-the-ultimate-guide-fdcb8d7a4f30)