如何在Python中执行多条SQL查询？解决仅返回最后一条结果的问题

问题核心在于：每次调用cursor.execute()执行新查询时，游标会切换到新的结果集，之前未获取的结果会被直接覆盖。必须在执行下一条查询前，先把当前查询的结果取出保存。

方法一：每次执行后立即获取结果 #

因为每个查询都是count(*)，用fetchone()比fetchall()更高效，直接取唯一的结果值即可：

# 执行第一条查询并保存结果
cursor.execute('select count(*) from event where update_count > 0 and date > now() - interval 24 hour')
updated_count = cursor.fetchone()[0]

# 执行第二条查询并保存结果
cursor.execute('select count(*) from event where deleted > 0 and date > now() - interval 24 hour')
deleted_count = cursor.fetchone()[0]

# 执行第三条查询并保存结果
cursor.execute('select count(*) from event where date > now() - interval 24 hour')
total_last_24 = cursor.fetchone()[0]

# 执行第四条查询并保存结果
cursor.execute('select count(*) from event')
total_count = cursor.fetchone()[0]

# 输出所有结果
print(updated_count)
print(deleted_count)
print(total_last_24)
print(total_count)

方法二：合并为单条SQL查询（更高效） #

把四个统计查询用UNION ALL合并成一条语句，同时给每个结果加标识字段，方便区分统计类型：

cursor.execute("""
    select 'updated' as type, count(*) as num from event where update_count > 0 and date > now() - interval 24 hour
    union all
    select 'deleted' as type, count(*) as num from event where deleted > 0 and date > now() - interval 24 hour
    union all
    select 'last_24h' as type, count(*) as num from event where date > now() - interval 24 hour
    union all
    select 'total' as type, count(*) as num from event
""")

# 获取所有结果并遍历输出
results = cursor.fetchall()
for item in results:
    print(f"{item[0]}: {item[1]}")

这种方式只需要和数据库交互一次，性能比四次单独查询更优，结果也能一次性获取。

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