Hi,
Thanks for SQLAlchemy. I'm trying to insert some JSONB into a PostgreSQL
database. My insert succeeds if the column type is JSON but fails if the
type is JSONB.
SQLAlchemy version: sqlalchemy-1.0.13
PostgreSQL: 9.5.3
DBAPI: py-postgresql
Python: 3.4
OS: Windows 8.1 64-bit
Here's my test code:
import postgresql
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import MetaData
from sqlalchemy.dialects.postgresql import JSON, JSONB
from datetime import datetime, tzinfo, timedelta
engine=create_engine("postgresql+pypostgresql://postgres:*******@localhost:5432/dbname")
NAMING_CONVENTION = {
"ix": 'ix_%(column_0_label)s',
"uq":"uq_%(table_name)s_%(column_0_name)s",
"ck":"ck_%(table_name)s_%(constraint_name)s",
"fk":"fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk":"pk_%(table_name)s"
}
metadata = MetaData(naming_convention=NAMING_CONVENTION)
Base = declarative_base(metadata=metadata)
class JSON_test(Base):
__tablename__ = 'json_test'
json_id = Column(Integer, primary_key=True)
history = Column(JSON)
class JSONB_test(Base):
__tablename__ = 'jsonb_test'
jsonb_id = Column(Integer, primary_key=True)
history = Column(JSONB)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(JSON_test(history={"entry":{"user":"andrew","datetime" :
"{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}}))
session.commit()
# This JSON object is identical to the last
session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" :
"{datetime}".format(datetime=datetime.now()),"reason":"init","description":"init","source":"andrew"}}))
session.commit()
After running this code, the JSON_test commit succeeds but the JSONB_test
commit throws this error:
>>> session.add(JSONB_test(history={"entry":{"user":"andrew","datetime" :
"{datetime}".format(datetime=datetim
e.now()),"reason":"init","description":"init","source":"andrew"}}))
>>> session.commit()
Traceback (most recent call last):
File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line
92, in __next__
r = self.buf[self.pos]
IndexError: list index out of range
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line
1019, in fetchone
row = self._fetchone_impl()
File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line
900, in _fetchone_impl
return self.cursor.fetchone()
File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line
187, in fetchone
return next(self._portal)
File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line
100, in __next__
self.buf = next(self.chunks)
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009,
in __next__
self.database._pq_complete()
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604,
in _pq_complete
self.typio.raise_error(x.error_message, cause = getattr(x, 'exception',
None))
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541,
in raise_error
self.raise_server_error(error_message, **kw)
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532,
in raise_server_error
raise server_error
postgresql.exceptions.InternalError: unsupported jsonb version number 123
CODE: XX000
LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER
RESULT:
chunksize: 4096
type: MultiXactInsideBlock
parameters:
('{"entry": {"user": "andrew", "datetime": "2016-06-01
09:07:30.502703", "reason": "init", "description":
"init", "source": "andrew"}}',)
cursor_id: py:0x4289550
STATEMENT: [prepared]
sql_parameter_types: ['"pg_catalog"."jsonb"']
results: ('jsonb_id' 'INTEGER')
statement_id: py:0x42d16a0
string:
INSERT INTO jsonb_test (history) VALUES ($1) RETURNING
jsonb_test.jsonb_id
CONNECTION: [idle in block]
client_address: 127.0.0.1/32
client_port: 64593
version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit
CONNECTOR: [Host] pq://postgres:***@localhost:5432/products
category: None
DRIVER: postgresql.driver.pq3.Driver
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 801,
in commit
self.transaction.commit()
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 392,
in commit
self._prepare_impl()
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 372,
in _prepare_impl
self.session.flush()
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line
2019, in flush
self._flush(objects)
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line
2137, in _flush
transaction.rollback(_capture_exception=True)
File "C:\Python34\lib\site-packages\sqlalchemy\util\langhelpers.py", line
60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 186,
in reraise
raise value
File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line
2101, in _flush
flush_context.execute()
File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line
373, in execute
rec.execute(self)
File "C:\Python34\lib\site-packages\sqlalchemy\orm\unitofwork.py", line
532, in execute
uow
File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line
174, in save_obj
mapper, table, insert)
File "C:\Python34\lib\site-packages\sqlalchemy\orm\persistence.py", line
800, in _emit_insert_statements
execute(statement, params)
File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line 914,
in execute
return meth(self, multiparams, params)
File "C:\Python34\lib\site-packages\sqlalchemy\sql\elements.py", line
323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line
1010, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line
1159, in _execute_context
result = context._setup_crud_result_proxy()
File "C:\Python34\lib\site-packages\sqlalchemy\engine\default.py", line
826, in _setup_crud_result_proxy
row = result.fetchone()
File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line
1028, in fetchone
self.cursor, self.context)
File "C:\Python34\lib\site-packages\sqlalchemy\engine\base.py", line
1341, in _handle_dbapi_exception
exc_info
File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 202,
in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "C:\Python34\lib\site-packages\sqlalchemy\util\compat.py", line 185,
in reraise
raise value.with_traceback(tb)
File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line
1019, in fetchone
row = self._fetchone_impl()
File "C:\Python34\lib\site-packages\sqlalchemy\engine\result.py", line
900, in _fetchone_impl
return self.cursor.fetchone()
File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line
187, in fetchone
return next(self._portal)
File "C:\Python34\lib\site-packages\postgresql\driver\dbapi20.py", line
100, in __next__
self.buf = next(self.chunks)
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 1009,
in __next__
self.database._pq_complete()
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 2604,
in _pq_complete
self.typio.raise_error(x.error_message, cause = getattr(x, 'exception',
None))
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 541,
in raise_error
self.raise_server_error(error_message, **kw)
File "C:\Python34\lib\site-packages\postgresql\driver\pq3.py", line 532,
in raise_server_error
raise server_error
sqlalchemy.exc.InternalError: (postgresql.exceptions.InternalError)
unsupported jsonb version number 123
CODE: XX000
LOCATION: File 'jsonb.c', line 122, in jsonb_recv from SERVER
RESULT:
chunksize: 4096
type: MultiXactInsideBlock
parameters:
('{"entry": {"user": "andrew", "datetime": "2016-06-01
09:07:30.502703", "reason": "init", "description":
"init", "source": "andrew"}}',)
cursor_id: py:0x4289550
STATEMENT: [prepared]
sql_parameter_types: ['"pg_catalog"."jsonb"']
results: ('jsonb_id' 'INTEGER')
statement_id: py:0x42d16a0
string:
INSERT INTO jsonb_test (history) VALUES ($1) RETURNING
jsonb_test.jsonb_id
CONNECTION: [failed block]
client_address: 127.0.0.1/32
client_port: 64593
version: PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit
CONNECTOR: [Host] pq://postgres:***@localhost:5432/products
category: None
DRIVER: postgresql.driver.pq3.Driver
My apologies if I'm handling the JSONB insert incorrectly, but from the docs
<http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#postgresql-data-types>,
it seems that the JSON and JSONB inserts are exactly the same syntax?
Thanks,
Andrew
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
