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
– Cesar, Commented Oct 7, 2016 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
– Steven Rumbalski, Commented Oct 7, 2016 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
– Cesar, Commented Oct 7, 2016 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
– Cesar, Commented Oct 7, 2016 at 14:45

Gord Thompson · Accepted Answer · 2016-10-07 15:43:45Z

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 7, 2016 at 15:43

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cesar Over a year ago

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?

Gord Thompson Over a year ago

@Cesar re: %f - I don't think that is necessary. In fact, I don't think it will even work. The pymssql documentation 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.

Collectives™ on Stack Overflow

CSV data causes error when uploading to SQL table via Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related