On 12/05/2016 05:01 PM, Adam Pletcher wrote:
I'm upgrading one of our tools from Python 2.6.4 (SQLAlchemy 0.9.2) to
Python 3.4.1 (SQLAlchemy 1.1.4), and hitting an error when accessing a
PickleType column in one of the ORM classes. This is on MS SQL Server
2012, connecting with pyodbc, and client code running on Windows 8.1 64-bit.
This ORM class is for storing binary image data. The column in question
is set to PickleType, with no constructor arguments like this:
sqlalchemy.Column( 'image_data', sqlalchemy.PickleType )
This turns into an "image" column type on the MS SQL server. For a long
time the Python 2.6 tool has populated objects with that column using
code like this:
self.image_data = open( image_filename, 'rb' ).read( )
Then pulled those images back out to files like this:
image_file = open( image_filename, 'wb' ).write( self.image_data )
Since the upgrade that last "write" line throws a UnicodeDecodeError, on
line 1478 of sqltypes.py, when it calls loads(value). Stack trace
pasted below.
It appears the "loads" call needs to use encoding='bytes' for it to
succeed, but I'm not sure why or how to set it up to do that. Is there
a configuration parameter I'm missing?
I thought this might require using PickleType(protocol=2), but that made
no difference. That column is set up as deferred, but disabling that
hits the same error.
pyodbc is likely not returning the value as bytes, issues like this w/
pyodbc are common.
I'd recommend building a TypeDecorator subclass of your own that does
the correct encode/decode and then the pickle loads on that value (I
would use pdb to inspect the specific type of data being returned by
pyodbc to determine how it should be modified). Basically write your
own PickleType. An example of how this looks is here:
http://docs.sqlalchemy.org/en/latest/core/custom_types.html?highlight=typedecorator#marshal-json-strings
Any suggestions appreciated.
- Adam
 File "d:\projects\ctg\python3\ctg\src\tools\vm_client\vm_client.pyw",
line 1357, in <module>
app.MainLoop( )
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\wx\core.py",
line 1893, in MainLoop
rv = wx.PyApp.MainLoop(self)
 File
"d:\projects\ctg\python3\ctg\src\tools\vm_client\vmlib\views\entries.py", line
156, in on_item_selected
self.parent.manager.callback_manager.call( vmlib.CB_LOAD_SCREENSHOTS,
[ entry ] )
 File
"d:\projects\ctg\python3\ctg\src\tools\vm_client\vmlib\core\callback.py", line
31, in call
function( *args, **kwargs )
 File
"d:\projects\ctg\python3\ctg\src\tools\vm_client\vmlib\views\screenshot.py",
line 926, in load_screenshots
ss.save_image( img_filename )
 File
"d:\projects\ctg\python3\ctg\src\tools\vm_client\vmlib\core\database.py", line
471, in save_image
image_file = open( image_filename, 'wb' ).write( self.image_data )
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\attributes.py",
line 237, in __get__
return self.impl.get(instance_state(instance), dict_)
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\attributes.py",
line 584, in get
value = self.callable_(state, passive)
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\strategies.py",
line 305, in _load_for_state
only_load_props=group, refresh_state=state) is None:
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\loading.py",
line 223, in load_on_ident
return q.one()
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\query.py",
line 2756, in one
ret = self.one_or_none()
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\query.py",
line 2726, in one_or_none
ret = list(self)
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\loading.py",
line 75, in instances
rows = [proc(row) for row in fetch]
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\loading.py",
line 75, in <listcomp>
rows = [proc(row) for row in fetch]
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\loading.py",
line 437, in _instance
loaded_instance, populate_existing, populators)
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\orm\loading.py",
line 498, in _populate_full
dict_[key] = getter(row)
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\engine\result.py",
line 93, in __getitem__
return processor(self._row[index])
File
"d:\projects\ctg\python3\ctg\publish\python\lib\site-packages\sqlalchemy\sql\sqltypes.py",
line 1478, in process
return loads(value)
builtins.UnicodeDecodeError: 'ascii' codec can't decode byte 0xff in
position 0: ordinal not in range(128)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
