Python SQL Update Syntax Issues

Question

Hello I was following Python MYSQL update statement and managed to produce this code for my programme's SQL Update with variables function:

def editInfo(start, userName):
    newFavGenre = input("Enter your favourite genre")
    newFavArtist = input("Enter your favourite artist")
    ## userName is a global variable
    con = lite.connect(db)
    cur = con.cursor()
    cur.execute ("""
    UPDATE users
    SET favGenre=%s, favArtist=%s
    WHERE username=%s
""", (newFavGenre, newFavArtist, userName))
    results = cur.fetchall()
    return result
    mainmenu()

And keep expericiencing this error code:

sqlite3.OperationalError: near "%": syntax error

Any ideas where I am going wrong?

Skam · Accepted Answer · 2018-03-31 23:46:43Z

2

It appears the post you are looking at is for MySQL and I conjecture that you're using the sqlite3 python interface based on your error.

Looking at the sqlite3 docs...

cur.execute("""
    UPDATE users
    SET favGenre=%s, favArtist=%s
    WHERE username=%s
""", (newFavGenre, newFavArtist, userName))

Should instead be

cur.execute("""
    UPDATE users
    SET favGenre=?, favArtist=?
    WHERE username=?
    """, (newFavGenre, newFavArtist, userName))

You could also use their named style which instead of taking a tuple takes a dictionary.

cur.execute("""
    UPDATE users
    SET favGenre=:genre, favArtist=:artist
    WHERE username=:username
""", {'genre':newFavGenre, 'artist': newFavArtist, 'username':userName})

edited Mar 31, 2018 at 23:46

answered Mar 31, 2018 at 23:36

Skam

7,8985 gold badges25 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Python SQL Update Syntax Issues

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related