2

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.

Improve this question

2 Answers 2

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I have tried both, the first resulted in TypeError: makedsn() takes no keyword arguments and the second resulted in the same not given the SERVICE_NAME error` when trying to connect
You might actually try using a tns_names entry. Try looking at: [oracle.com/technetwork/articles/dsl/… for an example.
The tns_names may work but I don't have those readily available. I don't understand why the nearly same string directly to cx_Oracle works but not when passed through sqlalchemy.
Maybe that is your answer "nearly the same"? What is the difference between the two? Why are you not using the same strings?
Because the syntax differs slightly according to the docs. See the cx_Oracle example I included above. If I use the exact same it gives the same error.
|
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.