insert data into MSSQL server using python

Question

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?

Dsw Wds · Accepted Answer · 2016-04-22 03:29:59Z

12

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

use ? instead of % in pyodbc module

edited Apr 22, 2016 at 3:29

answered Mar 15, 2016 at 9:11

Dsw Wds

5025 silver badges17 bronze badges

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

Comments

IvanS95 · Accepted Answer · 2018-12-12 15:25:53Z

6

This worked for me.

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

edited Dec 12, 2018 at 15:25

IvanS95

5,8028 gold badges35 silver badges74 bronze badges

answered Dec 12, 2018 at 13:54

PKInd007

3882 silver badges7 bronze badges

Comments

Sergej Steinhauer · Accepted Answer · 2019-09-18 13:32:11Z

3

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

answered Sep 18, 2019 at 13:32

Sergej Steinhauer

312 bronze badges

Comments

Shruti Srivastava · Accepted Answer · 2015-06-02 08:27:22Z

0

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

edited Jun 2, 2015 at 8:27

answered Jun 2, 2015 at 7:43

Shruti Srivastava

4081 gold badge7 silver badges26 bronze badges

Comments

Paula · Accepted Answer · 2018-10-10 15:18:05Z

0

try this:

a1 = row[0]
a2 = row[1]
valores = (a1, a2)

cursor2.execute('INSERT INTO [CENTRAL_ALARM].[dbo].[EEE](E3TimeStamp, CaptacaoSemAgua) VALUES (?,?)', valores)

answered Oct 10, 2018 at 15:18

Paula

1

Comments

itzMEonTV · Accepted Answer · 2015-06-02 07:14:49Z

-1

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

answered Jun 2, 2015 at 7:14

itzMEonTV

20.4k4 gold badges44 silver badges53 bronze badges

Comments

frlan · Accepted Answer · 2015-06-02 07:36:11Z

-1

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

edited Jun 2, 2015 at 7:36

answered Jun 2, 2015 at 7:15

frlan

7,2953 gold badges36 silver badges72 bronze badges

Collectives™ on Stack Overflow

insert data into MSSQL server using python

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related