Connecting to Teradata using Python

Connectivity
Enthusiast

Connecting to Teradata using Python

Hi Guys,

I am strugling to connnect teradata usning python. I have a user ID and password in text file and i need to read the test file to connect to teradata.. could you please help me with theb syntax if anyone has already done this

Tags (1)
6 REPLIES
Enthusiast

Re: Connecting to Teradata using Python

So I spent a good few hours trying to figure this one out a couple weeks back...  What type of driver are you trying to use and which python package are you using?  I found pyodbc to be the easiest to get up and running.

Re: Connecting to Teradata using Python

Hi, 

I have written a Python extension library which connect Teradata via CLIv2.  You can try to use it.

https://github.com/chfly2000/tdcliviipy

Note: This is not a Teradata product, so there is no support service. But you can modify the source code freely if you want.

Fan

Re: Connecting to Teradata using Python

you shall use this:

import pyodbc

    pyodbc.pooling = True

    a='\'DSN='+dsn+';uid='+uid+':pwd='+pwd+';\''

    conn = pyodbc.connect('DSN='+dsn+';uid='+uid+';pwd='+pwd+';',autocommit=True)

    qry="select * from dbc.dbcinfo;"

    rows = conn.cursor()

    qb1=rows.execute(qry)

    print qb1

Enthusiast

Re: Connecting to Teradata using Python

There is now an open source Teradata python module available:

http://developer.teradata.com/tools/reference/teradata-python-module

Enthusiast

Re: Connecting to Teradata using Python

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-27 18:15:40,037 - teradata.udaexec - INFO - Initializing UdaExec...

2016-01-27 18:15:40,038 - 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-27 18:15:40,038 - teradata.udaexec - INFO - Found run number file: "C:\test_pyscrript\.runNumber"

2016-01-27 18:15:40,038 - teradata.udaexec - INFO - Cleaning up log files older than 90 days.

2016-01-27 18:15:40,038 - teradata.udaexec - INFO - Removed 0 log files.

2016-01-27 18:15:40,066 - teradata.udaexec - INFO - Checkpoint file not found: C:\test_pyscrript\test.checkpoint

2016-01-27 18:15:40,066 - teradata.udaexec - INFO - No previous checkpoint found, executing from beginning...

2016-01-27 18:15:40,067 - teradata.udaexec - INFO - Execution Details:

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

 * Application Name: test

 *          Version: 1.0

 *       Run Number: 20160127181540-82

 *             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.20160127181540-82.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=20160127181540-82;ClientUser=an255019;Production=False;udaAppLogFile=C:\test_pyscrript\logs\test.20160127181540-82.log;UtilityName=PyTd;UtilityVersion=15.10.0.11

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

2016-01-27 18:15:40,067 - teradata.udaexec - INFO - Creating connection: {'method': 'odbc', 'password': 'XXXXXX', 'system': 'tddemo', 'username': 'dbc'}

2016-01-27 18:15:50,468 - teradata.udaexec - INFO - Connection successful. Duration: 10.400 seconds. Details: {'method': 'odbc', 'password': 'XXXXXX', 'system': 'tddemo', 'username': 'dbc'}

2016-01-27 18:15:50,495 - teradata.udaexec - INFO - Query Successful. Duration: 0.026 seconds, Rows: 1, Query: select Process_Type from GDEV1V_GCFR.GCFR_Process where Process_Name='LD_668_66_Customer'

2016-01-27 18:15:50,582 - teradata.udaexec - ERROR - Procedure Failed! Duration: 0.062 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=-4), 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 291, in create_unicode_buffer

    buftype = c_wchar * init

ValueError: Array length must be >= 0, not -4

2016-01-27 18:15:50,654 - teradata.tdodbc - WARNING - 1 open connections found on exit, attempting to close...

2016-01-27 18:15:50,848 - 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

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

Enthusiast

Re: Connecting to Teradata using Python

@Eric. the python for above 2nd query is this below ONE.

2016-01-28 15:02:52,794 - teradata.udaexec - INFO - Initializing UdaExec...

2016-01-28 15:02:52,795 - 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 15:02:52,795 - teradata.udaexec - INFO - Found run number file: "C:\test_pyscrript\.runNumber"

2016-01-28 15:02:52,795 - teradata.udaexec - INFO - Cleaning up log files older than 90 days.

2016-01-28 15:02:52,795 - teradata.udaexec - INFO - Removed 0 log files.

2016-01-28 15:02:52,832 - teradata.udaexec - INFO - Checkpoint file not found: C:\test_pyscrript\test.checkpoint

2016-01-28 15:02:52,832 - teradata.udaexec - INFO - No previous checkpoint found, executing from beginning...

2016-01-28 15:02:52,833 - teradata.udaexec - INFO - Execution Details:

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

 * Application Name: test

 *          Version: 1.0

 *       Run Number: 20160128150252-87

 *             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.20160128150252-87.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=20160128150252-87;ClientUser=an255019;Production=False;udaAppLogFile=C:\test_pyscrript\logs\test.20160128150252-87.log;UtilityName=PyTd;UtilityVersion=15.10.0.11

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

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

2016-01-28 15:03:04,126 - teradata.udaexec - INFO - Connection successful. Duration: 11.292 seconds. Details: {'system': 'tddemo', 'username': 'dbc', 'method': 'odbc', 'password': 'XXXXXX'}

2016-01-28 15:03:04,132 - teradata.udaexec - INFO - Query Successful. Duration: 0.005 seconds, Rows: 1, Query: select Process_Type from GDEV1V_GCFR.GCFR_Process where Process_Name='LD_668_66_Customer'

2016-01-28 15:03:04,199 - teradata.udaexec - ERROR - Procedure Failed! Duration: 0.063 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 15:03:04,334 - teradata.tdodbc - WARNING - 1 open connections found on exit, attempting to close...

2016-01-28 15:03:04,459 - teradata.udaexec - INFO - UdaExec exiting.