Mysql insert not working in python with %s

0
votes

Here is my code:

import csv import mysql.connector
mydb = mysql.connector.connect( host="localhost", user="root", passwd="root", database="4g_hs_upgrade" ) 
mycursor = mydb.cursor() 
sql = "INSERT INTO test (num) VALUES %s" 
val = 'John' 
mycursor.execute(sql, (val)) 
mydb.commit()

Here is the error I'm getting :

Traceback (most recent call last): File "csvreader.py", line 13, in mycursor.execute(sql, (val)) File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 507, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 722, in cmd_query result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 640, in _handle_result raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

3
python
You should probably indent and format your code to make it readable. What exactly did you expect to happen? You have %s in the string, but the string isn't followed by a % and an expression to substitute something for the string? See here pyformat.info for a more modern and Pythonic way to do things. And keep in mind that in the current version of python you can also write f'some {x} string' to format it with x, instead of 'some {} string'.format(x). - Grismar
If you trying to use %s to put in some string, use it this way - sql = "INSERT INTO test (num) VALUES %s" % your_variable - eiram_mahera
Use (val, ) to make it a tuple; note that (val) == val because parentheses are interpreted as arithmetic grouping, e.g. 1 + (2 + 3) + 4. On the other hand, (1, ) makes a tuple, just like (1, 2, ) == (1, 2). - Dima Tisnek
Possible duplicate of sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied - Dima Tisnek

3 Answers

2
votes

See below. Note parentheses around the %s in sql. Note params tuple, i.e., trailing comma after the single value.

sql = "INSERT INTO test (num) VALUES (%s)"
val = 'John'
params = (val,)
mycursor.execute(sql, params)

See docs for the MySQLCursor.execute() Method

1
votes

Your use of %s in the query string won't work as expected, you could follow it with % replacement_string, but it appears you want to executemany() and have MySQL do the replacement on the fly. This is the safer and better way to do this, so you are on the correct path.

The correct syntax:

import csv 
import mysql.connector

mydb = mysql.connector.connect(host="localhost", user="root", passwd="root", database="4g_hs_upgrade") 
mycursor = mydb.cursor() 
sql = "INSERT INTO test (num) VALUES (%s)" 
val = 'John' 
mycursor.execute(sql, (val)) 
mydb.commit()

Note that the value 'John' doesn't sound very logical for a column named num, but I'm merely following your example.

You were missing the parentheses, which caused confusion because it suggested you were looking to use the Python %s string replacement functionality, while you were probably after this: https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

1
votes
    mydb = mysql.connector.connect( host="localhost", user="root", passwd="root", database="4g_hs_upgrade" ) 
    mycursor = mydb.cursor() 
    sql = "INSERT INTO test (num) VALUES %s" % ('John',) 
    mycursor.execute(sql, (val)) 
    mydb.commit()

You must do it like this, % ('John',)

If you have a list of values you may do it like this,

values = ['John', 'address1']
sql = "INSERT INTO test (num, address) VALUES (%s)" % (','.join(values))