Teradata Python Module

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

Re: Teradata Python Module

how do you pass Null as parameter

Teradata Employee

Re: Teradata Python Module

@privet3711 - The Python Built-in constant "None" is used to represent a SQL NULL. 

Enthusiast

Re: Teradata Python Module

Issue with reading timestamp (3) I extract same data via teradata and pyodbc , 3rd column has type timestamp(3)

_____________________ here is my code _______________

import teradata
import pyodbc

udaExec = teradata.UdaExec (appName="DfToTera", version="1.0",logConsole=False,logRetention=0,configureLogging=False)
session = udaExec.connect(method="odbc", system="oneview",username="xxx", password="xxx",database="ud183")

for row in session.execute("""SELECT * from test4 order by 1"""):
print(row[2])

conn = pyodbc.connect('DRIVER={Teradata};DBCNAME=oneview;UID=xxx;PWD=xxx;database=ud183',autocommit=True)

for row in conn.execute("""SELECT * from test4 order by 1"""):
print(row[2])

___________________________________________

results below when selecting using teradata connection shows incorrect decimal point , any ideas ?

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

In[292]: for row in session.execute("""SELECT  * from test4 order by 1"""):

        print(row[2])

2015-07-01 12:18:32.000673

2013-09-10 10:34:08.000260

2014-09-18 15:27:31.000490

2014-06-25 11:59:59.000873

2015-01-16 17:30:43.000900

In[293]: for row in conn.execute("""SELECT  * from test4 order by 1"""):

        print(row[2])

2015-07-01 12:18:32.673000

2013-09-10 10:34:08.260000

2014-09-18 15:27:31.490000

2014-06-25 11:59:59.873000

2015-01-16 17:30:43.900000

Teradata Employee

Re: Teradata Python Module

@privet3711 - I opened a bug for the timestamp(3) milliseconds being reported incorrectly and released a new version with the fix (15.10.00.04).  Thanks for reporting.

Enthusiast

Re: Teradata Python Module

when trying to export large panda dataframe I'm getting error like this

[Teradata][ODBC Teradata Driver] SQL request exceeds maximum allowed length of 1 MB

Do you know anyway around it except manauly splitting parameters array into smaller arrrays

df2 = pd.read_sql("select top 50000 story_id,story_id_moment,chng_ts,est, cast (chng_ts as date) dt1 from ver1_story_dtl_tbl",conn)
data = [tuple(x) for x in df2.to_records(index=False)]
con.executemany("insert into ud183.test4 values(?,?,?,?,?)",data,batch=True)

______

DatabaseError: (0, u'[22001] [Teradata][ODBC Teradata Driver] SQL request exceeds maximum allowed length of 1 MB')

Teradata Employee

Re: Teradata Python Module

@privet3711 - No, there is no other way.  Teradata ODBC limits batch inserts to only certain size and number of rows.   Therefore, to continue using Batch mode, you will need to call executemany for chunks of data whose size/count are below this limit.

Enthusiast

Re: Teradata Python Module

Eric , just a comment , it would be very nice to see fastload / multiload capabilities added to the tool. Teradata is designed for handling pretty large tables and right now we can't efficiently load data back to Teradata which limits out use of Python with Teradata and pushes us to use SAS instead. would really like to see something that loads pandas dataframe to teradata using fastload

Enthusiast

Re: Teradata Python Module

Anyone have advice or examples for compiling stored procedures?  I've tried a few ways but am unable to get past it.  Closest I've been able to come was trying to simply execute the definition from a file.  The SP compiles just fine from SQL Assistant or Studio.  Curious, on the last example, if I can just change the max object / string size?

Both of the below are executed with

x = session.execute(file='sql/sp_grnt_rvk_role.sql')

Trying the file directly:

File "C:\Python34\lib\site-packages\teradata-15.10.0.3-py3.4.egg\teradata\tdodbc.py", line 147, in checkStatus

teradata.api.DatabaseError: (3706, "[42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between the beginning of the request and the 'DECLARE' keyword. ")

Triple quoting the replace statement within the file:

  File "C:\Python34\lib\site-packages\teradata-15.10.0.3-py3.4.egg\teradata\tdodbc.py", line 147, in checkStatus

teradata.api.DatabaseError: (6724, '[HY090] [Teradata][ODBC Teradata Driver][Teradata Database] The object name is too long in NFD/NFC. ') 

Teradata Employee

Re: Teradata Python Module

@chillerm - If your file contains a single replace statement that includes semi-colons, then I'm guessing the multi-statement parsing performed by the python module is what is causing the error.   Since you actually don't want to use the semi-colon as the statement delimiter, try passing in a different character sequence as the delimiter, one that does not occur in your file.   E.g. 

x = session.execute(file='sql/sp_grnt_rvk_role.sql', delimiter=';;')

Enthusiast

Re: Teradata Python Module

Ok, so interestingly enough if I read my file in as a string

with open ('sql/sp_grnt_rvk_role.sql', 'r') as myfile:
data=myfile.read()
proc_def=str(data)

and then execute with 

session.execute(proc_def): 

it seems to work just fine.