support psycopg2cffi features under their own versioning scheme

[Pablo Marti]

Hi there,

I have a User model class with a profile attribute marked as mutable

class User(db.Model):
    ...
    profile = db.Column(MutableDict.as_mutable(JSONB()))

This works perfectly under python2.7, but now when I issue a query using pypy 2.6 I get this:

Traceback (most recent call last):
  File "/Users/pablo/.virtualenvs/drop/src/flask/flask/app.py", line 1537, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/pablo/.virtualenvs/drop/src/flask/flask/app.py", line 1523, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/pablo/Development/drop-server/drop/api/decorators.py", line 73, in decorated_function
    .get(user_id)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 818, in get
    return self._get_impl(ident, loading.load_on_ident)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 851, in _get_impl
    return fallback_fn(self, key)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 217, in load_on_ident
    return q.one()
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/query.py", line 2472, in one
    ret = list(self)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 69, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/orm/loading.py", line 430, in _instance
    state.manager.dispatch.load(state, context)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/event/attr.py", line 258, in __call__
    fn(*args, **kw)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 428, in load
    val = cls.coerce(key, val)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 638, in coerce
    return Mutable.coerce(key, value)
  File "/Users/pablo/.virtualenvs/drop/site-packages/sqlalchemy/ext/mutable.py", line 403, in coerce
    raise ValueError(msg % (key, type(value)))
ValueError: Attribute 'profile' does not accept objects of type <type 'list'>

Dependencies:

SQLAlchemy==1.0.4 psycopg2cffi==2.7.0

[Mike Bayer]

Duplicate of #3427.

[Mike Bayer]

Hello -

this is likely a duplicate of #3427. Feel free to confirm this against latest master as 1.0.5 is not yet released. I apologize for closing this however without a test case I cannot confirm anything on my end, thanks.

[Pablo Marti]

Hi Mike,

apologies for not providing a test case, I've tried again and still experiencing the same problem with master. I'll try to create a contained test case and get back to you. Thanks

[Mike Bayer]

OK, psycopg2cffi doesn't have JSONB support, psycopg2 does. Apparently the fact that we assume the same versioning in the psycopg2cffi dialect is a mistake b.c. psycopg2cffi is already up to 2.7, psycopg2 is only on 2.6.

workaround:

e = create_engine("postgresql+psycopg2cffi://scott:tiger@localhost/test", echo='debug')
e.connect().close()  # this has to be called first before setting the flag
e.dialect._has_native_jsonb = False

[Mike Bayer]

• Repaired some typing and test issues related to the pypy psycopg2cffi dialect, in particular that the current 2.7.0 version does not have native support for the JSONB type. The version detection for psycopg2 features has been tuned into a specific sub-version for psycopg2cffi. Additionally, test coverage has been enabled for the full series of psycopg2 features under psycopg2cffi. fixes #3439

→ <<cset 4e6ec9eef4e6>>

[Mike Bayer]

it's a little weird that your error message reports that its getting a list. So there is still a slight chance you have something else going on also. Please confirm latest master works, thanks!

[Pablo Marti]

@zzzeek thanks for the reply and the time! I'm still experiencing the same problems. I've cooked a small test case that shows the problem

# dependencies:
# flask
# flask-sqlalchemy
# psycopg2cffi==2.7.0
# https://bitbucket.org/zzzeek/sqlalchemy/get/master.zip

import flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.dialects.postgresql import JSONB
from psycopg2cffi import compat
compat.register()

app = flask.Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://localhost:5432/test'
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = 'todos'
    id = db.Column('id', db.Integer, primary_key=True)
    profile = db.Column(MutableDict.as_mutable(JSONB()))


@app.route('/test')
def test():
    try:
        db.create_all()
        user = User(profile={'foo': 'bar'})
        db.session.add(user)
        db.session.commit()
    except:
        app.logger.exception("Foo")

    return flask.jsonify(User.query.one().profile)

if __name__ == '__main__':
    app.run()

If you hit the test endpoint you get this error:

pablo@Pablos-MacBook-Pro ~ $ pypy test_case.py
/Users/pablo/.virtualenvs/pypy/src/flask-sqlalchemy/flask_sqlalchemy/__init__.py:755: UserWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.
  warnings.warn('SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True to suppress this warning.')
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
[2015-06-10 10:59:09,104] ERROR in app: Exception on /test [GET]
Traceback (most recent call last):
  File "/Users/pablo/.virtualenvs/pypy/src/flask/flask/app.py", line 1884, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/pablo/.virtualenvs/pypy/src/flask/flask/app.py", line 1539, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/pablo/.virtualenvs/pypy/src/flask/flask/app.py", line 1443, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/pablo/.virtualenvs/pypy/src/flask/flask/app.py", line 1537, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/pablo/.virtualenvs/pypy/src/flask/flask/app.py", line 1523, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "test_case.py", line 29, in test
    return flask.jsonify(User.query.one().profile)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/query.py", line 2472, in one
    ret = list(self)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 69, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 430, in _instance
    state.manager.dispatch.load(state, context)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/event/attr.py", line 258, in __call__
    fn(*args, **kw)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/ext/mutable.py", line 451, in load
    val = cls.coerce(key, val)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/ext/mutable.py", line 671, in coerce
    return Mutable.coerce(key, value)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/ext/mutable.py", line 403, in coerce
    raise ValueError(msg % (key, type(value)))
ValueError: Attribute 'profile' does not accept objects of type <type 'str'>
127.0.0.1 - - [10/Jun/2015 10:59:09] "GET /test HTTP/1.1" 500 -

[Mike Bayer]

flask certainly has nothing to do with it but if i make a standalone case w/o flask, works fine (I dont have flask here)

from sqlalchemy import *

from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

Base = declarative_base()

from psycopg2cffi import compat
compat.register()


class User(Base):
    __tablename__ = 'todos'
    id = Column('id', Integer, primary_key=True)
    profile = Column(MutableDict.as_mutable(JSONB()))

e = create_engine(
    "postgresql+psycopg2cffi://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

user = User(profile={'foo': 'bar'})
s.add(user)
s.commit()

import sqlalchemy
print sqlalchemy.__version__
print s.query(User).one().profile

output:

#!

$ /usr/local/src/pypy-2.5.1-osx64/bin/pypy ~/dev/sqlalchemy/test.py
2015-06-10 10:06:24,956 INFO sqlalchemy.engine.base.Engine select version()
2015-06-10 10:06:24,956 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,959 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-06-10 10:06:24,959 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,960 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-06-10 10:06:24,960 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,962 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-06-10 10:06:24,962 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,963 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-06-10 10:06:24,963 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,966 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-06-10 10:06:24,966 INFO sqlalchemy.engine.base.Engine {'name': u'todos'}
2015-06-10 10:06:24,975 INFO sqlalchemy.engine.base.Engine 
DROP TABLE todos
2015-06-10 10:06:24,976 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,978 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-10 10:06:24,980 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-06-10 10:06:24,980 INFO sqlalchemy.engine.base.Engine {'name': u'todos'}
2015-06-10 10:06:24,982 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE todos (
    id SERIAL NOT NULL, 
    profile JSONB, 
    PRIMARY KEY (id)
)


2015-06-10 10:06:24,982 INFO sqlalchemy.engine.base.Engine {}
2015-06-10 10:06:24,987 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-10 10:06:24,991 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-10 10:06:24,994 INFO sqlalchemy.engine.base.Engine INSERT INTO todos (profile) VALUES (%(profile)s) RETURNING todos.id
2015-06-10 10:06:24,994 INFO sqlalchemy.engine.base.Engine {'profile': '{"foo": "bar"}'}
2015-06-10 10:06:24,996 INFO sqlalchemy.engine.base.Engine COMMIT
1.0.5
2015-06-10 10:06:24,998 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-10 10:06:24,999 INFO sqlalchemy.engine.base.Engine SELECT todos.id AS todos_id, todos.profile AS todos_profile 
FROM todos
2015-06-10 10:06:24,999 INFO sqlalchemy.engine.base.Engine {}
{u'foo': u'bar'}

[Pablo Marti]

@zzzeek is it flask-sqlalchemy then? you reckon I should open an issue there? thanks!

[Mike Bayer]

the only thing I can think of is that you aren't actually running the latest SQLAlchemy. I can't see flask having any actual involvement here.

[Pablo Marti]

I'm getting that error with the latest tip ( https://bitbucket.org/zzzeek/sqlalchemy/get/master.zip ) what can I do to help you get to the bottom of the problem?

This were my steps:

virtualenv test
source test/bin/activate
pip install https://bitbucket.org/zzzeek/sqlalchemy/get/master.zip
pip install flask flask-sqlalchemy psycopg2cffi
python test_case.py

[Mike Bayer]

OK, I'm not sure how this works, I ran and hit the url at /test which had 500 server error, but I dont know where the stack trace is.

But also, your test case has the url with "postgresql+psycopg2". that wont work under pypy, it needs to be "postgresql+psycopg2cffi".

what happens if you just run the script I gave you ?

[Pablo Marti]

@zzzeek didn't realise that 1.0.5 had been released in the meantime! Installing 1.0.5 from pypi I get the correct behaviour and the problem is gone, yesterday installing from bitbucket's generated tip didn't. Strange! Anyways thanks a lot for your time and help, much appreciated :)

[Pablo Marti]

@zzzeek since we last talk psycopg2cffi 2.7.1 has been released, it includes support for jsonb. The script you gave me fails with this error now:

could you please have a look and see what's going on with 1.0.5 and 2.7.1 when you have some time? Thanks!

$ pypy mike.py
2015-06-22 11:11:21,028 INFO sqlalchemy.engine.base.Engine select version()
2015-06-22 11:11:21,028 INFO sqlalchemy.engine.base.Engine {}
2015-06-22 11:11:21,030 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-06-22 11:11:21,030 INFO sqlalchemy.engine.base.Engine {}
2015-06-22 11:11:21,032 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-06-22 11:11:21,032 INFO sqlalchemy.engine.base.Engine {}
2015-06-22 11:11:21,033 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-06-22 11:11:21,033 INFO sqlalchemy.engine.base.Engine {}
2015-06-22 11:11:21,035 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-06-22 11:11:21,035 INFO sqlalchemy.engine.base.Engine {}


2015-06-22 11:11:21,038 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-06-22 11:11:21,038 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-06-22 11:11:21,040 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-06-22 11:11:21,040 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-06-22 11:11:21,042 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
    id SERIAL NOT NULL,
    profile JSONB,
    PRIMARY KEY (id)
)


2015-06-22 11:11:21,042 INFO sqlalchemy.engine.base.Engine {}
2015-06-22 11:11:21,050 INFO sqlalchemy.engine.base.Engine COMMIT
2015-06-22 11:11:21,055 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-22 11:11:21,056 INFO sqlalchemy.engine.base.Engine INSERT INTO users (profile) VALUES (%(profile)s) RETURNING users.id
2015-06-22 11:11:21,056 INFO sqlalchemy.engine.base.Engine {'profile': '{"foo": "bar"}'}
2015-06-22 11:11:21,058 INFO sqlalchemy.engine.base.Engine COMMIT
1.0.5
2015-06-22 11:11:21,061 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-06-22 11:11:21,062 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.profile AS users_profile
FROM users
2015-06-22 11:11:21,062 INFO sqlalchemy.engine.base.Engine {}
Traceback (most recent call last):
  File "<builtin>/app_main.py", line 75, in run_toplevel
  File "mike.py", line 33, in <module>
    print s.query(User).one().profile
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/query.py", line 2472, in one
    ret = list(self)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 84, in instances
    util.raise_from_cause(err)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 69, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 455, in _instance
    unloaded, populators)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/orm/loading.py", line 518, in _populate_partial
    dict_[key] = getter(row)
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/engine/result.py", line 90, in __getitem__
    return processor(self._row[index])
  File "/Users/pablo/.virtualenvs/pypy/site-packages/sqlalchemy/dialects/postgresql/json.py", line 228, in process
    return json_deserializer(value.decode(encoding))
AttributeError: 'dict' object has no attribute 'decode'

[Mike Bayer]

psycopg2cffi broke this again

[Mike Bayer]

• Re-fixed this issue first released in 1.0.5 to fix psycopg2cffi JSONB support once again, as they suddenly switched on unconditional decoding of JSONB types in version 2.7.1. Version detection now specifies 2.7.1 as where we should expect the DBAPI to do json encoding for us. fixes #3439

→ <<cset 24f8ab2bc360>>

[Mike Bayer]

fixed again. you'll need to alter the feature_version_map directly until 1.0.6:

e = create_engine(
    "postgresql+psycopg2cffi://scott:tiger@localhost/test")
e.dialect.FEATURE_VERSION_MAP['native_jsonb'] = (2, 7, 1)