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.
Try three things
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
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
June 21st, 2003, 12:21 PM
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).
name = "Freddy"
address = "Elm Street"
queryText = "INSERT INTO testtable(personsname,personsaddress) values(%s,%s)"
queryArgs = (name,address)
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.