SQLAlchemy parameter binding not

Question

I am trying to use SQLAlchemy to do some query bindings on some raw SQL (due to complexity) but it doesn't seem to be binding the named parameters properly. I'm getting an error when running the following code which works fine if I manually edit the SQL string to use the set values:

engine = create_engine('mysql://scott:tiger@localhost/foo')
vals = {
    'tag_id': 1, 
    'interval': 300,
    'start_time': datetime.datetime(2014, 5, 1, 0, 0),
    'end_time': datetime.datetime(2014, 6, 1, 0, 0)
}

sql = """SELECT
                FROM_UNIXTIME( CEIL( UNIX_TIMESTAMP( event_time ) / :interval ) * :interval ) AS pseudo_time,
                AVG( value ) AS average_value
            FROM  `data_point_double`
            WHERE
              (tag_id = :tag_id) AND
              (event_time BETWEEN :start_time AND :end_time)
            GROUP BY CEIL( UNIX_TIMESTAMP( event_time ) /  :interval  ), tag_id"""
result = engine.execute(sql, **vals)

this results in the following error:

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 ':interval ) * :interval ) AS pseudo_time, ...

the logs seem to indicate that the parameters are being passed:

2014-05-26 23:23:55,506 INFO sqlalchemy.engine.base.Engine SELECT
                FROM_UNIXTIME( CEIL( UNIX_TIMESTAMP( event_time ) / :interval ) * :interval ) AS pseudo_time,
                AVG( value ) AS average_value
            FROM  `data_point_double`
            WHERE
              (tag_id = :tag_id) AND
              (event_time BETWEEN :start_time AND :end_time)
            GROUP BY CEIL( UNIX_TIMESTAMP( event_time ) / :interval ), tag_id
2014-05-26 23:23:55,506 INFO sqlalchemy.engine.base.Engine {'start_time': datetime.datetime(2014, 5, 1, 0, 0), 'interval': 300, 'end_time': datetime.datetime(2014, 6, 1, 0, 0), 'tag_id': 1L}
2014-05-26 23:23:55,506 INFO sqlalchemy.engine.base.Engine ROLLBACK

Am I using named parameters correctly or is this a bigger problem? I have tried mysql+mysqldb as the engine as well but this didn't work either.

AndrewWhalan · Accepted Answer · 2014-05-26 14:29:16Z

1

Just before posting this I found the solution, use text to wrap the query so this:

result = engine.execute(sql, **vals)

becomes this:

from sqlalchemy.sql import text
result = engine.execute(text(sql), **vals)

Hopefully the content with the error message will help someone find this solution!

answered May 26, 2014 at 14:29

AndrewWhalan

4734 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy parameter binding not

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related