sqlalchemy : executing raw sql with parameter bindings

Question

I'm trying to run this simple raw sql statement with parameters with SQLALchemy (within an alembic script) :

from alembic import op

t = {"code": "123", "description": "one two three"}

op.execute("insert into field_tags (id, field_id, code, description) "+
               "values (1,'zasz', :code ,:description')", t)

And I get the following error :

sqlalchemy.exc.StatementError: A value is required for bind parameter 
  'description' (original cause: InvalidRequestError: A value is required for 
  bind parameter 'description') "insert into field_tags (id, field_id, code, 
  description) values (1, 'math', 
  %(code)s ,%(description)s)" []

The solution:

t = {"code": "123", "description": "one two three"}
from sqlalchemy.sql import text

op.get_bind().execute(text("insert into field_tags (id, field_id, code, description) "+
               "values (1,'zasz', :code ,:description')"), **t)

score 75 · Accepted Answer · edited Nov 29 '17 at 16:04

75

You need to get the connection object, call execute() on it and pass query parameters as keyword arguments:

from alembic import op
from sqlalchemy.sql import text

conn = op.get_bind()
conn.execute(
    text(
        """
            insert into field_tags 
            (id, field_id, code, description) 
            values 
            (1, 'zasz', :code , :description)
        """
    ), 
    **t
)

Also see: How to execute raw SQL in SQLAlchemy-flask app.

edited Nov 29 '17 at 16:04

André C. Andersen

8,955
3
53
79

answered Apr 21 '14 at 21:50

alecxe

462,703
120
1,088
1,195

2

I tried this (passing **t as argument) and got : TypeError: execute() got an unexpected keyword argument 'code' – Max L. Apr 21 '14 at 23:44
@MaxL., my bad, could you try the code from the updated answer? The idea is to get the connection object and call `execute()` on it. – alecxe Apr 22 '14 at 13:35
Thanks, that helped, there was another change I had to make : the query must be wrapped by the text function (from sqlalchemy.sql import text), gave a +1 to your answer, it you add the text() wrapp, (like in my update above) I'll accept it as the definitive answer. – Max L. Apr 22 '14 at 14:43
7

The solution is almost correct. It still gives a 'got an unexpected keyword argument' error. Change '**t' to 't', and it works. – Aneil Mallavarapu Nov 17 '14 at 15:52
3

@AneilMallavarapu Note that `execute(..)` from [Session](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.execute) is different from the one in [Connection](http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Connection.execute) or Engine. – André C. Andersen Nov 29 '17 at 16:13

sqlalchemy : executing raw sql with parameter bindings

1 Answers1