面试题：Python的SQLite操作中如何进行事务处理并优化查询性能

Question

假设在Python项目中使用SQLite数据库，有一系列对数据库的插入、更新操作，要求将这些操作放在一个事务中以确保数据的一致性。同时，对于一个经常查询的表，简述如何优化查询性能，例如从索引创建、查询语句优化等方面阐述，并给出相应的Python代码示例。

Answer 1

事务操作

在Python中使用SQLite进行事务操作，可以使用sqlite3模块。以下是一个将插入和更新操作放在事务中的示例代码：

import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

try:
    # 开始事务
    cursor.execute('BEGIN')
    
    # 插入操作
    cursor.execute('INSERT INTO your_table (column1, column2) VALUES (?,?)', ('value1', 'value2'))
    
    # 更新操作
    cursor.execute('UPDATE your_table SET column2 =? WHERE column1 =?', ('new_value', 'value1'))
    
    # 提交事务
    cursor.execute('COMMIT')
except Exception as e:
    # 回滚事务
    cursor.execute('ROLLBACK')
    print(f"事务操作失败: {e}")
finally:
    # 关闭连接
    conn.close()

查询性能优化

1. 索引创建：
   • 对于经常查询的列创建索引可以显著提高查询性能。例如，如果经常根据column1查询your_table表，可以这样创建索引：

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 创建索引
cursor.execute('CREATE INDEX idx_column1 ON your_table (column1)')

conn.close()

2. 查询语句优化：
   • 避免使用SELECT *，而是明确指定需要的列，这样可以减少数据传输量。
   • 确保查询条件使用的列上有合适的索引。
   • 例如，优化前的查询：

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM your_table WHERE column1 =?', ('value1',))
results = cursor.fetchall()
conn.close()

优化后的查询：

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('SELECT column1, column2 FROM your_table WHERE column1 =?', ('value1',))
results = cursor.fetchall()
conn.close()