[Introduction to cx_Oracle] (Part 3) Basics of Table Reference

Verification environment

--Using Oracle Cloud

Basics of SELECT processing

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')

Cursor object and with syntax

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)

List comprehension

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

[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to cx_Oracle] (16th) Handling of LOB types
Introduction to PyQt4 Part 1
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
Introduction to Anomaly Detection 1 Basics
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
Introduction to Ansible Part ③'Inventory'
Series: Introduction to cx_Oracle Contents
Basics of Python × GIS (Part 1)
Introduction to Ansible Part ④'Variable'
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
Basics of Python x GIS (Part 3)
Introduction to Ansible Part 2'Basic Grammar'
Introduction to Python Hands On Part 1
Basics of Python x GIS (Part 2)
Introduction to Ansible Part 1'Hello World !!'
Introduction to Python Basics of Machine Learning (Unsupervised Learning / Principal Component Analysis)
[Introduction to Data Scientists] Basics of Python ♬ Conditional branching and loops
[Introduction to Data Scientists] Basics of Python ♬ Functions and anonymous functions, etc.
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 Data Scientists] Basics of Probability and Statistics ♬ Probability / Random Variables and Probability Distribution
Jupyter Notebook Basics of how to use
Basics of PyTorch (1) -How to use Tensor-
Introduction to Scapy ① (From installation to execution of Scapy)
Kaggle: Introduction to Manual Feature Engineering Part 1
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
Basics of Supervised Learning Part 1-Simple Regression- (Note)
[Introduction to Udemy Python 3 + Application] 19. Copy of list
From the introduction of pyethapp to the execution of contract
Basics of Supervised Learning Part 3-Multiple Regression (Implementation)-(Notes)-
[Introduction to Python] Basic usage of lambda expressions
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Supervisor
Introduction of Python
Introduction of scikit-optimize
Introduction to Tkinter 1: Introduction
Introduction of PyGMT
Introduction to PyQt
Introduction to Scrapy (2)
Basics of Python ①
Basics of python ①
[Linux] Introduction to Linux
Introduction to machine learning ~ Let's show the table of K-nearest neighbor method ~ (+ error handling)
Introduction to Scrapy (4)
Introduction to discord.py (2)
Introduction of cymel
Introduction to discord.py
Introduction of Python
Image processing by matrix Basics & Table of Contents-Reinventor of Python image processing-