如何通过SQLite3与Pandas更高效地处理图书库数据统计？

我正在处理一个包含整个图书馆图书详情的大型数据库，需要获取馆藏的各类统计数据。例如，我编写了如下代码来定义获取馆藏数量最多的前10位作者的方法：

def most_owned_authors():
    db = 'database.db'
    conn = sqlite3.connect(db)
    cursor = conn.cursor()

    cursor.execute('''SELECT AUTHOR AS author FROM MAINTABLE WHERE OWNEDCHECKBOX = TRUE;''')

    authors_df = pd.DataFrame(cursor.fetchall())

    authors = []
    author_dict = {}

    for x in authors_df.iloc:
        authors.append(x.to_string(index=False))
    for x in authors:
        amount = authors.count(x)
        author_dict[x] = amount

    author_dict = dict(sorted(author_dict.items(), key=lambda item: item[1], reverse=True))

    top_10_owned_authors = {}

    for x, k in enumerate(author_dict):
        if x == 10: break
        top_10_owned_authors[k] = author_dict[k]

请问是否有更简便的方式，使用SQLite3和Pandas从SQL查询生成统计数据？是否必须像上述示例那样手动编写逻辑？能否通过SQL的SUM/COUNT函数对重复条目进行统计后，直接从DataFrame中提取结果？另外，类似的需求还有生成标记为“已读”的图书及其阅读年份的DataFrame，该如何实现？

1. 简化馆藏最多的前10位作者统计 #

完全不需要手动编写Python循环统计，直接用SQL的COUNT()和GROUP BY就能在数据库层面完成统计，再结合Pandas直接读取结果，效率和简洁性都远高于原代码。

优化后的代码 #

import sqlite3
import pandas as pd

def most_owned_authors():
    db = 'database.db'
    conn = sqlite3.connect(db)
    
    # 直接用SQL完成分组统计、排序并取前10
    query = '''
        SELECT AUTHOR AS author, COUNT(*) AS book_count
        FROM MAINTABLE 
        WHERE OWNEDCHECKBOX = TRUE
        GROUP BY AUTHOR
        ORDER BY book_count DESC
        LIMIT 10;
    '''
    # Pandas直接读取查询结果为DataFrame
    top_10_df = pd.read_sql_query(query, conn)
    conn.close()
    
    # 如果需要字典格式，直接转换即可
    top_10_dict = top_10_df.set_index('author')['book_count'].to_dict()
    return top_10_dict, top_10_df

优势说明 #

• 数据库引擎处理分组统计的效率远高于Python循环，面对大型数据库时能大幅减少内存占用和计算时间
• 代码逻辑清晰，避免手动循环可能出现的重复统计、字符串处理错误等问题
• 直接得到结构化的DataFrame，方便后续分析或可视化操作

2. 生成已读图书及阅读年份的DataFrame #

同样通过SQL过滤已读条目，指定需要的字段后，用Pandas直接读取结果即可：

实现代码 #

def get_read_books_with_year():
    db = 'database.db'
    conn = sqlite3.connect(db)
    
    # 假设标记已读的字段是READCHECKBOX，阅读年份字段是READ_YEAR
    query = '''
        SELECT BOOK_TITLE, AUTHOR, READ_YEAR
        FROM MAINTABLE 
        WHERE READCHECKBOX = TRUE
        -- 可选：按阅读年份排序
        ORDER BY READ_YEAR DESC;
    '''
    read_books_df = pd.read_sql_query(query, conn)
    conn.close()
    
    # 若READ_YEAR存储为字符串，可转换为日期类型
    read_books_df['READ_YEAR'] = pd.to_datetime(read_books_df['READ_YEAR'], format='%Y')
    return read_books_df

额外统计扩展 #

如果需要统计每年的已读图书数量，直接在SQL中加入分组统计即可：

SELECT READ_YEAR, COUNT(*) AS read_count
FROM MAINTABLE 
WHERE READCHECKBOX = TRUE
GROUP BY READ_YEAR
ORDER BY READ_YEAR DESC;

内容的提问来源于stack exchange，提问作者Kyle Carroll