Batch unload using teradatasql python module

Question

I have installed teradatasql python module recently. When I am doing batch select into table it is not providing all outputs. How to select multiple records at a time?

with teradatasql.connect ('{"host":"whomooz","user":"guest","password":"please"}') as con:
    with con.cursor () as cur:
        cur.fast_executemany=True
        cur.execute ("select * from table where userid=? and username=?", [
            [1, "abc"],
            [2, "def"],
            [3, "ghi"]])
        print(cur.fetchall())

And do you get only those rows where `userid=1` and `username="abc"`, or not even that, or something else entirely? — mkrieger1, Feb 15 '23 at 13:41
Your `executemany` generates 3 result sets, and `fetchall` returns all the rows in the current (i.e. first) result set. You need to use the `nextset` method to advance the cursor to the next result set (it will return `True` if there are more results or `None` if not) and then do another fetch. — Fred, Feb 15 '23 at 16:23

score 0 · Answer 1 · answered Feb 15 '23 at 20:56

It sounds like you are trying to specify multiple possible column values to match, and you want a single output result set that contains all matching rows. That kind of query would look like the following, and it would produce a single result set:

select * from table
where userid=1 and username='abc'
or userid=2 and username='def'
or userid=3 and username='ghi'

In contrast, when you bind multiple rows of parameter values to question-mark parameter markers, then you will get multiple output result sets. That is how the database works. Each row of bound parameter values is treated as a separate SQL statement that is executed by the database.

Another approach to obtain a single result set would be to INSERT the parameter values to a VOLATILE temporary table and then JOIN to that temp table in your SELECT. — Fred, Feb 16 '23 at 00:01

Batch unload using teradatasql python module

1 Answers1