Import CSV file into SQL Server using Python

python python-3.x pymssql
Cesar picture Cesar · Oct 6, 2016 · Viewed 26.7k times · Source

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

enter image description here

Answer

citynorman picture citynorman · Dec 17, 2018

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')

Related questions

Convert bytes to a string

I'm using this code to get standard output from an external program: >>> from subprocess import * >>> command_stdout = Popen(['ls', '-l'], stdout=PIPE).communicate()[0] The communicate() method returns an array of bytes: >>> …

Read More
Best way to convert string to bytes in Python 3?

There appear to be two different ways to convert a string to bytes, as seen in the answers to TypeError: 'str' does not support the buffer interface Which of these methods would be better or more Pythonic? Or is it …

Read More
How to install pip with Python 3?

I want to install pip. It should support Python 3, but it requires setuptools, which is available only for Python 2. How can I install pip with Python 3?

Read More