Python 使用psycopg2批量插入PG库

import psycopg2

conn = psycopg2.connect(database="sdp", user="kiki", password="123", host="", port="5432")
cursor = conn.cursor()
stats_sql = "select max(a) from table_a group by vin"
cursor.execute(stats_sql)
res_rows = cursor.fetchall()
tp_arr = []
simple = {}
for row in res_rows:
    for item in row:
        # 处理行数据
        for res in eval(item):
            if len(simple) == 0:
                simple = res
            tp = tuple(res.values())
            tp_arr.append(tp)
cols = ", ".join('{}'.format(k) for k in simple.keys())
val_cols = ','.join('%s'.format(v) for v in simple.keys())
sql = "insert into temp.table_b (%s) values (%s)"
insert_sql = sql % (cols, val_cols)
cursor.executemany(insert_sql, tp_arr)
conn.commit()
conn.close()

　　# 结果返回为字典

import psycopg2
 
def get_data(database_info,sql):
    conn = psycopg2.connect(database=database_info["database"], 
                            user=database_info["user"],
                            password=database_info["password"],
                            host=database_info["host"], 
                            port=database_info["port"])
    cur = conn.cursor()
    try:
        cur.execute(sql)
        #获取表的所有字段名称
        coloumns = [row[0] for row in cur.description]
        result = [[str(item) for item in row] for row in cur.fetchall()]
        return [dict(zip(coloumns, row)) for row in result]
    except Exception as ex:
        print(ex)
    finally:
        conn.close()
#数据库连接信息
database_info={
    "database":"test_base_inf",
    "user":"data_inf_root",
    "password":"BASE_root~589",
    "host":"192.168.12.101",
    "port":"2345"
}
sql="select * from nric_affiliation"
data=get_data(database_info,sql)
for item in data:
    print(item)