Python - MySQL Connector syntax error

Question

This is probably something really simple, but I am going crazy trying to figure out what I am doing wrong here. My goal: perform a simple INSERT operation using Connector/Python to communicate with my MySQL database. My code:

def insert(title, artist, img):

    conn = mysql.connector.connect(***credentials***)
    cursor = conn.cursor()

    title = title.lower().replace(' ', '_')
    path = 'img/' + title + '.png'
    sp.misc.imsave(path, img)

    add_img = ("INSERT INTO ref "
            "(song_title, artist, path) "
            "VALUES (%s, %s, %s)")

    img_data = (title, artist, path)

    # execute and commit 
    cursor.execute(add_img, img_data)
    conn.commit()

    cursor.close()
    conn.close()

This: insert("Back in Black Live Mono", "ACDC", img) Gives me:

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 'references (song_title, artist, path) VALUES ('Back in'
Black Live Mono', 'ACDC',' at line 1`

The image saves successfully, but the INSERT statement is never executed. Any ideas? Thank you.

Now that you've changed the code, please edit the question to include the new error message this returns. There's no way your updated code can report an error about references when it no longer contains this word. — Luke Woodward
– Luke Woodward, Commented Mar 14, 2016 at 22:09
The weird thing is that it does report an error about references. That MySQL command must be cached or something, I'm going to restart my server and see if that fixes it. — angerboy
– angerboy, Commented Mar 14, 2016 at 22:11
Any luck? I'd be surprised if MySQL was caching that query somewhere. I suspect it's either a Python caching issue (try deleting .pyc files (Python 2) or __pycache__ folders (Python 3)) or you're not running the code you think you are (try adding a line print("test") and see if that turns up in the output). — Luke Woodward
– Luke Woodward, Commented Mar 14, 2016 at 22:48
Sorry about that, quick dinner break. Yes, it worked! Thanks for the insight. — angerboy
– angerboy, Commented Mar 14, 2016 at 23:13

Luke Woodward · Accepted Answer · 2016-03-14 21:59:27Z

1

REFERENCES is a MySQL reserved word. If you want to use it as a table name you must quote it in backticks:

add_img = ("INSERT INTO `references` "
        "(song_title, artist, path) "
        "VALUES (%s, %s, %s)")

answered Mar 14, 2016 at 21:59

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

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

2 Comments

angerboy Over a year ago

I tried that and I also tried changing the table name to 'ref', and still no dice. That was a good call, but I guess was not the only problem.

angerboy Over a year ago

Nevermind, this was the correct solution. For some reason Python kept my old query and was running that when I tried to run the new query. Thanks!

Collectives™ on Stack Overflow

Python - MySQL Connector syntax error

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related