如何正确将DataFrame数据类型转换为SQL类型？解决日期列转float问题

从CSV文件生成空SQL表时，日期列（如END_DATE）被识别为FLOAT类型，导致生成的SQL表结构错误。现有Python代码及执行结果如下：

import pandas as pd

def generate_table_creation_query(csv_file_path, table_name):
    df = pd.read_csv(csv_file_path)

    sql_data_types = {
        'int64': 'INT',
        'float64': 'FLOAT',
        'object': 'NVARCHAR(MAX)',  
        'datetime64': 'DATETIME'
    }

    column_definitions = []
    for column_name, dtype in df.dtypes.items():
        column_type = sql_data_types.get(str(dtype), 'NVARCHAR(MAX)')
        column_definitions.append(f'{column_name} {column_type}')

    columns_str = ',\n'.join(column_definitions)

    table_creation_query = f'''
    CREATE TABLE {table_name} (
    {columns_str}
    )
    '''
    return table_creation_query

# Example usage:
csv_file_path = 'transactions.csv'  
table_name = 'transactions'
sql_query = generate_table_creation_query(csv_file_path, table_name)
print(sql_query)

执行结果：

CREATE TABLE transactions (
    CUST_ID NVARCHAR(MAX),
    START_DATE NVARCHAR(MAX),
    END_DATE FLOAT,
    TRANS_ID NVARCHAR(MAX),
    DATE NVARCHAR(MAX),
    YEAR INT,
    MONTH INT,
    DAY INT,
    EXP_TYPE NVARCHAR(MAX),
    AMOUNT FLOAT
    )

问题核心是CSV中的日期列存在空值，pd.read_csv()会将含空值的日期列自动解析为float类型（因为pandas中缺失值NaN默认是float类型）。可通过以下方式修复：

• 显式指定日期列并统一缺失值处理
  读取CSV时用parse_dates参数指定需解析为日期的列，同时用na_values统一标记缺失值，确保日期列被正确识别为datetime类型。
• 优化数据类型映射逻辑
  针对datetime64的所有子类（如datetime64[ns]）做统一判断，避免因类型字符串不匹配导致的映射错误。

修改后的代码：

import pandas as pd

def generate_table_creation_query(csv_file_path, table_name, date_columns=None):
    # 读取CSV时显式解析日期列，统一缺失值标记
    df = pd.read_csv(
        csv_file_path,
        parse_dates=date_columns,
        na_values=['', 'NA', 'NULL']
    )

    sql_data_types = {
        'int64': 'INT',
        'float64': 'FLOAT',
        'object': 'NVARCHAR(MAX)',
        'datetime64': 'DATETIME'
    }

    column_definitions = []
    for column_name, dtype in df.dtypes.items():
        dtype_str = str(dtype)
        # 匹配所有datetime64相关类型
        if 'datetime64' in dtype_str:
            column_type = 'DATETIME'
        else:
            column_type = sql_data_types.get(dtype_str, 'NVARCHAR(MAX)')
        column_definitions.append(f'{column_name} {column_type}')

    columns_str = ',\n'.join(column_definitions)

    table_creation_query = f'''
    CREATE TABLE {table_name} (
    {columns_str}
    )
    '''
    return table_creation_query

# Example usage: 指定需要解析的日期列
csv_file_path = 'transactions.csv'  
table_name = 'transactions'
sql_query = generate_table_creation_query(csv_file_path, table_name, date_columns=['START_DATE', 'END_DATE', 'DATE'])
print(sql_query)

额外提示：

• 若不确定日期列列表，可先读取CSV后执行df = df.apply(pd.to_datetime, errors='ignore')，自动转换所有可解析的日期列。
• 若目标SQL数据库支持更精确的日期类型（如DATE、DATETIME2），可直接修改sql_data_types中的映射值。

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