[sqlalchemy] SQLAlchemy subquery to find variance
DBSession.query(func.length(class_name.cpu_util), (DBSession.query(func.variance(class_name.util)).\ filter(class_name.entity_id==entity_id).filter(class_name.metric_type==metric_type). \ filter(class_name.cdate=from_date).filter(class_name.cdate=to_date).all()), \ func.avg(class_name.m_util)).\ filter(class_name.entity_id==entity_id).filter(class_name.metric_type==metric_type). \ filter(class_name.cdate=from_date).filter(class_name.cdate=to_date).all() ERROR == File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/scoping.py, line 123, in do return getattr(self.registry(), name)(*args, **kwargs) File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/session.py, line 895, in query return self._query_cls(entities, self, **kwargs) File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/query.py, line 92, in __init__ self._set_entities(entities) File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/query.py, line 99, in _set_entities entity_wrapper(self, ent) File /root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6- py2.4.egg/sqlalchemy/orm/query.py, line 2099, in __init__ raise sa_exc.InvalidRequestError( InvalidRequestError: SQL expression, column, or mapped entity expected - got '[(0.0,)]' Here i am using a subquery to find variance. How give a name to subquery result column. (DBSession.query(func.variance(class_name.util)).\ filter(class_name.entity_id==entity_id).filter(class_name.metric_type==metric_type). \ filter(class_name.cdate=from_date).filter(class_name.cdate=to_date).all()) -- 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.
[sqlalchemy] Re: double data insert in many-to-many relations
Okay, i have got a stupid question. i have got something like this: result = Session.query(Messages).filter( Messages.from_user == 1).filter( User.id != 1).all() This query is supposed to get all the messages ( Messages table) that have the senders id (from_user) but select ONLY this messages that are connected to all users except user with id = 1. This tables are many-to-many( Messages, Messages_Users, Users ). The relationship is set correctly. Is this even possible? ;-) -- 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] Use regexp in like
On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? Thanks, Michael -- 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] Use regexp in like
On Sep 17, 2010, at 9:14 AM, Michael Hipp wrote: On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? if you were using Postgresql, you could use somecolumn.op(~)(someregexp) http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP cant speak for other platforms though you'd have to consult their documentation. Thanks, Michael -- 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. -- 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.
[sqlalchemy] another many-to-many question
Hi. I'm quering many-to-many tables (ex. User, User_Car, Car), as a result i'm getting a data like this: result: + id + name + street + car - list of users Cars Is it possible, if yes then how, to filter this result to math this variables ex. user.id = 1 and car.id = 3? So i can get User object with User.car list contaning one Car object? -- 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] Use regexp in like
On Sep 17, 2010, at 11:12 AM, Michael Bayer wrote: On Sep 17, 2010, at 10:58 AM, Michael Bayer wrote: On Sep 17, 2010, at 9:14 AM, Michael Hipp wrote: On 9/14/2010 2:23 PM, Michael Hipp wrote: Is it possible to use a regexp in a like() clause? Or some other way to achieve something similar? Can anyone suggest an approach to search a field with a regexp? if you were using Postgresql, you could use somecolumn.op(~)(someregexp) http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP cant speak for other platforms though you'd have to consult their documentation. PG also supports MATCH since I see we have some unit tests for that, i.e. column.match(other). I'm not seeing offhand in PG's docs what it interprets the MATCH operator as, i.e. is it a ~, SIMILAR TO, not sure. I guess the reasons I've never had interest in regexp matching in databases are: 1. its always bad to search through tables without being able to use indexes 2. if you're needing to dig into text, it suggests the atoms of that text should be represented individually in their own column (i.e. normalize) 3. no really, I'm doing flat out full text searching on documents and don't want to reinvent. Well then I'd use a full blown text extension (http://www.postgresql.org/docs/8.3/static/textsearch.html) or a separate search engine. The SQLAlchemy match operator compiles to PostgreSQL full-text search (@@ to_tsquery). test/dialect/test_postgresql.py Cheers, M -- 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.
[sqlalchemy] Views recipe - is it usable with a stored procedure as a select
Looking through the different recipes I noted the one on views (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views). Would like to use this with the ORM but some of my views have a stored procedure as a select. Is it possible with SA to create a selectable as is needed for the Views recipe from/with a stored procedure? The view definition in Firebird SQL is: CREATE OR ALTER VIEW COUNTRIES_LV( ID, NAME, ISO2, ISO3, TELCODE, CREATED_AT, UPDATED_AT) AS select * from countries_lp; countries_lp being the stored procedure. Werner -- 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.
