{"position":0,"title":"介绍","layout":"doc-fullscreen","text":"## 介绍nn在本实验中，你将学习如何在 SQLite 中处理 JSON 数据。你会逐步了解如何在 SQLite 数据库中存储、提取、筛选以及更新 JSON 数据。这个实验为你提供了一个关于 SQLite JSON 数据处理的实用入门，帮助你掌握现代数据管理中越来越重要的一项技能。n","need_verify":false,"has_solution":false},{"position":1,"title":"创建数据库和表","layout":"doc-workbench-split","text":"## 创建数据库和表nn在这一步中，你将创建一个 SQLite 数据库，以及一个专门用于存储 JSON 数据的数据表。SQLite 是一种轻量级关系型数据库，它将所有数据保存在单个文件中，因此部署简单、管理方便。nn首先，打开终端。默认工作路径是 `/home/labex/project`。nn现在，让我们创建一个目录来存放数据库文件。nn```bashnmkdir sqlite_jsonncd sqlite_jsonn```nn以上命令会先创建一个名为 `sqlite_json` 的目录，然后切换到该目录中，这样可以让项目文件结构更加清晰。nn接下来，创建一个名为 `mydatabase.db` 的 SQLite 数据库。nn```bashnsqlite3 mydatabase.dbn```nn这条命令会打开 SQLite shell，并连接到 `mydatabase.db` 数据库。如果该数据库文件尚不存在，SQLite 会自动创建它。nn现在，创建一个名为 `products` 的表，其中包含 `id`、`name` 和 `details` 三个字段。`details` 列将以文本形式保存 JSON 数据。nn```sqlnCREATE TABLE products (n id INTEGER PRIMARY KEY AUTOINCREMENT,n name TEXT,n details TEXTn);n```nn这条 SQL 语句会创建 `products` 表：nn- `id`：唯一的整数主键，并会为每个新产品自动递增。n- `name`：产品名称，例如 Laptop、Smartphone。n- `details`：文本类型字段，用于存储 JSON 格式的数据内容。n","need_verify":true,"has_solution":false},{"position":2,"title":"插入 JSON 数据","layout":"doc-workbench-split","text":"## 插入 JSON 数据nn在这一步中，你将把 JSON 数据插入到 `products` 表中。nn下面先向 `products` 表插入两条示例记录。nn```sqlnINSERT INTO products (name, details) VALUES (n 'Laptop',n '{\"brand\": \"Dell\", \"model\": \"XPS 13\", \"specs\": {\"cpu\": \"Intel i7\", \"memory\": \"16GB\", \"storage\": \"512GB SSD\"}}'n);nnINSERT INTO products (name, details) VALUES (n 'Smartphone',n '{\"brand\": \"Samsung\", \"model\": \"Galaxy S21\", \"specs\": {\"display\": \"6.2 inch\", \"camera\": \"12MP这些 INSERT 语句会向 products 表插入两条产品记录，其中 details 列存储的是 JSON 格式的文本字符串。 插入完成后，可以执行下面的查询语句，检查数据是否已经成功写入： ```sql SELECT * FROM products; ``` 预期输出如下： ```text 1|Laptop|{\"brand\": \"Dell\", \"model\": \"XPS 13\", \"specs\": {\"cpu\": \"Intel i7\", \"memory\": \"16GB\", \"storage\": \"512GB SSD\"}} 2|Smartphone|{\"brand\": \"Samsung\", \"model\": \"Galaxy S21\", \"specs\": {\"display\": \"6.2 inch\", \"camera\": \"12MP\", \"storage\": \"128GB\"}} ``` 如果看到以上结果，说明 JSON 数据已经成功保存到 `products` 表中。 由于 SQLite 本身不直接提供内置 JSON 处理函数，接下来我们将借助一个自定义 Python 函数，从 JSON 字符串中提取指定字段。 先退出 SQLite shell： ```sql .exit ``` 然后创建一个名为 `json_extractor.py` 的 Python 文件： ```bash nano json_extractor.py ``` 接着，将下面这段 Python 代码写入 `json_extractor.py` 文件中：nn```pythonn## 导入必要的库nimport sqlite3nimport jsonnn## 定义一个函数，使用路径从 JSON 字符串中提取值ndef json_extract(json_str, path):n try:n ## 将 JSON 字符串解析为 Python 字典n json_data = json.loads(json_str)n ## 将路径拆分为组件（例如，'specs.cpu' 变为 ['specs', 'cpu']）n path_components = path.split('.')n ## 从完整的 JSON 对象开始n value = json_datan ## 使用路径组件遍历 JSON 对象n for component in path_components:n ## 获取当前组件的值n value = value.get(component)n ## 返回最终值n return valuen ## 处理 JSON 无效或路径不存在的错误n except (json.JSONDecodeError, AttributeError, TypeError):n return Nonenn## 定义一个连接数据库并注册自定义函数的函数ndef connect_db(db_path):n ## 连接到指定路径的 SQLite 数据库n conn = sqlite3.connect(db_path)n ## 将 Python 函数 'json_extract' 注册为自定义 SQL 函数n ## \"json_extract\" 是 SQL 中的名称，2 是参数数量，json_extract 是 Python 函数n conn.create_function(\"json_extract\", 2, json_extract)n ## 返回数据库连接n return connnn## 当脚本直接执行时运行此块nif __name__ == '__main__':n ## 连接到数据库并注册函数n conn = connect_db('mydatabase.db')n ## 创建一个游标对象来执行 SQL 查询n cursor = conn.cursor()nn ## 使用自定义 SQL 函数从 'details' 列中提取 'brand'n cursor.execute(\"SELECT json_extract(details, 'brand') FROM products WHERE name = 'Laptop'\")n ## 获取结果并打印n print(cursor.fetchone()[0])nn ## 使用自定义 SQL 函数从嵌套的 'specs' 对象中提取 'cpu'n cursor.execute(\"SELECT json_extract(details, 'specs.cpu') FROM products WHERE name = 'Laptop'\")n ## 获取结果并打印n print(cursor.fetchone()[0])nn ## 关闭数据库连接n conn.close()n```nn这段 Python 代码定义了一个 `json_extract` 函数。该函数接收 JSON 字符串和路径作为输入，并返回对应路径下的值。代码中还包含一个 `connect_db` 函数，用于连接 SQLite 数据库并注册 `json_extract` 自定义函数。nn- `json.loads(json_str)`：这一行会把 JSON 字符串解析为 Python 字典。n- `path.split('.')`：这一行会将路径拆分成组件列表。例如，`'specs.cpu'` 会变成 `['specs', 'cpu']`。n- 随后通过循环遍历路径组件，逐层访问 JSON 数据中的嵌套字段。nn保存文件并退出 `nano`。nn现在，运行 Python 脚本。nn```bashnpython3 json_extractor.pyn```nn**预期输出：**nn```nDellnIntel i7n```nn该脚本会连接数据库，注册 `json_extract` 函数，然后提取 Laptop 产品的品牌和 CPU 信息。n","need_verify":true,"has_solution":false},{"position":4,"title":"使用 JSON 查询过滤数据","layout":"doc-workbench-split","text":"## 使用 JSON 查询过滤数据nn在这一步中，你将使用自定义的 `json_extract` 函数，根据 JSON 字段中的值筛选数据。nn再次打开 `json_extractor.py` 文件。nn```bashnnano json_extractor.pyn```nn修改 `json_extractor.py` 文件，加入一个用于查询数据库的函数：nn```pythonn## 导入必要的库nimport sqlite3nimport jsonnn## 定义一个函数，使用路径从 JSON 字符串中提取值ndef json_extract(json_str, path):n try:n ## 将 JSON 字符串解析为 Python 字典n json_data = json.loads(json_str)n ## 将路径分割成组件（例如，'specs.cpu' 变成 ['specs', 'cpu']）n path_components = path.split('.')n ## 从完整的 JSON 对象开始n value = json_datan ## 使用路径组件遍历 JSON 对象n for component in path_components:n ## 获取当前组件的值n value = value.get(component)n ## 返回最终值n return valuen ## 处理 JSON 无效或路径不存在的错误n except (json.JSONDecodeError, AttributeError, TypeError):n return Nonenn## 定义一个函数来连接数据库并注册自定义函数ndef connect_db(db_path):n ## 连接到指定路径的 SQLite 数据库n conn = sqlite3.connect(db_path)n ## 将 Python 函数 'json_extract' 注册为自定义 SQL 函数n conn.create_function(\"json_extract\", 2, json_extract)n ## 返回数据库连接n return connnn## 定义一个函数来根据 JSON 字段过滤产品ndef filter_products(db_path, json_path, value):n ## 连接到数据库n conn = connect_db(db_path)n ## 创建一个游标对象n cursor = conn.cursor()n ## 使用 f-string 创建 SQL 查询，按 JSON 值进行过滤n query = f\"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'\"n ## 执行查询n cursor.execute(query)n ## 获取所有匹配的结果n results = cursor.fetchall()n ## 关闭数据库连接n conn.close()n ## 返回结果n return resultsnn## 当脚本直接执行时运行此块nif __name__ == '__main__':n ## 示例用法：n ## 过滤品牌为 'Dell' 的产品n dell_products = filter_products('mydatabase.db', 'brand', 'Dell')n print(\"Products with brand 'Dell':\", dell_products)nn ## 过滤 CPU 为 'Intel i7' 的产品n intel_products = filter_products('mydatabase.db', 'specs.cpu', 'Intel i7')n print(\"Products with CPU 'Intel i7':\", intel_products)n```nn这段代码新增了一个 `filter_products` 函数。该函数接收数据库路径、JSON 路径和值作为输入，然后连接数据库、注册 `json_extract` 函数，并执行查询，查找在指定 JSON 路径下值与给定条件匹配的所有产品。nn保存文件并退出 `nano`。nn现在，运行 Python 脚本。nn```bashnpython3 json_extractor.pyn```nn**预期输出：**nn```nProducts with brand 'Dell': [(1, 'Laptop', '{\"brand\": \"Dell\", \"model\": \"XPS 13\", \"specs\": {\"cpu\": \"Intel i7\", \"memory\": \"16GB\", \"storage\": \"512GB SSD\"}}')]nProducts with CPU 'Intel i7': [(1, 'Laptop', '{\"brand\": \"Dell\", \"model\": \"XPS 13\", \"specs\": {\"cpu\": \"Intel i7\", \"memory\": \"16GB\", \"storage\": \"512GB SSD\"}}')]n```nn以上输出说明，程序已经成功筛选出了符合指定 JSON 条件的产品数据。n","need_verify":true,"has_solution":false},{"position":5,"title":"更新 JSON 值","layout":"doc-workbench-split","text":"## 更新 JSON 值nn在这一步中，你将学习如何更新 JSON 字段中的值。nn打开 `json_extractor.py` 文件。nn```bashnnano json_extractor.pyn```nn修改 `json_extractor.py` 文件，加入用于更新 JSON 和数据库的函数：nn```pythonn## 导入必要的库nimport sqlite3nimport jsonnn## 定义一个函数，使用路径从 JSON 字符串中提取值ndef json_extract(json_str, path):n try:n ## 将 JSON 字符串解析为 Python 字典n json_data = json.loads(json_str)n ## 将路径分割成组件n path_components = path.split('.')n ## 从完整的 JSON 对象开始n value = json_datan ## 遍历 JSON 对象n for component in path_components:n value = value.get(component)n return valuen except (json.JSONDecodeError, AttributeError, TypeError):n return Nonenn## 定义一个函数来连接数据库并注册自定义函数ndef connect_db(db_path):n ## 连接到数据库n conn = sqlite3.connect(db_path)n ## 注册自定义 SQL 函数n conn.create_function(\"json_extract\", 2, json_extract)n return connnn## 定义一个函数，根据 JSON 字段过滤产品ndef filter_products(db_path, json_path, value):n ## 连接到数据库n conn = connect_db(db_path)n ## 创建一个游标对象n cursor = conn.cursor()n ## 创建用于按 JSON 值过滤的 SQL 查询n query = f\"SELECT * FROM products WHERE json_extract(details, '{json_path}') = '{value}'\"n ## 执行查询n cursor.execute(query)n ## 获取所有匹配的结果n results = cursor.fetchall()n ## 关闭连接n conn.close()n return resultsnn## 定义一个函数来更新 JSON 字符串中的值ndef update_json_value(json_str, path, new_value):n try:n ## 将 JSON 字符串解析为 Python 字典n json_data = json.loads(json_str)n ## 将路径分割成组件n path_components = path.split('.')n ## 导航到目标值的父级n target = json_datan for i in range(len(path_components) - 1):n target = target.get(path_components[i])nn ## 更新目标值n target[path_components[-1]] = new_valuen ## 将 Python 字典转换回 JSON 字符串n return json.dumps(json_data)n except (json.JSONDecodeError, AttributeError, TypeError):n ## 如果发生错误，则返回原始 JSON 字符串n return json_strnn## 定义一个函数来更新数据库中产品的详细信息ndef update_product_details(db_path, product_name, json_path, new_value):n ## 连接到数据库n conn = sqlite3.connect(db_path)n ## 创建一个游标对象n cursor = conn.cursor()nn ## 获取指定产品的当前详细信息n ## '?' 是一个占位符，用于防止 SQL 注入n cursor.execute(\"SELECT details FROM products WHERE name = ?\", (product_name,))n result = cursor.fetchone()n ## 如果产品不存在，则关闭连接并返回 Falsen if not result:n conn.close()n return Falsenn ## 获取当前的 JSON 详细信息字符串n current_details = result[0]nn ## 使用新值更新 JSON 字符串n updated_details = update_json_value(current_details, json_path, new_value)n使用新的 JSON 字符串更新数据库 cursor.execute(\"UPDATE products SET details = ? WHERE name = ?\", (updated_details, product_name)) 提交数据库中的更改 conn.commit() 关闭连接 conn.close() return True 当脚本被直接执行时，这一段代码会运行 if __name__ == '__main__': 示例用法：把笔记本电脑的内存更新为 32GB update_product_details('mydatabase.db', 'Laptop', 'specs.memory', '32GB') print(\"Laptop memory updated to 32GB\") 更新后再查一次数据，确认是否写入成功 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() cursor.execute(\"SELECT details FROM products WHERE name = 'Laptop'\") 取出更新后的详细信息 updated_details = cursor.fetchone()[0] print(\"Updated Laptop details:\", updated_details) 关闭连接 conn.close() 这段代码新增了两个函数： - `update_json_value`：该函数接收一个 JSON 字符串、一个路径以及一个新值作为输入。它会先解析 JSON 字符串，然后在指定路径上更新对应的值，并返回更新后的 JSON 字符串。n- `update_product_details`: 该函数接收数据库路径、产品名称、JSON 路径和新值作为输入。它会连接数据库，读取产品当前的 JSON 数据，调用 `update_json_value` 在指定路径更新值，然后再将更新后的 JSON 数据写回数据库。nn保存文件并退出 `nano`。nn现在，运行 Python 脚本。nn```bashnpython3 json_extractor.pyn```nn**预期输出：**nn```nLaptop memory updated to 32GBnUpdated Laptop details: {\"brand\": \"Dell\", \"model\": \"XPS 13\", \"specs\": {\"cpu\": \"Intel i7\", \"memory\": \"32GB\", \"storage\": \"512GB SSD\"}}n```nn以上输出表明，笔记本电脑的内存信息已经成功更新为 32GB，并写入到了 SQLite 数据库中。n","need_verify":true,"has_solution":false},{"position":6,"title":"总结","layout":"doc-fullscreen","text":"## 总结nn在本实验中，你学习了如何在 SQLite 中处理 JSON 数据。你首先创建了数据库和数据表，用于保存 JSON 格式的数据；然后完成了 JSON 数据的插入操作。接着，你通过自定义 Python 函数实现了从 JSON 数据中提取指定字段，并基于 JSON 字段值进行数据过滤。最后，你还学习了如何更新 JSON 字段中的内容。这些 SQLite JSON 数据处理技能，为你后续更高效地管理和查询数据库中的半结构化数据打下了坚实基础。n","need_verify":false,"has_solution":false}