Python使用sqlite3的7个实用技巧
本文介绍在Python项目中高效使用sqlite3的七个关键技巧。通过executemany进行批量插入可显著提升速度，利用连接对象直接执行命令简化代码。掌握上下文管理器确保事务安全，合理使用PRAGMA调整同步策略，并推迟索引创建以优化写入性能。此外，使用占位符防止SQL注入，并通过迭代光标实现流式处理，帮助开发者构建更快速、安全的本地数据库应用。
Python sqlite3高效编程技巧
在最近处理大规模数据的项目中，我频繁使用了sqlite3。起初，我尝试完全在内存中操作所有数据，包括字典查找、迭代和条件查询。这种方法虽然简单，但受限于内存容量，且将数据从磁盘重新生成或加载到内存的过程既繁琐又耗时。
随后我决定尝试sqlite3。只需建立数据库连接，就能处理更大量的数据，并将应用的加载时间降至零。此外，使用SQL查询可以替代许多复杂的Python逻辑语句。
在此分享我在这一过程中的心得与发现。
TL;DR（太长不看版）
- 使用批量操作（即executemany）。
- 大多数情况下无需手动管理游标。
- 游标支持迭代操作。
- 使用上下文管理器。
- 在必要时使用编译指示（Pragmas）。
- 推迟索引的创建。
- 使用占位符插入Python值。
批量插入与游标管理
1. 使用批量操作
若需一次性向数据库插入大量数据，应避免使用execute。sqlite3模块提供了批量插入方法：executemany。
避免采用以下写法：
for row in iter_data():
connection.execute('INSERT INTO my_table VALUES (?)', row)
可以利用executemany接受元组生成器作为参数的特性：
connection.executemany(
'INSERT INTO my_table VALUE (?)',
iter_data()
)
这种方式不仅代码更简洁，效率也更高。实际上，sqlite3在底层利用executemany来实现execute，但后者仅插入单行数据。
我编写了一个小型基准测试，将一百万行数据插入空表（数据库位于内存中）： executemany耗时：1.6秒 execute耗时：2.7秒
2. 无需游标
初期常混淆的一个概念是游标管理。在线示例和文档中通常展示如下代码：
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
# Do something with cursor
但实际上，大多数情况下你根本不需要创建游标对象，可以直接使用连接对象（文末会详细说明）。像execute和executemany这类操作可直接在连接对象上调用。示例如下：
import sqlite3
connection = sqlite3(':memory:')
# Create a table
connection.execute('CREATE TABLE events(ts, msg)')
# Insert values
connection.executemany(
'INSERT INTO events VALUES (?,?)',
[
(1, 'foo'),
(2, 'bar'),
(3, 'baz')
]
)
# Print inserted rows
for row in connnection.execute('SELECT * FROM events'):
print(row)
3. 游标（Cursor）支持迭代
常见示例中常使用fetchone或fetchall来处理SELECT查询结果。但我发现，直接在游标上进行迭代是处理结果最自然的方式：
for row in connection.execute('SELECT * FROM events'):
print(row)
这样，一旦获取到所需数量的结果，即可终止查询，避免资源浪费。当然，若事先已知需要多少结果，可使用LIMIT SQL语句；但Python生成器非常便捷，能将数据生成与消费分离。
事务安全与性能优化
4. 使用上下文管理器
即使在处理SQL事务中途，也可能发生意外。为避免手动处理回滚或提交，可简单地将连接对象用作上下文管理器。以下示例中，我们创建了一个表，并错误地插入了重复值：
import sqlite3
connection = sqlite3.connect(':memory:')
with connection:
connection.execute(
'CREATE TABLE events(ts, msg, PRIMARY KEY(ts, msg))')
try:
with connection:
connection.executemany('INSERT INTO events VALUES (?, ?)', [
(1, 'foo'),
(2, 'bar'),
(3, 'baz'),
(1, 'foo'),
])
except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
print('Could not complete operation:', e)
# No row was inserted because transaction failed
for row in connection.execute('SELECT * FROM events'):
print(row)
connection.close()
5. 使用Pragmas（编译指示）
……仅在确实有用时使用
程序中可使用几个pragma来调整sqlite3的行为。其中，synchronous参数可改善性能：
connection.execute('PRAGMA synchronous = OFF')需注意这可能带来风险。若应用在执行事务中途意外崩溃，数据库可能处于不一致状态。因此请谨慎使用！但若需快速插入大量数据，这或许是一个可行选项。
6. 推迟索引创建
假设需在数据库上创建多个索引，且需在插入大量数据的同时创建索引。将索引创建推迟至所有数据插入之后，可带来显著的性能提升。
安全插入与最佳实践
7. 使用占位符插入Python值
使用Python字符串操作将值嵌入查询中虽方便，但极不安全。sqlite3提供了更优的方法：
# Do not do this!
my_timestamp = 1
c.execute("SELECT * FROM events WHERE ts = '%s'" % my_timestamp)
# Do this instead
my_timestamp = (1,)
c.execute('SELECT * FROM events WHERE ts = ?', my_timestamp)
此外，使用Python %s（或format/format字符串常量）进行字符串插值在executemany中并非总是可行。因此，尝试此类做法并无实际意义！
请记住，这些技巧可能（也可能不）带来收益，具体取决于特定用例。应始终自行测试，判断是否值得采用。
英文原文：remusao，译文：oschina
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持本站。
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
