CODEWITHSUNDEEP
sqlalchemy
WalkingDeadFan
WalkingDeadFan

Reputation: 647

Ambiguous literal error when converting SQL Case on SELECT to SQLAlchemy format

I'm having problems translating the following CASE Statement on SELECT to SQLAlchemy ORM. I keep getting the error : "Ambiguous literal: False. Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value."

The Case statement checks a variable, and then either returns a column on the database or selects a literal. The literal is set prior to running the sql statement.

  DECLARE @str_cntry_cd varchar(3)
  SELECT COUNTRY_CD,
         CASE WHEN @str_cntry_cd IS NOT NULL THEN RESOLUTION_ID 
              ELSE 10 
           END AS RESOLUTION_ID
  FROM COUNTRY

The SQLAlchemy code looks as follows:

        country_tbl = getattr(main_db.Base.classes, "COUNTRY")
        c_res = case([(country_code != None, country_tbl.RESOLUTION_ID),],
                else_ = literal(10)).label("resolution_id")

        programs_sa = main_db.session.query(country_tbl.COUNTRY_CD.label("country_cd"),
                                            c_res).all()

The table is as follows:

  CREATE TABLE dbo.COUNTRY
  (
      COUNTRY_CD varchar(4) NOT NULL,
      RESOLUTION_ID int NOT NULL
  )

SQLAlchemy .9.9 Windows 7 SQL Server 2008 R2 Python 2.7

Upvotes: 4

Views: 3031

Answers (1)

adarsh
adarsh

Reputation: 6978

country_code != None is causing the problem. That evaluates to python's True/False, which SQLAlchemy doesn't know how to convert to SQL's true/false as it is ambiguous. So you would need to either add the literal true/false for the SQL you are using, by using SQLAlchemy's text or literal functions. But a better way would be to use the true or false functions.

http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.true http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.false

So you would replace the buggy section of the code with:

db.false() if country is None else db.true()

Hope this helps.

Upvotes: 4

Related Questions