如何使用Python高效执行多条SQL查询并简化代码？

方案一：合并SQL查询（推荐，更高效） #

将多个统计查询合并为一条SQL语句，仅需与数据库交互一次，既减少IO开销，又简化代码：

cursor = mydb.cursor()

cursor.execute("""
    SELECT
        COUNT(CASE WHEN update_count > 0 AND date > NOW() - INTERVAL 24 HOUR THEN 1 END) AS updated,
        COUNT(CASE WHEN deleted > 0 AND date > NOW() - INTERVAL 24 HOUR THEN 1 END) AS deleted,
        COUNT(CASE WHEN date > NOW() - INTERVAL 24 HOUR THEN 1 END) AS total_last_24h,
        COUNT(*) AS total
    FROM event
""")

# 一次性获取所有统计结果
updated, deleted, total_last_24h, total = cursor.fetchone()

print(f'Updated records in the last 24 hours: {updated}')
print(f'Deleted records in the last 24 hours: {deleted}')
print(f'Total records in the last 24 hours: {total_last_24h}')
print(f'Total records: {total}')

方案二：封装通用统计函数（适合需单独查询的场景） #

如果必须保留独立查询逻辑，可封装一个重复使用的函数，消除冗余代码：

cursor = mydb.cursor()

def get_stat_count(query):
    cursor.execute(query)
    # 直接获取单个统计值，无需遍历结果集
    return cursor.fetchone()[0]

# 调用函数获取各统计结果
updated = get_stat_count('select count(*) from event where update_count > 0 and date > now() - interval 24 hour')
deleted = get_stat_count('select count(*) from event where deleted > 0 and date > now() - interval 24 hour')
total_last_24h = get_stat_count('select count(*) from event where date > now() - interval 24 hour')
total = get_stat_count('select count(*) from event')

# 统一输出结果
print(f'Updated records in the last 24 hours: {updated}')
print(f'Deleted records in the last 24 hours: {deleted}')
print(f'Total records in the last 24 hours: {total_last_24h}')
print(f'Total records: {total}')

关键优化点 #

• 用fetchone()替代fetchall()：每个统计查询仅返回单个值，fetchone()直接获取结果行，无需循环遍历结果集。
• 合并SQL优先：数据库交互的开销远大于代码逻辑开销，一次查询的性能远优于多次独立查询。

内容的提问来源于stack exchange，提问作者Anastasia Quart