Connecting to Oracle with SQLAlchemy and cx_Oracle

When working with Oracle databases in Python, a common approach is to use SQLAlchemy as the ORM or query layer and cx_Oracle as the underlying database driver. One of the most common stumbling blocks is getting the connection string syntax exactly right.

This post walks through the correct oracle+cx_oracle connection string formats, with examples for the most common Oracle connection scenarios.

---

Basic Connection String Format

When using SQLAlchemy with cx_Oracle, the connection URL starts with:

oracle+cx_oracle://

From there, the full format depends on whether you connect using a service name, SID, or TNS alias.

---

Connecting with a Service Name (Recommended)

Most modern Oracle databases use a service name rather than a SID.

Syntax

oracle+cx_oracle://username:password@host:port/?service_name=SERVICE

Example

from sqlalchemy import create_engine

 

engine = create_engine(

“oracle+cx_oracle://desi:tiger@dbserver.example.com:1521/?service_name=ORCLPDB1”

)

---

Connecting with a SID

Some legacy systems still require a SID.

Syntax

oracle+cx_oracle://username:password@host:port/?sid=SID

Example

engine = create_engine(

“oracle+cx_oracle://desi:tiger@dbserver.example.com:1521/?sid=ORCL”

)

---

Using a TNS Alias

If your Oracle client is configured with a tnsnames.ora file and the appropriate environment variables (ORACLE_HOME or TNS_ADMIN) are set, you can connect using a TNS alias.

Syntax

oracle+cx_oracle://username:password@TNS_ALIAS

Example

engine = create_engine(

“oracle+cx_oracle://desi:tiger@PRODDB”

)

This approach is often useful in enterprise environments where connection details are centrally managed.

---

Handling Special Characters in Passwords

Because SQLAlchemy connection strings are URLs, special characters in passwords (@, /, :, etc.) must be URL-encoded.

Example

from urllib.parse import quote_plus

 

password = quote_plus(“p@ss/w:rd”)

 

engine = create_engine(

f”oracle+cx_oracle://desi:{password}@dbserver:1521/?service_name=ORCLPDB1″

)

Failing to encode the password is a common cause of confusing connection errors.

---

Important Notes

• cx_Oracle requires Oracle Client libraries, such as Oracle Instant Client.
• SQLAlchemy automatically constructs the underlying Oracle DSN for you based on the connection URL.
• For new projects, Oracle recommends the newer oracledb driver (which supports a thin mode without client libraries), but cx_Oracle remains widely used and supported in existing systems.

---

Final Thoughts

Getting the Oracle connection string right saves a lot of debugging time. Whether you’re using a service name, SID, or TNS alias, the key is understanding how SQLAlchemy maps the URL into an Oracle DSN and ensuring credentials are properly encoded.

Once that’s in place, connecting to Oracle with Python becomes straightforward and reliable.