--Using Oracle Cloud
Basically, as explained in 3rd, after calling the execute () method of the Cursor object, loop around the Cursor object. You can fetch records with.
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)
On the other hand, fetching by the following method based on PEP 249 (Python Database API Specification v2.0) is also possible.
fetchone()
As you can imagine from the name, it is a method that fetches only one line. Rather than fetching line by line in a loop as in the sample below
select 1+1 from dual
For SELECT statements that are known to return only one line, such as, or for scrollable cursors described below.
sample04a.py(Excerpt)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
while True:
row = cursor.fetchone()
if row is None:
break
print(row)
fetchall() Contrary to fetchone (), this method fetches all rows at once.
sample04b.py(Excerpt)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
rows = cursor.fetchall()
for row in rows:
print(row)
fetchmany() A method that fetches the number of rows specified in the argument between fetchone () and fetchall (). In the example below, 3 items are fetched.
sample04c.py(Excerpt)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
while True:
rows = cursor.fetchmany(3)
if not rows:
break
for row in rows:
print(row)
When a fetch method is issued, the number of fetches to be fetched is not actually fetched each time each method is executed. The number of cases specified in the variable called arraysize of the Cursor object (default is 100) is read into the buffer of Oracle Client, and cx_Oracle is fetched based on this buffer. This is a measure to reduce the number of round trips to the database, and is a mechanism provided by many Oracle Database access drivers, not just cx_Oracle. If you think simply, it seems to be the highest rate to fetch by fetchall () by specifying the arraysize that can fetch the planned number of fetches at once. However, in reality, it is rare that the number of acquisitions is known. In addition, arraysize must be set to a value that takes these into consideration because it is subject to restrictions such as server-side processing power, SQL weight, CPU usage and memory amount that may be used by the application, and network bandwidth. .. The following is a sample when issuing SQL with arraysize set to 1000. I am trying to fetch the same number as arraysize with fetchmany ().
sample04d.py(Excerpt)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.arraysize = 1000
cursor.execute(SQL)
while True:
rows = cursor.fetchmany(cursor.arraysize)
if not rows:
break
for row in rows:
print(row)
Normally, fetch only goes backwards. On the other hand, cx_Oracle implements a scrollable cursor that allows any record, including the front, to change its current position. To use a scrollable cursor, the scrollable argument must be set to True when the Cursor object is created. Use the scroll () method to scroll the cursor. This method has an argument called value and an argument called mode. The meaning of the value argument changes depending on the value specified in the mode argument. Below is a list of what can be done based on the mode argument.
| The value of the mode argument | Opinion and movement of the value argument |
|---|---|
| first | Scroll to the first line. The value argument is ignored. |
| last | Scroll to the last line. The value argument is ignored. |
| absolute | Moves to the number of lines specified in the value argument. |
| relative | The default value for the mode argument. Moves the number of lines specified in the value argument. Specify a negative value to return. |
Below is a sample. Note that scrollable cursors have a higher load than normal cursors, so do not use them unless necessary.
sample04e.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select object_id - 1, owner, object_name, object_type
from all_objects
order by object_id
fetch first 10 rows only
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor(scrollable=True) as cursor:
cursor.execute(SQL)
print("********Display all 10 lines********")
for row in cursor:
print(row)
print("********Show first line(value argument is ignored) ********")
cursor.scroll(8, "first")
print(cursor.fetchone())
print("********Show last line********")
cursor.scroll(mode="last")
print(cursor.fetchone())
print("********Show 3rd line********")
cursor.scroll(3, "absolute")
print(cursor.fetchone())
print("********Show after 4 lines********")
cursor.scroll(4)
print(cursor.fetchone())
print("********Display 2 lines before********")
cursor.scroll(-2, "relative")
print(cursor.fetchone())
$ python sample04e.py
********Display all 10 lines********
(1, 'SYS', 'C_OBJ#', 'CLUSTER')
(2, 'SYS', 'I_OBJ#', 'INDEX')
(3, 'SYS', 'TAB$', 'TABLE')
(4, 'SYS', 'CLU$', 'TABLE')
(5, 'SYS', 'C_TS#', 'CLUSTER')
(6, 'SYS', 'I_TS#', 'INDEX')
(7, 'SYS', 'C_FILE#_BLOCK#', 'CLUSTER')
(8, 'SYS', 'I_FILE#_BLOCK#', 'INDEX')
(9, 'SYS', 'C_USER#', 'CLUSTER')
(10, 'SYS', 'I_USER#', 'INDEX')
********Show first line(value argument is ignored) ********
(1, 'SYS', 'C_OBJ#', 'CLUSTER')
********Show last line********
(10, 'SYS', 'I_USER#', 'INDEX')
********Show 3rd line********
(3, 'SYS', 'TAB$', 'TABLE')
********Show after 4 lines********
(7, 'SYS', 'C_FILE#_BLOCK#', 'CLUSTER')
********Display 2 lines before********
(5, 'SYS', 'C_TS#', 'CLUSTER')
Recommended Posts