I am trying to insert data to the sql server table using following code,
import pyodbc
user='sa'
password='PC#1234'
database='climate'
port='1433'
TDS_Version='8.0'
server='192.168.1.103'
driver='FreeTDS'
con_string='UID=%s;PWD=%s;DATABASE=%s;PORT=%s;TDS=%s;SERVER=%s;driver=%s' % (user,password, database,port,TDS_Version,server,driver)
cnxn=pyodbc.connect(con_string)
cursor=cnxn.cursor()
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)",('thavasi','mumbai'))
cnxn.commit()
Its giving me the following error while executing,
Traceback (most recent call last):
File "sql.py", line 26, in <module>
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)",('thavasi','mumbai'))
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 2 parameters were supplied', 'HY000')
I have checked the syntax for the insert statement its correct. So what causes this error?
7 Answers 7
cursor.execute("INSERT INTO mytable(name,address) VALUES (?,?)",('thavasi','mumbai'))
use ? instead of % in pyodbc module
Comments
This worked for me.
cursor.execute("INSERT INTO tablename(field1,field2) VALUES(?,?) ", (data1,data2))
Comments
This works with SQL Server 2012 and later and integrated security
import pyodbc
conn = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
r'Server=YourServer\YourInstance;'
'Database=YourDatabase;'
'Trusted_Connection=yes;') #integrated security
cursor = conn.cursor()
SQLCommand = ("INSERT INTO PY.PackageTables (PackageName,PackageTables) VALUES (?,?);")
Values = ['Test1','Test3']
#Processing Query
cursor.execute(SQLCommand,Values)
conn.commit()
print("Data Successfully Inserted")
conn.close()
Comments
You forgot the % sign before the values also the %s must be in inverted commas
try this:
cursor.execute('INSERT INTO mytable(name,address) VALUES ("%s","%s")',% ('thavasi','mumbai'))
Comments
try this:
a1 = row[0]
a2 = row[1]
valores = (a1, a2)
cursor2.execute('INSERT INTO [CENTRAL_ALARM].[dbo].[EEE](E3TimeStamp, CaptacaoSemAgua) VALUES (?,?)', valores)
Comments
string formatting done here is wrong. Try this
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai'))
Look into http://www.diveintopython.net/native_data_types/formatting_strings.html
Comments
If are using placeholders in a string, you need to use % operator in favor of using a parameterlist with ,. so your call should look like:
cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai'))