getting results uncidoe from mssql with pyodbc (where mssql encoding is windows-1255) using turbogears scoped DBSession

[alonn]

some of my sqlalchemy 0.7.3 (with tubrogears 2.1.4) models work with a mssql 2005 db using pyodbc.

(No can't change this, don't bother suggesting, this is an enterprise financial system, I can just read and write to certain tables there)

the query returned are encoded windows-1255 instead of utf-8

failing to return unicode causes various 'UnicodeDecodeError' error in sprox and toscawidgets which I can override manualy by rewriting certain lines in the sprox/tw.forms source code but not exactly an optimal solution 

is there a  way to specify in the connection url to convert the data to standard unicode encoding?

currently using the following format:

sqlalchemy.second.url = mssql://user:password@SERVER\db

or maybe changing some parameter in the sqlalchemy engine should do the trick?

thanks for the help

[Michael Bayer]

what ODBC driver ?   the encoding issues are typically configured with ODBC.    it's a huge difference if you're on the windows drivers, vs. freetds, vs anything else.

also I use MSSQL 2005 in production financial applications as well.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/xTmE0yTs810J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[alonn]

thanks - I use pyodbc 2.1.11 with sqlalchemy 0.7.3
would upgrading one of them (or both) help me solve this? I saw in sqlalchemy 0.7.7 changlog :

[feature] Added interim create_engine flag
    supports_unicode_binds to PyODBC dialect,
    to force whether or not the dialect
    passes Python unicode literals to PyODBC 
    or not.

would using that solve my problem? how and where should I call that flag?

[Michael Bayer]

freetds or windows ?   critical 

plus:  stack trace?   critical

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/mIe-QOn7JRgJ.

[alonn]

I'm working on windows 7, where can I find the stack trace?

[Michael Bayer]

when it says UnicodeDecodeError, there should be a big stack trace.

Since I don't know from your description what feature is actually failing to decode.   I don't understand how a code change in a client of the result set could workaround such an issue if the decoding issue is on the result side, for example (if you didn't get the data, you didn't get it).

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wwRZkYf0dFEJ.

[alonn]

the error stack (before my ugly fix in sprox):

URL: http://localhost:8080/adminPriority/activitiess/
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\weberror-0.10.3-py2.7.egg\\weberror\\evalexception.py', line 431 in respond
  app_iter = self.application(environ, detect_start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\turbogears2-2.1.3-py2.7.egg\\tg\\configuration.py', line 825 in remover
  return app(environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\repoze.tm2-1.0b2-py2.7.egg\\repoze\\tm\\__init__.py', line 24 in __call__
  result = self.application(environ, save_status_and_headers)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\repoze.who-1.0.19-py2.7.egg\\repoze\\who\\middleware.py', line 107 in __call__
  app_iter = app(environ, wrapper.wrap_start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\toscawidgets-0.9.12-py2.7.egg\\tw\\core\\middleware.py', line 46 in __call__
  return self.wsgi_app(environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\toscawidgets-0.9.12-py2.7.egg\\tw\\core\\middleware.py', line 72 in wsgi_app
  resp = req.get_response(self.application)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\webob-1.0.8-py2.7.egg\\webob\\request.py', line 1053 in get_response
  application, catch_exc_info=False)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\webob-1.0.8-py2.7.egg\\webob\\request.py', line 1022 in call_application
  app_iter = application(self.environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\toscawidgets-0.9.12-py2.7.egg\\tw\\core\\resource_injector.py', line 70 in _injector
  resp = req.get_response(app)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\webob-1.0.8-py2.7.egg\\webob\\request.py', line 1053 in get_response
  application, catch_exc_info=False)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\webob-1.0.8-py2.7.egg\\webob\\request.py', line 1022 in call_application
  app_iter = application(self.environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\beaker-1.5.4-py2.7.egg\\beaker\\middleware.py', line 73 in __call__
  return self.app(environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\beaker-1.5.4-py2.7.egg\\beaker\\middleware.py', line 152 in __call__
  return self.wrap_app(environ, session_start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\routes-1.12.3-py2.7.egg\\routes\\middleware.py', line 131 in __call__
  response = self.app(environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\pylons-1.0-py2.7.egg\\pylons\\wsgiapp.py', line 107 in __call__
  response = self.dispatch(controller, environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\pylons-1.0-py2.7.egg\\pylons\\wsgiapp.py', line 312 in dispatch
  return controller(environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\priority-agent\\priorityagent\\lib\\base.py', line 31 in __call__
  return TGController.__call__(self, environ, start_response)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\pylons-1.0-py2.7.egg\\pylons\\controllers\\core.py', line 211 in __call__
  response = self._dispatch_call()
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\pylons-1.0-py2.7.egg\\pylons\\controllers\\core.py', line 162 in _dispatch_call
  response = self._inspect_call(func)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\pylons-1.0-py2.7.egg\\pylons\\controllers\\core.py', line 105 in _inspect_call
  result = self._perform_call(func, args)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\turbogears2-2.1.3-py2.7.egg\\tg\\controllers\\dispatcher.py', line 254 in _perform_call
  r = self._call(func, params, remainder=remainder)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\turbogears2-2.1.3-py2.7.egg\\tg\\controllers\\decoratedcontroller.py', line 116 in _call
  output = controller(*remainder, **dict(params))
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\tgext.crud-0.3.12-py2.7.egg\\tgext\\crud\\controller.py', line 120 in get_all
  values = self.table_filler.get_value(**kw)
File 'D:\\Python27\\learn\\agent\\agent\\lib\\site-packages\\sprox-0.7rc1-py2.7.egg\\sprox\\fillerbase.py', line 221 in get_value
  value = unicode(value, encoding='utf-8')
UnicodeDecodeError: 'utf8' codec can't decode byte 0xee in position 0: invalid continuation byte

the fix that solves it: change sprox fillebase.py get_value method

like this:

                if isinstance(value, str):
                    try:  # added this to fix unicode problem in CRUD forms TODO:better implentation
                        value = unicode(value, encoding='utf-8')

                    except UnicodeDecodeError: # overriding the default sprox behavior
                       
                        #TODO:add chardet checking here
                        #return
                        value = unicode(value, encoding='windows-1255')

[Michael Bayer]

If the encoding is uniformly coming back as "windows-1255", the engine can be configured with encoding="windows-1255" and "convert_unicode=True" and SQLAlchemy string types will intercept the values and convert.

You also might try going to your ODBC driver's configuration screen and trying to get it to return UTF-8 at that level, if it supports that.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/DEDYZIu9ecsJ.

[alonn]

just making sure should I configure it the connection url like this:

sqlalchemy.second.url = mssql://user:password@SERVER\db?charset=windows-1255&convert_unicode=True?

because this doesn't seem to work

what am I missing?

[Michael Bayer]

On Sep 4, 2012, at 2:07 AM, alonn wrote:

just making sure should I configure it the connection url like this:

sqlalchemy.second.url = mssql://user:password@SERVER\db?charset=windows-1255&convert_unicode=True?

because this doesn't seem to work

what am I missing?

the convert_unicode and encoding needs to be in create_engine() separately.  Also not sure if you want to be using charset there ?   

create_engine("mssql://user:password@server", encoding='windows-1255', convert_unicode=True)

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/26NWk6mM5FYJ.

[alonn]

thanks! did the trick!