CODEWITHSUNDEEP
pythonmysqlflasksqlalchemy
Razmooo
Razmooo

Reputation: 506

Sqlalchemy with Flask - deleted data is still shown

Description

I have an Flask application with original SQLalchemy. Application is intended to be used internally in a company for easier saving of measurement data with MySQL

On one page I have a table with all devices used for measurement and a form that is used to add, remove or modify measurement devices.

Problem

The problem is that when I enter a new device in the database, the page is automatically refreshed to fetch new data from DB and new device is sometimes shown and sometimes it is not when I refresh the page. In other words, added row in table is appearing and dissapearing even though the row is visible on database. Same goes when i try to delete the device from database. The row is sometimes shown, sometimes not when refreshing the page with row being deleted from DB. The same problem appears for all examples similar to this one (adding, deleting and modifying data).

What i have tried

Bellow is the code for table model:

class DvDevice(Base):
    __tablename__ = "dvdevice"

    id = Column("device_id", Integer, primary_key=True, autoincrement=True)
    name = Column("device_name", String(50), nullable=True)
    code = Column("device_code", String(10), nullable=True, unique=True)
    hw_ver = Column("hw_ver", String(10), nullable=True)
    fw_ver = Column("fw_ver", String(10), nullable=True)
    sw_ver = Column("sw_ver", String(10), nullable=True)

And here is the code that inserts/deletes data from table.

    #Insertion
    device = DvDevice()
    device.code = self.device_code
    device.name = self.device_name
    device.hw_ver = self.hw_ver
    device.fw_ver = self.fw_ver
    device.sw_ver = self.sw_ver
    ses.add(device)
    ses.commit()
    ses.expire_all() #Should this be here?

    # Deletion
    ses.query(DvDevice).filter_by(id=self.device_id).delete()
    ses.commit()
    ses.expire_all() # Should this be here?

I have read from some posts on stack to include the following decorator function in models.py

@app.teardown_appcontext
def shutdown_session(exception=None):
   ses.expire_all() #ses being database session object.

I tried this and it still doesn't work as it should be. Should I put the decorator function somewhere else?

Second thing i tried is to put ses.expire_all() after all commits and it still doesnt work.

What should I do to prevent this from happening?

Edit 1

from sqlalchemy import create_engine, update
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool

from config import MYSQLCONNECT

engine = create_engine(MYSQLCONNECT)
Session = sessionmaker(bind=engine)

session = Session()

Upvotes: 2

Views: 1744

Answers (1)

Razmooo
Razmooo

Reputation: 506

I solved the problem with the use of following function from http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it:

from contextlib import contextmanager

@contextmanager
def session_scope():
    """Provide a transactional scope around a series of operations."""
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()



with session_scope() as session:
    ... # code that uses session

The problem was that I created the session object in the beggining and then never closed it.

Upvotes: 2

Related Questions