如何在SQLite中打印包含最小人口值的城市整行数据

方法一：直接用SQL查询获取整行数据（推荐） #

不需要先查询所有人口再找最小值，直接让数据库完成排序和筛选，效率更高且代码更简洁：

# 替换原来的「Print city with the smallest population」部分
cursor.execute("SELECT * FROM cities ORDER BY city_population ASC LIMIT 1")
smallest_city = cursor.fetchone()
print('City with smallest population:', smallest_city)

解释：

• ORDER BY city_population ASC 按人口从小到大排序
• LIMIT 1 只取排序后的第一行，也就是人口最少的城市整行数据

方法二：先获取最小值再查询对应行 #

如果坚持先获取最小值再匹配整行，要使用参数化查询避免错误：

# 替换原来的「Print city with the smallest population」部分
cursor.execute("SELECT city_population FROM cities")
result = [pop[0] for pop in cursor.fetchall()]
min_pop = min(result)

# 查询对应整行
cursor.execute("SELECT * FROM cities WHERE city_population = ?", (min_pop,))
smallest_city = cursor.fetchone()
print('City with smallest population:', smallest_city)

额外优化：简化平均值计算 #

原来的平均值计算代码过于繁琐，可以用SQL直接计算，效率更高：

# 替换原来的「Print average」部分
cursor.execute("SELECT AVG(city_population) FROM cities")
average = cursor.fetchone()[0]
print(average)

完整修改后的代码 #

import sqlite3
import os 

# Remove Database file if it exists:
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 AUTOINCREMENT NOT NULL, cities_name TEXT, city_population INTEGER)")
    
cities_list = [('Minneapolis', 425336),
    ('St. Paul', 307193),
    ('Dallas', 1288000),
    ('Memphis', 628127),
    ('San Francisco', 815201),
    ('Milwaukee', 569330),
    ('Denver', 711463),
    ('Phoenix', 1625000),
    ('Chicago', 2697000),
    ('New York', 8468000)]

cursor.executemany("insert into cities(cities_name, city_population) values (?, ?)", cities_list)
connection.commit()

# Print entire table: 
for row in cursor.execute("select * from cities"):
    print(row)

# Print cities in alphabetical order:
# 直接用SQL排序，比拉到Python里排序更高效
cursor.execute("SELECT cities_name FROM cities ORDER BY cities_name ASC")
result = cursor.fetchall()
print(result)

# Print average:
cursor.execute("SELECT AVG(city_population) FROM cities")
average = cursor.fetchone()[0]
print(average)

# Print city with the smallest population:
cursor.execute("SELECT * FROM cities ORDER BY city_population ASC LIMIT 1")
smallest_city = cursor.fetchone()
print('City with smallest population:', smallest_city)

connection.commit()
connection.close()  

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