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
-~----------~----~----~----~------~----~------~--~---
