这篇文章我们将介绍如何结合 Python 的 sqlite3 接口使用 SQLite。SQLite 是 Python 内置的单个文件关系型数据库。SQLite 经常被用作小型应用的首选技术，特别是嵌入式系统和设备（如手机和平板电脑、智能家电和仪器），小型的 Web 和桌面应用也是可以使用SQLite进行快速开发。

创建数据库并建立连接

使用 Python 标准库中的 sqlite3 模块创建新的 SQLite 数据库就和创建连接一样简单。要建立连接，只需要在 sqlite3 模块中通过 connect(...) 方法传递一个文件路径，如果该路径代表的数据库不存在，则会在该路径下创建一个数据库。

import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')

你会发现，在日常的数据库编程中，你会不断地创建与数据库的连接，我们可以将这个简单的连接语句封装成可重用的通用函数。

# db_utils.py 
import os
import sqlite3

# 创建一个默认路径，用于连接和创建（如果必要的话）一个名为 'database.sqlite3' 的数据库，
# 数据库存放在与此脚本相同的目录中
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')

def db_connect(db_path=DEFAULT_PATH):
    con = sqlite3.connect(db_path)
    return con

创建表格

加入我们现在需要创建订单相关的表格，该保存的信息结构如下

在检查这些数据后，明显可以看出它包含有关客户、产品和订单的信息。在此类交易系统的数据库设计中，一个常见模式是将订单拆分为另外两个表，即订单和订单明细（有时称为订单详情），以实现更高的规范化。

在 Python 解释器中，在之前定义的 db_utils.py 模块所在的同一目录中，输入以下 SQL 以创建 customers 和 products 表：

>>> from db_utils import db_connect
>>> con = db_connect()# 连接DB
>>> cur = con.cursor()# 初始化游标对象
>>> customers_sql = """
... CREATE TABLE customers (
...     id integer PRIMARY KEY,
...     first_name text NOT NULL,
...     last_name text NOT NULL)"""
>>> cur.execute(customers_sql)
>>> products_sql = """
... CREATE TABLE products (
...     id integer PRIMARY KEY,
...     name text NOT NULL,
...     price real NOT NULL)"""
>>> cur.execute(products_sql)

上面的代码创建了一个连接对象，然后使用它实例化了一个游标对象。游标对象用于在 SQLite 数据库上执行 SQL 语句。

有了创建的游标后，我接着编写了 SQL 来创建顾客表，为其添加了主键以及名为 first 和 last 的文本字段，并将其分配给一个名为 customers_sql 的变量。然后我调用了游标对象的 execute(...) 方法，并传递了 customers_sql 变量。接着我以类似的方式创建了一个产品表。

你可以查询 sqlite_master 表，这是一个内置的 SQLite 元数据表，以验证上述命令是否成功。

要查看当前连接的数据库中的所有表，请查询 sqlite_master 表的 name 列，其中 type 等于”table”。

>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchall())
[('customers',), ('products',)]

要查看表的架构，请查询同一表中 type 仍为“表”且 name 等于“客户”和/或“产品”的 sql 列。

>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table' AND name='customers'""")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchone()[0])
CREATE TABLE customers (
    id integer PRIMARY KEY,
    first_name text NOT NULL,
    last_name text NOT NULL)

接下来要定义的表是订单表，它通过外键将客户与订单关联起来，并包括购买日期。由于 SQLite 不支持实际的日期/时间数据类型（或数据类以保持与 SQLite 术语一致），所有日期将以文本值表示。

>>> orders_sql = """
... CREATE TABLE orders (
...     id integer PRIMARY KEY,
...     date text NOT NULL,
...     customer_id integer,
...     FOREIGN KEY (customer_id) REFERENCES customers (id))"""
>>> cur.execute(orders_sql)

最终要定义的表格将是明细表，它详细说明了每个订单中的产品账目。

lineitems_sql = """
... CREATE TABLE lineitems (
...     id integer PRIMARY KEY,
...     quantity integer NOT NULL,
...     total real NOT NULL,
...     product_id integer,
...     order_id integer,
...     FOREIGN KEY (product_id) REFERENCES products (id),
...     FOREIGN KEY (order_id) REFERENCES orders (id))"""
>>> cur.execute(lineitems_sql)

插入数据

接下来将演示如何将我们的样本数据插入到刚刚创建的表中。

我们先将向产品表插入数据，因为没有产品我们就不能进行销售，也就无法有外键与行项目和订单相关联。查看样本数据，我发现有四种产品：

• 组合学入门（7.99）
• 编写短篇小说指南（17.99）
• 数据结构和算法（11.99）

执行 INSERT 语句的工作流程很简单：

1. 连接到数据库
2. 创建游标对象
3. 编写一个带参数的插入 SQL 语句，并将其存储为变量
4. 在游标对象上调用 execute 方法，将 sql 变量和要插入到表中的值（作为元组）传递给它

实际代码如下

>>> con = db_connect()
>>> cur = con.cursor()
>>> product_sql = "INSERT INTO products (name, price) VALUES (?, ?)"
>>> cur.execute(product_sql, ('组合数学简介', 7.99))
>>> cur.execute(product_sql, ('编写短篇小说指南', 17.99))
>>> cur.execute(product_sql, ('数据结构与算法', 11.99))

插入语句遵循标准的 SQL 语法，都是比较简单的语法。需要补充的是 ? 部分， ? 实际上是所谓的 “参数化查询” 中的占位符。

参数化查询是现代高级编程语言中所有数据库接口的重要特性，比如 Python 中的 sqlite3 模块。这种类型的查询有助于提高多次重复查询的效率。也许更重要的是，它们还会清理替代 ? 占位符的输入，这些输入在调用游标对象的 execute 方法时传递，以防止险恶的输入导致 SQL 注入。

接着是插入每个订单的数据，由顾客名和姓以及购买日期的组合来识别：

1. 将新客户插入到客户表中，并检索其主键 ID
2. 基于客户 ID 和购买日期创建订单条目，然后检索其主键 ID
3. 对订单中的每个产品确定其主键 ID，并创建一条行项目关联订单和产品的条目

>>> cur.execute("SELECT id, name, price FROM products")
>>> formatted_result = [f"{id:<5}{name:<35}{price:>5}" for id, name, price in cur.fetchall()]
>>> id, product, price = "Id", "Product", "Price"
>>> print('n'.join([f"{id:<5}{product:<35}{price:>5}"] + formatted_result))
Id   Product                            Price
1    组合数学简介       7.99
2    编写短篇小说指南   17.99
3    数据结构与算法     11.99

2021 年 2 月 22 日，张三购买了《组合数学简介》，价格为 7.99 ，这是他的首单订单。

首先为张三创建一个新的客户记录，然后通过访问游标对象的 lastrowid 字段来确定他的主键 id。

>>> customer_sql = "INSERT INTO customers (first_name, last_name) VALUES (?, ?)"
>>> cur.execute(customer_sql, ('三', '张'))
>>> customer_id = cur.lastrowid
>>> print(customer_id)
1

现在我们可以创建一个订单条目，并将其与 Turin 先生订购的产品相关联到行项目条目中。

>>> order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)"
>>> date = "2021-02-22"# ISO formatted date
>>> cur.execute(order_sql, (date, customer_id))
>>> order_id = cur.lastrowid
>>> print(order_id)
1
>>> li_sql = """INSERT INTO lineitems
...       (order_id, product_id, quantity, total)
...     VALUES (?, ?, ?, ?)"""
>>> product_id = 1
>>> cur.execute(li_sql, (order_id, 1, 1, 7.99))

剩下的订单插入方式完全相同，只是我们可以对重复性的任务进行封装成可重用的函数。在 db_utils.py 模块中添加以下代码：

def create_customer(con, first_name, last_name):
    sql = """
        INSERT INTO customers (first_name, last_name)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (first_name, last_name))
    return cur.lastrowid

def create_order(con, customer_id, date):
    sql = """
        INSERT INTO orders (customer_id, date)
        VALUES (?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (customer_id, date))
    return cur.lastrowid

def create_lineitem(con, order_id, product_id, qty, total):
    sql = """
        INSERT INTO lineitems
            (order_id, product_id, quantity, total)
        VALUES (?, ?, ?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (order_id, product_id, qty, total))
    return cur.lastrowid

我们需要 exit() 你的 Python 解释器并重新加载，以使你的新函数在解释器中可访问。

>>> from db_utils import db_connect, create_customer, create_order, create_lineitem
>>> con = db_connect()
>>> cus_id = create_customer(con, '四', '李')
>>> order = create_order(con, cus_id, '2021-07-03')
>>> create_lineitem(con, order, 2, 1, 17.99)

查询数据库

通常在数据库上执行的最常见操作是通过 SELECT 语句检索存储在其中的一些数据。下面我将演示如何使用 sqlite3 接口执行简单的 SELECT 查询。

要执行对 customers 表的基本多行查询，你需要将 SELECT 语句传递给 cursor 对象的 execute(...) 方法。之后，你可以通过调用相同 cursor 对象的 fetchall() 方法来迭代查询的结果。

>>> cur.execute("SELECT id, first_name, last_name FROM customers")
>>> results = cur.fetchall()
>>> for row in results:
...     print(row)
(1, '三', '张')
(2, '四', '李')
(3, '五', '王')

假设你想要从数据库中仅检索一条记录。你可以通过编写更具体的查询来实现这一点，比如针对 李四 的 id 为 2，并在此之后调用游标对象的 fetchone() 方法。

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> print(result)
(2, '四', '李')

看到每个结果的每一行都是以元组的形式吗？嗯，虽然元组是 Python 中非常有用的数据结构，适用于某些编程用例，但许多人在进行数据检索任务时发现它们有点麻烦，可读性比较差。其实我们可以用更灵活的方式表示数据。将连接对象的 row_factory 方法设置为更合适的值，比如 sqlite3.Row 。这将使你能够通过位置或关键字值访问每行的各个项目。

>>> import sqlite3
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> id, first_name, last_name = result['id'], result['first_name'], result['last_name']
>>> print(f"客户: {first_name}{last_name}的id是{id}")
客户: 李四的id是2

结论

在本文中，我们对 sqlite3 Python 接口的最基本的功能进行了简要演示，该接口用于轻量级的单文件 SQLite 数据库，该数据库已经内置在 Python3 中。如果对数据存储没有高并发的需求，可以考虑使用SQLite数据库最为快速开发的工具。

写作不易，欢迎关注