CODEWITHSUNDEEP
pythonmysql
Steve
Steve

Reputation: 505

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

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.

Upvotes: 8

Views: 73139

Answers (11)

Baka_coder
Baka_coder

Reputation: 11

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

Upvotes: 1

Mohammad Sadeghizad
Mohammad Sadeghizad

Reputation: 1

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)

Upvotes: 0

Ondra
Ondra

Reputation: 1

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 `

Upvotes: 0

Frank Lin
Frank Lin

Reputation: 1

when calling the execute() method:

  cursor.execute(query, (param))

should be:

  cursor.execute(query, (param,))

Upvotes: 0

Yastika Kumar
Yastika Kumar

Reputation: 39

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.

Upvotes: 0

K.BHUPENDER
K.BHUPENDER

Reputation: 9

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

Upvotes: -2

MVB76
MVB76

Reputation: 159

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

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

Upvotes: 0

Vik Kutenkov
Vik Kutenkov

Reputation: 89

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

Upvotes: 8

abhishek kumar
abhishek kumar

Reputation: 379

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

Have a good day.

Upvotes: 8

Hernán Borré
Hernán Borré

Reputation: 411

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

Upvotes: 3

pritesh
pritesh

Reputation: 203

Try this 

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

Upvotes: 2

Related Questions