Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Async IO writing to two Different Tables Help
#1
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()


Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Issues with async and yielding from API GSerum 1 434 Dec-18-2018, 08:37 PM
Last Post: nilamo
  async question on raspberry pi baukeplugge 2 423 Nov-07-2018, 07:58 PM
Last Post: baukeplugge
  Async server/client Bokka 2 1,668 May-29-2017, 03:09 PM
Last Post: Bokka

Forum Jump:


Users browsing this thread: 1 Guest(s)