Thank you! That worked, with one minor modification.
It looks like MSSQL requires an alias for an anonymous table (even if
you're not joining it with anything). As such, I needed to add .alias() to
the query.
In the end, it looked like this:
q3 = sqlalchemy.select([partial.label('left_string'),
different_column]).alias()
q4 = sqlalchemy.select([q3.c.left_string,
func.sum(q3.c.different_column)]).group_by(q3.c.left_string)
sql_engine.execute(q4).fetchall()
On Wednesday, 24 February 2016 09:26:59 UTC-6, Mike Bayer wrote:
>
>
>
> 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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.
