teradatasql Python driver executing macros not producing result set

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Highlighted
Enthusiast

teradatasql Python driver executing macros not producing result set

I am using the teradatasql Python driver version 16.20.0.41. When I execute a macro that does a DELETE followed by an INSERT then followed by a SELECT from the same table, the result of the select is not being returned.

 

import teradatasql

conn = teradatasql.connect(None, host='hostname', user='username', passowrd='password')

conn.cursor().execute('EXEC DatabaseName.MacroName(Param1,Param2,Param3)'

results = conn.cursor().fetchall() <---- returns an empty list

 

Is this a known issue?

 

Edit: Executing the same Macro from Teradata Assistant using the Teradata.net driver produces the result set correctly


Accepted Solutions
Teradata Employee

Re: teradatasql Python driver executing macros not producing result set

Hi @tduser1 , based on your code snippet, you have a couple of issues.

 

Issue #1

You need to obtain a cursor from a connection, store that cursor in a variable, and then use that cursor to both execute your SQL request and obtain results.

You won't get the behavior you want if you try to fetch results from a different cursor object than what you used to execute the SQL request.

 

Issue #2

You said that your macro is composed of a DELETE, INSERT, and SELECT. A macro composed of multiple SQL statements is treated like a multi-statement request.

For a macro or a multi-statement request that returns multiple results, your Python app must use the cursor.nextset() method to advance to each separate result produced by the multi-statement request.

 

import teradatasql

conn = teradatasql.connect(None, host='hostname', user='username', password='password')

cur = conn.cursor()

cur.execute('EXEC DatabaseName.MacroName(Param1,Param2,Param3)'

results = cur.fetchall() # returns an empty list for the first (DELETE) statement

cur.nextset() # advance to the second (INSERT) statement result

results = cur.fetchall() # returns an empty list for the second (INSERT) statement

cur.nextset() # advance to the third (SELECT) statement result

results = cur.fetchall() # returns the result set from the third (SELECT) statement

 

1 ACCEPTED SOLUTION
2 REPLIES 2
Teradata Employee

Re: teradatasql Python driver executing macros not producing result set

Hi @tduser1 , based on your code snippet, you have a couple of issues.

 

Issue #1

You need to obtain a cursor from a connection, store that cursor in a variable, and then use that cursor to both execute your SQL request and obtain results.

You won't get the behavior you want if you try to fetch results from a different cursor object than what you used to execute the SQL request.

 

Issue #2

You said that your macro is composed of a DELETE, INSERT, and SELECT. A macro composed of multiple SQL statements is treated like a multi-statement request.

For a macro or a multi-statement request that returns multiple results, your Python app must use the cursor.nextset() method to advance to each separate result produced by the multi-statement request.

 

import teradatasql

conn = teradatasql.connect(None, host='hostname', user='username', password='password')

cur = conn.cursor()

cur.execute('EXEC DatabaseName.MacroName(Param1,Param2,Param3)'

results = cur.fetchall() # returns an empty list for the first (DELETE) statement

cur.nextset() # advance to the second (INSERT) statement result

results = cur.fetchall() # returns an empty list for the second (INSERT) statement

cur.nextset() # advance to the third (SELECT) statement result

results = cur.fetchall() # returns the result set from the third (SELECT) statement

 

Teradata Employee

Re: teradatasql Python driver executing macros not producing result set

Using .fetchall() as shown returns the first result set, i.e. the one associated with the first statement, which is DELETE -  therefore that particular result set is empty. You need to use the .nextset() method to advance to the next result set (twice, in this case).