Get the last result set only/number of sets in cursor?

[dmlogv]

Well, let's talk about multiple statements (again).

cursor.execute("""
    SELECT 'Hey Joe'      title;
    SELECT 'Foxy Lady'    title;
    SELECT 'Purple Haze'  title;
    SELECT 'Voodoo Chile' title;
    """)

It's very easy to get Hey Joe only:

print(cursor.fetchall())

[['Hey Joe']]

it's not very difficult to get all sets:

while True:
    print(cursor.fetchall(), cursor.rowcount)
    if not cursor.nextset(): break

[['Hey Joe']] 1
[['Foxy Lady']] 2
[['Purple Haze']] 3
[['Voodoo Chile']] 4

(off-topic: what happened with the rowcount?)

But how to skip n first result set without fetching 'em all?
Or there is a method to get a number of result sets? (Of course query.count(';') looks like a crutch.)

[sitingren]

nextset() would skip the previous set of result, so you don't need to explicitly call fetchall(), you can call nextset() n times to skip them directly.

off-topic: Yes, rowcount is counting incorrectly. Thank you for reporting that.

[dmlogv]

you can call nextset() n times to skip them directly.

Of course, but this is the main question: how to get n if I don't know how many statements in my query?

E. g. I have to write a library function:

def get_last_set(query, conn):
    pass

How to implement it?

[sitingren]

nextset() reads/skips query result from the wire one by one. So vertica-python doesn't have the ability to know n without using nextset().

while True:
    last_set = cursor.fetchall()
    if not cursor.nextset(): break
# Now last_set holds the last set of query result

[dmlogv]

It's an explicit solution, but in opposition with your previous offer:

nextset() would skip the previous set of result, so you don't need to explicitly call fetchall()

Is it impossible in the current version of the vertica-python or the Vertica protocol don't provide this information?

[sitingren]

@dm-logv What API change for vertica-python do you expect to have?

[dmlogv]

@sitingren I expect:

• A number of received result sets in the current cursor
• And/or a flag for the last result set in the cursor.

Simply put, I want to know how many times I have to call nextset() and then fetchall() (just one time) to receive [['Voodoo Chile']] row from my example without a fetching all previous result sets.

[sitingren]

That's not a reasonable change vertica-python can make so far. If you set log level to DEBUG, you will have a better understanding of the mechanism from the log. The simplified mechanism is:

When you execute a sql, the server will send a sequence of messages like

<= Indication of a new result set
<= Data of result set 1
<= End of the result set
<= Indication of a new result set
<= Data of result set 2
<= End of the result set
...
<= Indication of a new result set
<= Data of result set N
<= End of the result set
<= End of execution

As you can see, the Vertica protocol doesn't provide information about how many result sets are there in an execution.

At a lower level, there is a read_message() function that controls reading each message from TCP.
To prevent out-of-memory in case the result sets are too large, cursor.execute() would call read_message() once to read just the first message sent by the server and wait for user's next function call. If cursor.fetchall() is called, read_message() would be called to get a result set, and the result returns to the user. If cursor.nextset() is called, read_message() would be called to get a result set and discard, and continue reading messages until a "Indication of a new result set" message or "End of execution" message, then return True/False. If other functions are called, read_message() would be called to read messages (and discard) until a "End of execution" message.

<= Indication of a new result set  -- cursor.execute() stops here  ⌉                ⌉
<= Data of result set 1                                            | fetchall()     |
<= End of the result set                                           ⌋                | nextset()
<= Indication of a new result set                                                   ⌋
<= Data of result set 2
<= End of the result set
...
<= Indication of a new result set
<= Data of result set N
<= End of the result set
<= End of execution

Therefore, cursor.fetchall() and cursor.nextset() are doing similar message reading process, the difference is how they handle each message. Until cursor.nextset() reads a "End of execution" message, there is no way to tell whether a result set is the last set.

[dmlogv]

@sitingren Thank you for the great explanation