31.Appendix D: Python Frameworks, SQL Generators, and ORMs
Python-oracledb’s Thin andThick modes cover the featureneeds of frameworks that depend upon the Python Database API.
31.1.Using python-oracledb with Data Frame Libraries
Python-oracledb can fetch directly to data frames that expose an Apache ArrowPyCapsule interface. This is an efficient way to work with data using Pythonlibraries such asApache PyArrow,Pandas,Polars,NumPy,PyTorch, or to write filesinApache Parquet format.
SeeWorking with Data Frames for more information.
31.2.Connecting with Flask
The Flask web application framework works well with python-oracledb, eitherdirectly or by using a library such asSQLAlchemy.
Examples using python-oracledb directly are available inconnection_pool.py,drcp_pool.py, andsession_callback.py.
31.3.Connecting with SQLAlchemy
SQLAlchemy, and libraries such asPandas that internally use SQLAlchemy, can connecteasily in python-oracledb as shown in this section.
Also, seeSQLAlchemy documentation on connecting andSQLAlchemy general documentation about Oracle Database.
31.3.1.Connecting with SQLAlchemy 2
SQLAlchemy 2 supports python-oracledb directly.
31.3.1.1.Standalone Connections in SQLAlchemy
An example of creating a standalone connection in SQLAlchemy 2 is:
# Using python-oracledb in SQLAlchemy 2importosimportgetpassimportoracledbfromsqlalchemyimportcreate_enginefromsqlalchemyimporttext# Uncomment to use python-oracledb Thick mode# Review the doc for the appropriate parameters#oracledb.init_oracle_client(<your parameters>)un=os.environ.get("PYTHON_USERNAME")cs=os.environ.get("PYTHON_CONNECTSTRING")pw=getpass.getpass(f'Enter password for{un}@{cs}: ')# Note the first argument is different for SQLAlchemy 1.4 and 2engine=create_engine('oracle+oracledb://@',connect_args={# Pass any python-oracledb connect() parameters"user":un,"password":pw,"dsn":cs})withengine.connect()asconnection:print(connection.scalar(text("""select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))
Note that thecreate_engine() argument driver declaration usesoracle+oracledb:// for SQLAlchemy 2 butoracle:// for SQLAlchemy 1.4.
Theconnect_args dictionary can use any appropriateoracledb.connect() parameter.
31.3.1.2.Pooled Connections in SQLAlchemy
Most multi-user applications should use aconnection pool.The python-oracledb pool is preferred because of its high availabilitysupport. Some single-user applications may also benfit from these availabilityfeatures.
To use a python-oracledb connection pool in SQLAlchemy:
# Using python-oracledb in SQLAlchemy 2importos,platformimportgetpassimportoracledbfromsqlalchemyimportcreate_enginefromsqlalchemyimporttextfromsqlalchemy.poolimportNullPool# Uncomment to use python-oracledb Thick mode# Review the doc for the appropriate parameters#oracledb.init_oracle_client(<your parameters>)un=os.environ.get("PYTHON_USERNAME")cs=os.environ.get("PYTHON_CONNECTSTRING")pw=getpass.getpass(f'Enter password for{un}@{cs}: ')pool=oracledb.create_pool(user=un,password=pw,dsn=cs,min=4,max=4,increment=0)engine=create_engine("oracle+oracledb://",creator=pool.acquire,poolclass=NullPool)withengine.connect()asconnection:print(connection.scalar(text("""select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))
31.3.2.Connecting with SQLAlchemy 1.4
SQLAlchemy 1.4 can use python-oracledb with the help of the module name mappingcode shown inOlder Versions of Python Frameworks, SQL Generators, and ORMs. An example is:
# Using python-oracledb in SQLAlchemy 1.4importosimportgetpassimportoracledbfromsqlalchemyimportcreate_enginefromsqlalchemyimporttextimportsysoracledb.version="8.3.0"sys.modules["cx_Oracle"]=oracledb# Uncomment to use python-oracledb Thick mode# Review the doc for the appropriate parameters#oracledb.init_oracle_client(<your parameters>)un=os.environ.get("PYTHON_USERNAME")cs=os.environ.get("PYTHON_CONNECTSTRING")pw=getpass.getpass(f'Enter password for{un}@{cs}: ')# Note the first argument is different for SQLAlchemy 1.4 and 2engine=create_engine('oracle://@',connect_args={# Pass any python-oracledb connect() parameters"user":un,"password":pw,"dsn":cs})withengine.connect()asconnection:print(connection.scalar(text("""select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))
Note that thecreate_engine() argument driver declaration usesoracle:// for SQLAlchemy 1.4 butoracle+oracledb:// for SQLAlchemy 2.
Theconnect_args dictionary can use any appropriateoracledb.connect() parameter.
You can also use python-oracledb connection pooling with SQLAlchemy 1.4. Thisis similar topooled connections in SQLAlchemy 2but use the appropriatename mapping code and firstargument tocreate_engine().
31.4.Connecting with Django
Django 5 supports python-oracledb directly. Earlier versions should usename mapping code.
SeeDjango 5.2 documentation for Oracle Database.
31.4.1.Standalone Connections
To connect in Django 5, an example settings.py file is:
DATABASES={"default":{"ENGINE":"django.db.backends.oracle","NAME":"example.com:1521/orclpdb","USER":"hr","PASSWORD":"the-hr-password"}}
31.4.2.Pooled Connections
Django 5.2 supports python-oracledbconnection pools.Most multi-user applications should use a connection pool. The python-oracledbpool is preferred because of its high availability support. Some single-userapplications may also benfit from these availability features.
To use a connection pool in Django 5.2, an example settings.py file is:
DATABASES={"default":{"ENGINE":"django.db.backends.oracle","NAME":"example.com:1521/orclpdb","USER":"hr","PASSWORD":"the-hr-password""OPTIONS":{"pool":{"min":0,"max":4,"increment":1,# Additional python-oracledb pool parameters can be added here}}},}
31.5.Older Versions of Python Frameworks, SQL Generators, and ORMs
For versions of SQLAlchemy, Django, Superset, other frameworks,object-relational mappers (ORMs), and libraries that support the obsoletecx_Oracle driver but do not have native support for python-oracledb, you canadd code like this to use python-oracledb:
importsysimportoracledboracledb.version="8.3.0"sys.modules["cx_Oracle"]=oracledb
Note
This must occur before any import of cx_Oracle by your code or the library.
SeeConnecting with SQLAlchemy 1.4 for an example.
To use Thick mode, for example, if you need to connect to Oracle Database11gR2, add a call tooracledb.init_oracle_client() with the appropriateparameters for your environment, seeEnabling python-oracledb Thick mode.
SQLAlchemy 2 and Django 5 have native support for python-oracledb so the abovecode snippet is not needed in those versions. Check your preferred library forwhich Oracle Database driver it requires.
For details on using Superset with python-oracledb, refer to the blog postSteps to use Apache Superset and Oracle Database.