1

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.

Community
  • 1
  • 1
cdeterman
  • 19,630
  • 7
  • 76
  • 100

2 Answers2

3

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
supreme Pooba
  • 868
  • 1
  • 7
  • 14
  • 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` – cdeterman Apr 06 '16 at 15:28
  • You might actually try using a tns_names entry. Try looking at: [http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html] for an example. – supreme Pooba Apr 06 '16 at 15:31
  • 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`. – cdeterman Apr 06 '16 at 15:38
  • Maybe that is your answer "nearly the same"? What is the difference between the two? Why are you not using the same strings? – supreme Pooba Apr 06 '16 at 19:44
  • 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. – cdeterman Apr 06 '16 at 21:23
  • AH! Ok, so the difference between what I suggested and what you wrote was to use the "oracle+cx_oracle" at the beginning of the connect string. Did you try this? ```oracle_db = sqlalchemy.create_engine('oracle+cx_oracle://user:pass@server:1521/?sid=dev')``` This is your example, only with ```+cx_oracle``` – supreme Pooba Apr 08 '16 at 15:18
  • Unfortunately that still results in the `ORA-12504` error when I try `oracle_db.connect()` – cdeterman Apr 12 '16 at 12:49
  • I'm sorry this isn't working for you. I don't have an oracle instance to attach to, otherwise I would be able to help you further. – supreme Pooba Apr 15 '16 at 14:38
  • `oracle+cx_oracle://user:pass@host:1521/?service_name=hr` this one works for me...But it's pretty weird. – hajimuz Oct 22 '18 at 02:04
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.

Praveen Gupta Sanka
  • 609
  • 1
  • 8
  • 25