SQL Server encoding of column names with pypyodbc

Question

I am trying to connect Python with a SQL Server database using pypyodbc. When I get the cursor.description for obtaining the column names of the tables I get the following:

u'\u6573\u7373\u6f69\u496ed' but I have this string in SQL Server: sessionId
u'\u6163\u6c6ce\u496ed' but I have this string in SQL Server: calle
u'\u6974\u656d\u7473\u6d61p' but I have this string in SQL Server: timestamp
u'\u6576\u7372\u6f69np' but I have this string in SQL Server: version

sessionId, calle, timestamp and version are the column names of a SQL Server table.

Strings I get from cursor.descriptions are left ones.

I want to convert the string u'\u6573\u7373\u6f69\u496ed' into the string sessionId and the same with the rest of columns.

How can I do it?

Thanks by advance!

I get those results using this code:

cursor = conexion_sql.conn.cursor()
cursor.execute('select top 5 * from DWH.COB.table')

Then I get the description:

cursor.description

And I obtain this:

[(u'\u6573\u7373\u6f69\u496ed', unicode, 37, 37L, 37L, 0, True),
 (u'\u6163\u6c6ce\u496ed', unicode, 4000, 4000L, 4000L, 0, True),
 (u'\u6974\u656d\u7473\u6d61p', datetime.datetime, 23, 23L, 23L, 3, False),
 (u'\u6576\u7372\u6f69np', unicode, 10, 10L, 10L, 0, True),
 (u'\u6546\u6863\u4361\u7261\u6167\u445f\u4857',
  datetime.datetime,
  23,
  23L,
  23L,
  3,
  False),
 (u'\u6946\u6863\u7265\u436f\u7261\u6167\u445f\u4857',
  unicode,
  100,
  100L,
  100L,
  0,
  False)]

Details:

Python version: 2.7.13
pypyodbc version: 1.3.4
ODBC Driver: ODBC Driver 17 for SQL Server


conn_string = '''
        DRIVER={4};
        SERVER={0};
        DATABASE={1};
        UID={2};
        PWD={3};
        TrustServerCertificate=yes;
        Connection Timeout=15
        '''.format(server, database, uid, pwd, driver)
        self.conn = pypyodbc.connect(conn_string)

How are you getting those column names (show code)? SQL Server uses UTF16 string coding for all `sysnames` but those look like pairs of ASCII characters stuffed into int16s. — AlwaysLearning, Nov 13 '19 at 12:39
You'd normally get the cursor's column names with `columns = [column[0] for column in cursor.description]`, see https://github.com/mkleehammer/pyodbc/wiki/Cursor#description, but your cursor.description dump looks messed up. — AlwaysLearning, Nov 13 '19 at 13:10
Please [edit] your question to include: Python version, pyodbc version, ODBC driver, connection string. — Gord Thompson, Nov 13 '19 at 13:30
Ah, so you're using pypyodbc, not pyodbc. Can you try using pyodbc and see if that works better? — Gord Thompson, Nov 13 '19 at 14:11

score 0 · Accepted Answer · answered Nov 19 '19 at 12:58

I was able to reproduce your issue:

# -*- coding: utf-8 -*-
import sys

import pypyodbc

print(sys.version.replace('\n', ''))
# 2.7.15+ (default, Oct  7 2019, 17:39:04) [GCC 7.4.0]
print(pypyodbc.version)
# 1.3.5

connection_string = (
    'DRIVER=ODBC Driver 17 for SQL Server;'
    'SERVER=192.168.0.179,49242;'
    'DATABASE=myDb;'
    'UID=sa;PWD=_whatever_;'
)
cnxn = pypyodbc.connect(connection_string)
crsr = cnxn.cursor()
crsr.execute("SELECT 1 AS foo")
desc = crsr.description
col_name = desc[0][0]
print(col_name)  # 潦o
print(repr(col_name))  # u'\u6f66o'

pypyodbc is no longer being maintained. Use pyodbc instead.

SQL Server encoding of column names with pypyodbc

1 Answers1