CSV data causes error when uploading to SQL table via Python

Question

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!

I tired it but it still threw an error and new one, (102, b"Incorrect syntax near 'Name'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") — Cesar, Oct 07 '16 at 14:25
Do your field names have spaces in them? If they do, they need to be enclosed in square brackets in your SQL. — Steven Rumbalski, Oct 07 '16 at 14:39
@StevenRumbalski Yes, I just checked exactly the same. And I looked in my SQL table design and 23 columns have (nvarchar) as the data type and the other 2 have numeric and float. This cant be it? Just a random guess. — Cesar, Oct 07 '16 at 14:41
Oh okay, i just ran print(Query) to give me the format of my query and it has **insert into dbo.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)** SO it is missing the " [ ] " I believe — Cesar, Oct 07 '16 at 14:45
How do join the [ ] to each column in my code? @StevenRumbalski — Cesar, Oct 07 '16 at 14:46
@Cesar sorry but I need to ask you: how could you have bothered to post on SO without even printing the SQL statement to check how it looks? — BangTheBank, Oct 07 '16 at 14:47
Change `','.join(columns)` to `'[' + '], ['.join(columns) + ']'`. — Steven Rumbalski, Oct 07 '16 at 15:41
Before casting `data` to a `tuple`, fix the non-string data types: `data[1] = int(data[1])`. — Steven Rumbalski, Oct 07 '16 at 15:44
Your solution of using 50k insert statements will be slow. You can [use `BULK INSERT`](http://stackoverflow.com/questions/29638136/how-to-speed-up-with-bulk-insert-to-ms-server-from-python-with-pyodbc-from-csv) instead. — Steven Rumbalski, Oct 07 '16 at 15:49

score 2 · Accepted Answer · answered Oct 07 '16 at 15:43

2

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

answered Oct 07 '16 at 15:43

Gord Thompson

116,920
32
215
418

Thanks @Gord Thompson I did this for the other join. **query.format(','.join('[' + x + ']' for x in columns),','.join("%" + "s" for m in columns))** So now I have the query in brackets and for values **25 "%s"**. which is Correct, However, #16 in the second join has to be **%f**, or a float, how can I make this an exception for the second join? – Cesar Oct 07 '16 at 16:26
@Cesar re: `%f` - I don't think that is necessary. In fact, I don't think it will even work. The [pymssql documentation](http://pymssql.org/en/latest/ref/pymssql.html) says that "... since formatting and type conversion is handled internally, only the `%s` and `%d` placeholders are supported. Both placeholders are functionally equivalent." Try just using `%s` for all of them. – Gord Thompson Oct 07 '16 at 16:57

CSV data causes error when uploading to SQL table via Python

1 Answers1