如何计算SQLite数据库表中列的平均值？代码报错求助

问题根源 #

1. 无效的数据存储：你插入的人口数据是带逗号、空格的字符串（例如'425,336'、'628, 127'），尽管表字段city_population定义为整数类型，但SQLite无法将这类格式化字符串转换为有效整数，最终存储的都是无效值（多数为0）。
2. 查询结果处理错误：cursor.fetchall()返回的是元组组成的列表（格式类似[(0,), (0,)]），直接调用sum(result)会触发TypeError——元组不能直接参与求和运算。

修复步骤 #

1. 修正数据插入逻辑

先清理人口字符串中的逗号和空格，转换为整数后再插入数据库：

# 清理人口数据，去除逗号和空格并转为整数
def clean_population(pop_str):
    return int(pop_str.replace(',', '').replace(' ', ''))

cities_list = [
    ('Minneapolis', clean_population('425,336')),
    ('St. Paul', clean_population('307,193')),
    ('Dallas', clean_population('1,288,000')),
    ('Memphis', clean_population('628, 127')),
    ('San Francisco', clean_population('815,201')),
    ('Milwaukee', clean_population('569,330')),
    ('Denver', clean_population('711,463')),
    ('Phoenix', clean_population('1,625,000')),
    ('Chicago', clean_population('2,697,000')),
    ('New York', clean_population('8,468,000'))
]

2. 计算平均值的两种方案

方案一：用SQL内置的AVG()函数（推荐，更高效）

直接让数据库计算平均值，避免在Python中处理大量数据：

# 使用SQL AVG函数计算平均值
cursor.execute("SELECT AVG(city_population) FROM cities")
average = cursor.fetchone()[0]
print(f"城市人口平均值: {average:.0f}")

方案二：修正Python端的结果处理逻辑

如果要在Python中计算，需要先提取元组中的整数值：

cursor.execute("SELECT city_population FROM cities")
result = cursor.fetchall()
# 提取元组中的人口数值
populations = [pop[0] for pop in result]
average = sum(populations) / len(populations)
print(f"城市人口平均值: {average:.0f}")

完整修复后的代码 #

import sqlite3
import os 

# 移除已存在的数据库文件（如果有）
if os.path.exists('cities.db'):
    os.remove('cities.db')

connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS cities(city_id INTEGER PRIMARY KEY NOT NULL, cities_name TEXT, city_population INTEGER)")

# 清理人口数据的辅助函数
def clean_population(pop_str):
    return int(pop_str.replace(',', '').replace(' ', ''))

cities_list = [
    ('Minneapolis', clean_population('425,336')),
    ('St. Paul', clean_population('307,193')),
    ('Dallas', clean_population('1,288,000')),
    ('Memphis', clean_population('628, 127')),
    ('San Francisco', clean_population('815,201')),
    ('Milwaukee', clean_population('569,330')),
    ('Denver', clean_population('711,463')),
    ('Phoenix', clean_population('1,625,000')),
    ('Chicago', clean_population('2,697,000')),
    ('New York', clean_population('8,468,000'))
]

cursor.executemany("INSERT OR IGNORE INTO cities VALUES (NULL, ?, ?)", cities_list)

# 按字母顺序打印城市名
cursor.execute("SELECT cities_name FROM cities")
result = sorted(cursor.fetchall())
print("按字母排序的城市:", result)

# 计算并打印平均值（使用SQL AVG方案）
cursor.execute("SELECT AVG(city_population) FROM cities")
average = cursor.fetchone()[0]
print(f"城市人口平均值: {average:.0f}")

connection.commit()
connection.close()

内容的提问来源于stack exchange，提问作者Josh T