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)