Python Forum
Async IO writing to two Different Tables Help
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()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  queue for async function python telegram.ext noctious 0 1,510 Jun-11-2023, 02:58 PM
Last Post: noctious
  get data from 2 async functions korenron 0 1,201 Sep-22-2021, 08:39 AM
Last Post: korenron
  Async requests lukee 0 1,487 Oct-06-2020, 04:40 AM
Last Post: lukee
  Issues with async and yielding from API GSerum 1 2,105 Dec-18-2018, 08:37 PM
Last Post: nilamo
  async question on raspberry pi baukeplugge 2 47,666 Nov-07-2018, 07:58 PM
Last Post: baukeplugge
  Async server/client Bokka 2 3,839 May-29-2017, 03:09 PM
Last Post: Bokka

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020