How to speed up bulk insert to MS SQL Server using pyodbc

by

As noted in a comment to another answer, the T-SQL BULK INSERT command will only work if the file to be imported is on the same machine as the SQL Server instance or is in an SMB/CIFS network location that the SQL Server instance can read. Thus it may not be applicable in the case where the source file is on a remote client.

pyodbc 4.0.19 added a Cursor#fast_executemany feature which may be helpful in that case. fast_executemany is “off” by default, and the following test code …

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')

… took approximately 22 seconds to execute on my test machine. Simply adding crsr.fast_executemany = True …

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()
crsr.execute("TRUNCATE TABLE fast_executemany_test")

crsr.fast_executemany = True  # new in pyodbc 4.0.19

sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"
params = [(f'txt{i:06d}',) for i in range(1000)]
t0 = time.time()
crsr.executemany(sql, params)
print(f'{time.time() - t0:.1f} seconds')

… reduced the execution time to just over 1 second.

More Related Contents:

  1. Writing a csv file into SQL Server database using python
  2. Error 28000: Login failed for user DOMAIN\\user with pyodbc
  3. Using SQL Server stored procedures from Python (pyodbc)
  4. to_sql pyodbc count field incorrect or syntax error
  5. Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio
  6. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  7. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
  8. create a loop inside a loop in python
  9. Writing a pandas DataFrame to CSV file
  10. Working with an Access database in Python on non-Windows platform (Linux or Mac)
  11. Python Pandas Error tokenizing data
  12. Read specific columns from a csv file with csv module?
  13. Read and Write CSV files including unicode with Python 2.7
  14. Load CSV file with Spark
  15. how to import csv data into django models
  16. pandas read_csv and filter columns with usecols
  17. SQL Server Bulk insert of CSV file with inconsistent quotes
  18. import text to pandas with multiple delimiters
  19. Can’t open lib ‘ODBC Driver 13 for SQL Server’? Sym linking issue?
  20. Error: Unsupported format, or corrupt file: Expected BOF record
  21. portable way to write csv file in python 2 or python 3
  22. Streaming large training and test files into Tensorflow’s DNNClassifier
  23. python pandas read_csv delimiter in column data
  24. How to skip the headers when processing a csv file using Python?
  25. convert csv file to list of dictionaries
  26. Why does CSV file contain a blank line in between each data line when outputting with Dictwriter in Python [duplicate]
  27. module ‘pandas’ has no attribute ‘read_csv
  28. Strip white spaces from CSV file
  29. Get data from Twitter using Tweepy and store in csv file
  30. Python Parse CSV Correctly

Leave a Comment