Python script returns old MySQL values (until restart)

Question

I have the following code that I run once and then again when a new value is inserted to refresh.

def getStageAdditives(self, stage):
        stagesAdditivesSelectQuery = """SELECT      a.id,
                                                    a.name,
                                                    IFNULL(sa.dose, 0) as dose,
                                                    IFNULL(sa.last_dose, 0) as last
                                        FROM        additives a
                                        LEFT JOIN   stage_additives sa
                                        ON          a.id = sa.additive_id
                                        AND         sa.stage_id = (
                                        SELECT      id
                                        FROM        stages
                                        WHERE       name = '""" + stage + """')
                                        ORDER BY    a.name"""

        self.cursor.execute(stagesAdditivesSelectQuery)
        data = self.cursor.fetchall()

        additives = []
        for additive in data:
            id = additive[0]
            name = additive[1]
            dose = additive[2]
            last = additive[3]
            additives.append({ 'title': name, 'dose': dose, 'last': last })
        print stagesAdditivesSelectQuery
        return additives

The issue is that after I use the following code to insert a value into 'additives' table I get old values (new value is missing).

def createAdditive(self, name):
        additiveInsertQuery = """ INSERT INTO additives
                                  SET         name = '""" + name + """'"""
        try:
            self.cursor.execute(additiveInsertQuery)
            self.db.commit()
            return "True"
        except:
            self.db.rollback()
            return "False"

I can confirm that values are being inserted into the database by looking at phpMyAdmin. If I restart the script I get the new values as expected. If I run the query with phpMyAdmin that also returns new values. Refreshing the page and waiting 10+ seconds doesn't help and I get old values still.

Both methods are in separate classes/files if it matters. The getStageAdditives is called with ajax after the 'createAdditive' method has returned successfully.

DB initialisation:

import MySQLdb
import time

class Stage:
    def __init__(self):
        self.db = MySQLdb.connect('192.168.0.100', 'user', 'pass', 'dbname')
        self.cursor = self.db.cursor()

Another method that retrieves similar values gets the new values as expected (same class as createAdditives):

def getAdditives(self, additive=None):
        where = ''
        if additive is not None:
            where = "WHERE pa.additive_id = ("
            where += "SELECT id FROM additives "
            where += "WHERE name = '" + additive + "') "
            where += "AND a.name = '" + additive + "'"

        additiveSelectQuery = """   SELECT      a.name,
                                                pa.pump_id
                                    FROM        additives a,
                                                pump_additives pa """ + where + """
                                    ORDER BY    a.name"""

        self.cursor.execute(additiveSelectQuery)
        data = self.cursor.fetchall()

        additives = []
        for item in data:
            additives.append( {'additive': item[0], 'pump': item[1]} )
        return additives

How are you confirming the result of the `createAdditive` method? In other words, where in your code do you check the result of this method? — Burhan Khalid, Oct 14 '13 at 21:16
I am not actually checking it in the method, but it returns "True" and with that an update is called for additives only (method in same class as createAdditive). I have edited my Question to include this code. — DominicM, Oct 14 '13 at 21:20
Maybe you're inside a transaction in getStageAdditives with a REPEATABLE READ isolation level, and therefore not seeing any updates from other transactions. — ykaganovich, Oct 14 '13 at 22:13

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

For those who find this question: Similar question was solved in Why are some mysql connections selecting old data the mysql database after a delete + insert?

The trick is to add an connection.commit().

edited May 23 '17 at 12:23

Community

1
1

answered Dec 19 '15 at 23:54

Kahitarich

395
2
7

Python script returns old MySQL values (until restart)

1 Answers1