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!
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})
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
