1

I'm new to Flask and have started designing a front end for an inventory management database using Flask-AppBuilder.

I have created several models and have have managed to display my sqlite data in tables using Flask-AppBuilder's views.

However, I don't seem to be able to find the equivalent of SQLite WHERE clause to filter or "restrict" column data. I've been reading a lot about sqlalchemy, filters, queries but this has left me more confused that anything else and the explanations seem to be extremely elaborate and complicated to do something which is extremely simple.

Assuming we reproduce the following SQLite query in Flask-AppBuilder:

SELECT Field_A
FROM Table_A
WHERE Field_A = 'some text'

with:

result = session.query(Table_A).filter_by(Field_A = 'some text').all()

Where does the above line of code go in my app?

Considering I have the following Class:

class Table_A(Model):
    id = Column(Integer, primary_key=True)
    Field_A =  Column(String)

    def __repr__(self):
        return self

and View:

class Table_AView(ModelView):
    datamodel = SQLAInterface(Table_AView)
    label_columns = {'Field_A':'A'}
    list_columns = ['Field_A']
Alex B
  • 1,092
  • 1
  • 14
  • 39

2 Answers2

1

After much digging flask-appbuilder uses it's own filterclass in order to enable you to filter your views.

All the classes are referenced here on GitHub: Flask Filter Clases List

Also not the difference between FilterEqual and FilterEqualFunction here: What is the difference between : FilterEqual and FilterEqualFunction?

For other customisation and first port of call of Flask-appbuilder go straight to the API Reference where you'll find a couple of examples of the filterclass in action.

In essence it is extremely simple. In your views.py code within the ModelView class you want to filter simply add base_filters = [['field_A', FilterEqual, 'abc']] like so:

`class Table_AView(ModelView):
    datamodel = SQLAInterface(Table_AView)
    label_columns = {'Field_A':'A'}
    list_columns = ['Field_A']
    base_filters = [['field_A', FilterEqual, 'abc']]`

This will only show the lines where the field_A variable is equal to abc.

Hope this helps someone as it took me nearly (sigh) two weeks to figure it out...

Alex B
  • 1,092
  • 1
  • 14
  • 39
0

SQLALchemy is an ORM (Object-Relational Mapping), it mean that you dont have to deal with raw SQL, you will call a function that you "build" (by adding filters in your case). It will transparently generate an SQL query, execute it, and return the result as python objects.

I would suggest you to read closely at sqlalchemy documentation about filters again, especially filter_by : http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.filter_by

It is the easiest way to apply a WHERE with sqlalchemy. If you have declared correctly the model for Table_A, you should be able to use it so:

result = session.query(Table_A).filter_by(Field_A = 'some text').all()

Here session.query(Table_A).filter_by(Field_A = 'some text') will generate the SQL, and .all() will execute it.

SivolcC
  • 3,258
  • 2
  • 14
  • 32
  • Thank you for your clear response and for clarifying that I am on the right path. I had indeed come across the SQLAlchemy Query filer documentation that you refer to. However, one final question which is, again, probably very obvious and which is not indicated in the documentation: where does the line of code `result = ...` or `session.query...` go in my app? In models or views ? I've looked high and low and found many examples of queries but none show where the code is inserted ? – Alex B Sep 05 '18 at 06:56
  • Well where do you need to get data from the database? The answer to this question can vary. But in your case, if you want for example to get a list of `Table_A`, you will call the query function it in your view function, and pass the result to flask's `render_template()` function for example. – SivolcC Sep 06 '18 at 07:31
  • Let's imagine I only want the list of users which are called "john" ? – Alex B Sep 06 '18 at 07:34