Mysql insert not working in python with %s

Question

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

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 https://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, Mar 28 '19 at 05:40
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, Mar 28 '19 at 05:41
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, Mar 28 '19 at 06:55
Possible duplicate of [sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied](https://stackoverflow.com/questions/16856647/sqlite3-programmingerror-incorrect-number-of-bindings-supplied-the-current-sta) — Dima Tisnek, Mar 28 '19 at 06:57

score 2 · Answer 1 · answered Mar 28 '19 at 05:52

2

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

answered Mar 28 '19 at 05:52

1

Thanks Justin, eventually i was missing that comma in set – Tinkeshwar Singh Mar 28 '19 at 05:57

Marlon Abeykoon · Answer 2 · 2019-03-28T05:57:22.787

1

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

edited Mar 28 '19 at 05:57

answered Mar 28 '19 at 05:42

Marlon Abeykoon

11,927
4
54
75

yeah it is working that way but i want to user executemany() as i have a large dataset and this can't be used – Tinkeshwar Singh Mar 28 '19 at 05:48
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ("John", "Highway 21") mycursor.execute(sql, val) why my code is not working this way? source: w3school – Tinkeshwar Singh Mar 28 '19 at 05:50

score 1 · Accepted Answer · answered Mar 28 '19 at 06:00

1

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

answered Mar 28 '19 at 06:00

Grismar

27,561
4
31
54

What is wrong here? sql = """INSERT INTO test (num) VALUES (%s)""" val = [('Peter'),('Amy'),('Hannah')] mycursor.executemany(sql, val) – Tinkeshwar Singh Mar 28 '19 at 08:44
Your problem there is that `('Peter')` is not a tuple, but just a string in parentheses. Python only understands an expression in parentheses to be a tuple if it includes commas. So, `sql = """INSERT INTO test (num) VALUES (%s)""" val = [('Peter',),('Amy',),('Hannah',)] mycursor.executemany(sql, val)` - it's an annoying gotcha, where we're paying for brevity with clarity. – Grismar Mar 29 '19 at 03:22
1

i tried executing """INSERT INTO test (num) VALUES (%s)""" val = [('Peter'),('Amy'),('Hannah')] mycursor.executemany(sql, val) in Python3 and it worked fine. – Tinkeshwar Singh Apr 02 '19 at 12:12
It's quite possibly that `executemany` is more flexible and deals with single values as if they were single value tuples. – Grismar Apr 03 '19 at 00:19

Mysql insert not working in python with %s

3 Answers3