Async IO writing to two Different Tables Help

Async IO writing to two Different Tables Help - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Async IO writing to two Different Tables Help (/thread-21661.html)

Async IO writing to two Different Tables Help - TiagoV - Oct-09-2019

Hi All,

I'm new to the forum, but please bear with me. I'm trying to create two functions that write into different SQL Tables within the same Database (Microsoft SQL Server).

At the moment, it appears my code doesn't INSERT to both tables at the same time. But does it in a synchronous manner.

I am using SPYDER as my IDE. I've tried to create the function and wrap it inside the logic of asyncio, however, when I look at the tables through MSQL studio as I'm executing the function. It inserts function WriteJobs first and then WritetoCustomers second.

I'm a bit stuck at the moment and any help would be appreciated.

        
import pyodbc
import asyncio
import time
from concurrent.futures import ThreadPoolExecutor

conn = pyodbc.connect('Driver={SQL Server};'
              'Server=xxxx;'
              'Database=Datawarehouse;'
              'user=xxxx, password=xxxx'
              'MultipleActiveResultSets=yes')    

async def WritetoJobs(Name,conn):
    
    Cursor1 = conn.cursor()
    # You must create a Cursor object. It will let you execute all the queries you need
    sql = ""
    for i in range(0, 10000):
        sql = sql +"('" + Name + str(i) + "')"
        sql = "INSERT INTO SIMPRO_Jobs ([Customer.CompanyName]) VALUES" + sql
        print("Inserting Job Records - {} .".format(i))
        Cursor1.execute(sql)
        sql = ""
        conn.commit()

async def WritetoCustomers(Name,conn):
    
    Cursor2 = conn.cursor()
    # You must create a Cursor object. It will let you execute all the queries you need
    sql = ""
    for i in range(0, 10000):
        sql = sql +"('" + Name + str(i) + "')"
        sql = "INSERT INTO SIMPRO_Customers ([CompanyName]) VALUES" + sql
        print("Inserting Customer Records - {} .".format(i))
        Cursor2.execute(sql)
        sql = ""
        conn.commit()

async def main():

    JobsTask = loop.create_task(WritetoJobs('John',conn)) #task inside the loop
    CustomersTask = loop.create_task(WritetoCustomers('John',conn)) #task inside the loop
    
    await asyncio.wait([JobsTask,CustomersTask])
    #conn.commit()
    print("process complete")
    end = time.time()
    print(f"Completed in {(end-start)*1000}ms")
    
    #return JobsTask,CustomersTask

if __name__ == "__main__":
   
    try:
        start = time.time()
        loop = asyncio.get_event_loop()
        #d1, d2, d3 = loop.run_until_complete(main())
        loop.run_until_complete(main())
    except Exception as e:
        # logging...etc
        pass
    #finally:
        #loop.close()