On 02/24/2016 10:13 AM, Alex Lowe wrote:
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 certainly had to work around this problem but the news that the raw
string works is new to me, but I would assume it has something to do
with the removal of bound parameters. ODBC actually has two different
execution APIs internally that interpret the given statement
differently, one is much more picky about being able to infer the type
of bound parameters, so that might be part of what's going on.
If i recall correctly the workaround is to make a subquery like this:
SELECT left_1, sum(different_column) FROM
(
SELECT left(some_string, ?) AS left_1, different_column
FROM [DEV].dbo.[AML_Test]
) GROUP BY left_1
so, paraphrasing
stmt = select([func.left(table.c.some_string, 5).label('left'),
table.c.different_column])
stmt = select([stmt.c.left,
func.sum(stmt.c.different_column]).group_by(stmt.c.left)
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
