How to insert JSONB using SQLAlchemy's 'execute'?

I need to run a function that takes an integer and JSONB data. In a normal console, I would run it like this:

select my_func(0, '{"foo": "bar"}'::jsonb)

This is how I assume it would work in SQLAlchemy:

params = {'p1': 0, 'p2': JSONB('{"foo": "bar"}'))}
result = db.engine.execute(text('select my_func(:p1, :p2)'), params)

But it does not. How am I supposed to get SQLAlchemy to understand that it is JSONB I am trying to insert?

Solution

JSONB is for defining column's type, not dealing with values. When using textual SQL and types that require special treatment Python or SQL side use the TextClause.bindparams() method to provide additional type information:

params = { 'p1': 0, 'p2': { "foo": "bar" } }
result = db.engine.execute(
    text('select my_func(:p1, :p2)').bindparams(
        bindparam('p2', type_=JSONB)),
    params)

Note that you should pass the dictionary without serializing it yourself. Without using bindparams() to provide the type information for SQLAlchemy your bind values are passed to the DB-API cursor pretty much as is when using textual SQL. In that light this should also work:

params = {'p1': 0, 'p2': '{"foo": "bar"}'}
result = db.engine.execute(
    text('select my_func(:p1, cast(:p2 as jsonb))'),
    params)

The DB-API driver sees a string bind value and handles that as usual. The explicit cast in SQL might also be redundant.