Softcoding .filter(...)

[RVince]

I'm trying to discern a means of creating a .filter(A rel B) where the
values for A, rel and B come from an parameters passed in to the web
page.

I already have an SQLAlchemy statement, say
query = Session.query(table).filter(A==B)

and I want to be able to allow for a "drilldown" of sorts by the, such
that from the web page they can pick a value from a dropdown, a
relation (from a dropdown) and a textbox to compare to. But my problem
is once I have these three values, how do I get them into
the .filter() function? That's not going to merely accept string
values -- is there a way to do this?

Thanks, RVince

[Mark Erbaugh]

You can build your SQLAlchemy queries dynamically, i.e.

q1 = query.Session.query(table).filter(A == B)

q2 = q1.filter(C == D)

q3 = q2.filter(E == F)

you could apply different relationships using conditional Python statements:

if rel == 'eq':
q4 = q3.filter(G == H)
elif rel == 'neq':
q4 = q3.filter(G != H)

is this what you're looking for?

Mark

[RVince]

Mark, yes, in part. What I cannot figure out -- and am not sure this
is possible with SQLAlchemy, is to go from strings, as returned from
HTTP Post's, representing the fields in the table (for the left side
of the relation) to the actual statemetn itself. In other words, if
the HTTP post calls for campring the "Lastname" column for equality to
the value smith, I can build the == 'smith' portfion, but how do I
Ibuild a String for the column name, into the value for that column
name so as to comport with SQLAlchemy's sytnax requirements. I don;t
think I can just have a query, q, and then say:
q = q.filter("lastname == 'smith'")
can I? RVince

[NiL]

say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass

field_attr = getattr(Klass, field)

 would give you the instrumented attribute

then

Session.query(Klass).filter(field_attr == searchString)

or

Session.query(Klass).filter(field_attr.endswith(searchString))

would run

HTH

NiL

[Mark Erbaugh]

You can also use the class's __dict__ member:

field_attr = Klass.__dict__['field']

It really amazes me how Pythonic SQLAlchemy makes database access.

Mark

[Wichert Akkerman]

Alternatively if you are only interested in equality you can skip the
getattr and use filter_by in combination with python's keyword argument
handling:

Session.query(klass).filter_by(**{field: value})

Wichert.

[RVince]

Wickert, can you give me an example ? I'm a little confused by this
posts of yours. RVince

[RVince]

Thanks to all you guys. Really. I didn't think I would be able to do
this! RVince

[Matt Bodman]

I think he means that if you only need an exact match on your query (instead of a 'like' or a '<' etc) you can do this:

dict_from_web = {'Title':'The Book','Author':'Bob Smith'}

for b in session.query(Book).filter_by(**dict_from_web)

will return the books that have the exact Title 'The Book' and the exact author 'Bob Smith'

MB