Python操作SQLite：轻量数据库入门教程

文章不知道大家是否熟悉？今天我将给大家介绍《Python操作SQLite教程：轻量数据库实用指南》，这篇文章主要会讲到等等知识点，如果你在看完本篇文章后，有更好的建议或者发现哪里有问题，希望大家都能积极评论指出，谢谢！希望我们能一起加油进步！

Python操作SQLite数据库的核心是使用内置的sqlite3模块，其流程包括：1. 导入模块；2. 使用sqlite3.connect()建立数据库连接（可为文件或内存）；3. 创建游标对象；4. 执行SQL命令进行增删改查；5. 通过commit()提交更改或rollback()回滚事务；6. 最后关闭游标和连接。操作中应使用参数化查询防止SQL注入，利用executemany提升批量操作效率，并结合try-except-finally或with语句确保资源释放和事务一致性。性能优化方面，应注意合理使用索引、控制提交频率、谨慎设置PRAGMA synchronous及定期执行VACUUM回收空间。

在Python中操作SQLite数据库，核心在于使用Python内置的sqlite3模块。这个模块提供了一套简洁直观的API，让你无需额外安装任何库，就能轻松地与SQLite数据库文件进行交互，实现数据的存储、查询和管理。它特别适合那些需要轻量级、无需独立服务器的本地数据存储场景。

解决方案

使用Python操作SQLite，通常遵循几个基本步骤：建立连接、创建游标、执行SQL命令、提交更改，最后关闭连接。

首先，你需要导入sqlite3模块。然后，通过sqlite3.connect()函数连接到一个数据库文件。如果文件不存在，它会自动创建一个。连接成功后，获取一个游标对象，所有的SQL命令都通过这个游标来执行。

import sqlite3

# 连接到数据库（如果不存在则创建）
# 也可以使用 ':memory:' 在内存中创建一个临时数据库，关闭连接后数据即消失
conn = sqlite3.connect('my_database.db')

# 创建一个游标对象
cursor = conn.cursor()

# 执行SQL命令：创建表
# 这里我喜欢用三重引号，写多行SQL看着舒服
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')

# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
# 批量插入是个好习惯，效率高
users_to_add = [
    ('Charlie', 'charlie@example.com'),
    ('David', 'david@example.com')
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_to_add)


# 查询数据
cursor.execute("SELECT * FROM users WHERE name LIKE ?", ('%ice%',))
results = cursor.fetchall() # 获取所有匹配的行
print("查询结果 (Alice):", results)

# 更新数据
cursor.execute("UPDATE users SET email = ? WHERE name = ?", ('alice.updated@example.com', 'Alice'))

# 删除数据
cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',))

# 提交更改。这步非常关键，没有它，你的数据操作不会真正保存到数据库文件
conn.commit()
print("数据已提交。")

# 再次查询所有数据，看看变化
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()
print("所有用户:", all_users)

# 关闭游标和连接，释放资源
cursor.close()
conn.close()
print("数据库连接已关闭。")

这段代码展示了从连接到数据库、创建表、插入、查询、更新、删除数据，再到提交和关闭连接的完整流程。实际开发中，你会把这些操作封装到函数或类里，让代码更模块化。

连接与创建：Python如何与SQLite数据库建立联系？

Python与SQLite建立联系，其实就是通过sqlite3.connect()这个函数。它的参数可以是数据库文件的路径，比如'my_database.db'。如果这个文件不存在，SQLite会很贴心地为你创建一个全新的数据库文件。这和那些需要你手动创建数据库实例的重量级数据库系统比起来，简直是傻瓜式操作。

当然，如果你只是想做一些临时的、不需要持久化的数据操作，或者想在测试时避免生成实际文件，可以使用特殊的文件名':memory:'。这样，数据库就会完全在内存中运行，一旦连接关闭，所有数据都会烟消云散。这对于单元测试或者一些只在程序运行时需要数据的场景非常有用。

import sqlite3

# 连接到文件数据库
file_db_conn = sqlite3.connect('my_app_data.db')
print("已连接到文件数据库 'my_app_data.db'")

# 连接到内存数据库
memory_db_conn = sqlite3.connect(':memory:')
print("已连接到内存数据库 (临时)")

# 通常，我会用try-finally或者with语句来管理连接，确保连接总是被关闭
# 这种方式更健壮，即使代码执行过程中出现错误，也能保证资源释放
try:
    cursor = file_db_conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)")
    file_db_conn.commit()
    print("文件数据库表已创建。")
except Exception as e:
    print(f"连接或操作文件数据库时出错: {e}")
finally:
    file_db_conn.close()
    print("文件数据库连接已关闭。")

# 内存数据库也是一样
try:
    cursor_mem = memory_db_conn.cursor()
    cursor_mem.execute("CREATE TABLE IF NOT EXISTS temp_data (id INTEGER, info TEXT)")
    cursor_mem.execute("INSERT INTO temp_data VALUES (1, '临时数据')")
    memory_db_conn.commit()
    cursor_mem.execute("SELECT * FROM temp_data")
    print("内存数据库数据:", cursor_mem.fetchall())
except Exception as e:
    print(f"连接或操作内存数据库时出错: {e}")
finally:
    memory_db_conn.close()
    print("内存数据库连接已关闭。")

我个人偏爱使用with语句来管理连接，因为它会自动处理连接的关闭，省去了手动调用close()的麻烦，代码也显得更简洁、更Pythonic。

import sqlite3

# 使用with语句管理连接，更安全、更简洁
with sqlite3.connect('another_db.db') as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT)")
    cursor.execute("INSERT INTO products (name) VALUES ('Laptop')")
    conn.commit() # 事务管理依然需要手动commit
    print("使用with语句：产品表已创建并插入数据。")

# 连接在with块结束后会自动关闭

数据操作：增删改查及事务管理的实战技巧

数据操作无非就是增、删、改、查（CRUD），在SQLite中，这些操作都围绕着cursor.execute()方法展开。但光会执行SQL还不够，事务管理才是确保数据完整性和一致性的关键。

插入数据 (INSERT): 使用INSERT INTO table_name (columns) VALUES (values)。为了防止SQL注入，切记使用占位符?来传递参数，而不是直接拼接字符串。sqlite3模块会自动帮你处理参数的转义，这比你手动去防范安全漏洞要靠谱得多。

# 插入单条数据
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Frank', 'frank@example.com'))

# 插入多条数据，使用executemany效率更高
new_users = [
    ('Grace', 'grace@example.com'),
    ('Heidi', 'heidi@example.com')
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", new_users)

查询数据 (SELECT):SELECT语句执行后，你需要用cursor.fetchone()获取一条记录，cursor.fetchall()获取所有记录，或者cursor.fetchmany(size)获取指定数量的记录。

# 查询所有用户
cursor.execute("SELECT id, name, email FROM users")
all_users = cursor.fetchall()
print("所有用户:", all_users)

# 查询特定用户
cursor.execute("SELECT * FROM users WHERE name = ?", ('Grace',))
grace_info = cursor.fetchone() # 即使只有一条，也用fetchone
print("Grace的信息:", grace_info)

# 遍历查询结果
for row in cursor.execute("SELECT name FROM users"):
    print(f"用户名字: {row[0]}") # row是一个元组

更新数据 (UPDATE):UPDATE table_name SET column = value WHERE condition。同样，参数化是必须的。

cursor.execute("UPDATE users SET email = ? WHERE name = ?", ('frank.new@example.com', 'Frank'))

删除数据 (DELETE):DELETE FROM table_name WHERE condition。小心使用，没有WHERE子句会删除所有数据！

cursor.execute("DELETE FROM users WHERE name = ?", ('Heidi',))

事务管理： SQLite默认是自动提交模式，但通过sqlite3.connect()连接时，通常会创建一个事务。这意味着你的INSERT, UPDATE, DELETE等操作在调用connection.commit()之前，都不会真正写入到数据库文件中。如果发生错误，你可以调用connection.rollback()来撤销当前事务中的所有操作，回到事务开始前的状态。这对于确保数据一致性至关重要，尤其是在进行一系列相互关联的操作时。

conn = sqlite3.connect('transaction_test.db')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL)")
conn.commit() # 确保表已创建

try:
    cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ('AccountA', 1000.0))
    cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ('AccountB', 500.0))

    # 模拟转账操作：从A扣钱，给B加钱
    cursor.execute("UPDATE accounts SET balance = balance - 200 WHERE name = ?", ('AccountA',))
    # 假设这里发生了一个错误，比如网络中断或者其他异常
    # raise ValueError("模拟一个错误")
    cursor.execute("UPDATE accounts SET balance = balance + 200 WHERE name = ?", ('AccountB',))

    conn.commit() # 只有这里执行了，数据才会真正保存
    print("转账成功，事务提交。")
except Exception as e:
    conn.rollback() # 出现错误时回滚所有操作
    print(f"转账失败，事务回滚: {e}")
finally:
    cursor.close()
    conn.close()

# 检查结果
with sqlite3.connect('transaction_test.db') as conn_check:
    cursor_check = conn_check.cursor()
    cursor_check.execute("SELECT * FROM accounts")
    print("当前账户余额:", cursor_check.fetchall())

我个人在处理复杂业务逻辑时，总是会把一系列数据库操作包裹在try...except...finally块中，并确保在成功时commit，失败时rollback，这样能极大降低数据不一致的风险。

性能与安全：使用SQLite时不得不防的那些坑和优化点

SQLite虽然轻量，但在实际使用中，仍然有一些性能和安全方面的问题需要注意。

SQL注入的防范： 这是老生常谈的问题，但真的太重要了。永远不要直接用字符串拼接的方式来构建SQL查询，特别是当查询中包含用户输入时。例如，"SELECT * FROM users WHERE name = '" + user_input + "'"就是个灾难。恶意用户输入' OR '1'='1就能绕过你的验证，甚至执行删除操作。

正确的做法，正如前面多次强调的，是使用参数化查询：

# 安全的做法：使用占位符 '?'
user_name = "Robert'; DROP TABLE users;" # 恶意输入
cursor.execute("SELECT * FROM users WHERE name = ?", (user_name,))
# SQLite会将 'Robert'; DROP TABLE users; 作为一个整体字符串来匹配，不会执行DROP TABLE

提交频率与性能： 频繁地调用conn.commit()可能会影响性能，因为它每次都会强制将数据写入磁盘。如果你需要进行大量的插入或更新操作，最好将它们放在一个事务中，然后一次性commit。executemany就是为此而生。

# 批量插入示例，性能优于单条循环插入
data_to_insert = [(f'User{i}', f'user{i}@example.com') for i in range(10000)]
conn = sqlite3.connect('bulk_insert.db')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS large_table (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")

# 在一个事务中完成所有插入
try:
    cursor.executemany("INSERT INTO large_table (name, email) VALUES (?, ?)", data_to_insert)
    conn.commit()
    print("10000条数据批量插入完成。")
except Exception as e:
    conn.rollback()
    print(f"批量插入失败: {e}")
finally:
    conn.close()

索引 (INDEXes)： 对于经常用于WHERE子句、JOIN条件或ORDER BY排序的列，创建索引可以显著提升查询速度。SQLite的索引和传统数据库类似，会增加写入的开销，但对于读操作多的场景，收益巨大。

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_products_name ON products (name);

如果你发现某个查询特别慢，第一个应该想到的就是是不是少了索引。

PRAGMA synchronous：PRAGMA synchronous = OFF;可以禁用SQLite的同步写入磁盘操作，这能极大地提高写入性能。但代价是，如果系统崩溃（比如断电），你可能会丢失最近的写入数据。所以，这个设置只在对数据丢失容忍度较高、或者写入性能是瓶颈的场景下才考虑使用。默认是FULL，最安全。

# 谨慎使用，可能导致数据丢失
cursor.execute("PRAGMA synchronous = OFF;")
conn.commit() # 提交PRAGMA设置

VACUUM命令： 当你从SQLite数据库中删除大量数据时，数据库文件的大小并不会立即减小，因为被删除的空间只是被标记为可用，而不是真正释放回文件系统。VACUUM命令可以重构数据库，从而回收这些空间，减小文件大小，有时还能优化性能。但这会是一个耗时的操作，并且会临时占用两倍的数据库空间。

# 执行VACUUM操作
conn.execute("VACUUM;")
conn.commit()
print("数据库已VACUUM。")

我通常会在应用程序不忙的时候，或者在数据量变化较大之后，手动执行VACUUM，或者在部署脚本中加入这一步，让数据库保持“苗条”。

并发性： SQLite是一个文件级锁定的数据库，这意味着在任何给定时刻，只有一个进程可以写入数据库。多个进程可以同时读取，但写入操作是独占的。如果你有多个进程或线程需要频繁写入同一个SQLite数据库，可能会遇到锁定问题。对于高并发写入的场景，SQLite可能不是最佳选择，你可能需要考虑PostgreSQL或MySQL这类客户端-服务器架构的数据库。但在Python的单进程多线程应用中，由于GIL的存在，通常不会遇到太大的写入并发问题，因为只有一个线程能真正执行Python字节码。

理解这些特性和技巧，能让你在使用Python操作SQLite时，不仅写出功能正确的代码，还能兼顾性能和安全性，避免一些常见的“坑”。

今天关于《Python操作SQLite：轻量数据库入门教程》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！