Python command for multiple data sources with Teradata/SQL module

Extensibility
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 6
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.

Highlighted
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.