Better way to do bulk update in mssql using pyodbc?

Question

I want to do bulk update from my python code to mssql DB. Im using pyodbc 4.0.26 version. What is the best way to do update in bulk? My cursor object lists only 'execute' method. I don't see any other methods. I want to scan db for few hundreds records for existing records and do update quickly

cnxn = pyodbc.connect('DRIVER=ODBC Driver 17 for SQL Server;'
                  'SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

`Cursor#executemany` is part of Python's DB API specification and is supported by all of the libraries I've seen, including pyodbc. pyodbc also includes a [Cursor#fast_executemany](https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#fast_executemany) option that can significantly improve performance for `.executemany`. — Gord Thompson, Jun 25 '19 at 13:36

score 0 · Answer 1 · answered Jun 25 '19 at 06:38

0

The right way is to execute an SQL statement that does the update for you. I recommend trying it out first in an SQL IDE (like DataGrip or SSMS) and then putting it into your python code.

answered Jun 25 '19 at 06:38

Tobias Feil

2,399
3
25
41

score 0 · Answer 2 · answered Jun 25 '19 at 06:43

Hope this will work

cnxn = pyodbc.connect('DRIVER=ODBC Driver 17 for SQL Server;'
                  'SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("UPDATE table set key=value where id=11;")

Better way to do bulk update in mssql using pyodbc?

2 Answers2