CODEWITHSUNDEEP
pythonmysql
wizzer
wizzer

Reputation: 7

Passing special characters into mysql using python

i am trying to pass variables from a list into a mysql table using python. I will get so far down the list untill i receive an eror about a special character. The line of code i am using is:

         new_value = "INSERT INTO favs VALUES ( null, '%s', '%s', '%s', '%s')" % (item1,item2,item3,item4).

The first item is the problem,as it has numerous variables with apostrophies in them. Thanks

Upvotes: 1

Views: 643

Answers (2)

Jesse Pardue
Jesse Pardue

Reputation: 190

In addition to the solution above, the escape character in mysql is the backtick ie. `.

So if you wrote this:

new_value = "INSERT INTO favs VALUES ( null, '%s', '%s', '%s', '%s')" % (item1,item2,item3,item4)

Change it to this:

new_value = "INSERT INTO favs VALUES ( null, `%s`, `%s`, `%s`, `%s`)" % (item1,item2,item3,item4)

That's the quick "hack". The better way is to parameterize it as stated above; but for all intents and purposes, this will work. The accepted usage is that backticks are used on column and table names. So if someone names a column key, which is a reserved keyword, you can escape it.

Upvotes: 0

alecxe
alecxe

Reputation: 474271

Let the database driver worry about it and parameterize your query:

query = """
    INSERT INTO 
        favs 
    VALUES 
        (null, %s, %s, %s, %s)
""" 
cursor.execute(query, (item1, item2, item3, item4))

Upvotes: 2

Related Questions