Insert data into MSSQL server using python

Question

I am trying to insert data into sql server table using the 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() 

It gives me the following error on execution,

  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 checked the syntax for the insert statement correctly. So what causes this error?

Answer 1

 cursor.execute("INSERT INTO mytable(name,address) VALUES (?,?)",('thavasi','mumbai')) 

use ? instead of % in pyobbc module

Answer 2

It worked for me.

 cursor.execute("INSERT INTO tablename(field1,field2) VALUES(?,?) ", (data1,data2)) 

Answer 3

You forgot the % sign before the values, and %s must be in quotation marks

try the following:

cursor.execute ('INSERT INTO mytable (name, address) VALUES ("% s", "% s")',% ('thavasi', 'mumbai'))

Answer 4

try it:

 a1 = row[0] a2 = row[1] valores = (a1, a2) cursor2.execute('INSERT INTO [CENTRAL_ALARM].[dbo].[EEE](E3TimeStamp, CaptacaoSemAgua) VALUES (?,?)', valores) 

Answer 5

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() 

Answer 6

string Formatting is incorrect here. try it

 cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai')) 

Take a look at http://www.diveintopython.net/native_data_types/formatting_strings.html

Answer 7

If you use placeholders in a string, you need to use the% operator in favor of using the list parameter c. so your call should look like this:

  cursor.execute("INSERT INTO mytable(name,address) VALUES (%s,%s)" %('thavasi','mumbai'))