#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    74
    Rep Power
    12

    Insert problems with Python


    I am trying to insert data into a table from a python script. I can perform a SELECT, but not an INSERT. The rowcount of the cursor shows a value of 1 after my insert, but the actual entry is not added to the table. What is going on???

    cursor1 = db.cursor()
    cursor1.execute("""INSERT into xyz (a,b,c) VALUES (1,'hello','goodbye')""")

    I have also tried:

    cursor1 = db.cursor()
    cursor1.execute("INSERT into xyz (a,b,c) VALUES (%s,%s,%S)", (1,'hello','goodbye') )

    I have also done some other variations of this by replacing the comma with a '%' and using ' around the %s, but these didn't work either. I can't seem to find any good documentation for any help. All I want to do is a simple insert.

    thanks for any help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,279
    Rep Power
    4279
    your mysql statement looks okay

    moving thread to python forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    70
    Rep Power
    10
    Hey, i want to know this one too.
    i am working with the same stuff.
  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Hey, it might help us if we had a little more info i.e. what database your using and what versions of Python, what OS etc.

    Remember that if your using SQLite you have to call commit() before closing the connection . Either that or set up autocommit at connection time.

    Take care,

    Mark.
    programming language development: www.netytan.com Hula

  8. #5
  9. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Try doing the query directly in your database first. This should tell you if you've got a syntax error or not. If it works there, then you've got a problem connecting to the database.

    --Simon
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    74
    Rep Power
    12

    Solution


    Sorry for the delay I was out of town a few days.

    I am using MySQL. I looked up in the spec and found out that cursors aren't supported well for MySQL and saw another method of performing my queries.

    rather than,
    cursor.execute(query_string)

    I do,

    db.query(query_string)

    I am now importing _mysql rather than MySQLdb. When using MySQLdb and using cursors it worked fine for the select commands, but not the update or insert commands. Using _mysql with db.query works evertime. So here is my sequence:


    import _mysql

    db = _mysql.connect("blablabla")
    query_txt = "select * from users"
    db.query(query_txt)
    result = db.store_result()
    row = result.fetch_row()

IMN logo majestic logo threadwatch logo seochat tools logo