Bulk update SQL Server and Python

Question

I have the following issues, i am selecting 17K records from Azure SQL server database into a list with Python, preforming text manipulation and i want to update the result into the database , i have two fields record_id and Supplier name , i added all the updates into a new list and trying to update the database ,the issue is that the last part(for rec_to_update in sqlupdates) takes a lot of time more than 15 minutes :

sqlupdates is the list of updates i want to preform , what am i doing wrong?

for record in Allrecords :
        Rec=re.sub(r'[^a-z]', ' ', record[1])
        querywords =Rec.split()
        resultwords  = [word for word in querywords if word.lower() not in List_of_words]
        result = ' '.join(resultwords)
        sqlupdates.append("Update labs_work_data_test set Supplier_Name='"+result+"' where record_id="+str(record[0])+";")    
    for rec_to_update in sqlupdates:
        newcursor.execute(rec_to_update)

    newcursor.commit()

it seems like a lot of processing happening. But the logic seems to me like something SQL can actually handle. Have you tried to move it into SQL? that can be faster... — MEdwin, Nov 06 '18 at 09:26
see if you can share with us the nature of the data. some sample can help. — MEdwin, Nov 06 '18 at 09:37
In this case, it's better to write a stored procedure by including a cursor in sql server side and calling with python by using the necessary parameters. Therefore it'll increase some of the performance. In the meantime could you please provide the relevant table structure and etc.. — Prasan Karunarathna, Nov 06 '18 at 09:55

score 0 · Answer 1 · answered Nov 07 '18 at 14:19

For now i couldn't find a solution for fast update , so what i did i created a temp table in the database , and did fast insert to it with the key from my table , and it take 1 minutes instead of 45 minutes.

sqlupdates=("insert into data_work_tmp values(?,?,?);")    
newcursor.fast_executemany = True
newcursor.executemany(sqlupdates,parms)
newcursor.commit()

Bulk update SQL Server and Python

1 Answers1