The Teradata Python Module is a freely available, open source, library for the Python programming language, whose aim is to make it easy to script powerful interactions with Teradata Database. It adopts the philosophy of udaSQL, providing a DevOps focused SQL Execution Engine that allows developers to focus on their SQL and procedural logic without worrying about Operational requirements such as external configuration, query banding, and logging.
 
The Teradata Python Module is released under an MIT license. The source is available on GitHub and the package is available for download and install from PyPI.

Table of Contents

1.0 Getting Started

1.1 Installing the Teradata Python Module

1.2 Connectivity Options

1.3 Hello World Example

2.0 DevOps Features

2.1 External Configuration

2.2 Logging

2.3 Checkpoints

2.4 Query Banding

3.0 Database Interactions

3.1 Cursors

3.2 Parameterized SQL

3.3 Stored Procedures

3.4 Transactions

3.5 Data Types

3.6 Unicode

3.7 Ignoring Errors

3.8 Password Protection

3.9 Query Timeouts

3.10 External SQL Scripts

4.0 Reference

4.1 UdaExec Parameters

4.2 Connect Parameters

4.3 Execute Parameters

1.0  Getting Started

The following sections run through installation, connectivity options, and a simple Hello World example.

1.1 Installing the Teradata Python Module

The Teradata Python Module has been certified to work with Python 3.4+ / 2.7+, Windows/Linux/Mac, 32/64 bit.

The easiest way to install the “teradata” python module is using pip.

pip install teradata

If you don’t have pip installed, you can download the package from PyPI, unzip the folder, then double click the setup.py file or run

setup.py install

1.2 Connectivity Options

The Teradata Python Module can use either the REST API for Teradata Database or Teradata ODBC to connect to Teradata.  If using the REST API, make sure Teradata REST Services (tdrestd) is deployed and the target Teradata system is registered with the Service.  If using ODBC, make sure the Teradata ODBC driver is installed on the same machine as where the Teradata Python Module will be executed.

The Teradata Python Module includes two sub-modules that implement the Python Database API Specification v2.0, one using REST (teradata.tdrest) and one using ODBC (teradata.tdodbc).  Though these modules can be accessed directly, its recommended to use the base UdaExec module instead as it provides all the extra DevOps enabled features.

1.3 Hello World Example

In this example, we will connect to a Teradata Database and run a simple query to fetch the Query Band information for the session that we create.  

Example 1 - HelloWorld.py

import teradata

udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
        logConsole=False)

session = udaExec.connect(method="odbc", system="tdprod",
        username="xxx", password="xxx");

for row in session.execute("SELECT GetQueryBand()"):
    print(row)

Let’s break the example down line by line.   The first line, “import teradata”, imports the Teradata Python Module for use in the script.   

The second line initializes the “UdaExec” framework that provides DevOps support features such as configuration and logging.  We tell UdaExec the name and version of our application during initialization so that we can get feedback about our application in DBQL and Teradata Viewpoint as this information is included in the QueryBand of all Database sessions created by our script.  We also tell UdaExec not to log to the console (e.g. logConsole=False) so that our print statement is easier to read.

The third line creates a connection to a Teradata system named “tdprod” using ODBC as the connection method.   The last line executes the “SELECT GetQueryBand()” SQL statement and iterates over the results, printing each row returned.  Since “SELECT GetQueryBand()” statement only returns one row, only one row is printed.

Let’s go ahead and run the script by executing “python HelloWorld.py”.   Below is the result:

Row 1: ['=S> ApplicationName=HelloWorld;Version=1.0;JobID=1;ClientUser=example;Production=false;
udaAppLogFile=/home/example/PyTd/Example1/logs/HelloWorld.20150608153012-1.log;gitRevision=f4cc453;
gitDirty=False;UtilityName=PyTd;UtilityVersion=15.10.00.00;']

From the output, we see that one row was returned with a single string column.  We also see quite a bit of information was added to the QueryBand of the session we created.  We can see the application name and version we specified when initializing UdaExec as well as the name of a log file.   If we look at this location on the file system we can see the log file that was generated:

2015-06-24 16:30:47,875 - teradata.udaexec - INFO - Initializing UdaExec...
2015-06-24 16:30:47,875 - teradata.udaexec - INFO - Reading config files: ['/etc/udaexec.ini: Not Found', '/home/example/udaexec.ini: Not Found', '/home/example/PyTd/Example1/udaexec.ini: Not Found']
2015-06-24 16:30:47,875 - teradata.udaexec - INFO - No previous run number found as /home/example/PyTd/Example1/.runNumber does not exist. Initializing run number to 1
2015-06-24 16:30:47,875 - teradata.udaexec - INFO - Cleaning up log files older than 90 days.
2015-06-24 16:30:47,875 - teradata.udaexec - INFO - Removed 0 log files.
2015-06-24 16:30:47,883 - teradata.udaexec - INFO - Checkpoint file not found: /home/example/PyTd/Example1/HelloWorld.checkpoint
2015-06-24 16:30:47,883 - teradata.udaexec - INFO - No previous checkpoint found, executing from beginning...
2015-06-24 16:30:47,884 - teradata.udaexec - INFO - Execution Details:
/********************************************************************************
 * Application Name: HelloWorld
 *          Version: 1.0
 *       Run Number: 20150624163047-1
 *             Host: sdlc4157
 *         Platform: Linux-2.6.32-431.el6.x86_64-x86_64-with-centos-6.5-Final
 *          OS User: example
 *   Python Version: 3.4.3
 *  Python Compiler: GCC 4.4.7 20120313 (Red Hat 4.4.7-11)
 *     Python Build: ('default', 'Apr  7 2015 16:47:35')
 *  UdaExec Version: 15.10.00.00
 *     Program Name: HelloWorld.py
 *      Working Dir: /home/example/PyTd/Example1
 *      Git Version: git version 1.7.1
 *     Git Revision: f4cc453
 *        Git Dirty: True [ M Example1/HelloWorld.py,?? Example2/]
 *          Log Dir: /home/example/PyTd/Example1/logs
 *         Log File: /home/example/PyTd/Example1/logs/HelloWorld.20150624163047-1.log
 *     Config Files: ['/etc/udaexec.ini: Not Found', '/home/example/udaexec.ini: Not Found', '/home/example/PyTd/Example1/udaexec.ini: Not Found']
 *      Query Bands: ApplicationName=HelloWorld;Version=1.0;JobID=20150624163047-1;ClientUser=example;Production=false;udaAppLogFile=/home/example/PyTd/Example1/logs/HelloWorld.20150624163047-1.log;gitRevision=f4cc453;gitDirty=True;UtilityName=PyTd;UtilityVersion=15.10.00.00
********************************************************************************/
2015-06-24 16:30:47,884 - teradata.udaexec - INFO - Creating connection: {'password': 'XXXXXX', 'method': 'odbc', 'username': 'xxx', 'system': 'tdprod'}
2015-06-24 16:30:47,884 - teradata.tdodbc - INFO - Loading ODBC Library: libodbc.so
2015-06-24 16:30:48,131 - teradata.udaexec - INFO - Connection successful. Duration: 0.246 seconds. Details: {'password': 'XXXXXX', 'method': 'odbc', 'username': 'xxx', 'system': 'tdprod'}
2015-06-24 16:30:48,132 - teradata.udaexec - INFO - Query Successful. Duration: 0.001 seconds, Rows: 1, Query: SELECT GetQueryBand()
2015-06-24 16:30:48,133 - teradata.tdodbc - WARNING - 1 open connections found on exit, attempting to close...
2015-06-24 16:30:48,185 - teradata.udaexec - INFO - UdaExec exiting.

In the logs, you can see connection information and all the SQL statements submitted along with their durations.  If any errors had occurred, those would have been logged too. 

The second to last log entry is a WARNING message that an open connection was not explicitly closed.  Explicitly closing resources when done is always a good idea.   In the next sections, we show how this can be done automatically using the “with” statement. 

2.0 DevOps Features

The following sections discuss the DevOps oriented features provided by the Teradata Python Module.  These features help simplify development and provide the feedback developers need once their applications are put into QA and production.

2.1 External Configuration

In the first “Hello World” example, we depended on no external configuration information for our script to run.  What if we now wanted to run our HelloWorld.py script against a different database system?  We would need to modify the source of our script, which is somewhat inconvenient and error prone.   Luckily the UdaExec framework makes it easy to maintain configuration information outside of our source code.

Example 2 – PrintTableRows.py

import teradata

udaExec = teradata.UdaExec ()

with udaExec.connect("${dataSourceName}") as session: 
    for row in session.execute("SELECT * FROM ${table}"):
        print(row)

In this example, we remove all the hard coded configuration data and instead load our configuration parameters from external configuration files. We also call connect using the “with” statement so that the connection is closed after use even when exceptions are raised.

You may be wondering what ${dataSourceName} means above.  Well, a dollar sign followed by   optional curly braces means replace ${whatever} with the value of the external configuration variable named “whatever”.   In this example, we make a connection to a data source whose name and configuration is defined outside of our script.  We then perform a SELECT on a table whose name is also configured outside of our script. 

UdaExec allows any SQL statement to make reference to an external configuration parameter using the dollar sign/curly brace syntax.  When actually wanting to include a “$” literal in a SQL statement that isn’t a parameter substitution, you must escape the dollar sign with another dollar sign (e.g. “$$”).

Here is our external configuration file that we name “udaexec.ini” and place in the same directory as our python script.

Example 2 - udaexec.ini

# Application Configuration
[CONFIG]
appName=PrintTableRows
version=2
logConsole=False
dataSourceName=TDPROD
table=DBC.DBCInfo 

# Default Data Source Configuration
[DEFAULT]
method=odbc
charset=UTF8

# Data Source Definition
[TDPROD]
system=tdprod
username=xxx
password=xxx

An external configuration file should contain one section named “CONFIG” that contains application configuration name/value pairs, a section named “DEFAULT” that contains default data source name/value pairs, and one or more user defined sections that contain data source name/value pairs.

In this example, we are connecting to ${dataSourceName}, which resolves to “TDPROD” as dataSourceName is a property in the CONFIG section.    The TDPROD data source is defined in our configuration file and provides the name of the system we are connecting to as well as the username and password.  It also inherits the properties in the DEFAULT section, which in this case, defines that we will use ODBC as the connection method and “UTF8” as the session character set.

You’ll notice in this example we didn’t specify the “appName” and “version” when initializing UdaExec.  If you look at the method signature for UdaExec, you’ll see that the default values for appName and version are “${appName}” and “${version}”.   When not specified as method arguments, these values are looked up in the external configuration.  This is true for almost all configuration parameters that can be passed to the UdaExec constructor so that any setting can be set or changed without changing your code. 

If we run the example script above using “python PrintTableRows.py”, we get the following output:

Row 1: ['LANGUAGE SUPPORT MODE', 'Standard']
Row 2: ['RELEASE', '15.00.01.02']
Row 3: ['VERSION', '15.00.01.02']

Looking at the generated log file, we see the following log entry:

2015-06-08 16:54:55,728 - teradata.udaexec - INFO - Reading config files: ['/etc/udaexec.ini: Not Found', 
'/home/example/udaexec.ini: Not Found', '/home/example/PyTd/Example2/udaexec.ini: Found']

As you can see, UdaExec is attempting to load external configuration from multiple files.   By default, UdaExec looks for a system specific configuration file, a user specific configuration file, and an application specific configuration file.   The location of these files can be specified as arguments to the UdaExec constructor.  Below are the argument names along with their default values.

Table 1 – Config File Locations

Name

Description

Default Value

systemConfigFile

The system wide configuration file(s).  Can be a single value or a list.  

"/etc/udaexec.ini"

userConfigFile

The user specific configuration file(s).  Can be a single value or a list.  

"~/udaexec.ini" or "%HOMEPATH%/udaexec.ini"

appConfigFile

The application specific configuration file (s).  Can be a single value or a list. 

"udaexec.ini"

Configuration data is loaded in the order shown above, from least specific to most specific, with later configuration files overriding the values specified by earlier configuration files when conflicts occur.

If we had wanted to name our configuration file in this example “PrintTableRows.ini” instead of “udaexec.ini”, then we could’ve specified that when creating the UdaExec object.   E.g.

udaExec = teradata.UdaExec (appConfigFile="PrintTableRows.ini")

If we wanted to have multiple application configuration files, then we could’ve specified a list of file names instead.  E.g.

udaExec = teradata.UdaExec (appConfigFile=["PrintTableRows.ini", "PrintTableRows2.ini"])

If you find that even that isn’t flexible enough, you can always override the external configuration file list used by UdaExec by passing it in the “configFiles” argument.   When the “configFiles” list is specified,  systemConfigFile, userConfigFile, and appConfigFile values are ignored.

In addition to using external configuration files, application configuration options can also be specified via the command line.  If we wanted to change the table name we select from in the example above, we can specify the table value on the command line e.g. “python PrintTableRows.py --table=ExampleTable” which would instead print the rows of a table named “ExampleTable”.  Configuration options specified on the command line override those in external configuration files.   UdaExec has a parameter named “parseCmdLineArgs” that is True by default.   You can set this value to False to prevent command line arguments from being included as part of the UdaExec configuration.

Sometimes it may be necessary to get or set UdaExec application configuration parameters in the code directly.  You can do this by using the “config” dictionary-like object on the UdaExec instance.  E.g.

udaExec = teradata.UdaExec ()
print(udaExec.config["table"])
udaExec.config["table"] = "ExampleTable" 

As you can see, using external configuration makes it easy to write scripts that are reasonably generic and that can execute in a variety of environments. The same script can be executed against a Dev, Test, and Prod environment with no changes, making it easier to adopt and automate a DevOps workflow.

2.2 Logging

The UdaExec object automatically enables logging when it is initialized.  Logging is implemented using Python’s standard logging module.  If you create a logger in your script, your custom log messages will also be logged along with the UdaExec log messages.

By default, each execution of a script that creates the UdaExec object gets its own unique log file.   This has the potential to generate quite a few files.   For this reason, UdaExec also automatically removes log files that are older than a configurable number of days.

Below is a list of the different logging options and their default values.  Logging options can be specified in the UdaExec constructor, in the application section of external configuration files, or on the command line.

Table 2 – Logging Options

Name

Description

Default Value

configureLogging

 Flags if UdaExec will configure logging.  

True

logDir

The directory that contains log files.

"logs"

logFile

The log file name. 

"${appName}.${runNumber}.log"

logLevel

The level that determines what log messages are logged (i.e. CRITICAL, ERROR, WARNING, INFO, DEBUG, TRACE)

"INFO"

logConsole

Flags if logs should be written to stdout in addition to the log file.

True

logRetention

The number of days to retain log files.  Files in the log directory older than the specified number of days are deleted.

90

If the logging features of UdaExec don’t meet the requirements of your application, then you can configure UdaExec not to configure logging and instead configure it yourself.

Log messages generated at INFO level contain all the status of all submitted SQL statements and their durations.   If there are problems during script execution, the log files provide the insight needed to diagnose any issues.  If more information is needed, the log level can be increased to "DEBUG" or "TRACE".

2.3 Checkpoints

When an error occurs during script execution, exceptions get raised that typically cause the script to exit.   Let’s suppose you have a script that performs 4 tasks but it is only able to complete 2 of them before an unrecoverable exception is raised.  In some cases, it would be nice to be able to re-run the script when the error condition is resolved and have it automatically resume execution of the 2 remaining tasks.   This is exactly the reason UdaExec includes support for checkpoints.

A checkpoint is simply a string that denotes some point during script execution.  When a checkpoint is reached, UdaExec saves the checkpoint string off to a file.  UdaExec checks for this file during initialization.  If it finds a previous checkpoint, it will ignore all execute statements until the checkpoint specified in the file is reached.

Example 3  - CheckpointExample.py

import teradata

udaExec = teradata.UdaExec()
with udaExec.connect("${dataSourceName}") as session:
    session.execute("-- Task 1")
    udaExec.checkpoint("Task 1 Complete")

    session.execute("-- Task 2")
    udaExec.checkpoint("Task 2 Complete")

    session.execute("-- Task 3")
    udaExec.checkpoint("Task 3 Complete")

    session.execute("-- Task 4")
    udaExec.checkpoint("Task 4 Complete")


# Script completed successfully, clear checkpoint
# so it executes from the beginning next time
udaExec.checkpoint()

In the example above, we are calling execute 4 different times and setting a checkpoint after each call. If we were to re-run the script after the 3rd execute failed, the first two calls to execute would be ignored.   Below are the related log entries when re-running our CheckpointExample.py script after the 3rd execute failed.

2015-06-25 14:15:29,017 - teradata.udaexec - INFO - Initializing UdaExec...
2015-06-25 14:15:29,026 - teradata.udaexec - INFO - Found checkpoint file: "/home/example/PyTd/Example3/CheckpointExample.checkpoint"
2015-06-25 14:15:29,027 - teradata.udaexec - INFO - Resuming from checkpoint "Task 2 Complete".
2015-06-25 14:15:29,028 - teradata.udaexec - INFO - Creating connection: {'method': 'odbc', 'system': 'tdprod', 'username': 'xxx', 'password': 'XXXXXX', 'dsn': 'TDPROD'}
2015-06-25 14:15:29,250 - teradata.udaexec - INFO - Connection successful. Duration: 0.222 seconds. Details: {'method': 'odbc', 'system': 'tdprod', 'username': 'xxx', 'password': 'XXXXXX', 'dsn': 'TDPROD'}
2015-06-25 14:15:29,250 - teradata.udaexec - INFO - Skipping query, haven't reached resume checkpoint yet.  Query:  -- Task 1
2015-06-25 14:15:29,250 - teradata.udaexec - INFO - Skipping query, haven't reached resume checkpoint yet.  Query:  -- Task 2
2015-06-25 14:15:29,250 - teradata.udaexec - INFO - Reached resume checkpoint: "Task 2 Complete".  Resuming execution...
2015-06-25 14:15:29,252 - teradata.udaexec - INFO - Query Successful. Duration: 0.001 seconds, Rows: 0, Query: -- Task 3
2015-06-25 14:15:29,252 - teradata.udaexec - INFO - Reached checkpoint: "Task 3 Complete"
2015-06-25 14:15:29,252 - teradata.udaexec - INFO - Saving checkpoint "Task 3 Complete" to /home/example/PyTd/Example3/CheckpointExample.checkpoint.
2015-06-25 14:15:29,253 - teradata.udaexec - INFO - Query Successful. Duration: 0.001 seconds, Rows: 0, Query: -- Task 4
2015-06-25 14:15:29,254 - teradata.udaexec - INFO - Reached checkpoint: "Task 4 Complete"
2015-06-25 14:15:29,254 - teradata.udaexec - INFO - Saving checkpoint "Task 4 Complete" to /home/example/PyTd/Example3/CheckpointExample.checkpoint.
2015-06-25 14:15:29,328 - teradata.udaexec - INFO - Clearing checkpoint....
2015-06-25 14:15:29,329 - teradata.udaexec - INFO - Removing checkpoint file /home/example/PyTd/Example3/CheckpointExample.checkpoint.
2015-06-25 14:15:29,329 - teradata.udaexec - INFO - UdaExec exiting.

As you can see from the logs, all calls to execute are skipped until the “Task 2 Complete” checkpoint is reached.    At the end of our script we call “udaExec.checkpoint()” without a checkpoint string.  This call clears the checkpoint file so that the next time we run our script, it will execute from the beginning.

While skipping calls to execute help to resume after an error, there are situations where this alone will not always work.   If the results of a query are necessary for program execution, then the script may hit additional errors when being resumed.   For example, let’s assume our script now loads a configuration parameter from a table. 

udaExec.config["mysetting"] = session.execute("SELECT mysetting FROM 
	MyConfigTable").fetchone()[0]

A call to execute returns a Cursor into a result set, so we call fetchone()[0] to get the first column of the first row in the result set. If the execute call is skipped, then fetchone() will return None and the lookup of the first column will fail.  There are several ways we can workaround this problem.  The first way is to force execute to run regardless of checkpoints by specifying the parameter runAlways=True.  E.g.

udaExec.config["mysetting"] = session.execute("SELECT mysetting FROM 
	MyConfigTable", runAlways=True).fetchone()[0]

This is a good approach if we want to set “mysetting” even on resume.  If “mysetting” is not necessary for resume though, then another way to prevent errors is to check the UdaExec “skip” attribute.  E.g.

if not udaExec.skip:
    udaExec.config["mysetting"] = session.execute("SELECT mysetting FROM 
	MyConfigTable").fetchone()[0]

With this approach, we only access the “mysetting” column if execute will not be skipped. 

UdaExec saves checkpoints to a file named "${appName}.checkpoint" located in the same directory the script is executed by default.  The checkpoint file can be changed by specifying the “checkpointFile” parameter in the UdaExec constructor, in an external configuration file, or on the command line.   To disable file-based checkpoints, “checkpointFile” can be set to None in the UdaExec constructor or it can be set to an empty string in an external configuration file.

If it is desirable to load checkpoints from and save checkpoints to a place other than a local file (e.g. a database table), then a custom checkpoint manager implementation can be used to handle loading, saving, and clearing checkpoint details. Below is an example of a custom checkpoint manager that loads and saves checkpoints to a database table.

class MyCheckpointManager (teradata.UdaExecCheckpointManager):
    def __init__(self, session):
	self.session = session
    def loadCheckpoint(self):
        for row in self.session.execute("""SELECT * FROM ${checkPointTable} 
                                           WHERE appName = '${appName}'"""):
            return row.checkpointName
    def saveCheckpoint(self, checkpointName):
        self.session.execute("""UPDATE ${checkPointTable} SET checkpointName = ? 
                                WHERE appName = '${appName}' ELSE 
                                INSERT INTO ${checkPointTable} VALUES ('${appName}', ?)""", 
                             (checkpointName, checkpointName))
    def clearCheckpoint(self):
        self.session.execute("""DELETE FROM ${checkPointTable} 
                                WHERE appName = '${appName}'""", 
                             ignoreErrors=[3802])      

To use this custom checkpoint manager, you can disable the checkpointFile and call the setCheckpointManager method on UdaExec.  E.g.

udaexec = teradata.UdaExec(checkpointFile=None)
with udaexec.connect("${dsn}") as session:  
    udaexec.setCheckpointManager(MyCheckpointManager(session))
    # The rest of my program logic.

2.4 Query Banding

UdaExec automatically sets session Query Bands for any connections you create so that the runtime characteristics of your application can be monitored in DBQL and Teradata Viewpoint.    Reviewing application log files along with the associated log entries in DBQL are great ways to get feedback on the overall execution of your application.  The table below lists the name and descriptions of the Query Bands that are set.

Table 3 - Query Bands

Name

Description

ApplicationName

The name of your application

Version

The version of your application

JobID

The run number of this particular execution

ClientUser

The OS user name.

Production

True if a production App, else False

udaAppLogFile

Path of the generated log file

gitRevision

The GIT revision of the application.

gitDirty

True if files have been modified since last commit to GIT

UtilityName

The nickname of the Teradata Python Module -  PyTd

UtilityVersion

The version of the Teradata Python Module

Additional custom Query Bands can be set by passing a map (dict) as the queryBand argument to UdaExec.connect().

3.0 Database Interactions

UdaExec implements the Python Database API Specification v2.0 while adding additional convenience on top.  The only deviation from this specification is that UdaExec enables auto commit by default.  It is recommended to review the Python Database API Specification v2.0 first and then review the following sections for more details.

3.1 Cursors

Since only a single Cursor is needed most of the time, UdaExec creates an internal cursor for each call to connect() and allows execute, executemany, and callproc to be called directly on the connection object.  Calls to these methods on the Connection object simply invoke those same methods on the internal cursor.   The internal cursor is closed when the connection is closed.

Calls to execute, executemany, and callproc return the Cursor for convenience.  Cursors act as iterators, so the results of an execute call can easily be iterated over in a “for” loop.  Rows act like tuples or dictionaries, and even allow columns to be accessed by name similar to attributes on an object.   Below is an example.  All 3 print statements print the same thing for each row.

import teradata
udaExec = teradata.UdaExec()
with udaExec.connect("${dataSourceName}") as session:
for row in session.execute("""SELECT InfoKey AS name, InfoData as val 
           FROM DBC.DBCInfo"""):
        print(row[0] + ": " + row[1])
        print(row["name"] + ": " + row["val"])
        print(row.name + ": " + row.val)

There are situations where it may be necessary to use a separate cursor in addition to the one created by default.   A good example of this is when wanting to perform queries while iterating over the results of another query.   To accomplish this, two cursors must be used, one to iterate and one to invoke the additional queries.  Below is an example.

import teradata
udaExec = teradata.UdaExec()
with udaExec.connect("${dataSourceName}") as session:
    with session.cursor() as cursor:
    for row in cursor.execute("SELECT * from ${tableName}"):
            session.execute("DELETE FROM ${tableName} WHERE id = ?", (row.id, )):

Like connections, cursors should be closed when you're finished using them.   This is best accomplished using the “with” statement.

3.2 Parameterized SQL

You can pass parameters to SQL statements using the question mark notation.   The following example inserts a row into an employee table.

session.execute("""INSERT INTO employee (id, firstName, lastName, dob) 
                   VALUES (?, ?, ?, ?)""", (1,"James", "Kirk", "2233-03-22"))

To insert multiple rows, executemany can be used.  To insert them using batch mode, pass in the parameter batch=True. E.g.

session.executemany("""INSERT INTO employee (id, firstName, lastName, dob) 
                       VALUES (?, ?, ?, ?)""", 
                    ((1,"James", "Kirk", "2233-03-22"), 
                     (2,"Jean-Luc", "Picard", "2305-07-13")), 
                    batch=True)

Batch mode sends all the parameter sequences to the database in a single “batch” and is much faster than sending the parameter sequences individually.

3.3 Stored Procedures

Stored procedures can be invoked using the “callproc” method.    OUT parameters should be specified as teradata.OutParam instances.  INOUT parameters should be specified as teradata.InOutParam instances.   An optional name can be specified with output parameters that can be used to access the returned parameter by name.  Additionally, a data type name can be specified so that the output parameter is converted to the proper Python object.  E.g.

results = session.callproc("MyProcedure", (teradata.InOutParam("inputValue", "inoutVar1"), teradata.OutParam(), teradata.OutParam("outVar2", dataType="PERIOD")))
print(results.inoutVar1)
print(results.outVar1)

3.4 Transactions

UdaExec enables auto commit by default.   To disable auto commit and instead commit transactions manually, set autoCommit=False on the call to connect or in the data source’s external configuration.

Transactions can be manually committed or rolled back using the commit() and rollback() methods on the Connection object.  E.g.

import teradata
udaExec = teradata.UdaExec()
with udaExec.connect("${dataSourceName}", autoCommit=False) as session:
    session.execute("CREATE TABLE ${tableName} (${columns})")
    session.commit()

3.5 Data Types

To keep a consistent interface and implementation for both REST and ODBC, UdaExec gets all data values, with the exception of binary data (e.g. BYTE, VARBYTE, BLOB), in their string representation before converting them to their Python representation.

The interface that UdaExec uses to perform the conversion is called teradata.datatypes.DataTypeConverter with the default implementation being teradata.datatypes.DefaultDataTypeConverter.   If you would like to customize how data gets converted from strings to Python objects, you can specify a custom DataTypeConverter during connect.   E.g.

udaExec.connect("${dataSourceName}", dataTypeConverter=MyDataTypeConverter())

It is recommended to derive your custom DataTypeConverter from DefaultDataTypeConverter so that you can perform conversion for the data types you’re interested in while delegating to the default implementation for any of the remaining ones.

The table below specifies the data types that get converted by the DefaultDataTypeConverter.  Any data types not in the table below are returned as a Python Unicode string (e.g. VARCHAR, CLOB, UDT, ARRAY, etc.)

Data Type

Python Object

BYTE

bytearray

VARBYTE

bytearray

BYTEINT

decimal.Decimal

SMALLINT

decimal.Decimal

INTEGER

decimal.Decimal

BIGINT

decimal.Decimal

REAL, FLOAT, DOUBLE PRECISION

decimal.Decimal

DECIMAL, NUMERIC

decimal.Decimal

NUMBER

decimal.Decimal

DATE

datetime.date

TIME

datetime.time

TIME WITH TIME ZONE

datetime.time

TIMESTAMP

datetime.datetime

TIMESTAMP WITH TIME ZONE

datetime.datetime

INTERVAL

teradata.datatypes.Interval

BLOB

bytearray

JSON

dict or list, result of json.loads()

PERIOD

teradata.datatypes.Period

3.6 Unicode

The Teradata Python Module supports Unicode by default but you must make sure your session character set is set to UTF8 or UTF16 to successfully submit or retrieve Unicode data.   If this is not the default, you can explicitly set your session character set by passing in “charset=UTF8” into the connect method or by specifying it in your data sources external configuration.

3.7 Ignoring Errors

Sometimes it is necessary to execute a SQL statement even though there is a chance it may fail.  For example, if your script depends on a table that may or may not already exist, the simple thing to do is to try to create the table and ignore the “table already exists” error.  UdaExec makes it easy to do this by allowing clients to specify error codes that can safely be ignored.   For example, the following execute statement will not raise an error even if the checkpoints table already exists.

session.execute("""CREATE TABLE ${dbname}.checkpoints (
    appName VARCHAR(1024) CHARACTER SET UNICODE, 
    checkpointName VARCHAR(1024) CHARACTER SET UNICODE)
    UNIQUE PRIMARY INDEX(appName)""",
    ignoreErrors=[3803])

If you want to ignore all errors regardless of the error code, you can include the “continueOnError=True” parameter to execute.  This will cause any errors to be caught and logged and not raised up to your application.

3.8 Password Protection

Teradata ODBC along with Teradata Wallet can be used to avoid storing passwords in clear text in external configuration files.   As UdaExec uses dollar signs to reference external configuration values, dollar signs used to reference Teradata Wallet keys must be escaped with an extra dollar sign.  E.g.

udaExec.connect("${dataSourceName}", password="$$tdwallet(password_$$(tdpid)")

3.9 Query Timeouts

The execute, executemany, and callproc methods all accept a queryTimeout parameter for specifying the number of seconds to wait for the query to return.   If the query does not complete within the specified timeout, it is aborted and an exception will be raised.   E.g.

session.execute("SELECT * FROM ${table}", queryTimeout=60)

3.10 External SQL Scripts

UdaExec can be used to execute SQL statements that are stored in files external to your Python script.  To execute the SQL statements in an external file, simply pass the execute method the location of the file to execute.  E.g.

session.execute(file="myqueries.sql")

A semi-colon is used as the default delimiter when specifying multiple SQL statements.   Any occurrence of a semi-colon outside of a SQL string literal or comments is treated as a delimiter.   When SQL scripts contain SQL stored procedures that contain semi-colons internal to the procedure, the delimiter should be change to something other than the default.  To use a different character sequence as the delimiter, the delimiter parameter can be used.  E.g.

session.execute(file="myqueries.sql", delimiter=";;")

UdaExec also has limited support for executing BTEQ scripts.   Any BTEQ commands starting with a “.” are simply ignored, while everything else is treated as a SQL statement and executed.   To execute a BTEQ script, pass in a fileType="bteq" parameter.  E.g.

session.execute(file="myqueries.bteq", fileType="bteq")

SQL statements in external files can reference external configuration values using the ${keyname} syntax.  Therefore, any use of “$” in an external SQL file must be escaped if it is not intended to reference an external configuration value.

Any parameters passed to execute will be passed as parameters to the SQL statements in the external file.   Execute will still return a cursor when executing a SQL script, the cursor will point to the results of the last SQL statement in the file.

Comments can be included in SQL files.  Multi-line comments start with "/*" and end with "*/".  Single line comments start with "--".   Comments are submitted to the database along with the individual SQL statements.

4.0 Reference

This section defines the full set of method parameters supported by the API.

4.1 UdaExec Parameters

UdaExec accepts the following list of parameters during initialization.   The column labeled “E” flags if a parameter can be specified in an external configuration file.

Name

Description

E

Default Value

appName

The name of our application

Y

None - Required field

version

The version of our application

Y

None - Required field

checkpointFile

The location of the checkpoint file.  Can be None to disable file-based checkpoints.

Y

${appName}.checkpoint

runNumberFile

The path of the file containing the previous runNumber.

Y

.runNumber

runNumber

A string that represents this particular execution of the python script.   Used in the log file name as well as included in the Session QueryBand.

Y

YYYYmmddHHMMSS-X

configureLogging

Flags if UdaExec will configure logging.  

Y

True

logDir

The directory that contains log files.

Y

"logs"

logFile

The log file name. 

Y

"${appName}.${runNumber}.log"

logLevel

The level that determines what log messages are logged (i.e. CRITICAL, ERROR, WARNING, INFO, DEBUG, TRACE)

Y

"INFO"

logConsole

Flags if logs should be written to stdout in addition to the log file.

Y

True

logRetention

The number of days to retain log files.  Files in the log directory older than the specified number of days are deleted.

Y

90

systemConfigFile

The system wide configuration file(s).  Can be a single value or a list.  

N

"/etc/udaexec.ini"

userConfigFile

The user specific configuration file(s).  Can be a single value or a list.  

N

"~/udaexec.ini" or "%HOMEPATH%/udaexec.ini"

appConfigFile

The application specific configuration file (s).  Can be a single value or a list. 

N

"udaexec.ini"

configFiles

The full list of external configuration files.  Overrides any values in systemConfigFile, userConfigFile, appConfigFile.

N

None

configSection

The name of the application config section in external configuration files.

N

CONFIG

parseCmdLineArgs

Flags whether or not to include command line arguments as part of the external configuration variables.

N

True

gitPath

The path to the GIT executable to use to include GIT information in the session QueryBand.

Y

Defaults to system path

production

Flags if this app is a production application, applies this value to session QueryBand.

Y

False

odbcLibPath

The path to the ODBC library to load.

Y

Defaults to OS specific library path

dataTypeConverter

The DataTypeConverter implementation to use to convert data types from their string representation to python objects.

N

datatypes.DefaultDataTypeConverter()

4.2 Connect Parameters

The following table lists the parameters that the UdaExec.connect() method accepts.   With the exception of the “externalDSN” parameter, all the parameters below can be specified in the DEFAULT or named data source sections of external configuration files.   While the externalDSN parameter cannot be specified directly in an external configuration file, it can reference the name of an external configuration variable using ${keyname} syntax.   The “Type” column indicates if a parameter is specific to a connectivity option, if it is blank it applies to all types.

When using ODBC as the connection method, any parameters passed to the connect method or specified in an external configuration that are not listed below will be automatically be appened to the connect string passed to the ODBC driver.  For example, to reference a named data source defined in an odbc.ini file, you can simply call udaExec.connect(method="odbc", DSN="mydsn"). 

Name

Description

Type

Default Value

externalDSN

The name of the data source defined in external configuration files.

 

None - Optional

method

The type of connection to make.  Possible values are “rest” or “odbc”

 

None - Required field

dbType

The type of system being connected to.  The only supported option at the present release is “Teradata”

 

Teradata

system

The name of the system to connect.  For ODBC it’s the tdpid, for REST its the system alias configured in the REST service

 

None

username

The Database username to use to connect.

 

None

password

The Database password to use to connect.  

 

None

host

The host name of the server hosting the REST service.

REST

None

port

The port number of REST Service

REST

Defaults to 1080 for http and 1443 for https

protocol

The protocol to use for REST connections (i.e. http or https).  When using https,

REST

http

webContext

The web context of the REST service

REST

/tdrest

charset

The session character set (e.g. UTF8, UTF16, etc.)

 

None

database

The default database name to apply to the session

  None

autoCommit

Enables or disables auto commit mode.  When auto commit mode is disabled, transactions must be committed manually.

 

True

transactionMode

The transaction mode to use i.e. “Teradata” or “ANSI”

 

Teradata

queryBands

A map (dict) of query band key/value pairs to include the session’s QueryBand.

 

None

dataTypeConverter

The DataTypeConverter implementation to use to convert data types from their string representation to python objects.

 

datatypes.DefaultDataTypeConverter()

sslContext

The ssl.SSLContext to use to establish SSL connections.

REST

None

verifyCerts

Flags if REST SSL certificate should be verified, ignored if sslContext is not None.

REST

True

**kwargs

A variable number of name/value pairs to append to the ConnectString passed to SQLDriverConnect.  For the full list of options supported by the Teradata ODBC driver, see the ODBC Driver for Teradata User Guide.

ODBC

None

4.3 Execute Parameters

The following table lists the parameters that the execute method accepts.

Name

Description

Default Value

query

The query to execute.

None, required if file is None

params

The list or tuple containing the parameters to pass in to replace question mark placeholders.

None

file

The path of an external script to execute.

None

fileType

The type of file to execute if different than a standard delimited SQL script (i.e. bteq)

None

delimiter

The delimiter character to use for  SQL scripts.

;

runAlways

When True, the query or script will be executed regardless if the previous checkpoint has been reached.

False

continueOnError

When True, all errors will be caught and logged but not raised up to the application.

False

ignoreErrors

The list or sequence of error codes to ignore.  

None

queryTimeout

The number of seconds to wait for a response before aborting the query and returning.

0 - indicates wait indefinitely

logParamCharLimit

The maximum number of characters to log per query parameter.  When a parameter exceeds the limit it is truncated in the logs and an ellipsis ("...") is appended.   

80 characters per parameter
logParamFrequency

The amount of parameter sets to log when executemany is invoked.  Setting this value to X means that every Xth parameter set will be logged in addition to the first and last parameter set.   When this value is set to zero, no parameters are logged.

 1 - all parameters sets are logged.

 

Discussion
DiEgoR 10 comments Joined 08/06
10 Jan 2016

If I specify odbcLibPath=/opt/teradata/client/14.10/odbc_64/lib/tdata.so
in  ~/udaexec.ini Then I get following error

2016-01-10 12:08:43,715 - teradata.tdodbc - INFO - Loading ODBC Library: /opt/teradata/client/14.10/odbc_64/lib/tdata.so
2016-01-10 12:08:43,716 - teradata.udaexec - ERROR - Unable to create connection: {same connection string}
Traceback (most recent call last):
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/udaexec.py", line 172, in connect
    **args))
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 345, in __init__
    init(odbcLibPath)
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 301, in init
    initOdbcLibrary(odbcLibPath)
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 275, in initOdbcLibrary
    odbc = ctypes.cdll.LoadLibrary(odbcLibPath)
  File "/home/centos/anaconda2/lib/python2.7/ctypes/__init__.py", line 443, in LoadLibrary
    return self._dlltype(name)
  File "/home/centos/anaconda2/lib/python2.7/ctypes/__init__.py", line 365, in __init__
    self._handle = _dlopen(self._name, mode)
OSError: libddicu26.so: cannot open shared object file: No such file or directory

 
And the libddicu26.so can be found here:
/opt/teradata/client/14.10/odbc_64/lib/libddicu26.so
 

input output putput

DiEgoR 10 comments Joined 08/06
10 Jan 2016
ldd /opt/teradata/client/14.10/odbc_64/lib/tdata.so
/opt/teradata/client/14.10/odbc_64/lib/tdata.so: /lib64/libodbcinst.so: no version information available (required by /opt/teradata/client/14.10/odbc_64/lib/tdata.so)
        linux-vdso.so.1 =>  (0x00007ffd0fd16000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f5f4f553000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f5f4f33d000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f5f4f120000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f5f4ef1c000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f5f4ed14000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f5f4eafa000)
        libodbcinst.so => /lib64/libodbcinst.so (0x00007f5f4e8e8000)
        libddicu26.so => not found
        libtdparse.so => not found
        libicudatatd.so.46 => /lib64/libicudatatd.so.46 (0x00007f5f4e09d000)
        libicuuctd.so.46 => /lib64/libicuuctd.so.46 (0x00007f5f4de10000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f5f4db0d000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f5f4d74c000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f5f4fc61000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f5f4d542000)

but both libddicu26.so and libtdparse.so are actually present

find /opt/teradata/ -name libddicu26.so
/opt/teradata/client/14.10/odbc_64/lib/libddicu26.so

find /opt/teradata/ -name libtdparse.so
/opt/teradata/client/14.10/odbc_32/lib/libtdparse.so
/opt/teradata/client/14.10/odbc_64/lib/libtdparse.so

What should I check to fix that?

input output putput

DiEgoR 10 comments Joined 08/06
10 Jan 2016

SOLVED!
I had to do this 

LD_LIBRARY_PATH=/opt/teradata/client/14.10/odbc_64/lib:/opt/teradata/client/14.10/tdicu/lib64:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

 now the libraries can be found

 ldd /opt/teradata/client/14.10/odbc_64/lib/tdata.so
        linux-vdso.so.1 =>  (0x00007ffddcd6a000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f7e8edeb000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7e8ebd4000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7e8e9b8000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7e8e7b4000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f7e8e5ab000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f7e8e392000)
        libodbcinst.so => /opt/teradata/client/14.10/odbc_64/lib/libodbcinst.so (0x00007f7e8e16d000)
        libddicu26.so => /opt/teradata/client/14.10/odbc_64/lib/libddicu26.so (0x00007f7e8d275000)
        libtdparse.so => /opt/teradata/client/14.10/odbc_64/lib/libtdparse.so (0x00007f7e8d0ef000)
        libicudatatd.so.46 => /opt/teradata/client/14.10/tdicu/lib64/libicudatatd.so.46 (0x00007f7e8c8a5000)
        libicuuctd.so.46 => /opt/teradata/client/14.10/tdicu/lib64/libicuuctd.so.46 (0x00007f7e8c617000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7e8c315000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7e8bf54000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f7e8f4f9000)

and the Python module gets connected and I get the data back.
 
Just in case others will hit the other problem I had:
I had udaExec.connect(method="odbc", system=""testdsn"... from my earlier experiments and that did not work.
The python module was reporting a following problem:

Traceback (most recent call last):
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/udaexec.py", line 172, in connect
    **args))
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 358, in __init__
    checkStatus(rc, hDbc=self.hDbc, method="SQLDriverConnectW")
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 194, in checkStatus
    raise DatabaseError(i[2], u"[{}] {}".format(i[0], i[1]), i[0])
DatabaseError: (113, u'[08001] [Teradata][Unix system error]  113 Unable to get catalog string.')

but I can connect with /opt/teradata/client/14.10/odbc_64/bin/tdxodbc
 

Enter Data Source Name: testdsn
Enter UserID: user_name
Enter Password:

Connecting with SQLConnect(DSN=testdsn,UID=user_name,PWD=*)...

.....ODBC connection successful.

ODBC version        = -03.52.0000-
DBMS name           = -Teradata-
DBMS version        = -14.10.0603  14.10.06.03A-
Driver name         = -tdata.so-
Driver version      = -14.10.00.07-
Driver ODBC version = -03.51-

Enter SQL string : select current_date;

Executing SQLExecDirect("select current_date;")...

SQL Statement [1]: 1 rows affected.

Date
2016-01-10


Enter SQL string : quit

'quit' command detected

ODBC connection closed.

OK, so I searched a bit more and found this:

vhari 111 posts Joined 12/08
10 Nov 2009
"[ODBC Teradata Driver] Unable to get catalog string."This error indicates that Teradata ODBC driver trying to post an error, but it cannot find it's error message catalog file.Search for "tdodbc.cat" file and set NLSPATH variable. For example if you find tdodbc.cat in/opt/teradata/client/lib/odbc/tdodbc.catthen setNLSPATH=/opt/teradata/client/lib/odbc/%N.cat---It would let you see the actual error message.

So I did this

export NLSPATH=/opt/teradata/client/14.10/odbc_64/msg/%N.cat

and now I get this

Traceback (most recent call last):
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/udaexec.py", line 172, in connect
    **args))
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 358, in __init__
    checkStatus(rc, hDbc=self.hDbc, method="SQLDriverConnectW")
  File "/home/centos/anaconda2/lib/python2.7/site-packages/teradata/tdodbc.py", line 194, in checkStatus
    raise DatabaseError(i[2], u"[{}] {}".format(i[0], i[1]), i[0])
DatabaseError: (113, u"[08001] [Teradata][Unix system error]  113 Socket error - The Teradata server can't currently be reached over this network")

This means what it says - something wrong with the connection. In my case I had to replace testdsn with the real dns name for the system.

input output putput

ankitp 2 comments Joined 01/16
12 Jan 2016

Hi,

 

I am trying to connect to teradata using python but cannot connect. Below is what i did, please suggest what is going wrong and how can i correct.

# more PrintTableRows.py
import teradata
udaExec = teradata.UdaExec (appName="PrintTableRows", version="1.0",
        logConsole=False)
session = udaExec.connect(method="odbc", system="TeradataPY",
        username="XXXX", password="XXXX");
for row in session.execute("SELECT GetQueryBand()"):
        print(row)

 

Below is udaexec.ini entry.

# more udaexec.ini

# Application Configuration

[CONFIG]

appName=PrintTableRows

version=2

logConsole=False

dataSourceName=TeradataPY

table=DBC.DBCInfo

 

# Default Data Source Configuration

[DEFAULT]

method=odbc

charset=UTF8

 

# Data Source Definition

[TeradataPY]

method=odbc

system=<TeradataDB IP Address>

username=XXXX

password=XXXX

charset=UTF8

 

Below is the execution result.

# python PrintTableRows.py

Traceback (most recent call last):

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

    username="XXXX", password="XXXX");

  File "build/bdist.linux-x86_64/egg/teradata/udaexec.py", line 172, in connect

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 358, in __init__

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 194, in checkStatus

teradata.api.DatabaseError: (0, u'[I] [')

Error in atexit._run_exitfuncs:

Traceback (most recent call last):

  File "/usr/local/lib/python2.7/atexit.py", line 24, in _run_exitfuncs

    func(*targs, **kargs)

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 131, in cleanupConnections

    conn.close()

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 399, in close

    SQL_STATE_INVALID_TRANSACTION_STATE])

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 194, in checkStatus

    raise DatabaseError(i[2], u"[{}] {}".format(i[0], i[1]), i[0])

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

Error in sys.exitfunc:

Traceback (most recent call last):

  File "/usr/local/lib/python2.7/atexit.py", line 24, in _run_exitfuncs

    func(*targs, **kargs)

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 131, in cleanupConnections

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 399, in close

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 194, in checkStatus

teradata.api.DatabaseError: (0, u'[0] [')

stk8806 1 comment Joined 01/16
13 Jan 2016

Running into issues trying to connect. I can connect fine through tdxodbc. Any help?

2016-01-13 12:20:53,782 - teradata.udaexec - INFO - Creating connection: {'method': 'odbc', 'username': 'XXXXXX', 'password': 'XXXXXX', 'system': 'XXXXXXXX'}

2016-01-13 12:20:53,783 - teradata.tdodbc - INFO - Loading ODBC Library: libodbc.so

2016-01-13 12:20:54,107 - teradata.udaexec - ERROR - Unable to create connection: {'method': 'odbc', 'username': 'XXXXXX', 'password': 'XXXXXX', 'system': 'XXXXXXXXX'}

Traceback (most recent call last):

  File "/upapps/sox/sox/lib/python3.4/site-packages/teradata/udaexec.py", line 172, in connect

    **args))

  File "/path/to/lib/python3.4/site-packages/teradata/tdodbc.py", line 374, in __init__

    queryTimeout=QUERY_TIMEOUT).fetchone()[0]

TypeError: 'NoneType' object is not subscriptable

2016-01-13 12:20:54,110 - teradata.udaexec - ERROR - Uncaught exception

Traceback (most recent call last):

  File "test.py", line 14, in <module>

    username=uid, password=pw)

  File "/path/to/lib/python3.4/site-packages/teradata/udaexec.py", line 172, in connect

    **args))

  File "/path/to/lib/python3.4/site-packages/teradata/tdodbc.py", line 374, in __init__

    queryTimeout=QUERY_TIMEOUT).fetchone()[0]

TypeError: 'NoneType' object is not subscriptable

Traceback (most recent call last):

  File "test.py", line 14, in <module>

    username=uid, password=pw)

  File "/path/to/lib/python3.4/site-packages/teradata/udaexec.py", line 172, in connect

    **args))

  File "/path/to/lib/python3.4/site-packages/teradata/tdodbc.py", line 374, in __init__

    queryTimeout=QUERY_TIMEOUT).fetchone()[0]

TypeError: 'NoneType' object is not subscriptable

2016-01-13 12:20:54,112 - teradata.udaexec - INFO - UdaExec exiting.

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

./tdxodbc -C "DRIVER=Teradata;DBCNAME=XXXXXX;UID=XXXXXX;PWD=XXXXXXX;"

 

 

Connecting with SQLDriverConnect("DRIVER=Teradata;DBCNAME=XXXXXXXX;UID=XXXXXXX;PWD=*;")...

 

.....ODBC connection successful.

 

 

ericscheie 68 comments Joined 11/08
13 Jan 2016

@ankitp - Try changing 

session = udaExec.connect(method="odbc", system="TeradataPY",
        username="XXXX", password="XXXX");

to

session = udaExec.connect("TeradataPY");
ericscheie 68 comments Joined 11/08
13 Jan 2016

@stk8806 - From the stack trace, it appears the connection was successful but the python modules call to "SELECT SESSION" to get the session ID for logging purposes is not returning any data.   Any idea why that would be or any details about your environment you can share to help reproduce? 

ankitp 2 comments Joined 01/16
16 Jan 2016

Hi @Ericscheie,
I changed as told, below is the error now.

 session = udaExec.connect("TeradataPY");

  File "build/bdist.linux-x86_64/egg/teradata/udaexec.py", line 172, in connect

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 358, in __init__

  File "build/bdist.linux-x86_64/egg/teradata/tdodbc.py", line 194, in checkStatus

teradata.api.DatabaseError: (0, u'[IM002] [DataDirect][ODBC lib] Data source name not found and no default driver specified')

 

kallu2020 10 comments Joined 03/14
25 Jan 2016

Is this rest service/Rest API available from Td15 onwards? Could you please confirm.
 
Thanks,
Kiran

kallu2020 10 comments Joined 03/14
25 Jan 2016

Np found the answer. Supported Teradata Database Versions
• 13.00
• 13.10
• 14.00
• 14.10
• 15.00

davidtzoor 2 comments Joined 06/14
26 Jan 2016

I get poor performance with the Python Teradata module compared to pyodbc and JDBC. 
Here is a comparison of the 3 modules and the time it took to execute the query with different dataset sizes:
==== time of sample size = 10000 ========
ODBC: 0:00:01.216346
PyTD: 0:00:09.844205
JDBC: 0:00:03.215781
=====================================
==== time of sample size = 50000 ========
ODBC: 0:00:01.270462
PyTD: 0:00:35.796656
JDBC: 0:00:08.558847
=====================================
==== time of sample size = 100000 ========
ODBC: 0:00:02.567906
PyTD: 0:01:09.826736
JDBC: 0:00:18.884113
 
Any idea?

ericscheie 68 comments Joined 11/08
27 Jan 2016

@davidtzoor -  I'm working on a fix to improve fetch performance.   Progress can be tracked here:  https://github.com/Teradata/PyTd/issues/29

28 Jan 2016

Hi Eric, thanks for previous query reply regarding CLOB large dataSet reutrn, you advised to use size param in OutParam to specify the output param size.
Now i have two queries.
Query1: I get this "WARNING WARNING - 1 open connections found on exit, attempting to close.." on teradata module suggested to use "with" clause to explicitly close the connection, but that closes just after first query and i need it be opened till end of script and i should close it my self? how can we get the control to close the connection, cursor and session etc. An example script will help alot.
Query2: Called SP CLOB can return upto 2GB data and i tested the below script till 177011324 (Chars) at this size or greater size memory error reported in python log. Can you tell what maximum return from CLOB this python module can consume or how can we allocate a large memory to the script?
=======
python log
=======

2016-01-28 13:02:47,543 - teradata.udaexec - INFO - Initializing UdaExec...

2016-01-28 13:02:47,543 - teradata.udaexec - INFO - Reading config files: ['C:\\etc\\udaexec.ini: Not Found', 'C:\\test_pyscrript\\%HOMEDRIVE%%HOMEPATH%\\udaexec.ini: Not Found', 'C:\\test_pyscrript\\udaexec.ini: Not Found']

2016-01-28 13:02:47,544 - teradata.udaexec - INFO - Found run number file: "C:\test_pyscrript\.runNumber"

2016-01-28 13:02:47,544 - teradata.udaexec - INFO - Cleaning up log files older than 90 days.

2016-01-28 13:02:47,544 - teradata.udaexec - INFO - Removed 0 log files.

2016-01-28 13:02:47,597 - teradata.udaexec - INFO - Checkpoint file not found: C:\test_pyscrript\test.checkpoint

2016-01-28 13:02:47,598 - teradata.udaexec - INFO - No previous checkpoint found, executing from beginning...

2016-01-28 13:02:47,598 - teradata.udaexec - INFO - Execution Details:

/********************************************************************************

 * Application Name: test

 *          Version: 1.0

 *       Run Number: 20160128130247-86

 *             Host: WPKAN255019-G96

 *         Platform: Windows-7-6.1.7601-SP1

 *          OS User: an255019

 *   Python Version: 3.5.1

 *  Python Compiler: MSC v.1900 32 bit (Intel)

 *     Python Build: ('v3.5.1:37a07cee5969', 'Dec  6 2015 01:38:48')

 *  UdaExec Version: 15.10.0.11

 *     Program Name: GenTPT.py

 *      Working Dir: C:\test_pyscrript

 *          Log Dir: C:\test_pyscrript\logs

 *         Log File: C:\test_pyscrript\logs\test.20160128130247-86.log

 *     Config Files: ['C:\\etc\\udaexec.ini: Not Found', 'C:\\test_pyscrript\\%HOMEDRIVE%%HOMEPATH%\\udaexec.ini: Not Found', 'C:\\test_pyscrript\\udaexec.ini: Not Found']

 *      Query Bands: ApplicationName=test;Version=1.0;JobID=20160128130247-86;ClientUser=an255019;Production=False;udaAppLogFile=C:\test_pyscrript\logs\test.20160128130247-86.log;UtilityName=PyTd;UtilityVersion=15.10.0.11

********************************************************************************/

2016-01-28 13:02:47,599 - teradata.udaexec - INFO - Creating connection: {'username': 'dbc', 'password': 'XXXXXX', 'system': 'tddemo', 'method': 'odbc'}

2016-01-28 13:02:58,276 - teradata.udaexec - INFO - Connection successful. Duration: 10.677 seconds. Details: {'username': 'dbc', 'password': 'XXXXXX', 'system': 'tddemo', 'method': 'odbc'}

2016-01-28 13:02:58,327 - teradata.udaexec - INFO - Query Successful. Duration: 0.027 seconds, Rows: 1, Query: select Process_Type from GDEV1V_GCFR.GCFR_Process where Process_Name='LD_668_66_Customer'

2016-01-28 13:02:58,406 - teradata.udaexec - ERROR - Procedure Failed! Duration: 0.075 seconds, Procedure: GDEV1P_FF.GCFR_FF_TPTLoadCLOB_Generate, Params: ('LD_668_66_Customer', 6, 17, 0, 'localhost', 'dbc', 'dbc', OutParam(name=oCode, size=None), OutParam(name=oMessage, size=None), OutParam(name=oScript, size=200000000), OutParam(name=oParams, size=None), OutParam(name=oLogon, size=None))

Traceback (most recent call last):

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\udaexec.py", line 690, in callproc

    outparams = self.cursor.callproc(procname, params, **kwargs)

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\tdodbc.py", line 477, in callproc

    self.execute(query, params, queryTimeout=queryTimeout)

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\tdodbc.py", line 501, in execute

    self.executemany(query, [params, ], queryTimeout)

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\tdodbc.py", line 566, in executemany

    param, length = _getParamValue(val, valueType, False)

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\tdodbc.py", line 827, in _getParamValue

    param = _createBuffer(length)

  File "C:\Python\PYTHON~1\lib\site-packages\teradata\tdodbc.py", line 104, in <lambda>

    _createBuffer = lambda l: ctypes.create_unicode_buffer(l)

  File "C:\Python\PYTHON~1\lib\ctypes\__init__.py", line 292, in create_unicode_buffer

    buf = buftype()

MemoryError

2016-01-28 13:02:58,493 - teradata.tdodbc - WARNING - 1 open connections found on exit, attempting to close...

2016-01-28 13:02:58,825 - teradata.udaexec - INFO - UdaExec exiting.

 
============
python log ends here
=============
Below is my py script, and SP Definition.
SP Definition is below.
REPLACE PROCEDURE GDEV1P_FF.GCFR_FF_TPTLoadCLOB_Generate
/*==========================================================================
# Purpose:  Generate a TPT Load script for Large table size.
#
# Comments:
#              Functional Flow Steps are
#    1. Init local parameters.
#    2. Verify Local Variable.
#    3. Generate TPT Script
#    4. Generate oReturn_Parameters
#    oReturn_Code - 
#     0 = Successful Execution
#     1 = Error/Failure
#              oReturn_Message -  Complete message describing the result of this PP API. 
===========================================================================*/
/* Stored Procedure Parameters */
        (
         IN iProcess_Name            VARCHAR(30)
  ,IN iDebug_Level             BYTEINT
  ,IN iProcess_Type    BYTEINT
  ,IN iProcess_State    BYTEINT
        ,IN iTD_Server                 VARCHAR(30)
        ,IN iTD_User_Name               VARCHAR(30)
        ,IN iPassword_String           VARCHAR(30)
        ,OUT oReturn_Code               SMALLINT
        ,OUT oReturn_Message            VARCHAR(255)
  ,OUT oReturn_Script             CLOB(2000000000) -- used for returning generated TPT script
        ,OUT oReturn_Parameters         VARCHAR(4000)
        ,OUT oReturn_LogonText   VARCHAR(1000)
        
        )
 
=================================
My py Script
==================================
import teradata
import sys
import time
 
start_time = time.time()
 
def createFiles( vclob, vstrProcName,vrtnParams,vstrLogonFile,vstrScriptsDir,vstrParamsDir,vlogWriter,logTextBuffer):
fileGenStartTime=time.time()
vParamDir = vstrParamsDir+vstrProcName+".param"
paramFileobj = open(vParamDir, "w")
paramFileobj.write(vrtnParams)
 
# Close opend file
paramFileobj.close()
 
paramList = []
paramList = vrtnParams.split("|")
logonPath=paramList[3]
 
vlogonFile = logonPath+vstrProcName+"_dbconnect.tpt"
logonFileobj = open(vlogonFile, "w")
logonFileobj.write(vstrLogonFile)
 
# Close opend file
logonFileobj.close()
 
#print(vclob)
strScriptFile = vstrScriptsDir + vstrProcName+".tpt";
ScriptFileobj = open(strScriptFile, "w")
ScriptFileobj.write(vclob)
 
# Close opend file
ScriptFileobj.close()
fileGenEndTime=time.time()
print("--- %s File Generation seconds ---" % str(fileGenEndTime-fileGenStartTime))
 
return;
 
# Retreiving command line arguements
strProcName = sys.argv[1]
procState = sys.argv[2]
strDBName = sys.argv[3]
userName = sys.argv[4]
password = sys.argv[5]
debugLevel = sys.argv[6]
strScriptsDir = sys.argv[7]
strLogsDir = sys.argv[8]
strParamsDir = sys.argv[9]
strLibsDir = sys.argv[10]
strGCFRViewDB = sys.argv[11]
strProcDB = sys.argv[12]
 
udaExec = teradata.UdaExec (appName="test", version="1.0",
        logConsole=False)
try:
# Creaing connection
Connect_time_bef = time.time()
session = udaExec.connect(method="odbc", system="tddemo",
        username=userName, password=password);
Connect_time_aft = time.time()
 
#Input parameters
#print(" Command line arguments are:  Process_name: " +sys.argv[1] + " Process_State: " + sys.argv[2] +
# " TD_server: " + sys.argv[3] +" TD_user_name: " + sys.argv[4] +" TD_user_passwd: " + "******" +" Debug_level: " + sys.argv[6] +
# " GCFR_Scripts_Path: " + sys.argv[7] +" GCFR_Logs_Path: " + sys.argv[8] +" GCFR_Params_Path: " + sys.argv[9] +
# " GCFR_Libs_Folder: " + sys.argv[10] +" GCFR_V: " + sys.argv[11] +" GCFR_P_FF: " + sys.argv[12])
# Running SQL
print("")
Query_Time_bef = time.time()
result_procType = session.execute("select Process_Type from "+strGCFRViewDB+".GCFR_Process where Process_Name='"+strProcName+"'")
Query_Time_aft = time.time()
rowcount=0;
for row in result_procType:
nProcType = row[0]
rowcount=rowcount+1
 
if(rowcount > 1):
print(" Invalid Meta-data for Process Name: " + strProcName)
print(" Encountered more than 1 row in GCFR Process Table, exiting...")
system.exit(1)
#
if ( nProcType == 17 or nProcType == 18 or nProcType == 20 or nProcType == 43 or nProcType == 44 ): # load
sCall = strProcDB+".GCFR_FF_TPTLoadCLOB_Generate"
 
before_clobSp_Call_time = time.time()
#size=177011324
results = session.callproc("GDEV1P_FF.GCFR_FF_TPTLoadCLOB_Generate",("LD_668_66_Customer",6,17,0,"localhost","dbc","dbc",teradata.OutParam("oCode"),teradata.OutParam("oMessage"),teradata.OutParam("oScript",dataType="CLOB", size=200000000),teradata.OutParam("oParams"),teradata.OutParam("oLogon")))
after_clobSp_Call_time = time.time()
if (int(results.oCode) == 0):
strScriptsDir = strScriptsDir.replace("\\","/")
strParamsDir = strParamsDir.replace("\\","/")
#print("pySplit"+results.oCode+"pySplit"+results.oMessage+"pySplit"+results.oParams+"pySplit"+results.oLogon+"pySplit"+results.oScript)
 
createFiles(results.oScript, strProcName, results.oParams, results.oLogon, strScriptsDir+"/", strParamsDir+"/", "a", "a");
 
#results = session.callproc("GDEV1P_UT.GCFR_UT_shMessage_Log",(6,12,"LD_668_66_Customer","84","GCCR_RenameFiles.renFiles","1","warning:",results.oScript,0,1,"U0146","0","The Data Files could not be found. Please see the list under iSQL_Text column"))
#logTextBuffer.append(getTimeStampLog()+"\t\tJAVA Gen_TPT Completed Successfully.\n");
 
elif ( nProcType == 19 or nProcType == 32 ): #export
 
sCall = "{CALL "+strProcDB+".GCFR_FF_TPTExportCLOB_Generate(?,?,?,?,?,?,?,?,?,?,?,?)}";
else:
print(" Invalid Process Type for Process Name: " + strProcName);
print(" Exiting...");
system.exit(1)
 
 
print("--- %s Pre Connection Library import Time seconds ---" % str(Connect_time_bef-start_time))
print("--- %s Connection Time seconds ---" % str(Connect_time_aft-Connect_time_bef))
print("--- %s Query Execution time seconds ---" % str(Query_Time_aft-Query_Time_bef))
print("--- %s Clob SP Execution seconds ---" % str(after_clobSp_Call_time-before_clobSp_Call_time))
print("--- %s Total seconds ---" % str(time.time() - start_time))
 
 
except Exception as e:
print(e)
sys.exit(1)
except api.ApiUnhandledError as e :
    print(e)
#print("--- %s seconds ---" % (time.time() - start_time))
 
==================================
Script Ended
==================================
 
Thanks,
Regards
Asim Naveed

ericscheie 68 comments Joined 11/08
28 Jan 2016

Hi Asim,

Regarding question 1, there are several ways to go about it.   One suggestion would be to organize your logic into functions and then call those functions within the "with" block.  E.g.:


def myfunc (session):
     session.execute("SELECT ...")
     session.execute("SELECT ...")
     session.execute("SELECT ...")

def myfunc2 (session): 
     session.execute("SELECT ...")
     session.execute("SELECT ...")
     session.execute("SELECT ...")

with udaExec.connect("${dataSourceName}") as session:
    myfunc1(session)
    myfunc2(session)

Regarding question 2,  I think the amount of memory that can be allocated is system dependent and depends on how much RAM is available.   For instance, I have no problem calling ctypes.create_unicode_buffer(2000000000) on my MAC as it has plenty of RAM.  However, on my Windows VM with only 4 GBs of RAM,  the highest number I could allocate was 690000000.   It looks like in newer versions of ODBC there is a way to receive Output Parameters in chunks instead of pre-allocating the entire buffer upfront, I'll have to investigate this further to see if this is feasible.  

28 Jan 2016

Hi Eric, That great information and will help. Query 1 of mine is cleared and i will do as u suggested, but regarding query 2 can i call ctypes.creat_unicode_buffer directly in my python script and allocate a buffer size like above and it that amount of memory is not available than this will raise error. 
It will be more feasible for us to get the buffer in chunks and not in total, like we do in JAVA we read line by line till the buffer stream end. I hope this can be achieved in python as well. Looking forward if this can be developed, because we are going to replace JAVA as JDBC do not support tdwallet and we need that in our project aloing with LDAP. So we have to move on python and the best solution for us is the Teradadata Python module. So it will be great help if you help us to retrieve the OutParam in chunks.
 
Really gratefull for your help Eric.
Regards
Asim Naveed

28 Jan 2016

@eric:sorry for typos mistakes.

ericscheie 68 comments Joined 11/08
01 Feb 2016

@Asim - I've engage the ODBC support team and support for streaming output parameters is not currently available in the Teradata ODBC driver.  However, there are several workarounds you could attempt.   The first would be to refactor your SP to return a dynamic Result set containing the CLOB instead of returning it as an output parameter.   The second would be to enable to the deprecated "OutputAsResultSet" ODBC driver option.  Enabling this option, would result in the output parameters being returned as a ResultSet and therefore would not require you to preallocate the output buffer.   The first option is obviously better as it does not depend on the deprecated option, but if you don't have the abiltiy to modify the SP, then second approach would at least be a stop gap until streaming output parameter is supported.

16 Feb 2016

Hi Eric,
            Before putting my query I would like to thank you for your support and time you had given to us. I would like to share some more information and abnormility we faced regarding Teradata Python Module because this will make it more mature.
From the start we got issue’s in consuming Clob as output parameter of Teradata Stored Procedure, while most of them addressed  with your help and guidance such as “ truncation issue is resolved with usage of ‘size’ parameter” in “Teradata.OutParam” clause.  But there are still some concerns and behavioral inconsistencies we found and wish to bring them into your knowledge, so they can be checked and rectified.
1.       We are calling Teradata stored procedure using python udaExec module and SP returns four output parameters and Clob is one of it. SP size of Clob is 2000,000,000 (2GB) and can return up to 2GB return text/script. Now in python we tried to consume as much as we could using python  ‘size’ parameter of "teradata.OutParam", which advocates python about the buffer size for clob and we tried different size values and with that we got some abnormal behaviour.
Behaviour: More important thing we experienced is while retrieving clob through “Teradata.OutParam” using size parameter that most of time we get the complete result including clob of any size (10MB,100MB,500MB etc) but it sometime returns NULL in output, however do not give any error/exception and the execution went succesful .  Python SP CALL and the culprit clob is highlighted below,

results = session.callproc("GDEV1P_FF.GCFR_FF_TPTLoadCLOB_Generate",("LD_668_66_Customer",6,17,0,"localhost","dbc","dbc",teradata.OutParam("oCode"),teradata.OutParam("oMessage"),teradata.OutParam("oScript",dataType="CLOB", size=4999999999),teradata.OutParam("oParams"),teradata.OutParam("oLogon")))

 

And when we retrieve like =>  results.oScript  (The oScript is NULL). The python log showed successfull execution of SP and we can retrieve the other output parameter like => results.oMessage , results.oCode
 

We checked our SP and it is working fine, this NULL behaviour often happen with clob with higher sizes like 180 MB, 300MB or >500MB clob has to be returned. But this NULL behaviour is not retricted to higher sizes it also happened for smaller sizes as well.

 

Second we observed the buffer size restriction for python module,

 

  1. For Python 32-bit installed at 64-bit windows, we could only consume 178 MB in size parameter of Teradata.OutParam. For Clob contents greater than 178 MB will be truncated.  If we try to set “size” greater than 178 MB we got “MemoryError”.
  2. "MemoryError" issue disappeared with Python 64 bit and we were able to set the “size” up to 500MB and but still not able to get the 2GB as output and for 2GB we get NULL behaviour in output.
  3. TD Python module log showed successful execution of SP but the CLOB return parameter was NULL. This mostly happen when ‘size’ parameter is set to a higher value like 180MB or greater but this also happened for small sizes as 

This behavior puts a question mark on python module regarding CLOB consumption and we were about to incorporate this module in our TD project. 
Do you experienced this behaviour as well? Is there any bug reported for such behaviour. Please check and help us resolve the issue because we were unable to find the root cause.
Thanks in advance, we are really grateful for your help, support and time.
 
Regards
Asim Naveed

14 Apr 2016

Hi I couldnt get the LDAP authentication working with the package. In the documentation above there is no reference i tried below DSN config.
[GDWPROD]

system=My server

username=my user

password= my password

MechanismName=LDAP

 

Is LDAP supported? If yes how to configure that in the udaexec config file?

 

Thanks

Ranjith

davebutler 2 comments Joined 06/11
15 Apr 2016

Ranjith - Try this in your DSN config:
authentication=ldap

 

19 Apr 2016

@davebutler , Thanks that worked. Appreciate the help
authentication=ldap

Anubeig 6 comments Joined 12/13
21 Apr 2016

Can anybody give me simple example ?? How to compile and execute store procedures from teradata python module??

Anubeig 6 comments Joined 12/13
21 Apr 2016

I want example of parametric store procedure..atleast one IN and one OUt paramter ..

ericscheie 68 comments Joined 11/08
21 Apr 2016

Here is a simple example of creating and calling a SQL Stored Procedure with both an input and output parameter.   Note that Teradata REST and ODBC do not support creating non-SQL based Stored Procedures (e.g. C and Java) at this time, so only SQL-based Stored Procedures can be created with the Python module.

 

  for result in session.execute(
    """REPLACE PROCEDURE test.testProcedure1
        (IN p1 INTEGER,  OUT p2 INTEGER)
        BEGIN
            SET p2 = p1;
        END;"""):
    print(result)

  result = session.callproc(
       "test.testProcedure1",
        (12345, teradata.OutParam("p2", dataType="INTEGER")))
  print(result.p2)
    

 

Anubeig 6 comments Joined 12/13
22 Apr 2016

Thanks eric..

privet3711 6 comments Joined 07/11
02 May 2016

I'm getting this error when trying to connect from linux. 

any ideas ?

 

Traceback (most recent call last):

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

    session = udaExec.connect(method="odbc", system="oneview",username='xxxxx', password='xxxx');

  File "/prod/user/sam/ent/fdm/Users/IUA019/miniconda/lib/python2.7/site-packages/teradata/udaexec.py", line 181, in connect

    **args))

  File "/prod/user/sam/ent/fdm/Users/IUA019/miniconda/lib/python2.7/site-packages/teradata/tdodbc.py", line 374, in __init__

    checkStatus(rc, hDbc=self.hDbc, method="SQLDriverConnectW")

  File "/prod/user/sam/ent/fdm/Users/IUA019/miniconda/lib/python2.7/site-packages/teradata/tdodbc.py", line 206, in checkStatus

    raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])

teradata.api.DatabaseError: (0, u'[I] [')

 

ericscheie 68 comments Joined 11/08
02 May 2016

@privet3711 - Whenever these non-sense error codes/messages come back from the ODBC driver manager, it usually means the Teradata ODBC driver was not found.  This could be either because its not not installed, or the ODBCINI environment variable is not set, or the unixodbc driver manager was being loaded instead of the DataDirect driver manager that comes with the Teradata ODBC driver.  I added code to the python module to try to detect these cryptic errors messages and display a more informative message but "[I] [" is not something I've seen.  I would check your Teradata ODBC install, make sure the ODBCINI environment variable is set, and check that unixodbc is not installed.

mhousley 1 comment Joined 12/15
09 May 2016

The module seems to have issues with paired curly braces ({}) inside passwords. I was able to work around this by putting my password inside double quotes. Here is the the Traceback:

Traceback (most recent call last):

  File "/Users/mhousley/anaconda/lib/python3.5/site-packages/teradata/udaexec.py", line 181, in connect

    **args))

  File "/Users/mhousley/anaconda/lib/python3.5/site-packages/teradata/tdodbc.py", line 374, in __init__

    checkStatus(rc, hDbc=self.hDbc, method="SQLDriverConnectW")

  File "/Users/mhousley/anaconda/lib/python3.5/site-packages/teradata/tdodbc.py", line 206, in checkStatus

    raise DatabaseError(i[2], u"[{}] {}".format(i[0], msg), i[0])

teradata.api.DatabaseError: (0, '[HY000] [Teradata][ODBC Teradata Driver] Major Status=0x1000000 Minor Status=0xe3000205-Inaccessible read, [Teradata][ODBC Teradata Driver] Major Status=0x1000000 Minor Status=0xe3000205-A name is required but was passed as NULL., [Teradata][ODBC Teradata Driver] Unable to logon with Authentication Mechanism selected.')

 

bchu 1 comment Joined 05/16
17 May 2016

would someone please let me what i did wrong here? here is the script:
--
import teradata
with udaExec.connect(method="odbc", system="elm", username="dbc", password='dbc') as test_con:
--
NameError: name 'udaExec' is not defined
--
i have ran "pip install teradata", and odbc driver was installed too
 
 
 
 
 
 
 

bill chu

19 May 2016

I am repeatedly getting socket when trying to connect to Teradata:
Error: Teradata.api.DatabaseError: (65, "[08001] [Teradata][Unix system error]  65 Socket error - The Teradata server can't currently be reached over this network")
 
Anyhelp would be great... 

Learning

nani255 3 comments Joined 11/15
30 May 2016

Hi Eric,
I've a requirement of running same sql query with 10 different users by logging on to bteq. I've used teradata python module for this & my code looks like below.
The problem here is, Connection establishment & executing the sql query is running  2 times for 2nd iteration & 3 times for 3rd iteration in loop &  so on. so, for last iteration is running for 9 times.
I suspect that problem is not closing the previous connenction causes the issue, even i tried with "session.close()" method, but it is not working.
Could you please have a look ?.
 

import teradata

 

for a in range(1,10):

    user="USER"+str(a)

    pwd="PASSWORD"+str(a)

    udaExec = teradata.UdaExec (appName="sample",version="1.0",logConsole=True,logDir="./logs")

    with udaExec.connect(method="odbc", system="[machinename]", username=user, password=pwd) as session:

       session.execute("select * from dbc.dbcinfo")
 
Thanks
nani

ericscheie 68 comments Joined 11/08
30 May 2016

@nani -  Move the line "udaExec = teradata.UdaExec(..." above the "for" loop.   udaExec should only be initializd once.   Its not that the query is being executed 9 times but rather the logging system is initialized multiple times and so is logging messages multiple times.

@Jagadish - Make sure that the value for the "system" parameter is a valid tdpid and that it can be resolved to an IP address.

@bchu - You are missing the line in your program that initializes udaExec (e.g. "udaExec = teradata.UdaExec(...").  See the Hello World program for an example.

 

 

alihammad 6 comments Joined 03/11
12 Jun 2016

I am executing a query and I want to iterate over the result set several times. The problem is that if i iterate over the result set once, I can't do it again. Looks like the result set is exhausted and no more available for any further computation.
Please see the below code for details and suggest how can I preserve the result set.

import teradata

class DB():
    def __init__(self):
        udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",logConsole=False)
        session = udaExec.connect(method="odbc", system="tddemo",username="dbc", password="dbc")
        self.session = session

    def fun1(self):
        rows = self.session.execute("SELECT  databasename, ownername  FROM DBC.DATABASES  where DatabaseName='financial'")
        return rows

db = DB()
rows = db.fun1()

# This loop prints accurate result like
#Row 1: [financial, Samples]
for row in rows:
    print(row)

# This loop does not print anything
for row in rows:
    print(row)  

# This line also gets printed
print("The End")

Thanks

 

alihammad 6 comments Joined 03/11
12 Jun 2016

Got the answer to my question here http://stackoverflow.com/questions/37777512/teradata-python-module-cursor-result-set-is-exhausted-after-one-iteration/37777540#37777540

alihammad 6 comments Joined 03/11
12 Jun 2016

I am trying to fetch table ddl with the help of SHOW TABLE statement and it behaves weird and only returns a few words out of whole DDL. Please see my attempt and error below
 

import teradata
class DB():
    def __init__(self):
        udaExec = teradata.UdaExec (appName="test", version="1.0",logConsole=False)
        session = udaExec.connect(method="odbc", system="tddemo",username="dbc", password="dbc")
        self.session = session

    def fun1(self):
        # session.execute("create table financial.dummytable1(a varchar(10))")
        rows = self.session.execute("SHOW TABLE financial.dummytable1")
        for row in rows:
            print(row)
db = DB()
db.fun1()

print("---The End---")

 
Here is the unexpected result

PRIMARY INDEX ( a );]HARACTER SET LATIN NOT CASESPECIFIC)CK ,
---The End---

Desired result

CREATE SET TABLE financial.dummytable1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      a VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( a );
---The End---

 
Please help me understand what's going on here.
 
Thanks
Ali

ericscheie 68 comments Joined 11/08
13 Jun 2016

@Ali - The reason the results of SHOW TABLE were not displayed properly on the terminal is because the newline characters in the result were most likely carriage returns instead of line feeds.  To ensure it displays correctly, you can split the output based on the presence of any newline sequence and print the lines individually.  E.g.

import re
for line in re.split("\r\n|\n\r|\r|\n", row[0]):
    print(line)
 

 

alihammad 6 comments Joined 03/11
14 Jun 2016

Thanks for your help. That worked. But took a few minute to write the exact required code, which is here
 

import teradata
import re
class DB():
    def __init__(self):
        udaExec = teradata.UdaExec (appName="test", version="1.0",logConsole=False)
        session = udaExec.connect(method="odbc", system="tddemo",username="dbc", password="dbc")
        self.session = session
 
    def fun1(self):
        rows = self.session.execute("SHOW TABLE financial.dummytable1")
        for row in rows:
            for line in re.split("\r\n|\n\r|\r|\n", row[0]):
                print(line)
 
db = DB()
db.fun1()
 
print("---The End---")

 

dn186005 2 comments Joined 10/15
01 Jul 2016

I am trying to execute the Printtable.py script using external configuration file.The file udaexec.ini is in the same location as the python file.
Script:
import teradata
 
udaExec = teradata.UdaExec ()
 
with udaExec.connect("${dataSourceName}") as session:
    for row in session.execute("SELECT * FROM ${table}"):
        print(row)
Configuration File:
# Application Configuration
[CONFIG]
appName=Printtable
version=2
logConsole=False
dataSourceName=E_Dev
table=DWH_STAGE.EMP

# Default Data Source Configuration
[DEFAULT]
method=odbc
charset=UTF8

# Data Source Definition
[E_Dev]
system=100.65.07.120
username=XXX
password=XXX
 
 
Error:
C:\Python\Teradata_Python_Module>python C:\Python\Teradata_Python_Module\Practice\Printtable.py
Traceback (most recent call last):
  File "C:\Python\Teradata_Python_Module\Practice\Printtable.py", line 3, in <module>
    udaExec = teradata.UdaExec ()
  File "build\bdist.win32\egg\teradata\udaexec.py", line 106, in __init__
  File "build\bdist.win32\egg\teradata\udaexec.py", line 575, in resolve
  File "build\bdist.win32\egg\teradata\udaexec.py", line 596, in _resolve
teradata.api.InterfaceError: ('CONFIG_ERROR', 'appName is a required field, it must be passed in as a parameter or specified in a config file.')

MH186045 2 comments Joined 03/13
02 Jul 2016

Is there some way to get the names of the columns returned from a query?

ericscheie 68 comments Joined 11/08
06 Jul 2016

@dn186005 - The Teradata Python module looks for the config file relative to the working directory and not the directory the script is located in.   Try changing to the directory where your script is located before running it.

@MH186045 - 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])

 

aagrawal 11 comments Joined 06/08
06 Jul 2016

Hi Eric,
 I have python 2.6 (default location /usr/bin) and python 2.7 (/usr/local/bin)
 I'm getting following error, while tryign to install Teradata module. can you please help?
$ python2.7 ./setup.py install
Traceback (most recent call last):
  File "./setup.py", line 24, in <module>
    from setuptools import setup
ImportError: No module named setuptools
 

ericscheie 68 comments Joined 11/08
07 Jul 2016

@aagrawal - Looks like you are missing the setuptools module.  Instructions to install it can be found here:  https://pypi.python.org/pypi/setuptools

aagrawal 11 comments Joined 06/08
07 Jul 2016

Thanks Eric,
    I got the modeule installed. Now I'm running my first 'HelloWorld" example and got followign error.
 
  File "build/bdist.linux-x86_64/egg/teradata/__init__.py", line 22, in <module>
  File "build/bdist.linux-x86_64/egg/teradata/udaexec.py", line 41, in <module>
  File "build/bdist.linux-x86_64/egg/teradata/tdrest.py", line 29, in <module>
  File "/usr/local/lib/python2.7/ssl.py", line 97, in <module>
    import _ssl             # if we can't import it, let the error propagate
ImportError: No module named _ssl
 

ericscheie 68 comments Joined 11/08
07 Jul 2016

@aagrawal - If you're building python from source, make sure you are building it with SSL support.   For Python to be built with SSL support, the openssl header files need to be installed (e.g. install openssl-devel package).

aagrawal 11 comments Joined 06/08
08 Jul 2016

perfect. Thanks Eric. its working. great job.

dn186005 2 comments Joined 10/15
10 Jul 2016

Hi Eric,
As suggested by you,i tried to changing the directory but I am still facing the same error.

teradata.api.InterfaceError: ('CONFIG_ERROR', 'appName is a required field, it must be passed in as a parameter or specified in a config file.')

 

 

For some reason the config file is not being read.Please help.

 

 

B_Rock 1 comment Joined 04/12
20 Jul 2016

How do you update the config file? I'm trying to build a gui and I want to allow the user to change the config file settings. 
 
Thanks,
 

eo186005 6 comments Joined 08/14
26 Jul 2016

Hi Eric.

I've been hapilly using your excellent module for a few months now. However, today I hit a snag and I'm unable to resolve.

I am using some simple code:

   for resultRow in DBSession.execute(sSQLStatement,ignoreErrors=[3807]):
      if resultRow[0] is not None:
         sDDL = resultRow[0]

to run a simple SHOW VIEW statement:

SHOW VIEW P1V_INP.TX030007_01_BD_BKKG_ACCNT_SA;

This is returning only 21333 characters of the view DDL, the full size being 26273 chars.

I'm stumped as to why this is happening.
The result set only has a single row, single column.

Can you give any pointers to help diagnose the reason for the truncation?
Thanks
E

eo186005 6 comments Joined 08/14
26 Jul 2016

Hi Eric.

I've been hapilly using your excellent module for a few months now. However, today I hit a snag and I'm unable to resolve.

I am using some simple code:

for resultRow in DBSession.execute(sSQLStatement,ignoreErrors=[3807]):
if resultRow[0] is not None:
sDDL = resultRow[0]
to run a simple SHOW VIEW statement:

SHOW VIEW P1V_INP.TX030007_01_BD_BKKG_ACCNT_SA;
This is returning only 21333 characters of the view DDL, the full size being 26273 chars.

I'm stumped as to why this is happening.
The result set only has a single row, single column.

Can you give any pointers to help diagnose the reason for the truncation?
Thanks
E

eo186005 6 comments Joined 08/14
26 Jul 2016

Further to the above, I turned on DEBUG logging and I can see this:
SQLFetch succeeded with info:  [01004] [Teradata][ODBC Teradata Driver] Character string truncated
Anyone got any ideas on how to increase this?
I tried the below, but it didn't work

keywords = {'MaxRespSize': '1048576'}
session = udaExec.connect(sConnectionName, **keywords)

 

 

Pages

You must sign in to leave a comment.