sqlalchemy.exc.UnsupportedCompilationError JSONB

[Roman D.]

Trying to compile query:

_update = str(S.__table__.update().where(and_(*_filters)).values({col.name: func.jsonb_concat(col,func.cast(bindparam(bind_name), JSONB))}))

and getting error:

sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.GenericTypeCompiler object at 0x108cd6110> can't render element of type <class 'sqlalchemy.dialects.postgresql.json.JSONB'>

if I remove cast then sqlalchemy trying to cast dict to HSTORE

[Mike Bayer]

JSONB is a postgresql specific type so you need to compile with the PG dialect:

str(stmt.compile(dialect=postgresql.dialect()))

[Mike Bayer]

see #3081

[Roman D.]

ooops, sorry, really though thought sqlalchemy knows it from "S(Base)"

[Mike Bayer]

it will, #3081 will improve this at some point

[Roman D.]

btw, str(stmt.compile(dialect=postgresql.dialect())) returns different string, I always was getting params like ":b_state" now they all compiled and I see "%(b_state)s"

[Mike Bayer]

yes because that's the paramstyle accepted by the psycopg2 dialect.

[Roman D.]

now I have to escape all params by myself? :) hope for something like passing them to execute(stmt, **params) :)

[Mike Bayer]

i dont see why, just connection.execute() the statement. the string format here is not necessary as something you need to access explicitly.

[Roman D.]

Long story...I use statement where i can, but I'm using partitioning in PG and for update queries I'm generating string where I change table name

_update = str(S.__table__.update().where(and_(*_filters)).values(_values)).replace(S.__tablename__, '%(tablename)s'))

then I do:

execute(text(_update % {'tablename': tablename}), params)

couldn't made up a better solution.

[Mike Bayer]

See the recipe at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionModifiedSQL which illustrates using before_cursor_execute() along with connection.info to intercept table names and search and replace them.

[Roman D.]

Thank you, definitely will try.

[Roman D.]

can't make statement with returning

S.__table__.update().where(and_(*_filters)).values(_values).returning(S.id)

where should I put dialect name? Really can't find anything in docs :(

[Mike Bayer]

http://docs.sqlalchemy.org/en/rel_1_0/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined

[Roman D.]

Right now I'm not trying to render expression as string. I'm just trying to build update statement with "returning".

[Mike Bayer]

the returning() method as you have above

plenty of docs:

http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html?highlight=returning#sqlalchemy.sql.expression.Update.returning

[Roman D.]

I'm sorry, my mistake, copypasted code without buildng str from statement but was actually building it in my code. Sorry for stupid questions :)