Connecting Python to Teradata over ODBC

Connectivity
Teradata Employee

Connecting Python to Teradata over ODBC

Has anyone ever connected a Python based script to Teradata using the ODBC driver on Linux? We have a Sales Consultant trying to work with a prospect and getting seg faults while using the ODBC driver.

ODBC connect string for Teradata Express 12.0:

DRIVER={Teradata};DBCNAME=localhost;UID=dbc;PWD=dbc;QUIETMODE=YES;

Example interactive Python session: session: >>> conn = >>> pyodbc.connect('DRIVER={Teradata};DBCNAME=localhost;UID=dbc;PWD=dbc; >>> QUIETMODE=YES;') >>> conn
>>> curs = conn.cursor() >>> curs.execute('select current_timestamp')
>>> curs.fetchone() (datetime.datetime(2009, 8, 13, 11, 34, 1, 250), )


MikeC (for jpm185170)

Tags (2)
17 REPLIES
N/A

Re: Connecting Python to Teradata over ODBC

The example you gave is from a pyodbc website.
If you "import pyodbc" before running this example, it works fine (at least on Windows)
See below for example.

Can you give me any details on the actual error you are getting?

>>> import pyodbc
>>> conn=pyodbc.connect('DRIVER={Teradata};DBCNAME=localhost;UID=dbc;PWD=dbc;QUIETMODE=YES;')
>>> conn

>>> curs=conn.cursor()
>>> curs.execute('select current_timestamp')

>>> curs.fetchone()
(datetime.datetime(2009, 9, 17, 10, 40, 8, 870), )
>>>

Teradata Employee

Re: Connecting Python to Teradata over ODBC

You are correct - that is where the example came from. pyodbc was imported before getting the seg fault. Driver manager is unixODBC on 64 bit, pyodbc is also built on 64 bit. unixODBC's isql tool and the DataDirect driver can connect as long as I specify uid and pwd on the command line -- e.g. #isql testdsn dbc dbc works fine, #isql testdsn returns with error inavlid username,password, or account string - even though they are specified in the dsn. Windows connectivity is fine no problems. I also tried DataDirect's ddtestlib on pyodbc.so to see if it would load ok. Error was "undefined symbol: _Py_TrueStruct". This might have passed me by in the build process and not sure how this might affect connectivity.

The seg faults do not allow any odbc tracing logs or additional information. I've also tried an strace and ltrace, but nothing I can glean from that either.
Teradata Employee

Re: Connecting Python to Teradata over ODBC

Resolution
Once we verified the connection was successful by viewing the ODBC Trace, I called in Vittal to help understand why the isql was encountering a SEGV after the connection. We will investigate this failure later as it might be a problem with the ODBC Driver. Since it was verified that the connection was successful we decided to move ahead and try the actual program python. There was an initial problem identified by Vittal that needed to be corrected by adding the defines below and a SQLSetEnvAttr call to set the SQL_ATTR_APP_UNICODE_TYPE attribute in a python module. Recompile pyodbc using "python setup.py build install".

Defines
#define SQL_CONOPT_START 1040
#define SQL_ATTR_APP_UNICODE_TYPE (SQL_CONOPT_START+24)
#define SQL_DD_CP_ANSI 0
#define SQL_DD_CP_UCS2 1
#define SQL_DD_CP_UTF8 2
#define SQL_DD_CP_UTF16 SQL_DD_CP_UCS2

And added the API call to set the SQL_ATTR_APP_UNICODE_TYPE attribute.

odbc_ret_code = SQLSetEnvAttr(henv, SQL_ATTR_APP_UNICODE_TYPE,
(void *) SQL_DD_CP_UTF16, SQL_IS_INTEGER);

Re: Connecting Python to Teradata over ODBC

Hello, Anyone out there using Python for database updates ?
I am having trouble getting my very basic first python script to commit a database update to Teradata (TD12, TD13). Using a simple example like Jimm's above, but with an insert statement and followed by a
conn.commit() - as per pyodbc examples.
This isn't working .
A colleague said he had it running OK on V2R6, with matching TTU ODBC driver, where he did a number of inserts and a commit at the end. All OK until the upgrade to TD13 ODBC and now only the last insert is saved.
Any tips on using TD13 ODBC with python ? I suspect the transaction processing with the Teradata ODBC drivers is somewhat different to other OLTP drivers, but I haven't found the answer yet.
Thanks

Re: Connecting Python to Teradata over ODBC

Hi there Hussey,
I'm glad you found the solution. Did you solve the commit issue for inserts and updates ? I'd be very interested to hear what you did.

Re: Connecting Python to Teradata over ODBC

A note for the forum: It is now working !

I was actually using conn.commit instead of conn.commit(). The former still compiles and runs without errors which is why I didn't pick it up sooner.

It appears that SQL_AUTOCOMMIT or TMODE in the connection string don't make a difference, you must use conn.commit().

thanks

Re: Connecting Python to Teradata over ODBC

hi everyone am trying to use java jdbc type 4 driver to connect to the teradata.........am encountered with following error....

2010-09-01.20:14:05.125 TERAJDBC4 ERROR [main] com.teradata.jdbc.jdbc_4.TDSession@9df354 Connection to localhostcop1 Wed Sep 01 20:14:05 CDT 2010 socket orig=localhostcop1 cid=1a80a69 sess=0 java.net.UnknownHostException: localhostcop1 at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:849) at java.net.InetAddress.getAddressFromNameService(InetAddress.java:1200) at java.net.InetAddress.getAllByName0(InetAddress.java:1153) at java.net.InetAddress.getAllByName(InetAddress.java:1083) at java.net.InetAddress.getAllByName(InetAddress.java:1019) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF$Lookup.(TDNetworkIOIF.java:148) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.connectToHost(TDNetworkIOIF.java:232) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.(TDNetworkIOIF.java:86) at com.teradata.jdbc.jdbc_4.TDSession.getIO(TDSession.java:580) at com.teradata.jdbc.jdbc.GenericStateController.(GenericStateController.java:41) at com.teradata.jdbc.jdbc.GenericLogonController.(GenericLogonController.java:40) at com.teradata.jdbc.jdbc_4.TDSession.(TDSession.java:198) at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalConnection.(TeraLocalConnection.java:95) at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:54) at com.teradata.jdbc.TeraDriver.doConnect(TeraDriver.java:217) at com.teradata.jdbc.TeraDriver.connect(TeraDriver.java:150) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:185) at com.jdbc.training.practise.jdbc4.main(jdbc4.java:21)
errorcom.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.06] [Error 1000] [SQLState 08S01] Login failure for Connection to localhostcop1 Wed Sep 01 20:14:05 CDT 2010 socket orig=localhostcop1 cid=1a80a69 sess=0 java.net.UnknownHostException: localhostcop1 at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:849) at java.net.InetAddress.getAddressFromNameService(InetAddress.java:1200) at java.net.InetAddress.getAllByName0(InetAddress.java:1153) at java.net.InetAddress.getAllByName(InetAddress.java:1083) at java.net.InetAddress.getAllByName(InetAddress.java:1019) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF$Lookup.(TDNetworkIOIF.java:148) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.connectToHost(TDNetworkIOIF.java:232) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.(TDNetworkIOIF.java:86) at com.teradata.jdbc.jdbc_4.TDSession.getIO(TDSession.java:580) at com.teradata.jdbc.jdbc.GenericStateController.(GenericStateController.java:41) at com.teradata.jdbc.jdbc.GenericLogonController.(GenericLogonController.java:40) at com.teradata.jdbc.jdbc_4.TDSession.(TDSession.java:198) at com.teradata.jdbc.jdbc_3.ifjdbc_4.TeraLocalConnection.(TeraLocalConnection.java:95) at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:54) at com.teradata.jdbc.TeraDriver.doConnect(TeraDriver.java:217) at com.teradata.jdbc.TeraDriver.connect(TeraDriver.java:150) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:185) at com.jdbc.training.practise.jdbc4.main(jdbc4.java:21)

my program....

public class jdbc4 extends IOException {
public static void main(String[] args) {
try
{
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println("driver loaded");
Connection con=DriverManager.getConnection("jdbc:teradata://localhostcop1/TMODE=ANSI,CHARSET=UTF8","tduser","tduser");
System.out.println("connection established");
Statement stmt=con.createStatement();
boolean created=stmt.execute("create table tduser.emp4(empno varchar(5),ename varchar(20),sal varchar(20));");
System.out.println("Table Create "+created);
}
catch (Exception e){System.out.println("error"+e);}
}

}

please help me ...
Thanks,
Sreeharsha
Teradata Employee

Re: Connecting Python to Teradata over ODBC

The important part of the exception message is: java.net.UnknownHostException: localhostcop1
Which tells you that "localhostcop1" is not a defined hostname.
If the Teradata Database is running on your local machine, then you should specify localhost as the hostname.
If the Teradata Database is running on some other machine, then you should specify the hostname for it.

Re: Connecting Python to Teradata over ODBC

To connect to Teradata using python, this is what I have done.

1. I ensured that the teradata ODBC drivers are correctly installed and I was able to connect via SQL Assistant.
2. Then I installed pyodbc downloaded from http://code.google.com/p/pyodbc/.
3. Then I tested 2 methods. First using the DNS entry used to connect to SQL Assistant. The code for this is as follows:

connection = pyodbc.connect('DSN=TEST_DSN;PWD=xxxx')

3. The second method was as follows:

connection = pyodbc.connect('DRIVER{Teradata};DBCNAME=10.10.10.10;UID=test123;PWD=xxx;QUIETMODE=YES;')

Both methods worked perfectly for 32 Bit Python 2.7.2 and 32 Bit pyodbc 2.1.8 installed on Windows XP 32-Bit and Teradata residing on a remote server.

However, I still have not managed to get these steps work on 64 Bit Windows 7 Professional.