Python导入Excel至PostgreSQL后数据顺序与原表不符问题排查

使用Python结合Pandas、SQLAlchemy工具将Excel文件数据导入PostgreSQL数据库，数据内容加载正确，但数据库内部分行的顺序与Excel原表不一致，已确认Excel数据整理规范，询问该问题的原因。

• PostgreSQL存储特性限制：PostgreSQL作为关系型数据库，其默认的堆表结构不会保证数据的存储顺序，也不会保留插入顺序。查询数据时若未明确指定ORDER BY子句，返回的行顺序由数据库物理存储位置、查询优化器策略等因素决定，并非固定为插入时的顺序。
• 插入顺序与存储顺序脱节：代码中通过df.iterrows()按Excel行顺序遍历生成DataFrame，插入数据库的顺序与DataFrame行顺序一致，但PostgreSQL会将数据写入磁盘空闲页，不会为了保留插入顺序特意调整存储位置，最终导致查询顺序和原Excel顺序不符。
• 缺少排序标识列：目标表中没有能唯一对应原Excel顺序的列（如自增ID、原Excel行索引），无法通过查询语句精准还原原顺序。

• 查询时强制指定排序：每次查询数据都添加ORDER BY子句，指定一个能对应原Excel顺序的列（比如在DataFrame中新增row_index列记录原Excel行号，导入后按该列排序）。
• 添加自增主键列：在目标表中创建自增主键列（如id SERIAL PRIMARY KEY），数据插入时会自动获得递增ID，查询时按id排序即可还原插入顺序。
• 验证DataFrame顺序：插入数据库前，可将df_to_insert导出为临时文件或打印前若干行，确认DataFrame行顺序是否与原Excel一致，排除代码遍历过程中的顺序错误。

import pandas as pd
from sqlalchemy import create_engine

DB_NAME = "Test"
DB_USER = "*********"
DB_PASSWORD = "j*********"
DB_HOST = "localhost"
DB_PORT = "5432"

engine = create_engine(f'postgresql+psycopg2://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}')

excel_file_path = 'C:\\.....'
df = pd.read_excel(excel_file_path)

columns = ['Name_of_the_file', 'Name_of_sheet', 'Item_code', 'tbd', 'tbd1', 'tbd2', 'Item_07', 'Item_08', 'Item_09', 'Item_10', 'Qty', 'Unit', 'Datum']
data = []


current_name_of_sheet = None
current_item_07 = None
current_item_08 = None
current_item_09 = None
Name_of_the_file = 'BoQ Building Services-Base'

for index, row in df.iterrows():
    A_value = row['A']
    B_value = row['B']
    C_value = row['C']
    D_value = row['D']
    E_value = row['E']
    if B_value == 2:
        current_name_of_sheet = D_value
        current_item_08 = None
        current_item_09 = None
        current_item_07 = None

        data.append([Name_of_the_file, current_name_of_sheet, A_value, None, None, None, None, None, None, None, None, None, None])
    elif B_value == 7:
        current_item_07 = D_value
        data.append([Name_of_the_file, current_name_of_sheet, A_value, None, None, None, current_item_07, None, None, None, None, None, None])
    elif B_value == 8:
        current_item_08 = D_value
        data.append([Name_of_the_file, current_name_of_sheet, A_value, None, None, None, None, current_item_08, None, None, None, None, None])
    elif B_value == 9:
        current_item_09 = D_value
        data.append([Name_of_the_file, current_name_of_sheet, A_value, None, None, None, None, None, current_item_09, None, None, None, None])
    elif B_value == 10:
        data.append([Name_of_the_file, current_name_of_sheet, A_value, None, None, None, None, None, None, D_value, E_value, C_value, None])

df_to_insert = pd.DataFrame(data, columns=columns)

table_name = 'table3'
df_to_insert.to_sql(table_name, engine, if_exists='append', index=False)

print("Files are imported in table!")

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