Oracle Python - POSOCO/MIS_Weekly_Report GitHub Wiki

User guide to install oracle instant client is available at

https://docs.oracle.com/en/database/oracle/r-enterprise/1.5.1/oread/installing-oracle-database-instant-client.html#GUID-A0AAF57E-D74F-43F1-BD92-81D197EFE17F

In python install cx_Oracle package for oracle connectivity.

Oracle python official tutorial is available at https://www.oracle.com/technetwork/articles/dsl/python-091105.html

The following is the basic python code for oracle connectivity in python

import cx_Oracle
con = cx_Oracle.connect("username/password@host:port/servicename")
print(con.version)
con.close()

Following is the code for executing oracle statements

import cx_Oracle
con = cx_Oracle.connect("username/password@host:port/servicename")
print(con.version)

cur = con.cursor()
cur.execute('SELECT * FROM BUS_REACTOR')

for result in cur:
    print(result)

cur.execute('SELECT * FROM BUS_REACTOR')
res = cur.fetchall()
print(res)

cur.close()
con.close()

Following is the code for executing oracle statements with parameters in python

import cx_Oracle

con = cx_Oracle.connect('username/password@host/servicename')
cur = con.cursor()

cur.prepare('select * from departments where department_id = :id')

cur.execute(None, {'id': 210})
res = cur.fetchall()
print(res)

cur.execute(None, {'id': 110})
res = cur.fetchall()
print(res)

cur.close()

sqlalchemy oracle notes - https://stackoverflow.com/questions/28453545/connect-to-oracle-database-using-sqlalchemy

oracle python create table example - https://www.geeksforgeeks.org/oracle-database-connection-in-python/

Python sqlalchemy example code for oracle

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

oracle_connection_string = (
    'oracle+cx_oracle://{username}:{password}@' +
    cx_Oracle.makedsn('{hostname}', '{port}', service_name='{service_name}')
)

engine = create_engine(
    oracle_connection_string.format(
        username='username',
        password='password',
        hostname='host',
        port='1521',
        service_name='servicename'
    )
)

data = pd.read_sql("SELECT * FROM BUS_REACTOR", engine)


con = engine.connect()
outpt = con.execute("SELECT * FROM BUS_REACTOR")
df = pd.DataFrame(outpt.fetchall())
df.columns = outpt.keys()
print(df.head())
con.close()