PGValues recipe and PostgreSQL JSONB data column
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: [{}]]
Comments (8)
-
repo owner -
repo owner 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
-
repo owner - changed status to closed
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.
-
reporter 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.
-
reporter - changed status to open
I'm reopening, instead of creating a new one. It seems that the
values()
does not support Date columns:After adding
mydate
column tot1
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 andtype_
is 'DATE' -
reporter One somewhat working option is to use String for
mydate
column and do the cast during query, but this unfortunately breaks withNone
values. -
reporter As a workaround for the Date column, I now use
str(date) if date else 'NULL'
and do the cast to Date in update. -
repo owner - changed status to closed
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.
- Log in to comment
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.