1

I am trying to insert 2,000,000 records from a .CSV file into Microsoft SQL Server using Python. Standard insert function took me 3 hours to upload all records. I am trying to reduce this to minutes.

I am fairly new to Python.

import pyodbc
import csv
import time
import pandas as pd

new_data = []
t0 = time.time()

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=xyz;'
                      'Database=Rst;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.fast_executemany = True

parameter = [()]

with open(r"\\abc.csv") as csvDataFile:
    csvReader = csv.reader(csvDataFile)
    next(csvReader)

    for row in csvReader:
        cursor.executemany('INSERT INTO [dbo].[Science_Catalogue_Extraction]([Supplier],[Catalogue_Number],[Description],[Long_Description],[UNSPSC_Code],[CAS_Number],[Victoria_Hazard_Flag],[Overall_Status],[Chemical_Buyers_Override],[Standard_Buyers_Override])''VALUES(?,?,?,?,?,?,?,?,?,?)',row)

print(f'{time.time() - t0:.1f} seconds')    
conn.commit()
cursor.close()
conn.close()`

I am getting an error

TypeError: ('Params must be in a list, tuple, or Row', 'HY000').

Please help :(

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shiv Chandra
  • 11
  • 1
  • 1
    the error should tell you what the issue is, why don’t you print out the row and see what it looks like – gold_cy Jul 31 '19 at 01:36
  • 1
    Possible duplicate of [pyodbc: ('Params must be in a list, tuple, or Row', 'HY000') with NumPy data](https://stackoverflow.com/questions/42286679/pyodbc-params-must-be-in-a-list-tuple-or-row-hy000-with-numpy-data) – pauli Jul 31 '19 at 02:47
  • Cheers for the help. Now i am getting memory error. – Shiv Chandra Jul 31 '19 at 03:38
  • @Shiv Chandra- try below approach and let me know if it works faster https://stackoverflow.com/questions/46684359/speed-up-insert-to-sql-server-from-csv-file-without-using-bulk-insert-or-pandas – vikrant rana Aug 02 '19 at 16:15
  • below is one more approach loading huge csv file using powershell. I haven't tested it.. https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/ – vikrant rana Aug 02 '19 at 16:18
  • Let me know which one works faster – vikrant rana Aug 02 '19 at 16:18

0 Answers0