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.
