Master Python‑Oracle Integration: Install Instant Client & Query Data

1. Preparation

① First, you must install Oracle Database. The installation is not trivial; refer to an installation guide and study it carefully.

② Next, because you will use Python to operate Oracle, you need to install Python. Use a 64‑bit version, not 32‑bit.

③ Finally, download and configure the Instant Client, the Oracle database client, also using the 64‑bit version.

2. Installing and Configuring Instant Client

1) Instant Client download link

Download a high‑version package such as 11 or 12 from the following URL:

http://suo.im/5Dlqrr

2) Instant Client installation

① Extract the Instant Client zip file.

② Do not place the extracted files arbitrarily; put the folder under the product subdirectory of the Oracle installation directory.

3) Environment configuration

① Follow the diagram to set the required environment variables.

② Copy oraociei11.dll, oraocci11.dll, and oci.dll from the extracted folder to the Lib/site-packages directory of your Python installation.

Instant Client folder after extraction:

Python Lib/site-packages folder:

3. Additional Important Points

1) Solving Chinese garbled characters

When reading tables containing Chinese characters from Oracle, you may encounter garbled output. Set the NLS_LANG environment variable using one of two methods.

① In a CMD window:

C:\Users\AA>set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

② Directly in Python code:

import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

Note: The two methods differ in where the command is placed; they affect only the current session.

2) Installing the cx_Oracle driver

cx_Oracle is the driver needed for Python to access Oracle databases, similar to pymysql for MySQL.

Install it with: pip install cx_Oracle If installation fails due to your system environment, refer to the following links for downloads and tutorials:

cx_Oracle download: http://suo.im/6fGFSR

cx_Oracle installation tutorial: http://suo.im/5DlvxT

4. Python–Oracle Interaction

Python’s interaction with Oracle follows the same principles as with MySQL. A detailed MySQL tutorial is available via a provided link.

1) Three ways to connect to an Oracle server

① Combine username, password, and listener in one string:

import cx_Oracle
db = cx_Oracle.connect('scott/a123456@DESKTOP-V4LKB10:1521/orcl')

② Separate username, password, and listener:

import cx_Oracle
db = cx_Oracle.connect('scott', 'a123456', '192.168.2.1:1521/orcl')

③ Use a DSN created with makedsn:

import cx_Oracle
monitor = cx_Oracle.makedsn('192.168.2.1', 1521, 'orcl')
db = cx_Oracle.connect('scott', 'a123456', monitor)

2) Retrieving data from Oracle

Data analysts often need to “query” data. Below are three common methods.

① fetchone() – retrieve one record at a time:

import cx_Oracle
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect('scott', 'a123456', '192.168.2.1:1521/orcl')
cursor = db.cursor()

cursor.execute('select count(*) from emp1')
count = cursor.fetchone()
print(count)

cursor.execute('select ename, deptno, sal from emp1')
for _ in range(count[0]):
    a, b, c = cursor.fetchone()
    d = "My name is {}, department is {}, salary is {} USD".format(a, b, c)
    print(d)

db.close()

Result snapshot:

② fetchall() – retrieve all records at once:

import cx_Oracle
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect('scott', 'a123456', '192.168.2.1:1521/orcl')
cursor = db.cursor()

cursor.execute('select ename, deptno, sal from emp1')
rows = cursor.fetchall()
for a, b, c in rows:
    d = "My name is {}, department is {}, salary is {} USD".format(a, b, c)
    print(d)

db.close()

Result snapshot:

③ Use pandas.read_sql() to load query results directly into a DataFrame:

import cx_Oracle
import pandas as pd
import os
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

db = cx_Oracle.connect('scott', 'a123456', '192.168.2.1:1521/orcl')

df1 = pd.read_sql('select * from emp where deptno=20', db)
print(df1)

df2 = pd.read_sql('select * from emp where deptno=30', db)
print(df2)

db.close()

Result snapshot: