[sqlalchemy] Re: group_by, count, and name count field as ?
Also what would be a syntax to make a same selection using
session.query(th)?
what would you like the ORM query to return ? Object instances ?
currently, using group_by() and such with Query implies you're getting
individual column tuples back using _values(), in which case its
sess.query(THsClass).group_by(whatever)._values(col expressions).
I would like to get similar result like I would via query.all, because
it allows me to look through it multiple times.
b=session.query(th).filter(th.RECORD_NO==5).all()
I will iterate through b multiple times, and getting it via select I
would have to get it from the database each time I loop. (correct?)
So I tried instead of doing:
s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute()
do this:
a=session.query(th).add_column(sqlalchemy.func.count(th.c.RECORD_NO).label('MYGREAT_COUNT')).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO).all()
or
a=session.query(th).group_by(th.RECORD_NO).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all()
but I get an error.
Am I using incorect syntax here?
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/orm/query.py, line 853, in all
return list(self)
File sqlalchemy/orm/query.py, line 914, in __iter__
return self._execute_and_instances(context)
File sqlalchemy/orm/query.py, line 917, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File sqlalchemy/orm/session.py, line 605, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File sqlalchemy/engine/base.py, line 844, in execute
return Connection.executors[c](self, object, multiparams, params)
File sqlalchemy/engine/base.py, line 895, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File sqlalchemy/engine/base.py, line 907, in _execute_compiled
self.__execute_raw(context)
File sqlalchemy/engine/base.py, line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File sqlalchemy/engine/base.py, line 958, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File sqlalchemy/databases/mssql.py, line 805, in do_execute
super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement,
parameters, context=context, **kwargs)
File sqlalchemy/databases/mssql.py, line 499, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
Lucas
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
On Apr 11, 2008, at 10:41 AM, Lukasz Szybalski wrote:
So I tried instead of doing:
s
=
sqlalchemy
.select
([th
.RECORD_NO
,sqlalchemy
.func
.count
(th
.RECORD_NO
)],sqlalchemy
.and_
(th
.APPLIED_TEST
==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute()
do this:
a
=
session
.query
(th
).add_column
(sqlalchemy
.func
.count
(th
.c
.RECORD_NO
).label
('MYGREAT_COUNT
')).filter
(sqlalchemy
.and_
(th
.APPLIED_TEST
==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO).all()
or
a
=
session
.query
(th
).group_by
(th
.RECORD_NO
).filter
(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all()
OK, you want a combination of entities and columns. The SQL being
issued above is still not what you want. From a SQL perspective,
saying session.query(MyClass) is equivalent to saying:
SELECT * from table
I.e., *all* columns from th are being added to the columns clause of
the select. According to the SQL standard, these names all need to
be added to the GROUP BY as well - if MS-SQL is allowing only a
partial GROUP BY list, thats just poor behavior on the part of MS-SQL
(MySQL has this bug as well). What this query really looks like is:
SELECT *, count(somecol) FROM table GROUP BY *
where the * includes the primary key column of the table, and
therefore it groups by the full set of columns on the table which
renders the count() meaningless.
So, the exact analogy to the select statement you're issuing is:
sess
.query
(th
).filter
(and_
(th
.APPLIED_TEST
=
=
1
,th
.CODING_DATE
=='20080325')).group_by(th.RECORD_NO)._values(th.RECORD_NO,
func.count(th.RECORD_NO))
where _values() will return an iterator of exactly the columns you
want.
in the next release of SA, you'll also be able to say
sess.query(th.RECORD_NO,
func.count(th.RECORD_NO)).filter(...).group_by(...).all() to get a
similar result (and _values() will be named values()).
File sqlalchemy/databases/mssql.py, line 499, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self
.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
This seems to be some weird error either with pyodbc or with the MS-
SQL dialect, MS-SQL people we've seen this before, correct ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
File sqlalchemy/databases/mssql.py, line 499, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self .identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception This seems to be some weird error either with pyodbc or with the MS- SQL dialect, MS-SQL people we've seen this before, correct ? That statement should only every be issued on an insert to a table with an auto-generated PK that contains an explicit value for the PK, not on any select() or query(). Even then it should be fine, unless pyodbc is having trouble with SQL statement sequence. A small testcase would be needed to track this one down. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
I.e., *all* columns from th are being added to the columns clause of the select. According to the SQL standard, these names all need to be added to the GROUP BY as well - if MS-SQL is allowing only a partial GROUP BY list, thats just poor behavior on the part of MS-SQL (MySQL has this bug as well). What this query really looks like is: SELECT *, count(somecol) FROM table GROUP BY * where the * includes the primary key column of the table, and therefore it groups by the full set of columns on the table which renders the count() meaningless. so a=session.query(th).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO)._values(th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)).all() is just a slightly more complicated way of saying: s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute().fetchall() If I had to pick I would just go with select. (unless there is other reason to use query) Is there a way to get this ? SELECT RECORD_NO, count(RECORD_NO) FROM table GROUP BY RECORD_NO (primary key is RECORD_ID) The only way I know how to do it is via sqlalchemy.select but I would like the query type of the return where my results are type(a) type 'list' type(a[0]) class '__main__.th' How would I do that? Lucas --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
On Apr 11, 2008, at 12:51 PM, Lukasz Szybalski wrote:
Is there a way to get this ?
SELECT RECORD_NO, count(RECORD_NO) FROM table GROUP BY RECORD_NO
(primary key is RECORD_ID)
The only way I know how to do it is via sqlalchemy.select
but I would like the query type of the return where my results are
type(a) type 'list'
type(a[0])
class '__main__.th'
If I understand correctly, you want the instances that correspond to
those record_no records, and the count. In this case:
s = select([th.RECORD_NO,
func.count(th.RECORD_NO).label('count')]).group_by(th.RECORD_NO)
sess.query(th).select_from(th_table.join(s,
th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all()
for some insight into this approach, see
http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx
.
also again, the next release will have a more concise method of
building that join as well.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
ok, I don't think we are on a same page. So let me explain what I
want and maybe unconfused myself on what is a proper way to get data
out of database via sqlalchemy orm style.
Query class should not be confused with the Select class, which
defines database SELECT operations at the SQL (non-ORM) level. Query
differs from Select in that it returns ORM-mapped objects and
interacts with an ORM session, whereas the Select construct interacts
directly with the database to return iterable result sets
I need to return ORM-mapped object.
This is non-ORM object:
s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO).
label('RECORD_NO_COUNT')],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).
group_by(th.RECORD_NO)
s.execute().fetchall()
Above statements should result in query like: 'select a,count(a) from
x where b=5,c=6 group by a'
In order to get ORM object I need to use query. (That is what I get
from reading the sentence I quoted)
By not knowing how sqlalchemy works and just using my intuition I
would assume that I should be able to do something like this:
sess.query(th).select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO).label('RECORD_NO_COUNT')],
sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404'))
.group_by(th.RECORD_NO).all()
but obviously the above does not work. I guess what I am confused is
how to use non-ORM syntax (its easy to follow) in an ORM way?
What you emailed below is making a join if I read it correctly, and I
don't think that is what I want.
sess.query(th).select_from(th_table.join(s,
th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all()
Lucas
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
On Apr 11, 2008, at 2:51 PM, Lukasz Szybalski wrote: Above statements should result in query like: 'select a,count(a) from x where b=5,c=6 group by a' In order to get ORM object I need to use query. (That is what I get from reading the sentence I quoted) From the above, what ORM object would you like ? the one which has a as one of its columns ? if so, the recipe I gave you is the correct approach. What you emailed below is making a join if I read it correctly, and I don't think that is what I want. sess.query(th).select_from(th_table.join(s, th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all() if you want to select a full row from the table, including its primary key, and you'd like that row to correspond to a particular aggregate query which needs to GROUP BY only part of the columns which does not include the primary key column, you must use a join or subquery to create the aggregate. To illustrate, this is the query you think you want: select *, count(a) from table group by a Why * ? because you say that you want ORM objects. The ORM object represents the *full* row, including the primary key, of the table. but again, that query is invalid SQL.Most SQL-compliant databases will raise an error. Another article explaining this: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx . Therefore, the join or subquery is necessary if you wish to GROUP BY only particular columns. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
Above statements should result in query like: 'select a,count(a) from x where b=5,c=6 group by a' In order to get ORM object I need to use query. (That is what I get from reading the sentence I quoted) From the above, what ORM object would you like ? the one which has a as one of its columns ? if so, the recipe I gave you is the correct approach. What you emailed below is making a join if I read it correctly, and I don't think that is what I want. sess.query(th).select_from(th_table.join(s, th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all() if you want to select a full row from the table, including its primary key, and you'd like that row to correspond to a particular aggregate query which needs to GROUP BY only part of the columns which does not include the primary key column, you must use a join or subquery to create the aggregate. To illustrate, this is the query you think you want: select *, count(a) from table group by a Why * ? because you say that you want ORM objects. The ORM object represents the *full* row, including the primary key, of the table. It makes sense now. I assumed that ORM object will only return 1 column I group by, and a count. When it returns the full row then my group_by makes no sense. Thanks Lucas ps. The links were really helpful ! but again, that query is invalid SQL.Most SQL-compliant databases will raise an error. Another article explaining this: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx . Therefore, the join or subquery is necessary if you wish to GROUP BY only particular columns. -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ Install Broadcom wireless card on Linux: http://lucasmanual.com/mywiki/bcm43xx --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
On Apr 11, 2008, at 4:40 PM, Lukasz Szybalski wrote: It makes sense now. I assumed that ORM object will only return 1 column I group by, and a count. When it returns the full row then my group_by makes no sense. Thanks Lucas ps. The links were really helpful ! greatglad that worked out ! --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: group_by, count, and name count field as ?
On Apr 10, 2008, at 5:11 PM, Lukasz Szybalski wrote:
Hello,
I am trying to do a simple query which gets id, count(*) group by id,
s
=
sqlalchemy
.select
([th
.RECORD_NO
,sqlalchemy
.func
.count
(th
.RECORD_NO
)],sqlalchemy
.and_
(th
.APPLIED_TEST
==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute()
This produces:
SELECT [].[RECORD_NO], count([].[RECORD_NO]) AS count_1
FROM []
WHERE [].[APPLIED_TEST] = ? AND [].[CODING_DATE] = ? GROUP BY
[].[RECORD_NO]
is there a way to replace this the 'count(*) as count_1' to whatever
name I pick? count(*) as MY_GREAT_COUNT ??!?
count('*').label('somename')
Also what would be a syntax to make a same selection using
session.query(th)?
what would you like the ORM query to return ? Object instances ?
currently, using group_by() and such with Query implies you're getting
individual column tuples back using _values(), in which case its
sess.query(THsClass).group_by(whatever)._values(col expressions).
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
