import asyncio
import asyncpg
import pandas as pd


async def main():
    data = pd.read_excel('D:\\excel\\retry.xlsx')
    second_column = data.iloc[:, 1]
    # print(second_column)

    need_insert_list = []
    need_update_list = []
    # 创建连接池
    async with asyncpg.create_pool(user='otascmadm', password='xxxx', database='otadm', host='xxxx',
                                   command_timeout=60) as pool:
        async with pool.acquire() as con:
            for vin in second_column:
                result = await con.fetchrow('SELECT * FROM dm."DV_EXEC_INFO" where "VIN"=$1', vin)
                if result:
                    if result["EXEC_CMD"] != '0001':
                        need_update_list.append(vin)
                        print(result)
                else:
                    device_result = await con.fetchrow('SELECT * FROM dm."DEVICE_NEW" where "DV_ID" LIKE $1', f"VIN:{vin}%")
                    if device_result:
                        need_insert_list.append((device_result["PCKG_NM"], device_result["DV_ID"], vin))



        # 关闭连接池
        await pool.close()

    print("need_insert_list: {}".format(need_insert_list))
    print("need_update_list: {}".format(need_update_list))
    async with asyncpg.create_pool(user='postgres', password='postgres123', database='otadm', host='127.0.0.1',
                                   command_timeout=60) as pool2:
        async with pool2.acquire() as con2:
            insert_result = await con2.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)
        # 关闭连接池
        await pool2.close()

if __name__ == '__main__':
    asyncio.run(main())