如何将Pandas DataFrame导出为可读取的SQL文件？

问题说明 #

原代码可将Pandas DataFrame导出为Excel并插入本地MySQL数据库，但替换为SQLite引擎后生成的是二进制数据库文件，无法直接打开读取纯文本SQL内容。需求是生成可直接查看、编辑的纯文本SQL脚本文件。

解决方案 #

SQLite引擎生成的是二进制数据库文件，并非纯文本SQL脚本。要生成可读的SQL文件，需要手动构建CREATE TABLE和INSERT INTO语句并写入文件：

步骤1：保留原有DataFrame处理逻辑

df3 = df3[["home","away","scorehome","scoreaway","htresult","shresult","result","best_bets","oddtwo","oddthree","over05ht","over15ht","over05sh","over15sh","over05","over15","over25","over35","over45","goal","esito","tournament","uniquefield"]]
df3 = df3.sort_values('best_bets')

# 原有Excel导出逻辑可保留
file_name = camp + '.xlsx'
df3.to_excel(file_name)
print('Tournament is written to Excel File successfully.')

步骤2：生成纯文本SQL脚本

替换原有的MySQL/SQLite插入逻辑，改为生成包含建表和插入语句的SQL文件：

import pandas as pd

# 1. 生成CREATE TABLE语句（适配MySQL语法）
def generate_create_table(df, table_name):
    # 映射Pandas数据类型到SQL类型
    dtype_mapping = {
        'object': 'VARCHAR(255)',
        'int64': 'INT',
        'float64': 'FLOAT',
        'bool': 'BOOLEAN',
        'datetime64[ns]': 'DATETIME'
    }
    columns = []
    for col, dtype in df.dtypes.items():
        sql_type = dtype_mapping.get(str(dtype), 'VARCHAR(255)')
        columns.append(f"`{col}` {sql_type}")
    return f"CREATE TABLE IF NOT EXISTS `{table_name}` ({', '.join(columns)});\n"

# 2. 生成INSERT INTO语句
def generate_insert_statements(df, table_name):
    columns = ', '.join([f"`{col}`" for col in df.columns])
    insert_stmts = []
    for _, row in df.iterrows():
        values = []
        for val in row.values:
            if pd.isna(val):
                values.append('NULL')
            elif isinstance(val, str):
                # 转义字符串中的单引号，避免SQL语法错误
                escaped_val = val.replace("'", "''")
                values.append(f"'{escaped_val}'")
            else:
                values.append(str(val))
        values_str = ', '.join(values)
        insert_stmts.append(f"INSERT INTO `{table_name}` ({columns}) VALUES ({values_str});")
    return '\n'.join(insert_stmts)

# 3. 将SQL语句写入文件
sql_file_name = 'fullresults.sql'
with open(sql_file_name, 'w', encoding='utf-8') as f:
    # 写入建表语句
    f.write(generate_create_table(df3, 'fullresults'))
    # 写入插入语句
    f.write(generate_insert_statements(df3, 'fullresults'))

print(f"可读SQL文件已生成：{sql_file_name}")

补充说明

• 生成的fullresults.sql是纯文本文件，可直接用记事本、VS Code等工具打开编辑
• 若需适配SQLite等其他数据库，可调整dtype_mapping中的SQL类型（比如将INT改为INTEGER）
• 若DataFrame数据量极大，建议分批次写入INSERT语句，避免内存占用过高

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