CODEWITHSUNDEEP
pythonmysqlsqlalchemyliteralsfunc
kyle
kyle

Reputation: 129

sqlalchemy syntax for using mysql constants

So i am trying to call the mysql function TIMEDATEDIFF with the SECOND constant as the first parameter like so

    query = session.query(func.TIME(Log.IncidentDetection).label('detection'), func.TIMESTAMPDIFF(SECOND,Log.IncidentDetection, Log.IncidentClear).label("duration")).all()
    print(query)

I have tried it as a string and I get a mysql/mariadb error:

query = session.query(func.TIME(Log.IncidentDetection).label('detection'), func.TIMESTAMPDIFF("SECOND",Log.IncidentDetection, Log.IncidentClear).label("duration")).all()
print(query)

Gives me this

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''SECOND', log.`IncidentDetection`, log.`IncidentClear`) AS duration 
FROM log' at line 1 [SQL: 'SELECT TIME(log.`IncidentDetection`) AS detection, TIMESTAMPDIFF(%(TIMESTAMPDIFF_1)s, log.`IncidentDetection`, log.`IncidentClear`) AS duration \nFROM log'] [parameters: {'TIMESTAMPDIFF_1': 'SECOND'}]

I am sure it is something simple, some sort of escape sequence or import that I am missing. I have looked through the sqlalchemy documentation to no avail.

Upvotes: 0

Views: 662

Answers (1)

kyle
kyle

Reputation: 129

To get sqlalchemy to parse the string exactly into the query I used the _literal_as_text() function

Working solution

from sqlalchemy.sql.expression import func, _literal_as_text
# ...
query = session.query(func.TIME(Log.IncidentDetection).label('detection'), func.TIMESTAMPDIFF(_literal_as_text("SECOND"),Log.IncidentDetection, Log.IncidentClear).label("duration")).all()
print(query)

Upvotes: 1

Related Questions