cx_Oracle & Connecting to Oracle DB Remotely

How do you connect to a remote server via IP address in the manner that TOAD, SqlDeveloper, are able to connect to databases with just the ip address, username, SID and password?

Whenever I try to specify and IP address, it seems to be taking it locally.

In other words, how should the string for cx_Oracle.connect() be formatted to a non local database?

There was a previous post which listed as an answer connecting to Oracle via cx_Oracle module with the following code:

#!/usr/bin/python

import cx_Oracle
connstr='scott/tiger'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

curs.execute('select * from emp')
print curs.description
for row in curs:
    print row
conn.close()

Solution 1:

I like to do it this way:

ip = '192.168.0.1'
port = 1521
SID = 'YOURSIDHERE'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)

db = cx_Oracle.connect('username', 'password', dsn_tns)

One of the main reasons I like this method is that I usually have a TNSNAMES.ORA file lying around someplace, and I can check that the dsn_tns object will do the right thing by doing:

print dsn_tns

and comparing the output to my TNSNAMES.ORA

Solution 2:

You can specify the server in the connection string, e.g.:

import cx_Oracle
connstr = 'scott/tiger@server:1521/orcl'
conn = cx_Oracle.connect(connstr)

• "server" is the server, or the IP address if you want.
• "1521" is the port that the database is listening on.
• "orcl" is the name of the instance (or database service).

Solution 3:

import cx_Oracle

CONN_INFO = {
    'host': 'xxx.xx.xxx.x',
    'port': 12345,
    'user': 'user_name',
    'psw': 'your_password',
    'service': 'abc.xyz.com',
}

CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)

connection = cx_Oracle.connect(CONN_STR)