0

I am trying to load the data from the REST API into SQL server table using the Python script. The script below works perfectly but the issues is it takes too long to upload the data in to the database , it took few minutes to load 8000 records

import requests
import pandas as pd
import pyodbc
import sys
from requests.auth import HTTPBasicAuth

#SQL Connection
conn_str = (
    r'DRIVER={SQL Server Native Client 11.0};'
    r'SERVER=tcp:abcd.database.windows.net;'
    r'DATABASE=DEF;'
    r'UID=xxxxx;'
    r'PWD=yyyy;'
    r'Trusted_Connection=no;'
)

sqlconn = pyodbc.connect(conn_str)


cursor = sqlconn.cursor()
cursor.execute("TRUNCATE TABLE [dbo].[RM_Approved_Room_State]")
sqlconn.commit()

#API Connection
baseURL = 'https://testing.org/api/fdf'
appRoomsResponse = requests.get(baseURL, verify=False)
appRoomsJson = appRoomsResponse.json()
appRoomsDF = pd.json_normalize(appRoomsJson)
appRoomsDF = appRoomsDF.fillna('')

try:
        cursor = sqlconn.cursor()
        for index,row in appRoomsDF.iterrows():
            cursor.execute("INSERT INTO RM_Approved_Room_State(APPROVED_ROOM_STATEID,SOURCE_ROOMID,DEST_ROOMID,ENTITY_TYPEID)\
                                        values(?,?,?,?)"

                            ,row['id']
                            ,row['sourceRoomRefId']
                            ,row['destinationRoomRefId']
                            ,row['entityRefId']
                        )
            sqlconn.commit()
except Exception:
    pass

#Load main Dim_appRooms table
cursor = sqlconn.cursor()
sqlconn.commit()


cursor.close()
sqlconn.close()

Is there something I am missing to increase the speed of the insert here. My first script in Python, anyhelp is greatly appreciated

user4912134
  • 1,003
  • 5
  • 18
  • 47
  • Thank you, Can you please give me an idea how can I use dataframe here – user4912134 Apr 27 '22 at 20:22
  • 1
    Sorry, deleted my comment before I saw you replied. Have a look at this questions: https://stackoverflow.com/questions/29638136/how-to-speed-up-bulk-insert-to-ms-sql-server-using-pyodbc – squillman Apr 27 '22 at 20:35
  • @squillman looks too complex and confusing.. If possible can you give me a simple example here – user4912134 Apr 27 '22 at 20:52
  • 1
    Did you actually even look at that link? It really isn't all that complicated. If @squillman wrote out the example here it can't get much simpler than the link already posted. – Sean Lange Apr 27 '22 at 21:02
  • @SeanLange Thank you I didnt understand how/where the dataframe is used on the answer on the posted link – user4912134 Apr 28 '22 at 12:45
  • @squillman I tried adding `crsr.fast_executemany = True` to my above script before the isert statement I dont see any difference in execution time – user4912134 Apr 28 '22 at 13:17
  • `fast_executemany = True` modifies the behaviour of `executemany()`. You are using individual `.execute()` statements in a loop, so `fast_executemany = True` will have no effect. – Gord Thompson Apr 28 '22 at 17:35
  • @GordThompson Thank you.. Do you recommend anything to improve the performance of the script – user4912134 Apr 28 '22 at 18:25
  • You could use your loop to build a list of tuples and then call `.executemany()` once with that list of tuples as the parameter values. That way `fast_executemany = True` will be able to help you. – Gord Thompson Apr 28 '22 at 18:30

0 Answers0