[PYTHON] [Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.8
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

Overview

cx_Oracle returns the result set as a list of tuples. However, there may be cases where you want to return this as a list or dictionary. Cx_Oracle is prepared for that case, so I will explain how to do it.

Cursor.rowfactory The rowfactory attribute of the Cursor object defines the method that will be called when retrieving the record. This attribute produces tuples by default. By overwriting this movement, it is possible to change the format of the record to another form.

Let's take a look at the actual coding by revising the following application.

sample15a.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    select object_id, owner, object_name, object_type
      from all_objects
     order by object_id
     fetch first 5 rows only
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        for row in cursor.execute(SQL):
            print(row)

$ python sample15a.py
(2, 'SYS', 'C_OBJ#', 'CLUSTER')
(3, 'SYS', 'I_OBJ#', 'INDEX')
(4, 'SYS', 'TAB$', 'TABLE')
(5, 'SYS', 'CLU$', 'TABLE')
(6, 'SYS', 'C_TS#', 'CLUSTER')

Returns a list of result sets

sample15b.py(Excerpt)

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        cursor.execute(SQL)
        cursor.rowfactory = lambda *args: list(args)
        rows = cursor.fetchall()
        for row in rows:
            print(row)

The fourth line from the bottom is the implementation of rowfactory. I'm using a lambda expression to convert each record from a tuple to a list. Only this one line has been added, and no other coding has been changed. The execution results are listed below.

$ python sample15b.py
[2, 'SYS', 'C_OBJ#', 'CLUSTER']
[3, 'SYS', 'I_OBJ#', 'INDEX']
[4, 'SYS', 'TAB$', 'TABLE']
[5, 'SYS', 'CLU$', 'TABLE']
[6, 'SYS', 'C_TS#', 'CLUSTER']

Returns the result set in a dictionary

It is also possible to return records in a dictionary with column names as elements.

sample15c.py(Excerpt)

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        cursor.execute(SQL)
        columns = [col[0] for col in cursor.description]
        cursor.rowfactory = lambda *args: dict(zip(columns, args))
        rows = cursor.fetchall()
        for row in rows:
            print(row)
        for row in rows:
            print(row["OBJECT_NAME"])

The column name is obtained in the 4th row from the top. cursor.description is a read-only attribute in the form of a list of tuples that store the metadata for each column. The column name is the element number 0 of each tuple of this attribute. The fifth line from the top uses a lambda expression to create a dictionary using the information from the previous line. The execution result is a dictionary as shown below.

$ python sample15c.py
{'OBJECT_ID': 2, 'OWNER': 'SYS', 'OBJECT_NAME': 'C_OBJ#', 'OBJECT_TYPE': 'CLUSTER'}
{'OBJECT_ID': 3, 'OWNER': 'SYS', 'OBJECT_NAME': 'I_OBJ#', 'OBJECT_TYPE': 'INDEX'}
{'OBJECT_ID': 4, 'OWNER': 'SYS', 'OBJECT_NAME': 'TAB$', 'OBJECT_TYPE': 'TABLE'}
{'OBJECT_ID': 5, 'OWNER': 'SYS', 'OBJECT_NAME': 'CLU$', 'OBJECT_TYPE': 'TABLE'}
{'OBJECT_ID': 6, 'OWNER': 'SYS', 'OBJECT_NAME': 'C_TS#', 'OBJECT_TYPE': 'CLUSTER'}
C_OBJ#
I_OBJ#
TAB$
CLU$
C_TS#

Return result set as Data Class

rowfactory also supports Data Class, which is a new feature of Python 3.7. Creating the same Data Class as the result set seems to be useful. See below for a description of the Data Class itself.

From Python 3.7, "Data Classes" may become the standard for class definitions

Below are samples and execution results.

sample15d.py

import cx_Oracle
from dataclasses import dataclass

@dataclass
class AllObject:
    object_id: int
    owner: str
    object_name: str
    object_type: str


    def display(self):
        return f"{self.owner}.{self.object_name}Is{self.object_type}(ID:{self.object_id})is"


USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
    select object_id, owner, object_name, object_type
      from all_objects
     order by object_id
     fetch first 5 rows only
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
    with connection.cursor() as cursor:
        cursor.execute(SQL)
        cursor.rowfactory = lambda *args: AllObject(*args)
        rows = cursor.fetchall()
        [print(r.display()) for r in rows]

$ python sample15d.py
SYS.C_OBJ#Is CLUSTER(ID:2)is
SYS.I_OBJ#Is INDEX(ID:3)is
SYS.TAB$Is TABLE(ID:4)is
SYS.CLU$Is TABLE(ID:5)is
SYS.C_TS#Is CLUSTER(ID:6)is