SqlAlchemy dynamic query generation

[Sana]

Hi All,

I am trying to do query based on the user input as follows

condition = 'and_(db.User.id == id'
if user_name != ' ':
condition += ',db.User.name == user_name'
elif age != ' ':
condition += ',db.User.age == age'
elif place != ' ':
condition += ',db.User.place == place'

where = condition+')'

result = db.User.filter(where).all()


But Im getting error when i do this.

Is there any way by which i can do this


Thank you
Sana

[Jackson, Cameron]

You might get more help if you provide more details. What error are you getting?

Hi All,

where = condition+')'

result = db.User.filter(where).all()

Thank you
Sana

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

-------------------------------------------------------------------------
DISCLAIMER: This e-mail transmission and any documents, files and
previous e-mail messages attached to it are private and confidential.
They may contain proprietary or copyright material or information that
is subject to legal professional privilege. They are for the use of
the intended recipient only. Any unauthorised viewing, use, disclosure,
copying, alteration, storage or distribution of, or reliance on, this
message is strictly prohibited. No part may be reproduced, adapted or
transmitted without the written permission of the owner. If you have
received this transmission in error, or are not an authorised recipient,
please immediately notify the sender by return email, delete this
message and all copies from your e-mail system, and destroy any printed
copies. Receipt by anyone other than the intended recipient should not
be deemed a waiver of any privilege or protection. Thales Australia
does not warrant or represent that this e-mail or any documents, files
and previous e-mail messages attached are error or virus free.

-------------------------------------------------------------------------

[Sana klh]

I am getting the following error

ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 1,db.User.age==23,d' at line 3")

Is there any way by which i can generate dynamic query?

Thank you

  Sana

[Robert Forkel]

You may want to look at the tutorial [1]. In the code you pasted you
are assembling a string to pass to filter. While you can do that (but
then it should be proper sql not python!), what you want to do is
passing a python expression like db.User.age==age, or a combination of
expressions combined using and_, or_, etc.

[1] http://www.sqlalchemy.org/docs/orm/tutorial.html#common-filter-operators

[robert rottermann]

the string you are generating must be proper sql.
to test is you migth print it and try in directly against the db.

but what you show us is for sure no valid sql. In sql the equality operator is not '==' but '='.
robert

[Sana klh]

Hi Robert Forkel,

You got it right i am trying to have a combination of expression combined in and_.

I need to add the attribute only when it is not equal to null.So i tried to check if it is not null and then from the expression and pass it to filter .But this does not work.

I went through the document but unable to figure out how to do this through sql.

Thank you

[Robert Forkel]

conditions = [db.User.id==id]
if user_name != ' ':
conditions.append(db.User.name == user_name)
elif age != ' ':
conditions.append(db.User.age == age)
elif place != ' ':
conditions.append(db.User.place == place)

result = db.User.filter(and_(*conditions)).all()

might do the trick (untested).

[Sana klh]

I tried it but i still get the same error.

[Pau Tallada]

Hi,

Try composing a criteria using and_(), or_(), or its equivalent binary operators &,|:

crit = literal(false)

crit |= (<table>.c.<field> == <value>)

crit &= (<table>.c.<field> == <value>)

etc...

And then:

q = session.query(<entity>).filter(crit)

2011/12/22 Sana klh <sana....@gmail.com>

--

----------------------------------

Pau Tallada Crespí

Dep. d'Astrofísica i Cosmologia

Port d'Informació Científica (PIC)

Tel: +34 93 586 8233

----------------------------------