3

Here I have created two MySQL connections to the same database.

When one connection updates the database present in class, the other connection can't get the changes. Here is my code

tm(): Database class which handles connect, execute the query and get the overview of database

class ClassB():
    b = None

    def __init__(self):
        self.b = database()

    def get_overview_for_b(self):
        self.b.mark_invalid('9')
        self.b.mark_invalid('8')
        b_str = ''.join(map(str, self.b.get_overview()))
        print("Getting the overview of b" + b_str)


# initializing class B
inside_class_b = ClassB()
# initializing class for A
a = database()

# get database overview for A
astart = a.get_overview()
a_str = ''.join(map(str, astart))
print("Getting the overview of a before testing" + a_str)

# updating database and get database overview for B
inside_class_b.get_overview_for_b()

# get another overview for A
aend = a.get_overview()
a_str = ''.join(map(str, aend))
print("Getting the overview of a after testing" + a_str)

# The final overview of both A and B should be same, but isn't

actual output

Getting the overview of a before testing('PENDING', 2)
Getting the overview of b('INVALID', 2)
Getting the overview of a after testing('PENDING', 2)

expected output

Getting the overview of a before testing('PENDING', 2)
Getting the overview of b('INVALID', 2)
Getting the overview of a after testing('INVALID', 2)

Although I just tried, if I use 'a' to update 'b' gets the updated values.

class ClassB():
    b = None

    def __init__(self):
        self.b = database()

    def get_overview_for_b(self):
        b_str = ''.join(map(str, self.b.get_overview()))
        print("Getting the overview of b" + b_str)


# initializing class B
inside_class_b = ClassB()
# initializing class for A
a = database()

# get database overview for A
astart = a.get_overview()
a_str = ''.join(map(str, astart))
print("Getting the overview of a before testing" + a_str)

# updating using 'a'
a.mark_invalid('9')
a.mark_invalid('8')

# get database overview for B
inside_class_b.get_overview_for_b()

# get another overview for A
aend = a.get_overview()
a_str = ''.join(map(str, aend))
print("Getting the overview of a after testing" + a_str)

Expected Output and Actual output are same

Getting the overview of a before testing('PENDING', 2)
Getting the overview of b('INVALID', 2)
Getting the overview of a after testing('INVALID', 2)

EDIT The following is my execute function used by invalid. This uses a common connection that is checked for None condition everytime.

    def execute(self, statement, attributes):
        """
            Execute a query for the database
            :arg:
                statement - Statement to be executed.
                attributes - Attributes supporting the statement.
        """
        if self._db_connection is None:
            self.connect()
        cursor = self._db_connection.cursor()
        cursor.execute(statement, attributes)
        self._db_connection.commit()
        t = cursor.rowcount
        cursor.close()
        del cursor
        return t
9
  • In case you have two separate instances of the database, like a and b in your case, whenever you call get_overview function, inside it, you need to update your SQL connection by connecting to the database again and getting the latest values. Commented Jul 8, 2019 at 10:16
  • But it is accessing the same database so why I need to update the sql connections Commented Jul 8, 2019 at 10:24
  • Does your code commit the changes to the database immediately when you update a value or just later? If the latter, then under default isolation level configuration, the other transactions cannot see the pending (uncommitted) changes to the data. You either have to commit the changes earlier (recommended option) or you need to change the isolation level to read uncommitted. I would not do the latter, since it may have undesirable consequences, such as seeing an inconsistent state of the database. Commented Jul 8, 2019 at 10:47
  • dev.mysql.com/doc/refman/8.0/en/… Commented Jul 8, 2019 at 10:57
  • 3
    @Saeed please read about transaction isolation levels and how to set them instead of posting such wrong advices.... Commented Jul 8, 2019 at 11:19

1 Answer 1

3

In get_overview() there was no commit command.After adding connection.commit() the code is working as expected.

Problem is resolved. Thanks to everyone who helped me.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.