CODEWITHSUNDEEP
pythonsqlalchemycx-oracle
cdeterman
cdeterman

Reputation: 19960

Python - sqlalchemy - oracle connection fails to connect to SID

I am trying to use sqlalchemy to connect to an oracle DB. I was expecting the following to work given that it appears the exact syntax is shown in the sqlalchemy documentation.

oracle_db = sqlalchemy.create_engine('oracle://user:pass@server:1521/dev')

but this results in the error:

dsn = self.dbapi.makedsn(url.host, port, **makedsn_kwargs) TypeError: makedsn() takes no keyword arguments

The following call initially works without the service name

oracle_db = sqlalchemy.create_engine('oracle://user:pass@server:1521')

But when trying to connect it fails with an error complaining that the SERVICE_NAME was not provided.

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Oddly this works with cx_Oracle directly:

con = cx_Oracle.connect('user/pass@server:1521/dev')

How am I supposed to connect to the specific service?

Attempts

I have tried to use cx_Oracle.makedsn() explicitly from this question with no luck as well.

Trying to use ? options in the connection string

oracle_db = sqlalchemy.create_engine('oracle://user:pass@server:1521/?sid=dev')

works initially but when I try oracle_db.connect() I get the same ORA-12504 error shown above.

Upvotes: 2

Views: 7571

Answers (2)

Praveen Gupta Sanka
Praveen Gupta Sanka

Reputation: 631

Try using this connection string:

engine = create_engine("oracle+cx_oracle://<username>:<password>@(DESCRIPTION = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)))")

It worked for me.

Upvotes: 1

supreme Pooba
supreme Pooba

Reputation: 988

Based on the documentation at Sqlalchemy Documentation, you should probably use the cx_oracle engine. The connect string is:

oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

with an option of service_name or sid as follows:

oracle+cx_oracle://user:pass@host:1521/?service_name=hr
oracle+cx_oracle://user:pass@host:1521/?sid=hr

Upvotes: 4

Related Questions