Python导入英国房价数据到MariaDB时价格为0、日期截断问题

• 字段整体错位是核心问题：pandas.DataFrame.to_csv()默认会导出行索引列，而你导入时没有跳过该列，导致所有字段都偏移了一位：原数据集的唯一标识列被塞入price字段，字符串转无符号整数失败默认赋值为0；原日期列被塞入长度仅为8的postcode字段，被截断为1995-xx-的格式，和你看到的异常完全匹配。
• 数据集读取配置错误：英国政府房产价格数据集本身没有表头，直接调用pd.read_csv(url)会把第一行数据识别为列名，导致首行数据丢失、列结构异常。
• LOAD DATA配置错误：一是没有指定字段映射，你表结构最后一列db_id是自增主键不需要导入，不指定字段会导致导入值和表字段无法对应；二是官方数据集的字符串字段默认用双引号包裹，你设置ENCLOSED BY ''会导致字段拆分异常。
• 事务未提交：mysql.connector默认开启手动事务，你执行导入后没有调用connection.commit()，可能导致数据丢失。

1. 修正CSV读取和导出逻辑 #

读取时指定无表头，导出时去掉行索引和表头，保证导出的CSV结构和官方原始数据集完全一致：

for url in filenames:
    # 官方数据集无表头，禁止将第一行识别为列名
    df = pd.read_csv(url, header=None)
    # 导出时去掉行索引、去掉表头行
    df.to_csv('out.csv', index=False, header=False, encoding='utf-8')
    cursor.execute(query)
    # 每次导入后提交事务
    connection.commit()

2. 修正LOAD DATA语句 #

指定字段映射，跳过自增主键db_id，配置正确的字段包裹符，同时处理带时间的日期格式：

LOAD DATA LOCAL INFILE 'out.csv' 
INTO TABLE property_prices.pp_data 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
-- 按数据集顺序指定对应表字段，跳过自增的db_id
(transaction_unique_identifier, price, @transfer_date, postcode, property_type, new_build_flag, tenure_type, primary_addressable_object_name, secondary_addressable_object_name, street, locality, town_city, district, county, ppd_category_type, record_status)
-- 将带时间的字符串转换为date类型存入数据库
SET date_of_transfer = STR_TO_DATE(@transfer_date, '%Y-%m-%d %H:%i:%s');

3. 可选优化 #

你定义的run函数每次执行都会新建数据库连接，执行后没有关闭连接，建议补充关闭逻辑避免连接泄露：

def run(query):
    connection = mysql.connector.connect(user=USR, password=PASSWORD, database=DBNAME, host=ENDPOINT, port=PORT, allow_local_infile=True)
    cursor = connection.cursor()
    cursor.execute(query, multi=True)
    connection.commit()
    cursor.close()
    connection.close()

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