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.
Teradata Employee

Re: Teradata Python Module

@rlobera - The Python module expects a decimal point to be used in decimals instead of a comma when retreiving numbers from the ODBC driver.   Can you try updating your data source to specify the following property either in your odbc.ini, your python external config file, or by passing it directly to the connect method, depending on how you are specifying your data source?

USEREGIONALSETTINGS=N

Enthusiast

Re: Teradata Python Module

thanks Eric , it s ok now

Teradata Employee

Re: Teradata Python Module

Hi Eric,

Yes I have a last one - teradata-15.10.0.8

I changed the example to only work with the CLOB column as you suggested.

It seems the python module operates only on VARCHAR which, as you know, has a the limitation of 64K.

---------------------------------------------- OUTPUT -------------------------------------------------------------------

Calculating len(TDCH_log)=1315287

Calculating sys.getsizeof(TDCH_log)=1315324

Getting REST connection to host

Traceback (most recent call last):

  File "./testCLOB.py", line 256, in <module>

    session.execute("""INSERT INTO TBFD20D.TABLE1(tdch_log) VALUES(?)""", (TDCH_log))

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

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

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

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

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

    raise e

teradata.api.DatabaseError: (1186, u'Parameter 1 length is 1315287 bytes, which is greater than the maximum 64000 bytes that can be set. ') 

Teradata Employee

Re: Teradata Python Module

@alpanchino -  I see you are using REST.   This is actually a limitation of the current Teradata REST interface.  There is an open JIRA (REST-310) to add this support in its next release.

Re: Teradata Python Module

from pandas import DataFrame
import Teradata

udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0", logConsole=False)
 
session = udaExec.connect(method="odbc", system="ICWDR", authentication="LDAP", username="${username}", password="${password}");

udaExec.config["my_query"] = "SELECT * from REFTABLE SAMPLE 10"
mq_cursor = session.execute("${my_query}")
df = DataFrame(mq_cursor.fetchall())
df.columns = mq_cursor.keys()

I there an easy way to get the column names from the result set? I want to run a query and put the result data set into a pandas dataframe.

I have a piece of code (above) which fails. The final line errors with AttributeError: UsaExecCursor instance has no attribute 'keys'.

I'm fairly new to python and pandas - so I might be overlooking something obvious!

Teradata Employee

Re: Teradata Python Module

@gabnash - The way you get column names is defined as part of the Python Database API 2.0 specification.  Here is an example:

cursor = conn.execute("SELECT * FROM Whatever")
for rowMetaData in cursor.description:
print("Column Name: " + rowMetaData[0])

Highlighted

Re: Teradata Python Module

cols = []
for row in mq_cursor.description:
    cols.append(row[0])
   
df.columns = cols

Thanks @ericscheie. I replaced the last line of code with the above and now it works.

Fan

Re: Teradata Python Module

Hello,

I am trying to connect to a teradata database on a remote machine using the python teradata module. I execute the following commands on a Python shell

import teradata
udaExec = teradata.UdaExec(appName="NAME", version="1.0", logConsole=False)
session = udaExec.connect(method="odbc", system="HOST_ADDRESS", username="MY_USERNAME", password="MY_PASSWORD")

I get the following exception

/usr/local/lib/python2.7/dist-packages/teradata/udaexec.pyc in connect(self, externalDSN, dataTypeConverter, **kwargs)
179 except Exception as e:
180 logger.exception("Unable to create connection: %s", paramsToLog)
--> 181 raise e
182
183 def checkpoint(self, checkpointName=None):

DatabaseError: (0, u'[I] [')

Can't make much sense of the message in DataBaseError. I would appreciate help in understanding what the exception is, and resolving it. 

Version information:

Python 2.7.6

terdatata 15.10.0.9

Would be happy to provide more information.

Teradata Employee

Re: Teradata Python Module

@vkrmsv - Can you make sure that ODBCINST or ODBCINI environment variables exist and if not, set them like below assuming you are running on 64-bit Linux?  These environment variables tell the Driver Manager where to find the Teradata ODBC driver.   When I've seen these cryptic error messages before from the driver manager, it was because the environment wasn't setup correctly.  

export ODBCINST=/opt/teradata/client/ODBC_64/odbcinst.ini 

 

or

 

export ODBCINI=/opt/teradata/client/ODBC_64/odbc.ini

Fan

Re: Teradata Python Module

@ericscheie Thanks for the pointer! The problem was because I was using the default odbc drivers. Was able to fix by installing teradata obdc drivers. Perhaps the python module should list them as dependencies (?).