CODEWITHSUNDEEP
pythonmysqldatabasesqlalchemy
anils
anils

Reputation: 1962

Sqlalchemy remote database session refresh/flush issue

I am working on python-pyside desktop application which uses a remote database and application is installed on multiple machines, application uses sql alchemy.

Problem is: Application have a table printer, it consist 8 records. Suppose application is running on 2 machine m1 and m2, if m1 user deletes/updates/inserts printer record, and after that if m2 gets printer then it shows 8 printers(same as that of start) if m2 performs any update/insert/delete operation in application and try to get records from printer then it shows correct data.

I think this issue/behavior is due to sql alchemy session.

Code:

#config file
self.engine = create_engine ('mysql://user:pwd@remotehost/database')

#Database session file
configuration=Config()         #config consist db,dbusername, 
                               #host name, pwd and engin
engine =configuration.engine 
db_session = scoped_session(sessionmaker(autocommit=False,
                        autoflush=False,
                        bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

#initialization
import models
Base.metadata.create_all(bind=engine)

#Printer model file
#get query 
printers = db_session.query(Printer).filter(Printer.status != 0)
.order_by(asc(Printer.name)).all()

#alternative get query used/it also have same problem
printers = Printer.query.filter(Printer.status != 0)
.order_by(asc(Printer.name)).all()

Session initialized only once in main/start file of application.

Please help me.

I tried db_session.flush() also autoflush=True, but it doesn't worked.

Upvotes: 2

Views: 2355

Answers (2)

Neel
Neel

Reputation: 21273

Use session.commit instead of session.flush before getting data.

Upvotes: 2

anils
anils

Reputation: 1962

Thank you Lafada,

session.commit before get(select/filter) query solved my problem.

#Printer model file
db_session.commit()
#get query 
printers = db_session.query(Printer).filter(Printer.status != 0)
.order_by(asc(Printer.name)).all()

#alternative get query used/it also have same problem
db_session.commit()
printers = Printer.query.filter(Printer.status != 0)
.order_by(asc(Printer.name)).all()

Upvotes: 1

Related Questions