Add iterator support for DbObject with collection content

[n8falke]

Allowing the usage of DbObject directly in example for-loops, map() or set()-constructor without aslist() to list conversion would simplify usage of from queries returned collections.

Example implementation:

class DbObject:
    ...
    def __iter__(self):
        """
        Return generator iterating over each of the collection’s elements in
        index order.
        """
        self._ensure_is_collection()
        ix = self._impl.get_first_index()
        while ix is not None:
            yield(self._impl.get_element_by_index(ix))
            ix = self._impl.get_next_index(ix)

Example usage with PL/SQL:

...
conn = oracledb.connect(...)
out_type = con.gettype("SYS.DBMS_SQL.VARCHAR2_TABLE")
cur = con.cursor()
out_var = cur.var(out_type, 10)
cur.execute("""
    DECLARE
        tbl SYS.DBMS_SQL.VARCHAR2_TABLE;
    BEGIN
        tbl(0) := 'red';
        tbl(1) := 'blue';
        tbl(2) := 'green';
        :out := tbl;
    END;""", out=out_var )
colors = set(out_var.getvalue())
print(colors)
cur.close()

Example with SELECT:

Prepare DB:

CREATE OR REPLACE TYPE STR_TABLE_T AS TABLE OF VARCHAR2(4000);

Python source:

...
cur.execute("SELECT STR_TABLE_T('first', 'second', 'third') FROM dual")
row = cur.fetchone()
for res in row[0]:
    print(res)
...

With table data a use case could be:

SELECT name, CAST(COLLECT(value) AS STR_TABLE_T) AS entries
  FROM name_value_tab
 GROUP BY name

[anthony-tuininga]

Thanks for your clear description of what you would like to see. It seems quite reasonable to me!

[anthony-tuininga]

I made the suggested change and added some tests. The aslist() method now simply uses the generator instead of duplicating the code! If you are able to build from source you can verify that it works for you, too.

[n8falke]

Thank you for the very fast reply and change.

Yes, it works perfect with for, *-unpacking, collection-constructors, map(...).

You are right, the proposed function was nearly a copy of aslist() and your solution is much cleaner code.

[n8falke]

Not important and probably the wrong place to ask: Do you perhaps know how I can change the returned type to int/decimal from DbObject with "TABLE OF NUMBER"?

[anthony-tuininga]

There is no way to do so, currently. The driver looks at the metadata and returns either int or float depending on whether the metadata says only integers are allowed or not. If you want the ability to return decimals you will need to log another enhancement request.

[n8falke]

Thank you for that hint. Controlling the type by definition works for me.

Took me while to understand the mechanism:

Column or collection defined with type	Data-value	Python resulting type
NUMBER or TABLE OF NUMBER	42	int
NUMBER or TABLE OF NUMBER	123.4	float
NUMBER(9) or TABLE OF NUMBER(9)	42	int
NUMBER(15,4) or TABLE OF NUMBER(15,4)	42	float
NUMBER(15,4) or TABLE OF NUMBER(15,4)	123.4	float
for all	NULL	NoneType

[anthony-tuininga]

This was included in version 2.2.0 which was just released.