[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

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

Recommended Posts

[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (16th) Handling of LOB types
How to clear tuples in a list (Python)
[Introduction to cx_Oracle] (5th) Handling of Japanese data
Introduction to Linear Algebra in Python: A = LU Decomposition
Define a task to set the fabric env in YAML
Try creating a Deep Zoom file format .DZI in Python
How to output a document in pdf format with Sphinx
[Introduction to cx_Oracle] (10th) Update DML and DDL / DCL statements
How to extract other than a specific index with Numpy
How to use a file other than .fabricrc as a configuration file