Getting SQL syntax error while using case when in sqlalchemy

Question

I am using case when in sqlAlchemy as shown below:

abc = "%abc%"
def = "%def%"
proj1 = "%project1%"
proj2 = "%project2%"
case_condition = case([
        (text('FPro.status = "ON" and Ab.name.like (''' + abc + ''') and F.project.like (''' + proj1 ''')'''), 'value1'),
        (text('FPro.status = "ON" and Ab.name.like (''' + abc + ''') and F.project.like (''' + proj2 + ''')'''), 'value2'),
        (text('FPro.status = "OFF" and Ab.name.like (''' + def + ''') and F.project.like (''' + abc + ''')'''), 'value3')]).label (deriver_vals)

query = db.session.query(F)\
        .join(FPro, F.id == FPro.f_id)\
        .join(Ab, Ab.id == F.ab_id).with_entities(FPro.f_id, case_condition,
                                                                    F.f_name, F.proj,
                                                                    FPro.status).subquery()

main_query = db.session.query(Tags).join(query, Tags.tag == query.c.derived_vals).\
        with_entities(query.c.f_id.label('f_id'), query.c.derived_vals.label('derived_vals'), Tags.id.label('tag_id')).all()

The above code generates a sql statement like below:

SELECT anon_1.f_id AS f_id, anon_1.derived_vals AS derived_vals, fw_tags.id AS tag_id 
FROM fw_tags INNER JOIN (SELECT fpro.f_id AS f_id, CASE WHEN FPro.status = "ON" and Ab.name.like (%%abc%%) and F.project.like (%%proj1%%) THEN %(param_1)s WHEN FPro.status = "ON" and Ab.name.like ( %%abc%%) and Flow.project.like ( %%proj2%%) THEN %(param_2)s WHEN FPro.status = "ON" and Ab.name.like (%%def%%) and F.project.like (%%proj1%%) THEN %(param_3)s END AS derived_vals, F.f_name , F.proj AS project,  
FROM F INNER JOIN FPro ON f.id = Fpro.f_id INNER JOIN Ab ON Ab.id = F.ab_id) AS anon_1 ON fw_tags.tag = anon_1.derived_vals]

This is exactly the query I want but I am getting below error while executing the script which contains above code:

sqlalchemy.exc.ProgrammingError: (pymysql.err.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 '(%abc%) and F.project.like (%proj1%) THEN 'value1' WHEN FPro' at line 2")

I am guessing the error is with the '%' getting appended, but I am not sure, so any help regarding why this error is occuring or what can be done to prevent the % getting added will be appreciated.

Is there a way to do this without using text or literal()?

Your sql statement generates "(%%abc%%) and F.project.like (%%proj1%%)", while your error shows that the statement is being interpreted as "(%abc%) and F.project.like (%proj1%)" - note one of the wildcards is missing. Some quick googling has led to this question https://stackoverflow.com/questions/58040032/how-to-use-wild-cards-in-sqlalchemy, which may help you to debug your code — bm13563, Sep 01 '20 at 11:06

Ilja Everilä · Answer 1 · 2020-09-01T19:17:31.500

In standard SQL "ON" is a delimited/quoted identifier, not a (text) literal, identifying a column, table, or some other schema object. Use single quotes instead: 'ON'. Some DBMS have modes that allow using double quotes for literals, or even attempt to infer the meaning from context, but perhaps it is not a good idea to get into the habit.

In Python 'FPro.status ...''' is the concatenation of 2 string literals, 'FPro.status ...' and '', not a single literal with an escaped single quote in it.

Please do not concatenate or otherwise manually format values to SQL queries, it is error prone as you have found out. It should be obvious from the generated SQL that the values are concatenated as is, without proper quoting, and so produce the incorrect statement. The correct way to pass values to (raw) SQL queries is to use placeholders, or in case of SQLAlchemy, use the SQL Expression Language.

Using placeholders:

abc = "%abc%"
def_ = "%def%"
proj1 = "%project1%"
proj2 = "%project2%"
# Using **raw SQL** fragments
case_condition = case([
    (text("FPro.status = 'ON' AND Ab.name LIKE :abc AND F.project LIKE :proj1"), 'value1'),
    (text("FPro.status = 'ON' AND Ab.name LIKE :abc AND F.project LIKE :proj2"), 'value2'),
    (text("FPro.status = 'OFF' AND Ab.name LIKE :def_ AND F.project LIKE :abc"), 'value3')
])

query = db.session.query(F)\
    .join(FPro, F.id == FPro.f_id)\
    .join(Ab, Ab.id == F.ab_id)\
    .with_entities(
        FPro.f_id,
        case_condition.label('derived_vals'),
        F.f_name,
        F.proj,
        FPro.status)\
    .subquery()

main_query = db.session.query(Tags)\
    .join(query, Tags.tag == query.c.derived_vals)\
    .with_entities(
        query.c.f_id.label('f_id'),
        query.c.derived_vals.label('derived_vals'),
        Tags.id.label('tag_id'))\
    .params(abc=abc, def_=def_, proj1=proj1, proj2=proj2)\
    .all()

Using the expression language:

from sqlalchemy import and_

abc = "%abc%"
def_ = "%def%"
proj1 = "%project1%"
proj2 = "%project2%"
# Using SQLAlchemy SQL Expression Language DSL **in Python**
case_condition = case([
    (and_(FPro.status == 'ON', Ab.name.like(abc), F.project.like(proj1)), 'value1'),
    (and_(FPro.status == 'ON', Ab.name.like(abc), F.project.like(proj2)), 'value2'),
    (and_(FPro.status == 'OFF', Ab.name.like(def_), F.project.like(abc)), 'value3')
])

query = db.session.query(F)\
    .join(FPro, F.id == FPro.f_id)\
    .join(Ab, Ab.id == F.ab_id)\
    .with_entities(
        FPro.f_id,
        case_condition.label('derived_vals'),
        F.f_name,
        F.proj,
        FPro.status)\
    .subquery()

main_query = db.session.query(Tags)\
    .join(query, Tags.tag == query.c.derived_vals)\
    .with_entities(
        query.c.f_id.label('f_id'),
        query.c.derived_vals.label('derived_vals'),
        Tags.id.label('tag_id'))\
    .all()

What do you mean by "without using `text()` or `literal()` as in my question"? Is that not using the expression language / ORM? — Ilja Everilä, Sep 01 '20 at 12:52

Getting SQL syntax error while using case when in sqlalchemy

1 Answers1