CODEWITHSUNDEEP
pythonsql-serverpython-3.xpymssql
Cesar
Cesar

Reputation: 617

CSV data causes error when uploading to SQL table via Python

So I've been struggling with this problem for the last couple of days. I need to upload a CSV file with about 25 columns & 50K rows into a SQL Server table (zzzOracle_Extract) which also contains 25 columns, same Column names & in the same order.

This is what a row looks like from the CSV file:

['M&M OPTICAL SHOP', '3001211', 'SHORE', '*', 'PO BOX 7891', '', '', '', 'GUAYNABO', 'GUAYNABO', 'PR', '0090', 'United States', '24-NSH RETAIL CUSTOMER', 'SH02-SHORE COLUMN 2', '3001211', '*', '*', '*', '3001211744-BILL_TO', '', '', '', '', 'RACHAEL']

So in total, there are 25 columns with some values being blank. Maybe this is causing an error. Here is my code:

import csv
import pymssql

conn = pymssql.connect(
    server="xxxxxxxxxx",
    port = 2433,
    user='SQLAdmin',
    password='xxxxx',
    database='NasrWeb'
)

with open('cleanNVG.csv','r') as f:
    reader = csv.reader(f)
    columns = next(reader)
    query = 'insert into dbo.zzzOracle_Extract({0}) Values({1})'
    query = query.format(','.join(columns),','.join('?' * len(columns)))
    cursor = conn.cursor()
    for data in reader:
        print(data) #What a row looks like
        cursor.execute(query,data)
    cursor.commit()

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

After the script is executed, one of the errors I get is the following:

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

What can be wrong with my code? I really appreciate the help!

Upvotes: 1

Views: 631

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

How do join the [ ] to each column in my code?

So you have something like 

>>> columns = ['ID','Last Name','First Name']

and you're currently using 

>>> ','.join(columns)
'ID,Last Name,First Name'

but now you need to wrap the column names in square brackets. That could be done with

>>> ','.join('[' + x + ']' for x in columns)
'[ID],[Last Name],[First Name]'

Upvotes: 2

Related Questions