Oct-09-2019, 04:45 AM
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.
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()