python-oracledb

https://python-oracledb.readthedocs.io/en/latest/
Admin
python-oracledb is an open source Python driver that enables access to Oracle Database with no extra...
Tokens:519,498
Snippets:3,863
Trust Score:9.6
Update:1 week ago
Show doc for...
Tokens:
Raw
### Output of fetch_df_all example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

Example output from the fetch_df_all example using Oracle's standard DEPARTMENTS table.

```text
['DEPARTMENT_ID', 'DEPARTMENT_NAME', 'MANAGER_ID', 'LOCATION_ID']
4 columns
17 rows
```

--------------------------------

### Create test.py connection script

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Initial setup for a test script to verify the installation by importing the oracledb module.

```python
import oracledb
import getpass

un = "scott"
cs = "localhost/orclpdb"
```

--------------------------------

### Manual Instant Client Installation Script

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Run the install script from a manually mounted DMG package.

```bash
/Volumes/instantclient-basic-macos.x64-19.16.0.0.0dbru/install_ic.sh
```

--------------------------------

### Create table for direct_path_load() example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

Creates a table with an id and name column for the direct path load example.

```sql
create table mytab (
               id   number(9),
               name varchar2(100));

```

--------------------------------

### Output of fetch_df_batches example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

Example output from the fetch_df_batches example using Oracle's standard DEPARTMENTS table.

```text
   DEPARTMENT_ID  DEPARTMENT_NAME  MANAGER_ID  LOCATION_ID
0             10   Administration         200         1700
1             20        Marketing         201         1800
2             30       Purchasing         114         1700
3             40  Human Resources         203         2400
   DEPARTMENT_ID   DEPARTMENT_NAME  MANAGER_ID  LOCATION_ID
0             50          Shipping         121         1500
1             60                IT         103         1400
2             70  Public Relations         204         2700
```

--------------------------------

### Manual Instant Client installation script on macOS

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Run the installation script from the mounted DMG package after manual download.

```bash
/Volumes/instantclient-basic-macos.arm64-23.3.0.23.09/install_ic.sh
```

--------------------------------

### Start the database service

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/ha.html

Start the newly created service using the DBMS_SERVICE package.

```sql
BEGIN
    DBMS_SERVICE.START_SERVICE('<service-name>');
END;
/
```

--------------------------------

### Connection String URL Example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of a connection string URL pointing to a specific configuration key within a secret.

```python
configgsmurl = "config-gcpsecretmanager://projects/my-project/secrets/app-configs/versions/latest?key=sales_app"
```

--------------------------------

### oracledb.SodaCollection.getDataGuide()

Source: https://python-oracledb.readthedocs.io/en/latest/genindex.html

Gets the data guide for a SODA collection.

```APIDOC
## getDataGuide()

### Description
Gets the data guide for a SODA collection.

### Method
getDataGuide()

### Applies to
- oracledb.SodaCollection

### Parameters
None documented.

### Returns
The data guide.
```

--------------------------------

### Connection.startup(force=False, restrict=False, pfile=None) -> None

Source: https://python-oracledb.readthedocs.io/en/latest/api_manual/connection.html

Starts up the database. Equivalent to the SQL*Plus command 'startup nomount'. The connection must be connected as AUTH_MODE_SYSDBA or AUTH_MODE_SYSOPER with AUTH_MODE_PRELIM.

```APIDOC
## Connection.startup(force=False, restrict=False, pfile=None) -> None

### Description
Starts up the database. This is equivalent to the SQL*Plus command 'startup nomount'. The connection must be connected as AUTH_MODE_SYSDBA or AUTH_MODE_SYSOPER with the AUTH_MODE_PRELIM option specified for this to work.

### Method
startup

### Parameters
- **force** (bool) - Optional - Whether to force startup. Default is False.
- **restrict** (bool) - Optional - Whether to restrict access. Default is False.
- **pfile** (str | None) - Optional - Location of the parameter file (PFILE) to use instead of the stored parameter file (SPFILE).

### Returns
None

### Notes
- This method is an extension to the DB API definition.
```

--------------------------------

### PL/SQL package with sparse associative array

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html

Defines a PL/SQL package with a procedure that outputs a sparse associative array (indexed by binary_integer) with large index gaps. This is the setup for the Python binding examples.

```plsql
create or replace package mypkg as

    type udt_StringList is table of varchar2(100) index by binary_integer;

    procedure DemoCollectionOut (
        a_Value                         out nocopy udt_StringList
    );

end;
/

create or replace package body mypkg as

    procedure DemoCollectionOut (
        a_Value                         out nocopy udt_StringList
    ) is
    begin
        a_Value(-1048576) := 'First element';
        a_Value(-576) := 'Second element';
        a_Value(284) := 'Third element';
        a_Value(8388608) := 'Fourth element';
    end;

end;
/
```

--------------------------------

### Connecting with End-User Security Context

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

This example demonstrates how to initiate a connection while providing the necessary end-user security parameters.

```python
connection = oracledb.connect(
    dsn=mydb_low,
    config_dir="path_to_unzipped_wallet",
    wallet_location="location_of_pem_file",
    wallet_password=wp,
    extra_auth_params={
      "end_user_sec_params":{
        "spi_type": "azure_tokens",
        "auth_flow": "client_credentials",
        "client_id": <client_id>,
        "client_credential": <client_credential>,
      }
    })
```

--------------------------------

### PL/SQL package defining record type and procedure

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html

Defines a PL/SQL package with a record type udt_DemoRecord and a procedure DemoRecordsInOut that modifies the record fields. Used as the database-side setup for the Python binding example.

```plsql
create or replace package mypkg as

    type udt_DemoRecord is record (
        NumberValue                     number,
        StringValue                     varchar2(30),
        DateValue                       date,
        BooleanValue                    boolean
    );

    procedure DemoRecordsInOut (
        a_Value                         in out nocopy udt_DemoRecord
    );

end;
/

create or replace package body mypkg as

    procedure DemoRecordsInOut (
        a_Value                         in out nocopy udt_DemoRecord
    ) is
    begin
        a_Value.NumberValue := a_Value.NumberValue * 2;
        a_Value.StringValue := a_Value.StringValue || ' (Modified)';
        a_Value.DateValue := a_Value.DateValue + 5;
        a_Value.BooleanValue := not a_Value.BooleanValue;
    end;

end;
/
```

--------------------------------

### Access Duality View with SODA NoSQL-style APIs

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/json_data_type.html

Opens the BookDV duality view as a SODA collection and runs a query-by-example on book titles. The example filters documents where book_title starts with 'The' and prints the book_title.

```python
soda = connection.getSodaDatabase()
collection = soda.openCollection('BOOKDV')

qbe = {'book_title': {'$like': 'The%'}}
for doc in collection.find().filter(qbe).getDocuments():
    content = doc.getContent()
    print(content["book_title"])

```

--------------------------------

### Scripted Instant Client installation on macOS ARM64

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Automated steps to download, mount, install, and unmount the Oracle Instant Client DMG.

```bash
cd $HOME/Downloads
curl -O https://download.oracle.com/otn_software/mac/instantclient/233023/instantclient-basic-macos.arm64-23.3.0.23.09.dmg
hdiutil mount instantclient-basic-macos.arm64-23.3.0.23.09.dmg
/Volumes/instantclient-basic-macos.arm64-23.3.0.23.09/install_ic.sh
hdiutil unmount /Volumes/instantclient-basic-macos.arm64-23.3.0.23.09
```

--------------------------------

### Batch delete with arraydmlrowcounts to get per-row affected counts

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/batch_statement.html

Use `arraydmlrowcounts=True` in `Cursor.executemany()` to enable per-row row counts, then retrieve them with `Cursor.getarraydmlrowcounts()`. The example deletes child rows by parent ID and prints the count for each parent. The output shows the expected counts for the sample data.

```python
parent_ids_to_delete = [20, 30, 50]
cursor.executemany("delete from ChildTable where ParentId = :1",
                   [(i,) for i in parent_ids_to_delete],
                   arraydmlrowcounts=True)
row_counts = cursor.getarraydmlrowcounts()
for parent_id, count in zip(parent_ids_to_delete, row_counts):
    print("Parent ID:", parent_id, "deleted", count, "rows.")

```

```output
Parent ID: 20 deleted 3 rows.
Parent ID: 30 deleted 2 rows.
Parent ID: 50 deleted 4 rows.

```

--------------------------------

### Connecting with OCI Token Authentication

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

This example demonstrates how to initiate a connection using the OCI token-based authentication configuration.

```python
token_based_auth = {
    "auth_type": "SimpleAuthentication",
    "user": <user>,
    "key_file": <key_file>,
    "fingerprint": <fingerprint>,
    "tenancy": <tenancy>,
    "region": <region>,
    "profile": <profile>
}

connection = oracledb.connect(
    dsn=mydb_low,
    extra_auth_params=token_based_auth)
```

--------------------------------

### Enable Thick Mode on Windows with lib_dir

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html

Call init_oracle_client() with the lib_dir parameter pointing to an unzipped Instant Client 'Basic' or 'Basic Light' package. Use a raw string for Windows paths containing backslashes. An exception is raised if the Oracle Client libraries cannot be loaded.

```Python
import oracledb

oracledb.init_oracle_client(lib_dir=r"C:\instantclient_23_5")

```

--------------------------------

### Install python-oracledb with pip

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html

Install or upgrade the python-oracledb driver using pip. This command is used both for new installations and when upgrading from cx_Oracle.

```bash
python -m pip install oracledb --upgrade

```

--------------------------------

### Connect using File Configuration Provider

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of connecting to a database using a file-based configuration path.

```python
connection = oracledb.connect(dsn="config-file:///opt/oracle/my-config1.json")
```

--------------------------------

### tnsnames.ora Entry Example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of a tnsnames.ora entry for a database connection.

```text
finance =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )
```

--------------------------------

### Azure App Configuration Connection String Example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

A sample connection string URL demonstrating the use of the Azure App Configuration provider with authentication parameters.

```python
configazureurl = "config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"
```

--------------------------------

### Expected output for DBMS_OUTPUT example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html

The output produced by the previous example.

```text
This is the python-oracledb manual
Demonstrating use of DBMS_OUTPUT

```

--------------------------------

### Example tnsnames.ora Entry

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

An example of a tnsnames.ora entry for an Oracle Autonomous Database.

```text
cjjson_high = (description=(retry_count=20)(retry_delay=3)
    (address=(protocol=tcps)(port=1522)
    (host=xxx.oraclecloud.com))
    (connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com))
    (security=(ssl_server_cert_dn="CN=xxx.oraclecloud.com,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))
```

--------------------------------

### Install Instant Client 23 on Oracle Linux 8

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Commands to install the release package and basic Instant Client 23 on Oracle Linux 8.

```bash
sudo dnf install oracle-instantclient-release-26ai-el8
sudo dnf install oracle-instantclient-basic
```

--------------------------------

### Create and enable edition

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/plsql_execution.html

Creates a new edition named 'DEMO' and grants the user permission to use it.

```sql
connect system/<password>

CREATE EDITION demo;
ALTER USER <username> ENABLE EDITIONS FORCE;
GRANT USE ON EDITION demo to <username>;
```

--------------------------------

### JDBC SID Example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of a specific JDBC SID connection string.

```text
jdbc:oracle:thin:@dbhost.example.com:1521:orcl
```

--------------------------------

### Thick mode output example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/tracing.html

Example output when running in Thick mode.

```text
python-oracledb thk : 4.0.0
```

--------------------------------

### Install Instant Client 23 on Oracle Linux 10

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Commands to install the release package and basic Instant Client 23 on Oracle Linux 10.

```bash
sudo dnf install oracle-instantclient-release-26ai-el10
sudo dnf install oracle-instantclient-basic
```

--------------------------------

### Thin mode output example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/tracing.html

Example output when running in Thin mode.

```text
python-oracledb thn : 4.0.0
```

--------------------------------

### Connect using Specific Configuration Key

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of connecting to a database using a specific key from a multi-configuration JSON file.

```python
connection = oracledb.connect(user="hr", password=userpwd,
             dsn="config-file:///opt/oracle/my-config2.json?key=production")
```

--------------------------------

### Sparse vector data example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

Two example sparse vectors stored in the table.

```text
[3, [1,2], [4.1, 5.2]]
[3, [0], [9.3]]

```

--------------------------------

### Enable Thick Mode on macOS with lib_dir

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html

Call init_oracle_client() with the lib_dir parameter pointing to an unzipped Instant Client 'Basic' or 'Basic Light' package. An exception is raised if the Oracle Client libraries cannot be loaded.

```Python
import oracledb

oracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_23_3")

```

--------------------------------

### Install Instant Client 23 on Oracle Linux 9

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Commands to install the release package and basic Instant Client 23 on Oracle Linux 9.

```bash
sudo dnf install oracle-instantclient-release-26ai-el9
sudo dnf install oracle-instantclient-basic
```

--------------------------------

### Dense vector data example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html

Two example dense vectors stored in the table.

```text
[4.1, 5.2, 6.3]
[7.1, 8.2, 9.3]

```

--------------------------------

### Create a basic CQN subscription

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/cqn.html

A basic example of creating a subscription using the subscribe method with a callback function.

```python
connection.subscribe(callback=my_callback)
```

--------------------------------

### Install python-oracledb wheel package

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Install the generated wheel file using pip.

```bash
python -m pip install dist/oracledb-4.0.0-cp314-cp314-macosx_14_0_arm64.whl
```

--------------------------------

### Create an Oracle Wallet

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/authentication_methods.html

Initializes a new Oracle Wallet in the specified directory using the mkstore command.

```bash
mkstore -wrl "/home/oracle/wallet_dir" -create
```

--------------------------------

### Basic python-oracledb connection example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html

A minimal example showing how to connect to Oracle Database using python-oracledb, prompt for a password, and execute a simple query. This example uses a context manager for the connection and cursor.

```python
import oracledb
import getpass

pw = getpass.getpass(f"Enter password for hr@localhost/orclpdb: ")

with oracledb.connect(user="hr", password=userpwd, dsn="localhost/orclpdb") as connection:
    with connection.cursor() as cursor:
        for r in cursor.execute("select sysdate from dual"):
            print(r)

```

--------------------------------

### JDBC Service Name Example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of a specific JDBC service name connection string.

```text
jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb
```

--------------------------------

### Multiple Configurations in JSON File

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

Example of a JSON configuration file defining multiple named configurations.

```json
{
    "production": {
        "connect_descriptor": "localhost/orclpdb"
    },
    "testing": {
        "connect_descriptor": "localhost/orclpdb",
        "user": "scott",
        "password": {
            "type": "base64",
            "value": "dGlnZXI="
        }
    }
}
```

--------------------------------

### Install Oracle Instant Client RPM

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Install the Oracle Instant Client using the dnf package manager for RPM files.

```bash
sudo dnf install oracle-instantclient-basic-23.9.0.25.07-1.el9.x86_64.rpm
```

--------------------------------

### Example SQL ID output

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html

Example output of the SQL ID printed by the previous code.

```text
6h6gj3ztw2wd8
```

--------------------------------

### Install python-oracledb Offline

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Install a downloaded wheel package on a machine without internet access.

```bash
python -m pip install "<file_name>"
```

--------------------------------

### Create table for version count example

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html

Creates a table with varchar2 columns of different lengths and a number column, used in the setinputsizes() example.

```sql
create table mytab (c1 varchar2(25), c2 varchar2(100), c3 number);
```

--------------------------------

### Multiple Configuration JSON Syntax

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

An example of a JSON configuration file containing multiple indexed configurations.

```json
{
    "sales_app": {
        "connect_descriptor": "localhost/orclpdb",
        "user": "sales_user",
        "password": {
          "type": "gcpsecretmanager",
          "value": "projects/my-project/secrets/sales-password/versions/latest"
        },
        "pyo": {
          "stmtcachesize": 20
        }
    },
    "hr_app": {
        "connect_descriptor": "localhost/orclpdb",
        "user": "hr_user",
        "password": {
            "type": "base64",
            "value": "dGlnZXI="
        }
    }
}
```

--------------------------------

### Install libaio dependency

Source: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html

Install the required libaio package using the system package manager.

```bash
sudo dnf install libaio
```
Tokens:
Raw