Python PYDOBC Insert Into SQL Server DB with Parameters

Question

I am currently trying to use pyodbc to insert data from a .csv into an Azure SQL Server database. I found a majority of this syntax on Stack Overflow, however for some reason I keep getting one of two different errors.

1) Whenever I use the following code, I get an error that states 'The SQL contains 0 parameter markers, but 7 parameters were supplied'.

import pyodbc
import csv
cnxn = pyodbc.connect('driver', user='username', password='password', database='database')
 cnxn.autocommit = True
cursor = cnxn.cursor()
csvfile = open('CSV File')
csv_data = csv.reader(csvfile)
SQL="insert into table([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) values ('?','?','?','?','?','?','?')"
for row in csv_data:
   cursor.execute(SQL, row)
time.sleep(1)
cnxn.commit()
cnxn.close()

2) In order to get rid of that error, I am defining the parameter markers by adding '=?' to each of the columns in the insert statement (see code below), however this then gives the following error: ProgrammingError: ('42000'"[42000] [Microsoft] [ODBC SQL Server Driver][SQL Server] Incorrect syntax near '=').

import pyodbc
import csv
cnxn = pyodbc.connect('driver', user='username', password='password', database='database')
cnxn.autocommit = True
cursor = cnxn.cursor()
csvfile = open('CSV File')
csv_data = csv.reader(csvfile)
SQL="insert into table([Col1]=?,[Col2]=?,[Col3]=?,[Col4]=?,[Col5]=?,[Col6]=?,[Col7]=?) values ('?','?','?','?','?','?','?')"
for row in csv_data:
   cursor.execute(SQL, row)
time.sleep(1)
cnxn.commit()
cnxn.close()

This is the main error I am haveing trouble with, I have searched all over Stack Overflow and can't seem to find a solution. I know this error is probably very trivial, however I am new to Python and would greatly appreciate any advice or help.

June7 · Accepted Answer · 2022-09-13 05:27:12Z

3

Since SQL server can import your entire CSV file with a single statement this is a reinvention of the wheel.

BULK INSERT my_table FROM 'CSV_FILE'
WITH ( FIELDTERMINATOR=',', ROWTERMINATOR='\n');

If you want to persist with using python, just execute the above query with pyodbc!

If you would still prefer to execute thousands of statements instead of just one

SQL="insert into table([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) values (?,?,?,?,?,?,?)"

note that the ' sorrounding the ? shouldn't be there.

edited Sep 13, 2022 at 5:27

June7

21.6k8 gold badges28 silver badges36 bronze badges

answered Jan 13, 2017 at 23:41

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Comments

David Buck · Accepted Answer · 2020-08-05 18:00:10Z

1

# creating column list for insertion
colsInsert = "["+"],[".join([str(i) for i in mydata.columns.tolist()]) +']'

# Insert DataFrame recrds one by one.
for i,row in mydata.iterrows():
    sql = "INSERT INTO Test (" +colsInsert + ") VALUES (" + "%?,"*(len(row)-1) + "%?)"
    cursor.execute(sql, tuple(row))
   # cursor.execute(sql, tuple(row))

    # the connection is not autocommitted by default, so we must commit to save our changes
    c.commit()

edited Aug 5, 2020 at 18:00

David Buck

3,87740 gold badges54 silver badges73 bronze badges

answered Aug 5, 2020 at 16:09

Akif

111 bronze badge

Collectives™ on Stack Overflow

Python PYDOBC Insert Into SQL Server DB with Parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related