Sometimes, we want to speed up bulk insert to MS SQL Server using pyodbc with Python.
In this article, we’ll look at how to speed up bulk insert to MS SQL Server using pyodbc with Python.
How to speed up bulk insert to MS SQL Server using pyodbc with Python?
To speed up bulk insert to MS SQL Server using pyodbc with Python, we can use the executemany method.
For instance, we write
cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")
crsr.fast_executemany = True
sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
crsr.executemany(sql, params)
to create a cursor object with cursor.
Then we set the cursor’s fast_executemany property to True to speed up execution.
Then we call executemany with the sql string and the params list to run all the queries.
Conclusion
To speed up bulk insert to MS SQL Server using pyodbc with Python, we can use the executemany method.
