Python command for multiple data sources with Teradata/SQL module

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Highlighted
Enthusiast

Python command for multiple data sources with Teradata/SQL module

Greetings

I am trying to retrieve a SHOW command with a Select statement, to get the DDLs which I think is not returning an array rather multiple data sets.

I am using the python Teradata module to display the result set, I also tried the tuple method and dump the records however for some reason I am not able to handle all the data sets, I am only getting the first set.

 

Here is my code:

def fun1(self):
        rows = self.session.execute("""SHOW  SELECT A.UserId, AccountName, RowType, AccessRight FROM dbc.Accounts  A
                                              INNER JOIN DBC.AccessRights  B
                                              ON
                                           A.UserId =B.UserId
                                   ;""")
        return rows

db = DB()
rows = tuple(db.fun1())
print (len(rows))

>> For formating purpose I have added the below:

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

>>>>>

My Question, is what method should I use so  I can get all the results/data sets.  Note from the above query in SQL Asssitant I get 2 ddl however python is showing only first one,

 

Any help is appreciated.

Thank you

 


Accepted Solutions
Teradata Employee

Re: Python command for multiple data sources with Teradata/SQL module

Here is example code showing how to display the multiple result sets that are returned by the SHOW command. Note that the Teradata Database returns the DDL text with embedded carriage returns, which should be converted to newlines for proper display.

 

import teradatasql
with teradatasql.connect (None, host='whomooz', user='guest', password='please') as con:
  with con.cursor () as cur:
    cur.execute("SHOW SELECT A.UserId, AccountName, RowType, AccessRight FROM dbc.Accounts A INNER JOIN DBC.AccessRights B ON A.UserId=B.UserId")
    while True:
      print('=== result ===')
      [ print (row [0].replace ('\r', '\n')) for row in cur.fetchall () ]
      if not cur.nextset ():
        break
1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

Re: Python command for multiple data sources with Teradata/SQL module

I don't think you can do this via "session.execute".

You will need to use a raw cursor to process multiple result sets from a single request.

Enthusiast

Re: Python command for multiple data sources with Teradata/SQL module

@Fred
Thanks for pointing me to the appropriate direction, I was thinking of fetch all or something similar. I have not used cursor.
Do you have any example or can you please point me to such where I can use cursor for multi datasets ?
I think the same applies if I want to get the "explain" of my query
Many thanks,
Teradata Employee

Re: Python command for multiple data sources with Teradata/SQL module

You will need to use the .nextset method, in particular.

Here's a simple SQLAlchemy example.

 

And yes, EXPLAIN also returns multiple result sets.

Enthusiast

Re: Python command for multiple data sources with Teradata/SQL module

@Fred
Thank you
Let me play with this example and try both show and explain
Regards
Teradata Employee

Re: Python command for multiple data sources with Teradata/SQL module

Here is example code showing how to display the multiple result sets that are returned by the SHOW command. Note that the Teradata Database returns the DDL text with embedded carriage returns, which should be converted to newlines for proper display.

 

import teradatasql
with teradatasql.connect (None, host='whomooz', user='guest', password='please') as con:
  with con.cursor () as cur:
    cur.execute("SHOW SELECT A.UserId, AccountName, RowType, AccessRight FROM dbc.Accounts A INNER JOIN DBC.AccessRights B ON A.UserId=B.UserId")
    while True:
      print('=== result ===')
      [ print (row [0].replace ('\r', '\n')) for row in cur.fetchall () ]
      if not cur.nextset ():
        break
Enthusiast

Re: Python command for multiple data sources with Teradata/SQL module

Thank both @tomnolan and @Fred

I was able to get the Show and Explain both using the .NextStet method as expected using teradata library.