CODEWITHSUNDEEP
pythonsqlmysql
Unai Rodriguez
Unai Rodriguez

Reputation: 31

Issues updating a MySQL table using Python's MySQLdb

I am trying to UPDATE a MySQL table using Python's MySQLdb module. Although the query seems fairly simple it just won't update the information. Here is my code:

for username,info in users.iteritems():
  if info[0] > 0 and info[1] > 0:
    month = 8
    year = 2010
    cursor.execute("""
        UPDATE users_disk
        SET
            bytes = %s,
            quota_in_mb = %s
        WHERE
            username = %s AND
            month = %s AND
            year = %s
        """, (info[0], info[1], username, month, year))
    print "Username: %s; Rowcount: %d" % (username, cursor.rowcount)

The output looks like this:

Username: niu666; Rowcount: 0
Username: antuan; Rowcount: 0
Username: tuyo; Rowcount: 0
Username: angela; Rowcount: 0
Username: felipe; Rowcount: 0
Username: Meni; Rowcount: 0
Username: tronco; Rowcount: 0
Username: queque; Rowcount: 0
Username: cerel; Rowcount: 0

Which means none of the rows have been updated! The table has the following contents:

mysql> select * from users_disk;
+----+----------+-------+------+---------+-------------+
| id | username | month | year | bytes   | quota_in_mb |
+----+----------+-------+------+---------+-------------+
|  1 | niu666   |     8 | 2010 |   28672 |     1024000 | 
|  2 | antuan   |     8 | 2010 |   77824 |     4608000 | 
|  3 | tuyo     |     8 | 2010 |   28672 |     1024000 | 
|  4 | angela   |     8 | 2010 |   45056 |     2048000 | 
|  5 | felipe   |     8 | 2010 |   53248 |      307200 | 
|  6 | Meni     |     8 | 2010 |   86016 |     4096000 | 
|  7 | tronco   |     8 | 2010 | 3067904 |     1024000 | 
|  8 | queque   |     8 | 2010 |   61440 |     4608000 | 
|  9 | cerel    |     8 | 2010 |  110592 |     5632000 | 
+----+----------+-------+------+---------+-------------+
9 rows in set (0.00 sec)

And users is a dictionary with the following contents:

{'niu666': (28672, 1024000), 'tutk': (-1, -1), 'antuan': (77824, 4608000), 'tolin': (-1, -1), 'tuyo': (28672, 1024000), 'angela': (45056, 2048000), 'felipe': (53248, 307200), 'Meni': (86016, 4096000), 'tronco': (3067904, 1024000), 'queque': (61440, 4608000), 'cerel': (110592, 5632000), 'carok': (-1, -1), 'niu': (-1, -1)}

I think the issue might be related to username because if I remove it the update works. But of course I need to use it...

Any pointers/recommendations will be highly appreciated.

Thank you so much,

Unai Rodriguez

-------------------------- update -------------------------

Guys, I am using the following "ugly" workaround... that works:

for username,info in users.iteritems():
    if info[0] > 0 and info[1] > 0:
        # The user has positive values, its valid!
        cursor.execute("DELETE FROM " + dbtable + " WHERE username = %s AND month = %s AND year = %s", \
            (username, month, year))
        cursor.execute("INSERT INTO " + dbtable + " (id, username, month, year, bytes, quota_in_mb) VALUES (NULL, %s, %s, %s, %s, %s)", \
                                            (username, month, year, info[0], info[1]))

Still I am interested in knowing what is wrong with the UPDATE (first implementation). I will leave the script like that for now. Thank you so much.

Upvotes: 3

Views: 4109

Answers (4)

SVK
SVK

Reputation: 1034

I figured it out that for some reason python doesn't read %s. So use ? instead of %S in you SQL Code.

And finally this worked for me.

  cur.execute ("update tablename set columnName = (?) where ID = (?) ",("test4","4"))

Upvotes: 0

Jacob Bridges
Jacob Bridges

Reputation: 745

This answer may be a little late, but for posterity's sake:

After your cursor.execute( <SQL_HERE> ), be sure to call connection.commit().

A proper method:

import MySQLbd
from contextlib import closing

connection = MySQLdb.connect( host="localhost",
                              user="root",
                              passwd="root",
                              db="tableName")

with closing( connection.cursor() ) as cursor:
    try:
        cursor.execute( "SQL UPDATE CODE" )
        connection.commit()
    except:
        connection.rollback()

Upvotes: 1

Jim Sjoo
Jim Sjoo

Reputation: 326

Did you try COMMIT command after update as following?

cursor.execute("UPDATE animals SET species=%s WHERE name=%s",('TEST', 'Rollo'))

cursor.connection.commit();

Upvotes: 6

Manoj Govindan
Manoj Govindan

Reputation: 74795

Can you check if username in table and username from query have same length? If there is any padding (with space character) in table it may not show up in the SQL command prompt and will cause the update to match zero rows.

Also can you pass in the primary key of the table (id) along with the year and month rather than username?

Upvotes: 0

Related Questions