2

we have been using sqlalchemy successfully to connect to Oracle. Just now our organization is moving to encrypted Oracle database and we have been asked to switch to the encrypted database.

The sample code given to me by the database engineering team is which uses cx_Oracle directly is:

import cx_Oracle
dsn = """(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcps)(HOST=test_host)(PORT=1531)))
(CONNECT_DATA=(SERVICE_NAME=test_service)))"""
connection = cx_Oracle.connect(user="test", password="test", dsn=dsn, encoding="UTF-8")

However, when I try to connect to the database using sqlalchemy using : oracle+cx_oracle://test:test@test_host:1531/test_service

I get an error : (cx_Oracle.DatabaseError) ORA-12547: TNS:lost contact\n(Background on this error at: http://sqlalche.me/e/13/4xp6)

I suspect that it is the protocol tcps that needs to be set.

I tried the following connection string : protocol_url = 'oracle+cx_oracle://test:test@test_host:1531?service_name=test_service&protocol=tcps'

I get the following error : ValueError: invalid literal for int() with base 10: '1531?service_name=test_service&protocol=tcps'

Is there a way to use sqlalchemy to connect to Oracle on an encrypted port?

EDIT : I went through the steps listed in Python connect to Oracle database with TCPS I still get the error : sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-12547: TNS:lost contact

NOTE: I know that I can successfully connect to Oracle with encryption using a direct cx_Oracle connection.

Krish Srinivasan
  • 568
  • 1
  • 6
  • 14
  • 2
    Does this answer your question? https://stackoverflow.com/questions/66094119/python-connect-to-oracle-database-with-tcps – pmdba Jan 04 '23 at 21:56
  • The above is very useful...but I was hoping to not have to do the wallet stuff on my machine. – Krish Srinivasan Jan 04 '23 at 22:09
  • That's the only way to do TCPS (TLS): it requires client certificates by definition. – pmdba Jan 04 '23 at 22:14
  • The sample code referenced(the one that uses cx_Oracle) does not need the wallet stuff. Are you saying that sqlalchemy with tcps requires client certs by definition? – Krish Srinivasan Jan 04 '23 at 22:20
  • The example did show the creation of a wallet using a certificate obtained from the DBA. TCPS always requires certificates. – pmdba Jan 04 '23 at 22:22
  • Does this answer your question? [Python connect to Oracle database with TCPS](https://stackoverflow.com/questions/66094119/python-connect-to-oracle-database-with-tcps) – ljmc Jan 04 '23 at 23:22
  • @ljmc that is literally the same link already posted in the first comment. – pmdba Jan 05 '23 at 03:40
  • @pmdba, I flagged the question with your link since you had not done that, this is the automatic comment added by a flag. It’ll be removed if the question is closed as a duplicate. – ljmc Jan 05 '23 at 07:32
  • Unfortunately the link referenced does not answer my question. After doing all the steps with reference to the wallet described in the link, I am still not able to connect to oracle using sqlalchemy. I will update the question with my findings. – Krish Srinivasan Jan 05 '23 at 17:02

2 Answers2

1

In order to use oracle encryption with sql alchemy the following needs to be done :

a) Follow the instructions to set up your client machine as described in the answer to the question : Python connect to Oracle database with TCPS

b) Then use the DSN as specified in the answer to this question : Using Oracle Service Names with SQLAlchemy

For my part, I needed to do both a) and b). Some of you using sqlalchemy may already have specified the DSN in the needed format. engine = create_engine("oracle+cx_oracle://:@(DESCRIPTION = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)))")

The main thing here is that the DSN format I was using :
oracle+cx_oracle://:@:/test_db does not allow the protocol to be specified as TCPS which is also required.

Krish Srinivasan
  • 568
  • 1
  • 6
  • 14
1

Since you're using Python/SQLAlchemy, you can use create_engine. My Python code constructs the connection string then calls create_engine like this:

dsnStr = cx_Oracle.makedsn(config[args.tier]['oracle_host'], config[args.tier]['oracle_port'],config[args.tier]['oracle_sid'],
    config[args.tier]['oracle_service'])
dsnStr = dsnStr.replace('TCP', config[args.tier]['oracle_protocol'])
oracle_conn_str = "oracle://" + config[args.tier]['username'] + ":" + config[args.tier]['password'] + "@" + dsnStr
engine = create_engine(oracle_conn_str, connect_args={ "encoding": "UTF-8", "nencoding": "UTF-8"})

In my code, I read a config file (via configparser) that specified the oracle_protocol to be TCPS.

dsnStr for me was:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=REDACTED)(PORT=1923))(CONNECT_DATA=(SID=REDACTED)(SERVICE_NAME=)))

In the end, oracle_conn_str for me was:

oracle://rumali:REDACTED@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=REDACTED)(PORT=1923))(CONNECT_DATA=(SID=REDACTED)(SERVICE_NAME=)))
rickumali
  • 707
  • 8
  • 17