CODEWITHSUNDEEP
pythondatabaseoracle-databasesqlalchemyconnection
Michael
Michael

Reputation: 1

I have successful connection to oracle database through python using cx_oracle but I couldn't connect to the database using sqlalchemy

 import cx_Oracle
 import sqlalchemy
 import pandas as pd
 from sqlalchemy import create_engine

 hostname = r'myhost'
 port = '0000'
 sid = 'ssss'
 username = 'myusername'
 password = 'mypass'


 dsn_tns = cx_Oracle.makedsn(hostname,port,sid) 
 conn = cx_Oracle.connect(username, password=password, dsn=dsn_tns) 
 conn.connect()
 print('Connected to oracle using cx_oracle')
 engine = create_engine(conn, connect_args={ "encoding": "UTF-8", "nencoding": "UTF-8"})
 engine.connect()
 print('Connected to sql alchemy')

This code displays an error after it connects to oracle database using cx_oracle it couldnt connect to sqlalchemy

Out Put

Connected to oracle using cx_oracle Traceback (most recent call last): File "D:\Project\Python Script to connect to oracle sql\sqlalchemy_connect.py", line 16, in engine = create_engine(conn, connect_args={ "encoding": "UTF-8", "nencoding": "UTF-8"}) File "", line 2, in create_engine
File "C:\Users\mikim\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\deprecations.py", line 277, in warned return fn(*args, **kwargs) # type: ignore[no-any-return] File "C:\Users\mikim\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\create.py", line 549, in create_engine u, plugins, kwargs = u._instantiate_plugins(kwargs) AttributeError: 'cx_Oracle.Connection' object has no attribute '_instantiate_plugins'

Upvotes: 0

Views: 1123

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10586

Install the latest versions:

python3 -m pip install sqlalchemy pandas oracledb --upgrade

Make sure you have SQLAchemy 2 and pandas 2 (for this example - the driver and engine create syntax needed is slightly different in the older versions). Note that python-oracledb is the new name for cx_Oracle.

In SQL*Plus, create a table:

drop table t purge;
create table t (k number, v varchar2(20));
insert into t (k, v) values (1234, 'neutral');
insert into t (k, v) values (5678, 'alkaline');
commit;

Create a file, t.py:

import os
import getpass
import platform

from sqlalchemy import create_engine, text
import pandas as pd

un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

engine = create_engine(
    f'oracle+oracledb://:@',
    connect_args={
        "user": un,
        "password": pw,
        "dsn": cs
    })

query = """select * from t"""
df = pd.read_sql_query(text(query), engine)
print(df)

Set your environment:

export PYTHON_USERNAME='cj'
export PYTHON_CONNECTSTRING='localhost/freepdb1'

Run it:

$ python3 t.py
Enter password for cj@localhost/freepdb1: XXXXX
      k         v
0  1234   neutral
1  5678  alkaline

Upvotes: 0

Related Questions