Series: Introduction to cx_Oracle Table of Contents
--Using Oracle Cloud
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')
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']
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#
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