CODEWITHSUNDEEP
pythondatabasesqlalchemypyodbc
adm1196
adm1196

Reputation: 42

How do I add numbers in a ORM query in SQLAlchemy?

I am trying to fire a DATEDIFF query with SQLAlchemy ORM where I need to add a variable to the output of DATEDIFF

ORM:

query = session.query(func.datediff(text("ss"), some_date, another_date) + 1000).all()

Desired SQL:

SELECT DATEDIFF(SS, some_date, another_date) + 1000 FROM Table

Executing the above ORM query outputs SQL as

 SELECT DATEDIFF(SS, some_date, another_date) || '' AS anon_1 FROM Table

and throws an ProgrammingError "Implicit conversion from datatype 'VARCHAR' to 'INT' not allowed. Use the CONVERT function to run this query"

I have tried using label() with func.datediff() but it did not yield expected results. How can I make this query work ?

Upvotes: 0

Views: 345

Answers (1)

UjinT34
UjinT34

Reputation: 4987

SQLAlchemy assumes that func.datediff returns aString. You need to specify the type:

func.datediff(text("ss"), some_date, another_date, type_=Integer) + 1000

or force the + operator:

func.datediff(text("ss"), some_date, another_date).op('+')(1000)

Upvotes: 1

Related Questions