Hi there,
I'm receiving a ProgrammingError with certain types of query to MSSQL (they
seem to work fine when querying SQLite though). Either my Google-fu is weak
or there hasn't been a solution posted publicly, since the two most
useful-looking pages were these two StackOverflow threads with no useful
responses:
http://stackoverflow.com/questions/18307466/group-by-case-in-sql-server-sqlalchemy
http://stackoverflow.com/questions/21742713/need-a-query-in-sqlalchemy-with-group-by-case
I'm also very new to SQLAlchemy and have mostly picked it up through a
combination of web searches and following the examples of my coworkers (who
picked it up by doing web searches for what they needed), so advice on how
to make my example code better are welcome.
I've got a table that contains a string column and an integer column, and
I'm trying to group by substrings. In so doing, it brings up an error
message about aggregate functions in the group by clause. Specifically, if
I write this code:
test_table = sqlalchemy.Table('AML_Test', dev_schema)
some_string = sqlalchemy.Column('some_string',
sqlalchemy.VARCHAR(length=50))
different_column = sqlalchemy.Column('different_column', sqlalchemy.INT())
partial = func.left(some_string, 3)
aggregate = func.sum(different_column)
qq = test_table.select().group_by(partial).column(partial).column(aggregate)
and then run qq.execute(), pyodbc gives me the follow error message:
ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC
SQL Server Driver][SQL Server]Column 'DEV.dbo.AML_Test.some_string' is invalid
in the select list because it is not contained in either an aggregate function
or the GROUP BY clause. (8120) (SQLExecDirectW)") [SQL: 'SELECT
left(some_string, ?) AS left_1, sum(different_column) AS sum_1 \nFROM
[DEV].dbo.[AML_Test] GROUP BY left(some_string, ?)'] [parameters: (3, 3)]
My workaround for the moment is to cast the compiled statement to a string and
execute that string, but it's unclear to me why that would do anything
different (despite the fact that it does).
c.session.execute(
str(qq.selectable.compile(compile_kwargs={'literal_binds': True}))
).fetchall()
If anyone can explain to me what I'm doing wrong and how to fix it, I'd be
extremely grateful.
Thanks,
Alex
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
