CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
Chuckerfly
Chuckerfly

Reputation: 11

How to connect to an Oracle database on a on premise server remotely using Python

How would you connect to an Oracle database on a on premise server remotelly using Python?

I've tried with the library cx_Oracle but I can't figure out how to connect to a database that's not on my local computer nor the cloud

Upvotes: 1

Views: 2242

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10586

Assuming that the network and firewall allow connections, then I'd do:

python -m pip install oracledb

And then use a script like:

import getpass
import os

import oracledb

un = os.environ.get('PYTHON_USERNAME')
cs = os.environ.get('PYTHON_CONNECTSTRING')
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

connection = oracledb.connect(user=un, password=pw, dsn=cs)

with connection.cursor() as cursor:
    sql = """select systimestamp from dual"""
    for r in cursor.execute(sql):
        print(r)

The key thing is what to set the PYTHON_CONNECTSTRING environment variable to. Start with 'Easy Connect' syntax. Find the hostname or IP address where the database is running (for example myhost.oracle.com). Find the service name (not the old "SID") of the database (for example the service name might be 'orclpdb1'). There will also be a port that is being listened on (the default is 1521). Then set the PYTHON_CONNECTSTRING environment variable to myhost.oracle.com:1521/orclpdb1.

There are other connection string syntaxes usable, as shown in the manual.

Upvotes: 4

Related Questions