Re: [sqlalchemy] dynamic query with many parametters
On 8 Sep 2013, at 02:36, Mohsen Pahlevanzadeh m.pahlevanza...@gmail.com wrote:
Suppose i need to send a set of table field and a value to search, i found
the following code:
///
q = session.query(myClass)
for attr, value in web_dict.items():
q
= q.filter(getattr(myClass, attr).like(%%%s%% % value))
///
But it have problem,
i'm newbie in python can you investigate it?
I'm not sure if you understand how the above code is supposed to work, so I'll
explain it. It assumes that web_dict is a dictionary mapping names of
attributes of myClass to strings, and it will return instances of myClass where
all the named attributes contain those values as substrings.
So, for example, if myClass had name and phonenumber properties, and
assuming that web_dict looks like this:
web_dict = {
'name': 'Mohsen',
'phonenumber': '1',
}
web_dict.items() returns a list like this:
[('name', 'Mohsen'), ('phonenumber', '1')]
So the first time round the for loop, attr will be set to name and
value will be Mohsen. The second time round, attr will be phonenumber
and value will be 1.
(Note that dictionaries aren't ordered, so web_dict.items() could actually
return the pairs in a different order)
Let's break the loop down into a few more lines:
for attr, value in web_dict.items():
column = getattr(myClass, attr)
condition = column.like('%%%s%%' % value)
q = q.filter(condition)
getattr is a python function that retrieves a named attribute from an object.
So:
getattr(myClass, phonenumber)
is exactly equivalent to:
myClass.phonenumber
So the first time round the loop, getattr(myClass, attr) returns
myClass.name, and the second time round it will return myClass.phonenumber.
(http://docs.python.org/2/library/functions.html#getattr)
'%%%s%%' % value is a python string-formatting function. %s will be
replaced by whatever is in value, and %% becomes %. So in our example
above, the first time round the loop we will have the string %Mohsen%' and the
second time round it will be %1%.
(http://docs.python.org/2/library/stdtypes.html#string-formatting)
That string is passed to the column.like function, which produces SQL such as
'name LIKE %Mohsen%'. That condition is then passed to Query.filter, which
will add it to the WHERE clause of the SQL using AND.
So the code will eventually end up doing something like this:
q = session.query(myClass)
q = q.filter(myClass.name.like('%Mohsen%'))
q = q.filter(myClass.phonenumber.like('%1%'))
which will end up producing SQL something like:
SELECT myClass columns
FROM myClass table
WHERE name LIKE '%Mohsen%'
AND phonenumber LIKE '%1%'
Is that the sort of SQL that you are hoping to see, or do you need something
different?
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Dynamic query
On Fri, 6 May 2011 17:11:39 +0100
King Simon-NFHD78 simon.k...@motorolasolutions.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
Sent: 06 May 2011 16:20
To: sqlalchemy
Subject: [sqlalchemy] Dynamic query
Dear all,
I've a form where people fill one or more fields to search in a db.
For the moment I solve it using a lot of if statement and a lot of
different query based on the filled fields. Something like that:
if start_date and end_date and instrument and details and
technician: c.results =
Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
Repairs.end_date=end_date,
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' % details),
Repairs.technician.like('%%%s%%' % technician)
)).order_by('start_date').all()
elif start_date and end_date and instrument and details:
c.results =
Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
Repairs.end_date=end_date,
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' %
details), )).order_by('start_date').all()
and so on for each combination (for 5 fields I have 20 query!).
There is
a way to do that in a more dynamic way?
You can call Query.filter multiple times. Here's an example:
query = Session.query(Repairs)
if start_date:
query = query.filter(Repairs.start_date = start_date)
if end_date:
query = query.filter(Repairs.end_date = end_date)
if instrument:
query = query.filter(Repairs.instrument_id == instrument)
# etc.
results = query.order_by('start_date').all()
Each filter condition will be combined using AND.
Hope that helps,
Simon
THANKS!!! Works very fine :-))
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Dynamic query
Given all this magic with .with_polymorphic(), I'm now wondering if
it's really worth to have a new Query class (by subclassing orm.Query)
per mapped object just to apply some filters...
At first I found quite elegant to have one Query object per mapped
class, but now I'm wondering if it's not better after all to have a
bunch of @staticmethod in the model ...
What do you think ?
On 10/13/2010 11:21, Julien Cigar wrote:
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Dynamic query
On Oct 14, 2010, at 9:28 AM, Julien Cigar wrote:
Given all this magic with .with_polymorphic(), I'm now wondering if it's
really worth to have a new Query class (by subclassing orm.Query) per mapped
object just to apply some filters...
At first I found quite elegant to have one Query object per mapped class, but
now I'm wondering if it's not better after all to have a bunch of
@staticmethod in the model ...
What do you think ?
I generally think subclasses of Query should be used very conservatively, and
that model specific behavior should be in the model, sure.
On 10/13/2010 11:21, Julien Cigar wrote:
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
jcigar.vcf
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Dynamic query
On 10/12/2010 18:05, Julien Cigar wrote:
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ),
func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic
the following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has
no attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType'
object has no attribute '_autoflush'
- etc
OK I think I found a solution, I need to pass session=Session.registry()
to my custom query:
model.content.ContentQuery.__mro__
(class 'amnesia.model.content.ContentQuery', class
'amnesia.model.root.RootQuery', class 'sqlalchemy.orm.query.Query',
type 'object')
PolymorphicQuery = type('PolymorphicQuery',
(model.content.ContentQuery, ), {})
q1 = PolymorphicQuery(model.Content)
q1.get(25)
Traceback (most recent call last):
File console, line 1, in module
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 595, in get
return self._get(key, ident)
File
/home/jcigar/venvs/pylons0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.6.4-py2.5.egg/sqlalchemy/orm/query.py,
line 1803, in _get
instance = self.session.identity_map.get(key)
AttributeError: 'NoneType' object has no attribute 'identity_map'
q2 = PolymorphicQuery(model.Content, session=meta.Session.registry())
q2.get(25)
amnesia.model.event.Event object at 0x939046c
I hope I'm not doing something wrong :p
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the
wiki trying to find what @dynamic is. Seems like something I'd have
come up with in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
Re: [sqlalchemy] Dynamic query
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ), func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic the
following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has no
attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType' object has
no attribute '_autoflush'
- etc
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the wiki
trying to find what @dynamic is. Seems like something I'd have come up with
in the past but I've no clue at the moment what that is.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Dynamic query
On 10/12/2010 17:09, Michael Bayer wrote:
On Oct 12, 2010, at 7:39 AM, Julien Cigar wrote:
Hello,
any idea why with
# Query
class BaseQuery(orm.Query):
@dynamic
def method_a(self):
...
def method_b(self):
...
class FooQuery(BaseQuery):
...
class BarQuery(FooQuery):
@dynamic
def method_c(self):
...
# Models
class BaseModel(object):
query = Session.query_property(BaseQuery)
#
myQuery = type('PolymorphicQuery, (content.BaseQuery, ), func_list)(BaseModel)
where func_list containing all the functions decorated by @dynamic the
following fail? :
- myQuery.get(45) fails with: AttributeError: 'NoneType' object has no
attribute 'identity_map'
- myQuery.method_a().all() fails with: AttributeError: 'NoneType' object has no
attribute '_autoflush'
- etc
My goal is to be able to build a custom Query object to use with the
.with_polymorphic() function ..
I've just grepped through all the source, examples and tests plus the wiki
trying to find what @dynamic is. Seems like something I'd have come up with
in the past but I've no clue at the moment what that is.
Sorry, I forgot to mention that it's just a custom decorator of mine
which add the function name to a list ... forget about it, it's just to
build the third argument of type() (func_list in my case)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
attachment: jcigar.vcf
