Incorrect SQL syntax on Python

Question

I can't find the error in this piece of code:

sql = "INSERT INTO diff (val) VALUES (%s)"
test = '99'
mycursor.execute(sql, test)

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s)' at line 1

Could anyone please explain it to me? Thanks in advance!

Answer 1

It would help if you could describe which database connector you're using, but there's a few things you can try:

If the value is meant to be a string, try wrapping the %s with quotes

"INSERT INTO diff (val) VALUES ('%s')"

You might need to have the second argument to your execute function be a tuple, i.e.

mycursor.execute(sql, (test,))

Your connector may also support keyword arguments

mycursor.execute("INSERT INTO diff (:val)", {'val': '99})

Answer 2

I got the same error on my device. I tested straight SQL with
INSERT INTO diff (val) VALUES ('99') ; which worked.

When I used '' as suggested by https://stackoverflow.com/users/6837068/james-wu the error stopped but the insert was inserting 0's until I changed the column to varchar then it inserted '%s' instead of '99'.

I discovered there is an alternative method of assigning variables which worked.

See https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html for more information.

Example code follows.

#!/usr/bin/env python

import mysql.connector # pip install mysql-connector-python 
from mysql.connector import errorcode

try:
  connection = mysql.connector.connect(user='root', host='localhost',database='stackoverflow_53346780',use_pure=True)
  cursor = connection.cursor()
  #https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html
  sql = "INSERT INTO diff (val) VALUES (%(val)s) ;"
  test = {'val':'99'}
  cursor.execute(sql, test)
  connection.commit()
  cursor.close()
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  connection.close()

Results

select * from diff ;

1   99
4   0
5   0
6   %s
7   99
8   99