[Python3] Connection to Oracle Database and SQL execution [cx_Oracle]

Introduction

We have summarized how to use the cx_Oracle package in Python3. With this, I think we can do some development.

It's important to say first, but it's best to look at the official documentation. cx_Oracle Official Documentation

System configuration

--Client PC

• Windows10 Pro 64bit
• Windows PowerShell
• Python 3.8.0
• pip 19.2.3
• cx_Oracle 7.2.3 --Database
• Oracle Database 12c Release 1

Package installation

PowerShell

pip install cx_Oracle

** When an error occurs ** Since Windows does not come with a C compiler as standard, an error including the following message may occur when installing with pip.

error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": https://visualstudio.microsoft.com/downloads/

In that case, you need to install "Build Tools for Visual Studio 2019" on the download page of Visual Studio. (As of November 23, 2019) [Minimum required configuration] Workload ・ C ++ Build Tools Optional ・ MSVC v142 --VS 2019 C ++ x64 / x86 Build Tool ・ Windows 10 SDK

Connect to database

connect.py

#Package import
import cx_Oracle

HOST = "localhost"
PORT = 1521
SVC_NM = "hoge.example.com"

#Generate connect descriptor
dsn = cx_Oracle.makedsn(HOST, PORT, service_name = SVC_NM)

#Establishing a connection
connection = cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8")

#Processing such as SQL issuance...

#Release the connection
connection.close()

Package import

import cx_Oracle

Generate connect descriptor

HOST = "localhost"
PORT = 1521

# SID(Instance identifier)When using
SID = "hoge"
dsn = cx_Oracle.makedsn(HOST, PORT, sid = SID)

# SERVICE_NAME(Service name)When using
SVC_NM = "hoge.example.com"
dsn = cx_Oracle.makedsn(HOST, PORT, service_name = SVC_NM)

# NET_SERVICE_NAME(Net service name, connection identifier)When using
NET_SN = "hogenet"
dsn = cx_Oracle.makedsn(HOST, PORT, NET_SN)

#Generated connect descriptor(Example: SERVICE_NAME)
print(dsn)
# >> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hoge.example.com)))

cx_Oracle.makedsn() Returns the connection identifier based on the parameters passed as arguments.

** * When using the net service name (connection identifier) ** You need to configure tnsnames.ora on the client side in advance.

Establishing and releasing connections

#Establishing a connection
connection = cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8")

#Processing such as SQL issuance...

#Release the connection
connection.close()

You can use the with syntax to automatically release a connection when you exit the with block. Unless there is a special reason, I personally think that this is better.

with cx_Oracle.connect(USER, PASS, dsn, encoding = "UTF-8") as connection:
    #Processing such as SQL issuance...
    # connection.close()No need to describe

SQL execution

query.py

#Cursor generation
cursor = connection.cursor()

#Bind variable
sql = "select * from countries where country_id = :id"
bind_data = { id: 1 }

#SQL issuance
cursor.execute(sql, bind_data)

#Data acquisition
rows = cursor.fetchall()

#Change the value to bind
bind_data["id"] = 2

#SQL issuance(Soft perspective)
cursor.execute(sql, bind_data)

#Cursor release
cursor.close()

Cursor generation and release

#Cursor generation
cursor = connection.cursor()

#Cursor release
cursor.close()

Like a connection, it can be released automatically with the with syntax. Unless you have a specific reason, we also recommend this.

with connection.cursor() as cursor:
    #SQL issuance etc....
    # cursor.close()No need to describe

Bind variable

#Bind variable(Placeholder)
sql = "select country_id, country_name from countries where country_id = :id"

#Definition of the value to bind
#For dictionary type
bind_data = { id: 1 }

#For list type
bind_data = [1]

SQL issuance

cursor.execute(sql, bind_data)

#Keyword arguments are also possible
cursor.execute(sql, id = 1)

#Change the value of the bind variable and reissue the SQL
bind_data["id"] = 2
cursor.execute(sql, bind_data)

--No semicolon (;) required at the end of SQL statement --Not only SELECT statements but also INSERT statements and UPDATE statements can be issued in the same way. --It is possible to reissue SQL by changing the value of the bind variable.

*** SOFT PARSE * ** The issued SQL is parsed by the parser and cached in the shared pool (HARD PARSE). In soft parsing, performance improvement can be expected by reusing the analysis results cached in the shared pool.

Data acquisition

#Get one record at a time from the cursor
row = cursor.fetchone()
print(row)
# >> (1, Japan)

#Get any number of records from the cursor(Example:10 records)
numRows = 10
rows = cursor.fetchmany(numRows)
print(rows)
# >> [(1, Japan)]

#Get all records from the cursor
rows = cursor.fetchall()
print(rows)
# >> [(1, Japan)]

--Data is retrieved as a tuple from the cursor --If multiple records are acquired, they will be acquired as a list of tuples.

transaction

transaction.py

#Explicit transaction initiation
connection.begin()

#SQL issuance
cursor.execute(
    "insert into countries (country_id, country_name) values (:id, :name))",
    id = 2, name = "United States"
)

#commit
connection.commit()

#roll back
connection.rollback()

Explicit transaction initiation

#Optional
connection.begin()

Oracle Database automatically starts a transaction when the first SQL is executed, so you do not need to explicitly start a transaction unless you have a specific reason to do so.

Commit and rollback

#commit
connection.commit()

#roll back
connection.rollback()

If the database connection is closed without committing, the transaction will be rolled back.

• Except when connection.autocommit is enabled

Click here for Python 3 development environment construction! [Python3] Development environment construction << Windows edition >>

There is also a Python3 cheat sheet! [Python3] Python cheat sheet for other language engineers << Basics >> [Python3] Python cheat sheet for other language engineers << Object-oriented >>