sqlalchemy-cockroachdb: Cockroachdb sqlalchemy dialect doesn't fully support JSONB

I’m testing recently added JSONB.

Setup:

python3.6
psycopg2==2.7.4
SQLAlchemy==1.2.5
cockroachdb v2.0.1

I was able to save a sample data into the database. This is how it looks in a dump file:

{
    "id" : "2a4b4578-1a5d-455b-9317-c90c5f3afe15",
    "enabled" : true,
    "language" : "en-us",
    "ui_metadata" : "{\"origin\": \"111\", \"ui\": null, \"version\": null}",
}

I’m now loading data with sqlalchemy session.query.all() Error:

    return json_deserializer(value)
  File "/usr/local/lib/python3.6/json/__init__.py", line 348, in loads
    'not {!r}'.format(s.__class__.__name__))
TypeError: the JSON object must be str, bytes or bytearray, not 'dict'

https://bitbucket.org/zzzeek/sqlalchemy/src/7541d9b258e4d0c115d6160592ac7e17f8fd50cc/lib/sqlalchemy/sql/sqltypes.py?at=master&fileviewer=file-view-default#sqltypes.py-2145:2155 https://github.com/cockroachdb/cockroachdb-python/blob/master/cockroachdb/sqlalchemy/dialect.py

This line fails:

json_deserializer = dialect._json_deserializer or json.loads

Because there is no _json_deserializer and it tries to call json.loads on a python dict.

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 18 (9 by maintainers)

Most upvoted comments

I’ve just uploaded a new version to pypi.

bdarnell on May 9, 2018

@bdarnell

I’m not using JSON 😃

from sqlalchemy.dialects.postgresql import JSONB

class myClass(Base):
    my_field = Column(JSONB, nullable=False, default=lambda: {})

I’m using dbeaver as a gui tool and it says that data type of that column is jsonb. So when my code throws TypeError: the JSON object must be str, bytes or bytearray, not 'dict', it somehow detects JSON instead of JSONB.

desprit on May 7, 2018

@bdarnell

Hi, sorry for slow replies. Yes, it does:

[sqlalchemy.dialects]
cockroachdb = cockroachdb.sqlalchemy.dialect:CockroachDBDialect

However, I’m now facing initial issue again:

    dict_[key] = getter(row)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 2156, in proc

    return json_deserializer(value)
  File "/usr/local/lib/python3.6/json/__init__.py", line 348, in loads
    'not {!r}'.format(s.__class__.__name__))
TypeError: the JSON object must be str, bytes or bytearray, not 'dict'

This part:

json_deserializer = dialect._json_deserializer or json.loads

returns “json.loads” function. There is no json_deserializer in dialect file: https://github.com/cockroachdb/cockroachdb-python/blob/master/cockroachdb/sqlalchemy/dialect.py

Update: I found json_deserializer here: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2210 will continue checking…

Update2: Yes, here json_deserializer is None as well: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/base.py#L2211

desprit on May 4, 2018

@desprit You need version 0.1.3 of the cockroachdb package for JSONB support. I think you need to use pip install -U cockroachdb to upgrade it.

@LouisBarranqueiro If you’re seeing this with version 0.1.3, there must be something else going on. However, I can’t reproduce it. I’ve added a version of our JSONB test that uses the ORM mode (instead of the query-builder mode) and it passes when I run with the versions you specify (except that I used python 3.6, but I don’t think that should matter). Are you sure that cockroachdb 0.1.3 is the version you have installed in this python environment?

bdarnell on May 3, 2018