select COLUMN_NAME from information_schema.columns where table_schema='xxx' and table_name='yyy' ORDER BY ordinal_position
其中,xxx表示postgre的模式名,yyy表示模式中的表名,ordinal_position表示原表中各字段的栏位信息,加了就可以返回跟原表栏位信息一模一样的顺序,否则就是乱序的。
经过尝试发现对于将要写入postgre数据库的数据进行排序是没有意义的,表中呈现的顺序依旧是乱序的。
def to_new_table():
"""排序无意义,gp是分片存储的,即使插入的列表数据id有序,但结果依然是无序的"""
results = conn.query_info(schema_name='xxx', table_name='yyy')
idx_list = []
sorted_results = []
start_idx = 2 # 表的最小id是多少,默认表的ID是连续的
for res in results:
rid = res[0]
idx_list.append(rid)
for i in range(len(idx_list)):
sorted_results.append(results[idx_list.index(i+start_idx)])
# print(sorted_results)
for sr in sorted_results:
rid = sr[0] - 1 # 确保id从1开始递增
english_name = sr[1]
chinese_name = sr[2]
eng_short_name = sr[3]
# chi_short_name = sr[4]
# insert_time = sr[5]
# update_time = sr[6]
insert_column = ['id', 'english_name', 'chinese_name', 'eng_short_name']
insert_tuple = (rid, english_name, chinese_name, eng_short_name)
try:
conn.insert_tuple(schema_name='xxx', table_name='yyy',
insert_column=insert_column, insert_tuple=insert_tuple)
except:
print(rid)
continue