Pyodbc Issue with Teradata

pyodbc connection to Teradata not working after upgrade to Teradata 16.00 driver

I had the exact same issue. This recommendation may sound silly, but my server admins changed the name of the connection string and Windows SQLDriverConnect was bombing.

On my workstation, in the DSN connection list, the DSN name is "TDPROD" and the string description is "Teradata". However, on the server, the DSN name is "TDPROD" and the string description is "Teradata Database ODBC Driver 16.10". So, I had to update my python function from this (Please note that I'm using Python 3.6 so I can use "f" strings; if you're using an earlier version of Python make sure you perform string interpolation by using supported methodologies):

    pyodbc.connect(f"""DRIVER=Teradata;
                       DBCNAME=TDPROD;
                       UID={user};
                       PWD={password};
                       QUIETMODE=YES""",
                       autocommit=True,
                       unicode_results=True)

to this:

    pyodbc.connect(f"""DRIVER=Teradata Database ODBC Driver 16.10;
                       DBCNAME=TDPROD;
                       UID={user};
                       PWD={password};
                       QUIETMODE=YES""",
                       autocommit=True,
                       unicode_results=True)

Hope this helps to at least give you something else to check that wasn't immediately obvious to me.

Issue Connecting to Teradata using Python

The following works in CentOS Linux server.

create a file with the below contents in any file (say odbc.ini)

[ODBC Data Sources]
my_data_source=tdata.so

[my_data_source]
Driver=/path/to/teradata/drivers/tdata.so
DBCName=<td_hostname>
LastUser=<user_name>
Username=<user_name>
Password=<password>
Database=<default_database>
DefaultDatabase=<default_database>
TDMSTPortNumber=<teradata_port>

set ODBCINI variable to the path of the odbc file

export ODBCINI=/file/to/path/of/odbc.ini

note: you can skip the setting of ODBCINI env variable by creating the odbc.ini file in the home directory i.e. /home/user/.odbc.ini (note that the .odbc.ini is a hidden file with a dot prefix in the file name)

now to connect to Teradata use the below snippet.

import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect('DSN=my_data_source',ansi=True, autocommit=True)

Related Topics