CODEWITHSUNDEEP
pythonmysqlsqlalchemy
L-R
L-R

Reputation: 1232

sqlalchemy always returning the same data

I am using SQLalchemy core (not sessions). I have a DB class that looks like this:

class DB(object):

    db_string = "mysql://%s:%s@%s/%s" % (db_username, db_password, db_host, db_name)
    db_engine = create_engine(db_string, encoding="UTF-8", echo=False)
    db_conn = db_engine.connect()
    db_meta = MetaData()

    table1 = Table("table1", db_meta, autoload=True, autoload_with=db_engine)

    def get_data(self, time_limit):
        stmt = select([self.table1]).where(self.table1.c.last_check < time_limit)
        return self.db_conn.execute(alexa_domains)

I instantiate this class once on load and pass the instance reference around. Now, the main program basically polls the DB in a loop to see if the time_limit is small enough to execute more code.

while 1:
    result = db_instance.get_data()
    check_if_result_has_data_smaller_than_time_limit(result) # THIS always returns False
    time.sleep(1)

The problem is that, if I change data in my database manually (the last_check field), or if another client make a change, this db_instance will apparently never see the new data and the size of result will always be 0. How can I prevent this behavior and make sure that on the next iteration it will see the new rows that should be returned?

Upvotes: 0

Views: 1778

Answers (2)

nicofonk
nicofonk

Reputation: 131

The right way is to force the autocommit in the selection instead of force a new connection.

 def get_data(self, time_limit):
     stmt = select([self.table1]).execution_options(autocommit=True).where(self.table1.c.last_check < time_limit)
     return connection.execute(alexa_domains)

see. http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit

Upvotes: 1

L-R
L-R

Reputation: 1232

The problem was that I was reusing the same connection. Here's the very simple fix:

class DB(object):

    db_string = "mysql://%s:%s@%s/%s" % (db_username, db_password, db_host, db_name)
    db_engine = create_engine(db_string, encoding="UTF-8", echo=False)

    table1 = Table("table1", MetaData(), autoload=True, autoload_with=db_engine)

    def connect():
        return self.db_engine.connect()

    def get_data(self, time_limit):
        connection = self.connect()
        stmt = select([self.table1]).where(self.table1.c.last_check < time_limit)
        return connection.execute(alexa_domains)

Upvotes: 0

Related Questions