pymssql and encoding - I can not get \x92 to be an '

[Stere...@gmail.com]

Hello all -
Unfortunately, we have a ms sql server at work. When I get tuples
from the server they look like this:

.. (55, 26, 'Small Business and Individual Chapter 11s - The NewCode
\x92s Effect on Strategies', 'AUDIO'...

with \x92 for apostrophe etc. I've tried putting every encoding in
the create_engine statement, including ISO-8859-1 used by MS SQL, but
the print statements always come out like:

.. The NewCode?s Effect on ..

I also tried passing the string to unicode(string, 'ISO-8859-1'), but
this gives me:

.. UnicodeEncodeError: 'ascii' codec can't encode character u'\x96' in
position 48: ordinal not in range(128) ..

Does anyone know about MSSQL or this encoding, or how to get
apostrophes where \x92 is?
Any help would be greatly appreciated.

-Steve

[Rick Morrison]

You've got hi-bit characters in your data. MSSQL with pymssql will store this, but not understand it.

You most likely are looking for a "normal" ASCII apostrophe (i.e. ord(39)) instead of the hi-bit version you've got.

to get it, try ${str}.replace(ord(146), "'")    <-- that last bit is a single quote (apostrophe) surrounded by double-quotes

Rick

[Rick Morrison]

Arghh, that last bit should be chr(146), not ord(146)

[Rick Morrison]

...and while I'm making this thread unnecessarily long, I should add that while pymssql may not understand Unicode data, the pyodbc DB-API interface does. Thanks to recent work by Paul Johnston, it's on fast-track to becoming the preferred MSSQL db-api for SA.

[Marco Mariani]

Rick Morrison wrote:
> ...and while I'm making this thread unnecessarily long, I should add
> that while pymssql may not understand Unicode data, the pyodbc DB-API
> interface does. Thanks to recent work by Paul Johnston, it's on
> fast-track to becoming the preferred MSSQL db-api for SA.

Since he starts with "unfortunately, we have a ms sql server at work",
maybe he's not developing on windows, and pyodbc is windows-specific.

I think the data could be encoded with the 1252 charset, which is
similar to 8859-1 but has an apostrophe in chr(146)

[Rick Morrison]

Last I heard, pyodbc was working on any POSIX system that supports odbc (most likely via unixodbc or iodbc)

http://sourceforge.net/projects/pyodbc/

-- check out the supported platforms

[Stere...@gmail.com]

I finally got the encoding to work. I moved from linux to windows,
and now the encoding works with both pymssql and pyodbc.
So it had to do with using FreeTDS. I experimented with FreeTDS.conf
to use version 7.0 and 8.0 and various charsets, but could not get it
to work, so I'll man up and use windows.

db = create_engine('mssql://./test', module=pyodbc,
module_name='pyodbc')

On Apr 11, 11:50 am, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> Last I heard, pyodbc was working on any POSIX system that supports odbc
> (most likely via unixodbc or iodbc)
>
> http://sourceforge.net/projects/pyodbc/
>
> -- check out the supported platforms
>