CODEWITHSUNDEEP
mysqlsqlalchemy
Monkey Boson
Monkey Boson

Reputation: 1292

Syntax Error in SQL Rendered by SQLAlchemy

I'm trying to replicate this query in SQLAlchemy:

select date,
sum(case when country = 'USA' or country like '%merica' then dollars else 0 end),
sum(case when country  != 'USA' and country not like '%merica' then dollars else 0 end)
from Spend
group date;

So, I have the following query object:

q = connection.session.query(Spend.date, \
        func.sum(func.case([ ( (Spend.country == 'USA') | (Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) ), \
        func.sum(func.case([ ( (Spend.country != 'USA') & (~Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) )) \
        .group_by(Spend.date)

When I run this query, I get the following exception:

sqlalchemy.exc.ProgrammingError: (ProgrammingError)
(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
\'))) AS sum_1, sum(case(((\\\\"Spend\\\\".country != :na\' at line 1')

So, clearly there's some hiccup in the rendering of the query. However, MySQL does not log queries with invalid syntax, and when I print the query in python, all I get it:

SELECT Spend.date AS Spend_Date, sum(case(%s)) AS sum_1, sum(case(%s)) AS sum_2
FROM Spend GROUP BY Spend.date
([(<sqlalchemy.sql.expression.BooleanClauseList object at 0xff6a7dec>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xff86f1ac>)], [(<sqlalchemy.sql.expression.BooleanClauseList object at 0xff6a7fec>, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xff86f1ac>)])

My question is twofold: how can I see what the rendered query looks like? (unfortunately, this answer https://stackoverflow.com/a/5698357/125193 doesn't help as it can't render BooleanClauseList). Also, does anybody have any tips on what may be wrong with my query?

Thanks!

Update

I was able to monkey-patch the error handler to get a better sense of the SQL that is being rendered.

def wrap_handler(self, e, statement, parameters, cursor, context):
    # Note: MySQLdb-specific
    print(cursor._last_executed)
    self.__handle_dbapi_exception(e, statement, parameters, cursor, context)

import types
connection.session.connection().__handle_dbapi_exception = connection.session.connection()._handle_dbapi_exception
connection.session.connection()._handle_dbapi_exception = types.MethodType(wrap_handler, connection.session.connection())

Here's the SQL:

SELECT `Spend`.date AS `Spend_date`,
sum(case((('\'\\"Spend\\".country = :name_1 OR \\"Spend\\".country LIKE :name_2\'', "'Spend.dollars'"),))) AS sum_1,
sum(case((('\'\\"Spend\\".country != :name_1 AND \\"Spend\\".country NOT LIKE :name_2\'', "'Spend.dollars'"),))) AS sum_2
FROM Spend
GROUP BY Spend.date

I'm still missing the actual parameters, but it's clear nonetheless that the SQL is messed-up. I'll re-read the docs for func.case.

Upvotes: 1

Views: 773

Answers (1)

Monkey Boson
Monkey Boson

Reputation: 1292

It seems that func has some serious black-magic around it. You can call func.anything_at_all and SQLAlchemy will try to turn it into valid SQL. The trick was to import case separately, and then call it directly.

from sqlalchemy import case, func
# ...
func.sum(case([ ( (Spend.country == 'USA') | (Spend.country.like('%merica') ), Spend.dollars ) ], else_ = 0) )
# Notice that func.case is now just case

Everything now works as expected.

Upvotes: 2

Related Questions