Escaping characters in SQL string for pypyodbc

Question

Suppose I have my_table in SQL Server like this:

  CREATE TABLE my_table
  (col1 VARCHAR(100),
  col2 VARCHAR(100),
  col3 INT)

I tried to insert a few records to that table using Python 3.5 and pypyodbc library as shown below:

import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};' 'Server=12.3.456.789.0123;' 'Database=mydb;' 'uid=me;pwd=mypwd')
cursor= connection.cursor()
data = [{'p1': "I'm hungry", 'p2': "text for col2", 'p3': '1234'}]
for dd in data:
    insert_sql = "INSERT INTO my_table (col1,col2,col3) VALUES (%s, %s, %s)"
    cursor.execute(insert_sql, (dd['p1'], dd['p2'], dd['p3']))

But when the above code is run, it returns:

pypyodbc.ProgrammingError: ('HY000', 'The SQL contains 0 parameter markers, but 3 parameters were supplied')

I don't know what I'm doing wrong. If anyone could help me resolve this, I'd greatly appreciate the help!

Answer 1

The issue here is you are using %s placeholder like in python or other programming language. However pypyodbc uses ? as placeholder (parameter marker). Simply replace %s with ?, your code should work fine.

import pypyodbc
connection = pypyodbc.connect('Driver={SQL Server};' 'Server=12.3.456.789.0123;' 'Database=mydb;' 'uid=BenchmarkingUS;pwd=mypwd')
cursor= connection.cursor()
data = [{'p1': "I'm hungry", 'p2': "text for col2", 'p3': 1234}]
for dd in data:
    insert_sql = "INSERT INTO phyo_test (col1,col2,col3) VALUES (?, ?, ?)"
    cursor.execute(insert_sql, (dd['p1'], dd['p2'], dd['p3']))
cursor.commit()

Note: You are setting value of p3 as string '1234' which wont give errors. But its better to change it to int 1234 just to prevent data type errors.