importasyncioimportasyncpgimportpandasaspdasyncdefmain():datapd.read_excel(D:\\excel\\retry.xlsx)second_columndata.iloc[:,1]# print(second_column)need_insert_list[]need_update_list[]# 创建连接池asyncwithasyncpg.create_pool(userotascmadm,passwordxxxx,databaseotadm,hostxxxx,command_timeout60)aspool:asyncwithpool.acquire()ascon:forvininsecond_column:resultawaitcon.fetchrow(SELECT * FROM dm.DV_EXEC_INFO where VIN$1,vin)ifresult:ifresult[EXEC_CMD]!0001:need_update_list.append(vin)print(result)else:device_resultawaitcon.fetchrow(SELECT * FROM dm.DEVICE_NEW where DV_ID LIKE $1,fVIN:{vin}%)ifdevice_result:need_insert_list.append((device_result[PCKG_NM],device_result[DV_ID],vin))# 关闭连接池awaitpool.close()print(need_insert_list: {}.format(need_insert_list))print(need_update_list: {}.format(need_update_list))asyncwithasyncpg.create_pool(userpostgres,passwordpostgres123,databaseotadm,host127.0.0.1,command_timeout60)aspool2:asyncwithpool2.acquire()ascon2:insert_resultawaitcon2.executemany(INSERT INTO dm.DV_EXEC_INFO ( EX_ID , PCKG_NM , DV_ID , VIN , EXEC_CMD , USE_YN , CRTE_TIM , UPDE_TIM ) VALUES( 223 , $1 , $2 , $3 , 0001 , Y , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) ON CONFLICT ON CONSTRAINT DV_EXEC_INFO_pkey DO UPDATE SET ( UPDE_TIM , EXEC_CMD ) ( CURRENT_TIMESTAMP , 0001 ),need_insert_list)print(insert_result)# 关闭连接池awaitpool2.close()if__name____main__:asyncio.run(main())
python asyncpg操作postgres数据库
发布时间：2026/6/4 16:34:13
importasyncioimportasyncpgimportpandasaspdasyncdefmain():datapd.read_excel(D:\\excel\\retry.xlsx)second_columndata.iloc[:,1]# print(second_column)need_insert_list[]need_update_list[]# 创建连接池asyncwithasyncpg.create_pool(userotascmadm,passwordxxxx,databaseotadm,hostxxxx,command_timeout60)aspool:asyncwithpool.acquire()ascon:forvininsecond_column:resultawaitcon.fetchrow(SELECT * FROM dm.DV_EXEC_INFO where VIN$1,vin)ifresult:ifresult[EXEC_CMD]!0001:need_update_list.append(vin)print(result)else:device_resultawaitcon.fetchrow(SELECT * FROM dm.DEVICE_NEW where DV_ID LIKE $1,fVIN:{vin}%)ifdevice_result:need_insert_list.append((device_result[PCKG_NM],device_result[DV_ID],vin))# 关闭连接池awaitpool.close()print(need_insert_list: {}.format(need_insert_list))print(need_update_list: {}.format(need_update_list))asyncwithasyncpg.create_pool(userpostgres,passwordpostgres123,databaseotadm,host127.0.0.1,command_timeout60)aspool2:asyncwithpool2.acquire()ascon2:insert_resultawaitcon2.executemany(INSERT INTO dm.DV_EXEC_INFO ( EX_ID , PCKG_NM , DV_ID , VIN , EXEC_CMD , USE_YN , CRTE_TIM , UPDE_TIM ) VALUES( 223 , $1 , $2 , $3 , 0001 , Y , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) ON CONFLICT ON CONSTRAINT DV_EXEC_INFO_pkey DO UPDATE SET ( UPDE_TIM , EXEC_CMD ) ( CURRENT_TIMESTAMP , 0001 ),need_insert_list)print(insert_result)# 关闭连接池awaitpool2.close()if__name____main__:asyncio.run(main())
