MySQLdb UPDATE does nothing

Discussion in 'Python' started by John Moore, Sep 15, 2005.

  1. John Moore

    John Moore Guest

    Hi,

    I normally work with Java but I'm interested in using Python as well,
    particularly for little tasks like doing some massaging of data in a
    MySQL database. Below is my first attempt. I'm sure it's inelegantly
    written, but my main concern is that the UPDATE sql doesn't actually
    work, and I can't understand why. No error is returned, it's just that
    the update does not take place. The SQL itself is fine, though - if I
    instead write the SQL to a file I can use it from the mysql command line
    and it does all the updates just fine. What have I missed?

    John

    ===================================



    #!/usr/bin/python

    # import MySQL module
    import MySQLdb

    # connect
    db = MySQLdb.connect(host="localhost", user="john",
    passwd="xxx",db="test_db")

    # create a cursor
    cursor = db.cursor()

    # execute SQL statement
    cursor.execute("SELECT DISTINCT product_id FROM product_attribute")

    # get the resultset as a tuple
    result = cursor.fetchall()

    # iterate through resultset
    for record in result:
    sql="SELECT id FROM product_attribute WHERE product_id =
    "+str(record[0])
    print " "+sql
    cursor.execute(sql)
    result2=cursor.fetchall()
    index=0
    for record2 in result2:
    sql="UPDATE product_attribute SET index_column = "+str(index)+"
    WHERE id = "+str(record2[0])
    print " "+sql
    cursor.execute(sql)
    index+=1

    cursor.close()
     
    John Moore, Sep 15, 2005
    #1
    1. Advertisements

  2. John Moore

    David Wilson Guest

    sql="UPDATE product_attribute SET index_column = "+str(index)+" WHERE id = "+str(record2[0])
    To allow the DB-API adaptor to correctly take care of value conversion
    and SQL escaping for you, this should be written as:

    cursor.execute("UPDATE product_attribute SET col1 = %s WHERE id = %s",
    (index, record2[0]))


    As for why the UPDATE has no effect, which version of MySQL are you
    using?


    David.
     
    David Wilson, Sep 15, 2005
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.