How to connect to oracle 11g (11.2.0.3.0) database using python 3.6

Question

import cx_Oracle
print('connection start')
db_connection = cx_Oracle.connect("jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Host1>)(PORT = <port_number>)) (ADDRESS = (PROTOCOL = TCP)(HOST = <Host2>)(PORT = <port_number>)) (FAILOVER=true)(LOAD_BALANCE=true) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <service_name>) (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))", "<username>", "<password>")
print(db_connection)
print('connection successful')

I'm trying to connect to oracle 11g database using python 3.6.1 (Anaconda 4.4 dist), but I'm encountering the following error.

DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

I'm able to successfully connect to same oracle 11g database by using Oracle-SQL-Developer version 17 (with the same connection string).

So, I'm wondering if there is any issue with my code or does cx_Oracle needs supporting drivers etc. Any help would be much appreciated.

Answer 1

Python cx_Oracle is C based (like PHP's OCI8, Node.js's node-oracledb, Ruby's ruby-oci8 etc) and uses a different connection syntax to JDBC (or SQL Developer - which is Java based), as @Baski said.

Take a look at https://github.com/oracle/node-oracledb/blob/v2.0.13-dev/doc/api.md#connectionstrings which shows various methods of connection like: the Easy Connect Syntax, Net Service Names, and full embedded strings. There's even a section on changing JDBC connection strings to strings usable by the C based languages.

In your case your cx_Oracle connection string would be something like:

"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Host1>)(PORT = <port_number>)) (ADDRESS = (PROTOCOL = TCP)(HOST = <Host2>)(PORT = <port_number>)) (FAILOVER=true)(LOAD_BALANCE=true) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <service_name>) (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))"

(Why so many retries?)