mysql.connector.errors.ProgrammingError: 1064 (4200): You have an error in your SQL syntax;

Question

I am attempting to insert data into a MySQL database. I am using python 2.7 and I am using the mysql.connector.

My error is:

mysql.connector.errors.ProgrammingError: 1064 (4200): 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.

This indicates a error in my code where I attempt to insert my variable np (VALUES (%s)");). np is a "noun-phrase" such as a "skate board".

import mysql.connector
from textblob import TextBlob

cnx = mysql.connector.connect(user='XXX', password='XXX',
                              host='XXXXX',
                              database='XXXX')

cursor = cnx.cursor(buffered=True)

Latest = ("SELECT * FROM SentAnalysis")
cursor.execute(Latest)

for row in cursor.fetchall():
    SentText = row[2]
    blob = TextBlob(SentText)
    for np in blob.noun_phrases:
        print(np)
        SQLInsertCmd = ("INSERT INTO TestNounPhrase (NPhrase) VALUES (%s)")
        cursor.execute(SQLInsertCmd,np)

cnx.commit()
cursor.close()
cnx.close()

The example from the manual is https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html. e.g.

 "VALUES (%s, %s, %s, %s, %s)")

I can't see a difference. This error is also discussed in detail here : How can I fix MySQL error #1064? Other similar examples on stackoverflow have been linked to reserved words, Redundant comas .But looking at these examples I can't spot an obvious error.

Any suggestions on where I am going wrong would be much appreciated.

If you only have a single entry for `VALUES` I believe you need a comma in your tuple: `VALUES (%s,)`. — andrew_reece, Aug 13 '17 at 23:47
I updated to include a comma and I still get the same error. — Steve, Aug 13 '17 at 23:50
I speculate that the issue is at least partly due to that you are trying to select and insert at the same time. Question upvoted +1. — Tim Biegeleisen, Aug 14 '17 at 01:28
Is there a way to avoid a select in this scenario? The select currently extracts the information required to run the "Noun Phrase" analysis . The output of this analysis is then inserted into another table. I have used select and inserts in the same script before without issue e.g. selecting data-> running sentiment analysis on the data-> then inserting the sentiment results into a new table. — Steve, Aug 14 '17 at 01:52
The correct way to fix it is to convert the tuple which contains the value to insert into a 'full tuple'. e.g.: — Hernán Borré, May 13 '19 at 21:01

score 8 · Answer 1 · answered Aug 13 '19 at 19:25

8

I think the issue here might be the semicolon at the end of the query.

Have a good day.

answered Aug 13 '19 at 19:25

abhishek kumar

339
3
11

Struggled for hours, thought it was the prepared statement issue. But this was the problem. – Vipin Mohan Oct 30 '19 at 08:02

Vik Kutenkov · Answer 2 · 2019-10-07T15:48:11.217

8

str parameter in SQL query must by in quote 'str'.
So, need use '%s' with ' ' for str, and %s without ' ' for numbers:

cursor.execute("""INSERT INTO db_name (str,int,str,int)
                  VALUES ('%s', %s, '%s', %s)""" % (str,int,str,int))
cnx.commit()

edited Oct 07 '19 at 15:48

answered Oct 07 '19 at 15:34

Vik Kutenkov

89
1
5

score 3 · Answer 3 · answered May 13 '19 at 21:04

3

You have to convert the tuple which contains the value to insert into a 'full tuple'. e.g.:

for np in blob.noun_phrases:
        np=(np,)

In a generic example, if you just have one column to insert, it would be:

to_insert=('A value to insert',)

Lastly, if you had multiple values to insert at a time:

to_insert_multiple=[('value1',), ('value2',), ('valueN',)]

I hope it helps, it worked for me in py3.7

answered May 13 '19 at 21:04

Hernán Borré

391
1
5

This is the correct answer - the _values_ argument to `cursor.execute` must be a _tuple_, and string formatting / concatenation solutions should be avoided due to the risk of quoting errors and SQL injection. – snakecharmerb Jun 25 '21 at 17:34

pritesh · Answer 4 · 2017-08-14T00:57:16.720

2

Try this

SQLInsertCmd = """INSERT INTO
                  TestNounPhrase (NPhrase) VALUES ((%s))"""  % (np)
cursor.execute(SQLInsertCmd)

edited Aug 14 '17 at 00:57

answered Aug 14 '17 at 00:11

pritesh

193
1
4
18

This generates a new error that I can't find much information (in relation to 'word"): "mysql.connector.errors.ProgrammingError. Failed processing format-parameters; Python 'word' cannot be converted to a MySQL type". – Steve Aug 14 '17 at 00:33
What if you try, SQLInsertCmd = ("INSERT INTO TestNounPhrase (NPhrase) VALUES ((%s))") cursor.execute(SQLInsertCmd,[np]) – pritesh Aug 14 '17 at 00:39
okay, not the most pythonic way but this ought to fix it, SQLInsertCmd = """INSERT INTO TestNounPhrase (NPhrase) VALUES ((%s))""" % (np) cursor.execute(SQLInsertCmd) – pritesh Aug 14 '17 at 00:54
Error again...It still references a 1064 (42000) error, but rather than saying "near '%s" it says "near 'seymour'". 'seymour' is the result of the first iteration of the loop (for np in blob.noun_phrases:) that extracts the "noun phrase" from the column "SentText". – Steve Aug 14 '17 at 01:18
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151847/discussion-between-pritesh-and-steve). – pritesh Aug 14 '17 at 01:25
full error: 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 'seymour))' at line 1 – Steve Aug 14 '17 at 01:29

Baka_coder · Answer 5 · 2022-10-03T22:35:22.070

I'm a little late for this post but here is my solution for this. I'm using py 3.10 I think this error comes because of using/passing string to cursor, to avoid this we can use tuple as pointed out by some answers above.

    from mysql import connector
    cnx = connector.connect(**config)
    cursor = cnx.cursor(buffered=True)
    YOUR_VALUES = (FIELD_NAME_1,FIELD_NAME_2,...)
    query = f"INSERT INTO `TABLENAME` (`FIELD_NAME_1`, `FIELD_NAME_2`) VALUES 
            {VAL}"
    cursor.execute(query)
    cnx.commit()
    cnx.close()

score 0 · Answer 6 · answered Mar 16 '20 at 05:32

0

I had the same issue with the single parameter statement, this worked:

mycursor.execute("INSERT INTO cust (user) VALUES(%s)" % (username))

answered Mar 16 '20 at 05:32

MVB76

149
7

score 0 · Answer 7 · answered Jun 14 '20 at 13:37

I was getting same error and while searching for its solution I stumbled upon this question after several attempts I was able to resolve. The mistake was a typing error for column name in WHERE clause of UPDATE statement. My column name was 'ROLL_NO' instead I had typed 'ROLL_NO.' Please look to this point as well.

score 0 · Answer 8 · edited Nov 16 '21 at 16:59

0

when calling the execute() method:

  cursor.execute(query, (param))

should be:

  cursor.execute(query, (param,))

edited Nov 16 '21 at 16:59

j__carlson

1,346
3
12
20

answered Nov 15 '21 at 23:08

Frank Lin

1

score 0 · Answer 9 · answered Mar 15 '22 at 09:09

0

I had a quite a struggle with this error because one of the column-name was exit. Be aware that database will accept this as a column name but when you try to insert from python, you need to cover the column in with backticks `

answered Mar 15 '22 at 09:09

Ondra

1
1
4

score 0 · Answer 10 · answered Jun 29 '22 at 16:51

you should give %s value like this:

"INSERT INTO LaptopPrice (Name) VALUES ('%s')" % value

if you have few values you have to use tuple of your valuse like this code: "INSERT INTO LaptopPrice (Name, Price, Size, Weight, ScreenSize, Resolution, CPU, RAM, RAMType, Memory, MemoryType, GraphicCard) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % tuple(values)

score -2 · Answer 11 · answered Mar 25 '20 at 18:58

-2

mycursor.execute("insert into table_name(column) values(%s)"%data_variable)

answered Mar 25 '20 at 18:58

K.BHUPENDER

9

1

I doubt that this helps or even works at all. To convince me otherwise please explain how this works and why it is supposed to solve the problem. – Yunnosch Mar 25 '20 at 19:11

mysql.connector.errors.ProgrammingError: 1064 (4200): You have an error in your SQL syntax;

11 Answers11

Linked