Teradata Python Module

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

Re: Teradata Python Module

Sorry I didn't see your response.  You're exactly right, I I was successful by replacing the delimiter.

Teradata Employee

Re: Teradata Python Module

Anyone can advice what I am doing wrong. Neither odbc or rest method is not working for my teradata python module (have tried for both of python versions 2.7.10 and 3.4.3):

TDExpress15.0.0.2_Sles10:cat TestRest.py

import teradata                                                                                                                                                           

udaExec = teradata.UdaExec(appName="Test", version="1.0", logConsole=False)                                                                                       session = udaExec.connect(method="rest", host="192.168.100.15", system="TD15", username="dbc", password="dbc");                       for row in session.execute("SELECT current_timestamp"):                                                                                                                   

    print(row) 

TDExpress15.0.0.2_Sles10:~/tdch/python/python TestRest.py 

Traceback (most recent call last):

  File "TestRest.py", line 7, in <module>

    for row in session.execute("SELECT current_timestamp"):

  File "/usr/local/lib/python2.7/site-packages/teradata/udaexec.py", line 514, in execute

    self.internalCursor.execute(query, params, **kwargs)

  File "/usr/local/lib/python2.7/site-packages/teradata/udaexec.py", line 560, in execute

    self._execute(self.cursor.execute, query, params, **kwargs)

  File "/usr/local/lib/python2.7/site-packages/teradata/udaexec.py", line 611, in _execute

    raise e

teradata.api.InterfaceError: (400, 'HTTP Status: 400, URL: /tdrest/systems/TD15/queries, Details:  {u\'message\': u\'Can not construct instance of com.teradata.rest.api.def.ResultFormat from String value \\'array\\': value not one of declared Enum instance names: [OBJECT, ARRAY, CSV]\n at [Source: org.apache.catalina.connector.CoyoteInputStream@38465c9a; line: 1, column: 93] (through reference chain: com.teradata.rest.api.model.QueryRequest["format"])\'}')

TDExpress15.0.0.2_Sles10:cat TestOdbc.py

import teradata                                                                                                                                                           

udaExec = teradata.UdaExec(appName="Test", version="1.0", logConsole=False)                                                                                       session = udaExec.connect(method="odbc", system="default", username="dbc", password="dbc");                                                           for row in session.execute("SELECT GetQueryBand()"):                                                                                                                      

    print(row) 

TDExpress15.0.0.2_Sles10:~/tdch/python/python TestOdbc.py 

Traceback (most recent call last):

  File "TestOdbc.py", line 5, in <module>

    session = udaExec.connect(method="odbc", system="default", username="dbc", password="dbc");

  File "/usr/local/lib/python2.7/site-packages/teradata/udaexec.py", line 137, in connect

    raise e          

teradata.api.DatabaseError: (113, u"[08001] [Teradata][Unix system error]  113 Socket error - The Teradata server can't currently be reached over this network")

------------------------------------------------------------------------------------------------

P.S. It seems ODBC works well:


TDExpress15.0.0.2_Sles10:/opt/teradata/client/15.00/odbc_64/bin/tdxodbc

Enter Data Source Name: tdlocal

Enter UserID: dbc

Enter Password: 

Connecting with SQLConnect(DSN=tdlocal,UID=dbc,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-

DBMS name           = -Teradata-

DBMS version        = -15.00.0002  15.00.00.02-

Driver name         = -tdata.so-

Driver version      = -15.00.00.00-

Driver ODBC version = -03.51-

(type quit to terminate adhoc) 

Enter SQL string : select current_timestamp

Executing SQLExecDirect("select current_timestamp")...

SQL Statement [1]: 1 rows affected.

Current TimeStamp(6) 

2015-08-19 21:35:24.600000+00:00 

------------------------------------------------------------------------------------------------

Teradata REST service works as well:

TDExpress15.0.0.2_Sles10:~/tdch/python/test.py 

#!/usr/bin/python                                                                                                                                                         

import requests                                                                                                                                                           

from requests.auth import HTTPBasicAuth                                                                                                                                   

import json                                                                                                                                                               

import os                                                                                                                                                                 

tdUser = os.environ.get('REST_USER', 'dbc')                                                                                                                               

tdPassword = os.environ.get('REST_PASSWORD', 'dbc')                                                                                                                       

restServer = "{0}:{1}".format(os.environ.get('REST_HOST', 'localhost'), os.environ.get('REST_PORT', '1080'))                                                              

response = requests.get('http://{}/tdrest/systems/TD15/databases'.format(restServer),                                                                                     

auth=HTTPBasicAuth(tdUser, tdPassword),                                                                                                                                   

headers={'content-type': 'application/json','Accept': 'application/vnd.com.teradata.rest-v1.0+json, */*; q=0.01'})                                                        

print(response.text)                                                                                                                                                      

print("status: {0}".format(response.status_code))  

TDExpress15.0.0.2_Sles10:~/tdch/python/python test.py 

[{"name":"All","dbKind":"U"},{"name":"console","dbKind":"U"},{"name":"Crashdumps","dbKind":"U"},{"name":"DBC","dbKind":"U"},{"name":"dbcmngr","dbKind":"D"},{"name":"Default","dbKind":"U"},{"name":"EXTUSER","dbKind":"U"},{"name":"financial","dbKind":"D"},{"name":"labs_querygrid_lab2","dbKind":"U"},{"name":"LockLogShredder","dbKind":"U"},{"name":"manufacturing","dbKind":"D"},{"name":"PUBLIC","dbKind":"U"},{"name":"qg2batt","dbKind":"D"},{"name":"qg2demo","dbKind":"D"},{"name":"querygrid_demo","dbKind":"D"},{"name":"retail","dbKind":"D"},{"name":"Samples","dbKind":"D"},{"name":"SQLJ","dbKind":"D"},{"name":"SysAdmin","dbKind":"U"},{"name":"SYSBAR","dbKind":"D"},{"name":"SYSJDBC","dbKind":"D"},{"name":"SYSLIB","dbKind":"D"},{"name":"SYSSPATIAL","dbKind":"D"},{"name":"SystemFe","dbKind":"U"},{"name":"SYSUDTLIB","dbKind":"D"},{"name":"SYSUIF","dbKind":"D"},{"name":"Sys_Calendar","dbKind":"U"},{"name":"td01","dbKind":"U"},{"name":"TDCH","dbKind":"D"},{"name":"TDPUSER","dbKind":"U"},{"name":"TDQCD","dbKind":"D"},{"name":"TDStats","dbKind":"D"},{"name":"tduser","dbKind":"U"},{"name":"tdwm","dbKind":"U"},{"name":"TD_SERVER_DB","dbKind":"D"},{"name":"TD_SYSFNLIB","dbKind":"D"},{"name":"TD_SYSXML","dbKind":"D"},{"name":"tpch","dbKind":"D"},{"name":"transportation","dbKind":"D"},{"name":"twm_md","dbKind":"D"},{"name":"twm_results","dbKind":"D"},{"name":"twm_source","dbKind":"D"},{"name":"viewpoint","dbKind":"U"}]

status: 200

sdc
Enthusiast

Re: Teradata Python Module

Is there a way to use the module to return a key which is generated as identity?  The most expensive thing in my application right now is inserting a row which has an automatically generated key and then finding the just-generated key by making sure it is not in a list of keys that are referenced in other tables.  This link suggests that the ODBC driver supports this kind of thing:

http://www.info.teradata.com/htmlpubs/DB_TTU_13_10/index.html#page/Connectivity/B035_2509_071A/2509c...

Teradata Employee

Re: Teradata Python Module

@alpanchino - What is the version of your REST service?  Is it a beta version?  That particular error is a case issue that was corrected before the GCA of rest.

For the ODBC error, I would check the name of the system you are passing python and make sure that is resolving to a pingable IP address.

Teradata Employee

Re: Teradata Python Module

@sds -  Yes, this definitely possible.   The trick is to pass in the ReturnGeneratedKeys="C" option to the ODBC driver.  This can be done directly on the call to udaExec.connect().  This option will cause the ODBC driver to return the generated keys in the result set which you access just like you would any result set.  E.g.

conn = udaExec.connect(..., ReturnGeneratedKeys="C")
generatedId = conn.execute("INSERT INTO ...").fetchone()[0]

Teradata Employee

Re: Teradata Python Module

@ericschie - The REST API version -> Version 1.00.00 

Regarding ODBC - yes the name is pingable by name/ip/localhost. Moreover the tdxodbc can connect to the host...

Does the module use ODBCINI environment variable or different method to find odbc.ini file with DSNnames?

-------------------------------------------------------------------------------

/opt/teradata/client/15.00/odbc_64/bin/tdxodbc

Enter Data Source Name: tdlocal

Enter UserID: dbc

Enter Password: 

Connecting with SQLConnect(DSN=tdlocal,UID=dbc,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-

DBMS name           = -Teradata-

DBMS version        = -15.00.0002  15.00.00.02-

Driver name         = -tdata.so-

Driver version      = -15.00.00.00-

Driver ODBC version = -03.51-

(type quit to terminate adhoc) 

Enter SQL string : select current_timestamp

Executing SQLExecDirect("select current_timestamp")...

SQL Statement [1]: 1 rows affected.

Current TimeStamp(6) 

2015-08-19 21:35:24.600000+00:00

===================================================================================

Teradata Employee

Re: Teradata Python Module

What is the last version of Teradata REST (tdrestd)? I have the issue with tdrestd-15.10.00.00.Dev-1 package installed.  

Teradata Employee

Re: Teradata Python Module

@alpanchino - Your REST version is indeed a pre-release version, please upgrade to the GCA version (there should not be "Dev" in the package name).

If you want to connect to a data source named "tdlocal" in an odbc.ini file, then replace the system="xxxx" parameter in your udaExec call with DSN="tdlocal".   The system parameter is for the tdpid, not the data source name.

Highlighted
Teradata Employee

Re: Teradata Python Module

Thanks, Eric. Now it is working with if system parameter is tdpid. Just for checking I have tried DSN=tdlocal - it seems the module ignores the parameter :

....

session = udaExec.connect(method="odbc", DSN="tdlocal", username="dbc", password="dbc"); 

.....

Traceback (most recent call last):

  File "TDCH_Odbc_Archive2.py", line 6, in <module>

    session = udaExec.connect(method="odbc", DSN="tdlocal", username="dbc", password="dbc");

  File "/usr/local/lib/python2.7/site-packages/teradata/udaexec.py", line 137, in connect

    raise e          

teradata.api.DatabaseError: (0, u'[08001] [Teradata][ODBC Teradata Driver] No DBCName entries were found in DSN/connection-string. ')

Enthusiast

Re: Teradata Python Module

@alpanchino: I believe issue is with your odbc.ini file. Can you check if DBCName exists for DSN "tdlocal" in your ODBCINI file?