Persistent where clause SQLAlchemy

Question

I'm trying to add a persistent SQL where clause in SQLalchimy. Basically I have a table containing a specific field, and I want all my request to add a where clause, so this field matches a clause.

For instance, if my request is

session.query( MyTable ).all()

I'd like the generated request to be as such :

SELECT * FROM Table WHERE specific_field = 'some_value';

Is there a way of doing this without adding a filter to each request ?

Thanks in advance.

See answers to this question: http://stackoverflow.com/questions/2885415/what-is-the-best-way-pre-filter-user-access-for-sqlalchemy-queries — Denis Otkidach, Mar 29 '11 at 08:44

phooji · Accepted Answer · 2011-03-25T15:50:17.653

1

If you add:

dbengine.echo = True
session.query(MyTable).filter_by(specified_field = 'some_value').all()

I'm not sure what you mean by: 'without adding a filter to each request.' My guess is that you don't want to filter the request client-side. The generated SQL for this should look a lot like what you're asking for; the dbengine.echo = True bit will show you the SQL that's generated.

Update based on OP comment:

If you really want all queries to have a filter, then a simple (but in my opinion not so pretty) way would be to add your own query method:

def myquery(self, table):
  self.query(table).filter_by(specified_field = 'some_value')

session.__class__.some_arbitrarily_filtered_query = myquery

After that, you can just call (for example):

session.some_arbitrarily_filtered_query(MyTable).all()

edited Mar 25 '11 at 15:50

answered Mar 25 '11 at 14:51

phooji

10,086
2
38
45

Yep but the aim would be to have the filter_by(specified_field = 'some_value') added automatically – chouquette Mar 25 '11 at 14:52
I think I'm going to use the filter by hand, but thanks for the idea though ! – chouquette Mar 25 '11 at 16:32
@chouquette: That sounds like the right decision. If you use certain kinds of queries frequently, you can always put them in a function later -- I think that's one of the main benefits of using SQLAlchemy's query construction methods. – phooji Mar 25 '11 at 20:21

score 0 · Answer 2 · answered Mar 25 '11 at 15:57

I'm not really suggesting this as it's overly complicated, but...

CREATE VIEW Table2 AS SELECT * FROM Table WHERE specific_field = 'some_value';

then you reflect the view with

table2 = sqlalchemy.Table('Table2', metadata, autoload=True)

and just query there, possibly mapping it (also providing the primary key) to

class MyTable2(MyTable):
    pass

untested, but should work

Persistent where clause SQLAlchemy

2 Answers2