PGValues recipe and PostgreSQL JSONB data column

[Priit Laes]

I'm running into troubles with JSONB field that's updated via UPDATE ... FROM VALUES (...) clause.

I have taken the PGValues example from wiki and added JSONB column called 'myjson' and I'm trying to update but it causes crash on both 1.0.4 and 0.9.8

Traceback (most recent call last):
  File "pgvalues.py", line 63, in <module>
    update(dict(mytext=t2.c.mytext, myint=t2.c.myint, myjson={'int': t2.c.myint}))
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2949, in update
    update_op.exec_()
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1074, in exec_
    self._do_exec()
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1223, in _do_exec
    mapper=self.mapper)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1023, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1078, in _execute_context
    None, None)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception
    exc_info
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 590, in _init_compiled
    for key in compiled_params
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 590, in <genexpr>
    for key in compiled_params
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/json.py", line 211, in process
    return json_serializer(value).encode(encoding)
  File "/usr/lib64/python2.7/json/__init__.py", line 243, in dumps
    return _default_encoder.encode(obj)
  File "/usr/lib64/python2.7/json/encoder.py", line 207, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/lib64/python2.7/json/encoder.py", line 270, in iterencode
    return _iterencode(o, 0)
  File "/usr/lib64/python2.7/json/encoder.py", line 184, in default
    raise TypeError(repr(o) + " is not JSON serializable")
sqlalchemy.exc.StatementError: (exceptions.TypeError) <sqlalchemy.sql.elements.ColumnClause at 0x7fb44a267e90; myint> is not JSON serializable [SQL: u"UPDATE mytable SET myjson=%(myjson)s, mytext=myvalues.mytext, myint=myvalues.myint FROM (VALUES (1, 'textA', 99), (2, 'textB', 88)) AS myvalues (mykey, mytext, myint) WHERE mytable.mykey = myvalues.mykey"] [parameters: [{}]]

[Mike Bayer]

well you're passing in a Python dictionary with a column expression in it, there's no way to intercept that and figure out it should be a SQL expression, you need to use the literal syntax. I'm looking to see waht that is in this case.

[Mike Bayer]

it's not pretty but this is how it can work

    sess.query(T).filter(T.mykey == t2.c.mykey).\
        update(
            dict(
                mytext=t2.c.mytext,
                myint=t2.c.myint,
                myjson=cast(func.to_json(
                    literal_column("'{\"int\":'") +
                    cast(t2.c.myint, String) +
                    literal_column("'}'")
                ), JSONB)
            ), synchronize_session=False)

output:

UPDATE mytable SET myjson=CAST(to_json('{"int":' || CAST(myvalues.myint AS VARCHAR) || '}') AS JSONB), mytext=myvalues.mytext, myint=myvalues.myint FROM (VALUES (1, 'textA', 99), (2, 'textB', 88)) AS myvalues (mykey, mytext, myint) WHERE mytable.mykey = myvalues.mykey

[Mike Bayer]

this is very complex on the PG SQL side, as it doesn't have a dedicated syntax I'm aware of for "key value set" except for that of HSTORE which we support, so this is the best we can do for the moment with JSON.

[Priit Laes]

Duh.. I am stupid. JSONB fields are not supposed (at least up to 9.4) to be used like that. I should've read the Postgres manual a bit more, before I created this issue.

[Priit Laes]

I'm reopening, instead of creating a new one. It seems that the values() does not support Date columns:

After adding mydate column to t1 table:

     Column('mydate', Date),

And adding the values construct Date column:

    from datetime import date
    t2 = values(
            [
                column('mykey', Integer),
                column('mytext', String),
                column('myint', Integer),
                column('mydate', Date),
            ],

            (1, 'textA', 99, date(1983, 10, 31)),
            (2, 'textB', 88, date(1999, 10, 10)),
            alias_name='myvalues'
        )
    e = create_engine("postgresql://@/kabushiki_test", echo=True)
    m1.create_all(e)
    sess = Session(e)
    sess.query(T).filter(T.mykey==t2.c.mykey).\
            update(dict(mytext=t2.c.mytext, myint=t2.c.myint, mydate=t2.c.mydate))

Causes it to crash with following backtrace:

 File "/home/plaes/code/kabushiki/kabushiki/distributors/__init__.py", line 184, in sync_stock
    db.session.execute(q)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 150, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1023, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1003, in _execute_clauseelement
    inline=len(distilled_params) > 1)
  File "<string>", line 1, in <lambda>
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 500, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 392, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1986, in visit_update
    dialect_hints, **kw)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1923, in update_from_clause
    for t in extra_froms)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1923, in <genexpr>
    for t in extra_froms)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py", line 413, in <lambda>
    lambda *arg, **kw: existing(*arg, **kw))
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py", line 451, in __call__
    return fn(element, compiler, **kw)
  File "/home/plaes/code/kabushiki/kabushiki/utils.py", line 43, in compile_values
    for tup in element.list
  File "/home/plaes/code/kabushiki/kabushiki/utils.py", line 43, in <genexpr>
    for tup in element.list
  File "/home/plaes/code/kabushiki/kabushiki/utils.py", line 42, in <genexpr>
    for elem, column in zip(tup, columns))
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1425, in render_literal_value
    value = super(PGCompiler, self).render_literal_value(value, type_)
  File "/home/plaes/.virtualenvs/kabushiki/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1116, in render_literal_value
    "Don't know how to literal-quote value %r" % value)
NotImplementedError: Don't know how to literal-quote value datetime.date(2015, 6, 2)

Parameters to the render_literal_value are following: value is datetime object and type_ is 'DATE'

[Priit Laes]

One somewhat working option is to use String for mydate column and do the cast during query, but this unfortunately breaks with None values.

[Priit Laes]

As a workaround for the Date column, I now use str(date) if date else 'NULL' and do the cast to Date in update.

[Mike Bayer]

lets try to be organized here. that's not at all the same issue that was first reported here. Also, your recipe is calling upon "render_literal_values()". This function does not support dates in the PG dialect so there is nothing unexpected here. You should be using normal bound parameters.