#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    0
    Rep 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.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Location
    San Francisco, CA
    Posts
    0
    Rep 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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    21
    Rep Power
    0

    Lightbulb 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
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    9
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo