[FIXED] AttributeError: 'Select' object has no attribute 'filter' - in Flask and Sqalchemy

Issue

from sqlalchemy import create_engine, MetaData, Table, Column, select, insert, and_, update, delete
load_dotenv()

engine = create_engine(os.getenv('SQLALCHEMY_DATABASE_URI'))
connection = engine.connect()
metadata = MetaData()

Inventory = Table('inventory', metadata, autoload=True, autoload_with=engine, extend_existing=True)   

@inventory.route('/', methods=["GET", "POST"])
def viewAll():
    query = select([Inventory]).filter(Inventory.id >=3)
    ResultProxy = connection.execute(query)
    ResultSet = ResultProxy.fetchall()
    res = []
    for rs in ResultSet:
        res.append(list_to_json(rs))
    return dict(enumerate(res))

I'm trying to run this code by adding a filter query. It shows an error saying AttributeError: 'Select' object has no attribute 'filter'.

The code runs fine without the filter query. So, Needed help to understand how to add a filter query to this code.

Solution

You have two options to fix this error

1. Execute raw query (since you are using connection.execute(query)), e.g.:

def viewAll():
  query = 'SELECT * FROM Inventory WHERE id >= 3'
  ResultProxy = connection.execute(query)
  ResultSet = ResultProxy.fetchall()

2. Use session.query, something like this:

def viewAll():
  ResultSet = self.session.query(Inventory).filter(Inventory.id >=3).all()

Answered By - therceman