pypyodbc and pandas printing Chinese characters on a Mac

Question

I am trying to query a MS SQL Server database from a Mac using pandas and pypyodbc. My columns names are returned in Chinese characters. This does not happen when running the code from a Windows-based machine. I tried setting the display encoding properties, but that does not work. It doesn't appear to just be a display issue because I cannot reference the columns such as data['col'] as I get a KeyError: 'col'

import pandas as pd
import pypyodbc
import sys
pd.options.display.encoding = sys.stdout.encoding

connection = pypyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                              'Server=server;'
                              'Database=database;'
                              'uid=username;'
                              'pwd=pw')

data = pd.read_sql("""SELECT * FROM dbo.Table""",con=connection)

print data

For fun & (possibly) enlightenment: what are the *unicode* values for these Chinese characters? Do they form regular text if listed in hex? — Jongware, Mar 16 '18 at 14:38
@usr2564301 Good idea, but not sure I fully understand how to do that. `print [unicode(x) for x in data.columns]` returns: `[u'\u6144\u6174\u6142\u6573\u6544\u6373']` That is for a column called DatabaseDesc. The chinese characters are 慄慴慂敳敄捳 — user2242044, Mar 16 '18 at 14:55
Well, there you go :) `print ([chr(ord(x) & 0xff)+chr(ord(x)>>8) for x in s])` (or something similar that works for your original input) shows `['Da', 'ta', 'Ba', 'se', 'De', 'sc']`. *Somewhere* in your workflow it gets assumed you are using 2-byte Unicode characters. — Jongware, Mar 16 '18 at 15:01
Oh interesting. I would have never figured that out. Is there a way to just have it default to this rather than having to run that list comprehension? — user2242044, Mar 16 '18 at 15:17
@usr2564301 can you explain what that code is doing and what is `s` in this case? I can't get that code to work. — user2242044, Mar 16 '18 at 17:52
If you're using unixODBC and it happens to be an older version then you might want to try upgrading to v2.3.5 and see if that helps. Or, you might try pyodbc v4.0.21 instead of pypyodbc to see if it behaves better. — Gord Thompson, Mar 17 '18 at 19:20

pypyodbc and pandas printing Chinese characters on a Mac

0 Answers0