Import CSV file into SQL Server using Python

Question

I am having trouble uploading a CSV file into a table in MS SQL Server, The CSV file has 25 columns and the header has the same name as table in SQL which also has 25 columns. When I run the script it throws an error

params arg (<class 'list'>) can be only a tuple or a dictionary

What is the best way to import this data into MS SQL? Both the CSV and SQL table have the exact same column names.

Here is the code:

import csv
import pymssql

conn = pymssql.connect(
    server="xx.xxx.xx.90",
    port = 2433,
    user='SQLAdmin',
    password='xxxxxxxx',
    database='NasrWeb'
)

cursor = conn.cursor()
customer_data = csv.reader('cleanNVG.csv') #25 columns with same header as SQL

for row in customer_data:
    cursor.execute('INSERT INTO zzzOracle_Extract([Customer Name]\
      ,[Customer #]\
      ,[Account Name]\
      ,[Identifying Address Flag]\
      ,[Address1]\
      ,[Address2]\
      ,[Address3]\
      ,[Address4]\
      ,[City]\
      ,[County]\
      ,[State]\
      ,[Postal Code]\
      ,[Country]\
      ,[Category ]\
      ,[Class]\
      ,[Reference]\
      ,[Party Status]\
      ,[Address Status]\
      ,[Site Status]\
      ,[Ship To or Bill To]\
      ,[Default Warehouse]\
      ,[Default Order Type]\
      ,[Default Shipping Method]\
      ,[Optifacts Customer Number]\
      ,[Salesperson])''VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,)',row)

conn.commit()
cursor.close()
print("Done")
conn.close()

This is what the first rows of the CSV file looks like

Answer 1

Try d6tstack which has fast pandas to SQL functionality because it uses native DB import commands. It works for Postgres and MYSQL, MS SQL is experimental. Comment or raise an issue if it doesn't work.

import pandas as pd
df = pd.read_csv('cleanNVG.csv')
uri_mssql = 'mssql+pymssql://usr:pwd@localhost/db'
d6tstack.utils.pd_to_mssql(df, uri_mssql, 'table', 'schema') # experimental

It is also useful for importing multiple CSV with data schema changes and/or preprocess with pandas before writing to db, see further down in examples notebook

d6tstack.combine_csv.CombinerCSV(glob.glob('*.csv'), 
    apply_after_read=apply_fun).to_mssql_combine(uri_psql, 'table')

Answer 2

You are using csv.reader incorrectly. The first argument to .reader is not the path to the CSV file, it is

[an] object which supports the iterator protocol and returns a string each time its __next__() method is called — file objects and list objects are both suitable.

Hence, according to the example in the documentation, you should be doing something like this:

import csv
with open('cleanNVG.csv', newline='') as csvfile:
    customer_data = csv.reader(csvfile)
    for row in customer_data:
        cursor.execute(sql, tuple(row))