sqlalchemy : configuring database client for python (pycharm)

 

The cx_Oracle module loads Oracle Client libraries which communicate over Oracle Net to an existing database. Oracle Net is not a separate product: it is how the Oracle Client and Oracle Database communicate.

Below steps for configuring database client for python (pycharm) on Mac OS. you may need to tweak it for any other OS installation

Step 1: configure instantclient-basiclite-macos.x64-19.8.0.0.0dbru.zip
	a. download https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html
	b. unzip to the local directory

Step 1a: configure it for window 

	a. download https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html 

	b. unzip to the local directory
	c. remember the path as you need to pass it in python programStep 2: install sqlalchemy and cx_Oracle
	a. in pycharm - go to preferences -> project Interpreter -> install sqlalchemy and cx_Oracle
	b. update the path of instantclient_19_3 in below
	cx_Oracle.init_oracle_client(lib_dir="<local path>/instantclient_19_3") in TestDB.py
	c. run

Linux Installation 

You may need to install using root in case of issues

pip3 install cx_Oracle
pip3 install sqlalchemy
sudo dnf install oracle-release-el8
sudo dnf install oracle-instantclient19.10-basic
sudo dnf install oracle-instantclient19.10-basic --allowerasing
path -> /usr/lib/oracle/19.10/client64/lib/ 

Code change - TestDB.py

cx_Oracle.init_oracle_client(lib_dir="/usr/lib/oracle/19.10/client64/lib/")

Program: TestDB.pyimport sqlalchemy
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="/Users/agnihotrip/instantclient_19_3")
user = "user"
password = "pass"
host = "<hostname>"
port = "1521"
service = "< service_name>"

connection_string = f'oracle+cx_oracle://{user}:{password}@{host}/?service_name={service}'
connection = sqlalchemy.create_engine(connection_string).connect()
result = connection.execute('select * from proc.proc_dnd_stage')
for row in result:
    print (row)	
connection.close()