JonathanHayward JonathanHayward - 6 months ago 18
SQL Question

Why can my Python MySQLdb script see, but not make live changes to, my database?

I am working with a customized Django installation, although the issue I am dealing with is from a command line tool intended to update the database. The tool reads the database as intended and functions comparably to typing SELECTs in MySQL's command line. However, it seems never to change the database. I tried a few variations, including a trial run of just

execute()
ing the literal query I want for trial purposes, but it doesn't do anything, failing without reported exception or error. The MySQL command line handles updates predictably well.

My code, both including the read-oriented code that works and the write-oriented code that silently fails, is (in sanitized form):

#!/usr/bin/python

import MySQLdb
import sys

connection = MySQLdb.connect(user='root', passwd="_______________")
cursor = connection.cursor()
cursor.execute('USE _____________')

if __name__ == '__main__':
if sys.argv[1] == 'check': # This seems to work
username = sys.argv[2]
cursor.execute(
'''SELECT username, foo FROM auth_user WHERE username = %s''',
[username])
for result in cursor:
print 'Username: ' + result[0]
print 'Foo: ' + str(result[1])
if sys.argv[1] == 'set': # This seems to function as a noop
username = sys.argv[2]
foo = int(sys.argv[3])
cursor.execute('UPDATE auth_user SET foo = ' + str(foo) +
" WHERE username = '" + username.replace("'", "\\'") + "'")


What other steps should I be doing to update the field foo on the given row? The update query is deliberately dumbed-down SQL, but I don't want to focus on sophistication before I even have the literal version working.

Thanks,

Answer

You need to commit data

    cursor.execute('UPDATE auth_user SET foo = ' + str(foo) + " WHERE username = '" + username.replace("'", "\\'") + "'")
    connection.commit()

Take a look at documentation to get more information