Update existing sql database

Question

I have all my value in a form of list of lists my_list = [[...][...]etc.]

I set up a function to update some values in the existing database

def sql_update(my_list_int): #my_list_int is an integer value that would call the list that I need
    db = MySQLdb.connect(host = 'my_host', user = 'my_user', 
                         passw='my_pass', dd='my_db')

    sql = """UPDATE my_database SET col2 = %s WHERE col1 = %s"""

    cursor.execute(sql, (my_list[%s][2], my_list[%s][1] )) 

    db.commit()
    cursor.close()

Everything runs when my_list_int = 0 or1or2. But for any other value I get

OperationalError: (1205, 'Lock wait timeout exceeded')

When I run it again immediately with no changes I get

IntegrityError: (1062, "Duplicate entry")

I can't figure out where is my mistake

Eventually I will but for now I just run it as a single line like `sql_update(1)`. And for 0,1,2 it works — AK9309, Nov 11 '15 at 17:56
You should probably debug the problem: http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded. — alecxe, Nov 11 '15 at 17:57
Run your sql query directly on mysql (thourgh phpmyadmin), I think you're updating too many records, execute a select with the same condition and see how many records will be returned — Serjik, Nov 11 '15 at 18:05
I am updating 1 record. In fact I have a similar function that loaded everything into mysql originally, and it worked without problems. — AK9309, Nov 11 '15 at 18:08
For example `my_list[1][2] = 'Hello'` .I don't think that it matters what are the values. All the lists that are in `my_list` have the same length and structure. The code works perfectly for first 3 lists, but crashes after. The only difference is that `len(my_list[1][0]) = 1` and `len(my_list[1][0]) = 2`. Essentially it is 'B' vs 'BB', but I don't see how that can cause a problem — AK9309, Nov 11 '15 at 18:27

Update existing sql database

0 Answers0