Inserting user entered variables into dbase

Dev Shed Forums Sponsor:

May 14th, 2002, 01:44 AM

PringleB

Junior Member

Join Date: May 2002

Posts: 0

Time spent in forums: < 1 sec
Reputation Power: 0

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.

May 14th, 2002, 02:51 PM

georoad

Junior Member

Join Date: May 2002

Location: San Francisco, CA

Posts: 0

Time spent in forums: < 1 sec
Reputation Power: 0

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

May 26th, 2002, 03:59 AM

knt

Registered User

Join Date: May 2002

Posts: 21

Time spent in forums: 35 m 48 sec
Reputation Power: 0

using variables

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

June 21st, 2003, 11:21 AM

red_over_blue

Registered User

Join Date: Jun 2003

Posts: 9

Time spent in forums: 10 m 28 sec
Reputation Power: 0

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:

Code:

name = "Freddy"
address = "Elm Street"

queryText = "INSERT INTO testtable(personsname,personsaddress) values(%s,%s)"
queryArgs = (name,address)
cursor.execute(queryText,*queryArgs)

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.

Inserting user entered variables into dbase

Developer Shed Advertisers and Affiliates