CODEWITHSUNDEEP
pythonsql-servercsvpyodbc
Jamesdabeard
Jamesdabeard

Reputation: 31

Escape Column names writing to SQL with python,pyodbc from csv

I am a pretty new to coding and am stuck. I am pulling a report through an api which gives me a .csv file. I want to paste this into a SQL database but am running into some trouble.

My current code looks like this:

reader is the decoded csv file.

columns = next(reader,None)
query = "INSERT INTO table({0}) VALUES ({1})"
query = query.format(','.join(columns),','.join('?'*len(columns)))
cursor = cnxn.cursor()
cursor.execute(query, columns)
for data in reader:
    cursor.execute(query, data)
cursor.commit()

When I run the code I get this error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'Group'. (156) (SQLExecDirectW)")

I think the issue is that my columns are:

Date |  Advertiser |    Campaign |  Ad Group | etc.

And it seems that maybe SQL is recognizing the "Group" of "Ad Group" as a variable.

Any ideas? Thank you.

Upvotes: 2

Views: 1493

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169434

As mentioned in a comment by @ZLK you need to enclose the column names in square brackets. This will do the trick:

query = query.format('[{0}]'.format('], ['.join(columns)),','.join('?'*len(columns)))

Upvotes: 4

Related Questions