如何在Pandas SQLite查询中去除索引，仅保留表行数？

问题描述 #

使用pd.read_sql()操作SQLite数据库时，想要创建一个表名为键、表行数为值的字典，但当前字典的值包含了DataFrame的索引（0），希望只保留纯数字的行数。

当前代码 #

table_names = list(db_tables['name'])

tables = {key: None for key in table_names}

for table_name in tables.keys():
    sqlite_table = f"SELECT COUNT(*) AS num_rows FROM {table_name}"
    tables[table_name] = pd.read_sql(sqlite_table, conn)
    
tables

当前输出 #

{'movie_basics':    num_rows
 0    146144,
 'directors':    num_rows
 0    291174,
 'known_for':    num_rows
 0   1638260,
 'movie_akas':    num_rows
 0    331703,
 'movie_ratings':    num_rows
 0     73856,
 'persons':    num_rows
 0    606648,
 'principals':    num_rows
 0   1028186,
 'writers':    num_rows
 0    255873}

解决方案 #

方法1：提取DataFrame中的标量值 #

pd.read_sql()返回的是DataFrame对象，你需要从中取出唯一的数值结果，有几种简洁写法：

• 通过iloc定位第一行第一列的值：

  tables[table_name] = pd.read_sql(sqlite_table, conn).iloc[0, 0]
  

• 按列名提取后取第一行：

  tables[table_name] = pd.read_sql(sqlite_table, conn)['num_rows'].iloc[0]
  

• 用item()直接获取标量值（仅当结果唯一时可用）：

  tables[table_name] = pd.read_sql(sqlite_table, conn)['num_rows'].item()
  

方法2：用SQLite原生连接直接查询（更高效） #

如果不需要借助pandas处理结果，直接用sqlite3的游标执行查询，避免DataFrame的额外开销：

cursor = conn.cursor()

tables = {}
for table_name in table_names:
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    tables[table_name] = cursor.fetchone()[0]

方法3：批量查询所有表行数（进阶） #

通过SQLite系统表sqlite_master批量获取用户表，再统一查询行数，减少循环次数：

# 先获取所有非系统表名
tables_df = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'", conn)

tables = {}
for _, row in tables_df.iterrows():
    table_name = row['name']
    count = pd.read_sql(f"SELECT COUNT(*) FROM {table_name}", conn).iloc[0, 0]
    tables[table_name] = count

内容的提问来源于stack exchange，提问作者Chris Kucewicz