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
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))
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.
"""SQL"""