Inserting user entered variables into dbase

Ok Im using python and mysql
Im using python to collect information frm a website that is stored as two strings called message and email


cursor.execute("""INSERT INTO stories (num,story,email) VALUES (NULL,"my message", "my email") """)

the above line works but when I try to do this

story = "This is my story"
email = "this is my email"
cursor.execute("""INSERT INTO stories (num,story,email) VALUES (NULL,story,email) """)

that doesn't work and gives me vague errors.
Any help is appreciated.

1) use %s substitution operators ... (NULL,%s,%s) """ %(story,email)

2) use variables sql = ("""insert ... (NULL,:story,:email) """)

cursor.execute(sql,story = story, email = email)

3) use + as concatenation operator ... (NULL """ + story + "," + email + ")") [seems like the least attractive alternative

i am a newbie in python but in my opinion (if it's right) we could send our sql code to a variable and than we call this variable as a sql command, like...


#himm, sql command could be appear
#good in such a variable ;)
my_sql_cmd = "insert stories (num,",story,email,") values (NULL,'my message', 'my email')"

#let's execute it
cursor.execute(my_sql_cmd)


at the end i am a newbie as i mentioned above, so if you could find any mistake in the code sorry for it...
also it could be perfect when you correct these mistakes too

After a couple hours on irc I was suggested a couple ways of inserted data in an easy to read way. The problem I was having was that one of my variables stored data from a large text field, and contained endlines and the ' character, which was making things difficult.

Combining the suggestions together, I ended up liking this method the best... create text for the query, and a tuple for the results. The benefit of this is that you can re-use the query, and define a new tuple if you want to insert different data. I think this ends up being the most flexible and easy to read. Anyway, enough blabbering, here is some code:



Notice the asterisk before queryArgs, since queryArgs is a tuple (just a sequence of values), and the asterisk indicates to pass the values one by one, instead of actually passing a tuple (which postgres or whatever else won't understand).

Hopefully this saves someone alot of head banging like I went through.

The trouble I was having was trying to learn two languages at once... Python and SQL. I couldn't figure out which one the %s's belonged to, etc, bla bla, anyway, just happy to have figured out a nice method.