How do I properly set TeraData pyodbc charset/encoding?

Question

I have a problem using pyodbc with a TeraData server -- My perl connection which uses the same ODBC drivers work without issue.

It appears pyodbc is connecting because I can fetch table names, however, the table names are not encoded properly (example below). Here is a list of things I've tried:

Setting CHARSET/ENCODING in my DSN as 'ASCII', 'UTF-8', and many others
Setting the locale:

locale.setlocale(locale.LC_ALL, 'en_US.utf8')
Changing autocommit and ANSI=True/False' in pyodbc.connect()

python==3.4.3, pyodbc==3.0.10

.odbc.ini:

[ODBC]
InstallDir=/opt/teradata/client/15.10/odbc_64
Trace=0
TraceDll=/opt/teradata/client/15.10/lib/odbctrac.so
TraceFile=/home/solberg/teratrace
TraceAutoStop=0

[ODBC Data Sources]
tera01=Teradata ODBC Driver 15.10

[tera01]
Driver=/opt/teradata/client/15.10/lib64/tdata.so
DBCName=tera01
Username=solberg
Authentication=LDAP

.odbcinst.ini:

[ODBC DRIVERS]
Teradata=Installed

[ODBC Translators]
OEB to ANSI=Installed

python code:

import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect('DSN=tera01;', password=pw)

cursor = conn.cursor()
cursor.tables()
rows = cursor.fetchall()

print(row[0].table_name)

扁牯䱴獩却獥楳湯

print(row[0].table_name.encode('utf_16_le'))

b'AbortListSession'

conn.execute("SELECT DISTINCT column FROM table;").fetchall()

Error: ('HY000', '[HY000] [unixODBC][Driver Manager]Driver returned SQL_ERROR or SQL_SUCCESS_WITH_INFO but no error reporting API found (0) (SQLExecDirectW)')

Future readers should refer to the encoding information for Teradata in the pyodbc wiki. — Gord Thompson

hhoeck hhoeck · Accepted Answer · 2016-03-22T11:31:51

not using pyodbc, but two things: 1) sometimes frameworks struggle if not all columns are explicitly named, so could you try

SELECT DISTINCT column as d_col FROM table;
SELECT column FROM table group by column;

2) did you try the python teradata module? - At the end of the day it also uses ODBC (or REST), but it encapsulates lot of nasty stuff. (https://github.com/Teradata/PyTd)

[sudo] pip install teradata

How do I properly set TeraData pyodbc charset/encoding?

1 Answers