How to resolve pythonodbc issue with Teradata in Ubuntu

Question

I am getting non text error with Pythonodbc in Teradata Ubuntu

`saranya@saranya-XPS-8500:~/Desktop$ python test.py`

Traceback (most recent call last): File "test.py", line 3, in conn=pyodbc.connect('DRIVER={Teradata};DBCNAME=...**;UID=*****;PWD=*****;', ANSI=True, autocommit=True)

pyodbc.Error: ('632', '[632] 523 630 (0) (SQLDriverConnect)')

The solution provided in post Pyodbc Issue with Teradata is not helping.

Also, exporting ODBCINI, NLSPATH, LD_LIBRARY_HOME,ODBC_HOME values are not helping either.

Any help will be appreciated

rmg rmg · Accepted Answer · 2016-05-06T16:17:14

I saw a similarly obscure response, which seems like it could be related to any number of problems while setting up the connection. Working on a linux box, I was able to get it to work by setting up a DSN. To do this, create a file in your home directory ~/.odbc.ini similar to the following:

[ODBC] 
InstallDir=/opt/teradata/client/15.10
Trace=0
TraceDll=/opt/teradata/client/15.10/lib64/odbctrac.so
TraceFile={trace location, ie. /home/yourusername/odbctrace/trace.log}
TraceAutoStop=0

[ODBC Data Sources]
testdsn=tdata.so

[testdsn]
Driver=/opt/teradata/client/15.10/lib64/tdata.so
Description=Teradata database
DBCName={ip address of your database}
LastUser=
Username={your database's username}
Password={your database's password}
Database={database to use}
DefaultDatabase={default database to use}

Note: you have to fill out the above {xxx} with your values. I've used the default library installation values for the teradata odbc drivers on linux.

Now, with this DSN file set up, set the environment variable

export ODBCINI=/home/yourusername/.odbc.ini

Then you should be able to run the script

import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect('DSN=testdsn')

Even better, if you are connecting to Teradata, install the python teradata module with:

sudo pip install teradata

Once this is installed, you can create connections with the following script

import teradata
from datetime import *

udaExec = teradata.UdaExec(appName="Generic Name" + 
    datetime.now().strftime("%Y_%m_%d:%H:%M:%S"), version="1.0", 
    configureLogging = True, logConsole=True)
session = udaExec.connect(method="odbc", DSN="testdsn")

By setting the configureLogging=True and logConsole=True options, you can output additional debugging information.

This is at least what worked for me!

Erroneous Errors

At the time of writing, the Teradata ODBC drivers in Python output erroneous error messages. If you drop a table that exists, it says:

Error:  (3807, u"[42S02] [Teradata][ODBC Teradata Driver][Teradata Database] Object 'database.table_name' does not exist. ")

And if you create a table that does not previously exist, it says:

Error:  (3803, u"[42S01] [Teradata][ODBC Teradata Driver][Teradata Database] Table 'database.table_name' already exists. ")

How to resolve pythonodbc issue with Teradata in Ubuntu

1 Answers

Erroneous Errors