--Using Oracle Cloud
In order to issue SQL statements, not limited to SELECT, you must first create a Curosr object in the following form.
cursor = connection.cursor()
Continue, for example
cursor.execute("select sysdate from dual")
And, with the SELECT statement as an argument, call the execute () method of the Curosr object. By looping the call result with a for statement, the record is retrieved in the form of a tuple. Finally, the Curosr object
cursor.close()
Close with. Below is an execution sample. Since the ALL_OBJECTS view is extracted in the order of OBJECT_ID, if you have some experience of running the cx_Oracle application, it will work without any special preparation.
sample03a.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
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:
cursor = connection.cursor()
cursor.execute(SQL)
for row in cursor:
print(row)
cursor.close()
In my environment, the output is as follows.
$ python sample03a.py
(2, 'SYS', 'C_OBJ#', 'CLUSTER')
(3, 'SYS', 'I_OBJ#', 'INDEX')
(4, 'SYS', 'TAB$', 'TABLE')
(5, 'SYS', 'CLU$', 'TABLE')
(6, 'SYS', 'C_TS#', 'CLUSTER')
In the above sample, the with syntax is used only for the Connection object, but the Curosr object also supports the with syntax. Therefore, if you rewrite the last 6 lines of the above sample as follows, it will be a safer coding with a simpler description amount and less trouble such as insufficient number of cursors (ORA-1000). As a bonus, I also shortened the SQL issuing part.
sample03b.py(Excerpt)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
for row in cursor.execute(SQL):
print(row)
If you want to handle the result set as a list, you can also use the list comprehension notation as follows. Depending on the environment, the pprint module must be pre-installed in order to execute the sample below.
sample03c.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import cx_Oracle
import pprint
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:
pprint.pprint([f"{r[1]}.{r[2]}Is{r[3]}(ID:{r[0]})is" for r in cursor.execute(SQL)])
$ python sample03c.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