mysql.connector for python commit() does not work

Question

I want to convert a date format in a sql table and I have no clue why this is not working:

import mysql.connector
from mysql.connector import errorcode
from dateutil.parser import parse

appname = "dropbox"

# connect to the database
# Add your DB connection information

try:

    database = mysql.connector.connect(user='root', password='root',

                              host='localhost',

                              database='google_play')

except mysql.connector.Error as err:

    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")

    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")

    else:
        print(err)

DBcursor = database.cursor(buffered=True)
DBcursor2 = database.cursor(buffered=True)

# DB connection established. Now get the table:

query = ("SELECT * FROM googleplay_%s_test" % appname)

DBcursor.execute(query)

# Execute the date conversion:

for (id, user_name, date, url, rating, title, text, reply_date, reply_text) in DBcursor:

    date_string = parse(date)
    date_string.strftime("%Y-%m-%d")

    conversion = ("UPDATE googleplay_%s_test SET date='date_string' WHERE id='id'" % appname)

    DBcursor2.execute(conversion)

    database.commit()

    print("Convertet to: ", date_string)

# close the database connection

DBcursor.close()
DBcursor2.close()
database.close()

The conversion seems to work. Output is:

Convertet to:  2016-12-02 00:00:00
Convertet to:  2016-11-25 00:00:00
Convertet to:  2016-11-16 00:00:00
Convertet to:  2016-12-04 00:00:00

which is fine. However, it does not write the new value into the table. First I was thinking that the commit() command is missing, but it's there.

Answer 1

this:

conversion = ("UPDATE googleplay_%s_test SET date='date_string' WHERE id='id'" % appname)
DBcursor2.execute(conversion)

is obviously not going to set googleplay_<whatever>_test to the value of the date_string variable - it will try to set it to the litteral 'date_string' string. Chances are MySQL just silently skip the operation (well, maybe issuing a warning at best) and pretends everything's ok, as often with default MySQL settings.

edit : the same applies for the where clause:

WHERE id='id'

will only try to update records which id is the litteral string 'id'.

You want:

 conversion = "UPDATE googleplay_%s_test SET date=%%s WHERE id=%%s" % appname
DBcursor2.execute(conversion, [date_string, id])

FWIW if you only need two fields, you'd better retrieve those two fields only:

query = "SELECT id, date FROM googleplay_%s_test" % appname
DBcursor.execute(query)

for id, date in DBcursor:
    # code here

and while we're at it:

1. cursor.execute() returns the number of rows impacted (selected, updated, deleted, whatever) by the query
2. you may want to put your database.commit() out of the loop - a single commit is way faster and also makes sure either all the changes are applied or none are, which avoids leaving your database in a half-backed state.

Also note that what you're passing as date_string here is actually not a string but a datetime.datetime object, since you discard the result of the call to date_string.strftime(). But that should be ok, dbapi connectors are supposed to know how to convert between db and python types.

And finally: a proper database schema would have a single googleplay_test table with appname as a field.