SQLAlchemy with PickleType and the Pyramid Debug Toolbar

[Will Wheatley]

Hi Guys,

Just thought I would post a note here about something I have come across.

If you are using SQLAlchemy as follows:

class MyModel(Base):

    field = Column(String())

    preferences = Column(PickleType)

mymodel = MyModel(field='blah', preferences=dict('pref': 'something'))

# Add/Flush etc

When the debug toolbar attempts to fetch the Query information, in pyramid_debugtoolbar/panels/sqla.py, lines 88-92:

try:

    params = json.dumps(query['parameters'])

except TypeError:

    pass # object not JSON serializable

Then dumps throws a UnicodeDecodeError because of SQLAlchemy PickleType by default uses the new pickling protocol, not the old ASCII one.

I am not sure where the fix here belongs. I have fixed my project by passing protocol=0 to PickleType as follows:

class MyModel(Base):

    field = Column(String())

    preferences = Column(PickleType(protocol=0))

BUT, I could also have easily added another except clause to the sola.py file above, such as:

try:

    params = json.dumps(query['parameters'])

except TypeError:

    pass # object not JSON serializable

except UnicodeDecodeError:

    pass

This prevents the Internal Server Error, but the debug toolbar does not show the parameters for the query.

The above could be re-written to iterate the parameters and catch the individual errors, but I don't feel confident that that would be the recommended solution.

I am more than happy to submit a bug/code/patch as required (if required) but I want to know what the proposed solution should be, even if that proposed solution is to update the documentation and specify that Pyramid does not support PickleType without the protocol as 0 :)