0

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

Improve this question
4
  • 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). Commented Mar 28, 2019 at 5: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 Commented Mar 28, 2019 at 5: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). Commented Mar 28, 2019 at 6:55
  • 2
    Possible duplicate of sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied Commented Mar 28, 2019 at 6:57

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Justin, eventually i was missing that comma in set
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

Improve this answer

4 Comments

What is wrong here? sql = """INSERT INTO test (num) VALUES (%s)""" val = [('Peter'),('Amy'),('Hannah')] mycursor.executemany(sql, val)
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.
i tried executing """INSERT INTO test (num) VALUES (%s)""" val = [('Peter'),('Amy'),('Hannah')] mycursor.executemany(sql, val) in Python3 and it worked fine.
It's quite possibly that executemany is more flexible and deals with single values as if they were single value tuples.
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))
Improve this answer

2 Comments

yeah it is working that way but i want to user executemany() as i have a large dataset and this can't be used
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.