I am trying to connect python to Oracle database using SQLAlchemy that uses cx_Oracle. I need to use SQLAlchemy and connect to Oracle Database and then I intend to use this code for langchain to connect OpenAI to Oracle. For now I need the below code to connect successfully to Oracle Database. What is preventing me from solving this myself is the error ORA-12505. The app.py file contains the below code.
from sqlalchemy import create_engine
import cx_Oracle
import config
sid = cx_Oracle.makedsn(config.DATABASE['host'], config.DATABASE['port'], sid=config.DATABASE['database'])
cstr = '{drivername}://{user}:{password}@{sid}'.format(
drivername = config.DATABASE['drivername'],
user=config.DATABASE['username'],
password=config.DATABASE['password'],
sid=sid
)
engine = create_engine(
cstr,
# convert_unicode=False,
pool_recycle=10,
pool_size=50,
echo=True
)
stmt = 'select * from emp'
with engine.connect() as conn:
result = conn.execute(stmt)
for row in result:
print(row)
The config.py file contains
DATABASE = {
'drivername' : 'oracle',
'host' : '192.168.1.3',
'port' : '1521',
'database' :'orclpdb',
'username' :'scott',
'password' :'tiger'
}
Currently the connection to the SCOTT user is successful using sqlplus.
C:\Users\HI\Desktop\New folder>sqlplus scott/tiger@//DESKTOP-CKEA6JR:1521/orclpdb
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 7 15:22:24 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Thu Mar 07 2024 14:55:54 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> sho con_name
CON_NAME
------------------------------
ORCLPDB
SQL>
SQL> alter system register;
System altered.
What I tried so far is to execute the below command.
C:\Users\HI\AppData\Local\Programs\Python\Python312\python.exe app.py