CODEWITHSUNDEEP
pythonmysql
Cyberlurk
Cyberlurk

Reputation: 764

Python Mysql Connector not getting new content

I making a simple python script which checks a mysql table every x seconds and print the result to the console. I use the MySQL Connector Driver.

However, running the script only prints the initalial values. By that I mean, that if I change the values in the database while my script is running, it's not registered by the script and it's keeps on writing the initial values.

The code which retrieves the values in a while loop is as follows:

def get_fans():
    global cnx
    query = 'SELECT * FROM config'
    while True:
        cursor = cnx.cursor()
        cursor.execute(query)
        for (config_name, config_value) in cursor:
            print config_name, config_value
        print "\n"
        cursor.close()
        time.sleep(3)

Why is this happening?

Upvotes: 3

Views: 731

Answers (1)

saaj
saaj

Reputation: 25184

Most likely, it's an autocommit issue. MySQL Connector Driver documentation states, that it has autocommit turned off. Make sure you commit your implicit transactions while changing your table. Also because default isolation is REPEATABLE READ you have:

All consistent reads within the same transaction read the snapshot established by the first read.

So I guess you have to manage transaction even for your polling script. Or change isolation level to READ COMMITTED.

Though, the better way is to restore to MySQL client default autocommit-on mode. Even though PEP 249 guides to have it initially disabled, it's mere a proposal and most likely a serious design mistake. Not only it makes novices wonder about uncommited changes, makes even your read-only workload slower, it complicates data-layer design and breaks explicit is better than implicit Python zen. Even sluggish things like Django have it rethought.

Upvotes: 1

Related Questions