[PYTHON] [Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set

Verification environment

--Using Oracle Cloud

Result set Fetch

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)

Adjusting arraysize

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)

Result set scroll

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

[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
[Introduction to cx_Oracle] (16th) Handling of LOB types
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
Introduction to PyQt4 Part 1
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
[Introduction to cx_Oracle] (10th) Update DML and DDL / DCL statements
[Introduction to Scipy] Calculation of Lorenz curve and Gini coefficient ♬
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
Machine learning to learn with Nogizaka46 and Keyakizaka46 Part 1 Introduction
Introduction of DataLiner ver.1.3 and how to use Union Append
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
Introduction to Ansible Part ③'Inventory'
Series: Introduction to cx_Oracle Contents
Introduction and tips of mlflow.Tracking
Introduction to Ansible Part ④'Variable'
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part1-
[Introduction to Python] I compared the naming conventions of C # and Python.
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part2-
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part4-
[Introduction to Udemy Python3 + Application] 69. Import of absolute path and relative path
[Introduction to pytorch-lightning] Autoencoder of MNIST and Cifar10 made from scratch ♬
[Introduction to Udemy Python3 + Application] 12. Indexing and slicing of character strings
Introduction to TensorFlow-Summary of four arithmetic operations and basic mathematical functions
Convert the result of python optparse to dict and utilize it
[Introduction to SIR model] Consider the fitting result of Diamond Princess ♬
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part3-
[Introduction to Data Scientists] Basics of Python ♬ Conditional branching and loops
[Introduction to Data Scientists] Basics of Python ♬ Functions and anonymous functions, etc.
Introduction to Ansible Part 2'Basic Grammar'
[Introduction to Python3 Day 1] Programming and Python
Introduction and implementation of activation function
Introduction to Python Hands On Part 1
Introduction to Ansible Part 1'Hello World !!'
Introduction and usage of Python bottle ・ Try to set up a simple web server with login function
[Introduction to Data Scientists] Basics of Probability and Statistics ♬ Probability / Random Variables and Probability Distribution
Introduction of cyber security framework "MITRE CALDERA": How to use and training
Introduction to Deep Learning ~ Convolution and Pooling ~
Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to AWS] Text-Voice conversion and playing ♪
Easy introduction of python3 series and OpenCV3
[Introduction to Udemy Python3 + Application] 29. Set method
Introduction to Scapy ① (From installation to execution of Scapy)
[Introduction to Data Scientists] Basics of Python ♬
Kaggle: Introduction to Manual Feature Engineering Part 1
[GKE] Set Deployment replicas to 0 and notify Go app of OS signal SIGTERM
Use Pillow to make the image transparent and overlay only part of it