Inserting Data Into Table from Python

Summary: in this tutorial, you will learn how to use the python-oracledb API to insert data into a table from an Oracle Database.

Inserting a single row into the table #

The following code illustrates how to insert a new row into the billing_headers table:

import oracledb
from connect import connect
from datetime import datetime
import logging


def insert(billing_date: datetime, amount: float, customer_id: int, note: str) -> bool:

    sql = '''INSERT INTO billing_headers(billing_date, amount, customer_id, note) 
            VALUES(:billing_date, :amount, :customer_id, :note)'''

    try:
        with connect() as connection:
            with connection.cursor() as cursor:
                params = {
                    'billing_date': billing_date,
                    'amount': amount,
                    'customer_id': customer_id,
                    'note': note
                }
                cursor.execute(sql, params)
                connection.commit()
                return True
    except oracledb.Error as e:
        error_message = f"Oracle database error during billing insertion for customer_id {customer_id}: {e}"
        logging.error(error_message)
        raise
    except Exception as e:
        error_message = f"An unexpected error occurred during billing insertion for customer_id {customer_id}: {e}"
        logging.critical(error_message) 
        raise


if __name__ == '__main__':
    insert(datetime.now(), 1200, 1, None)Code language: Python (python)

In the insert() function:

First, construct an insert statement that inserts a new row into the billing_headers table. This insert statement uses the named bind variables.

Second, connect to the Oracle Database with the information provided by the connect.py module.

Third, create a Cursor object from the Connection object and execute the insert statement.

Finally, commit the transaction by using the Connection.commit() method:

connection.commit()Code language: Python (python)

The following function call inserts a new row into the billing_headers table:

insert(datetime.now(), 1200, 1, None)Code language: Python (python)

After executing the program, you can query the billing_headers table using any Oracle client tool such as SQL*Plus and SQL Developer.

SELECT * FROM billing_headders;Code language: SQL (Structured Query Language) (sql)

It worked as expected.

Inserting multiple rows into the table #

If you want to insert multiple rows into a table once, you can use the Cursor.executemany() method.

The Cursor.executemany() is more efficient than calling the Cursor.execute() method multiple times because it reduces network transfer and database load.

The following code illustrates how to insert multiple rows into the billing_headers table:

import oracledb
from connect import connect
from datetime import datetime
import logging


def insert_many(billings: list) -> bool:

    sql = '''INSERT INTO billing_headers(billing_date, amount, customer_id, note) 
            VALUES(:billing_date, :amount, :customer_id, :note)'''

    try:
        with connect() as connection:
            with connection.cursor() as cursor:
                
                cursor.executemany(sql, billings)
                connection.commit()
                return True
    except oracledb.Error as e:
        error_message = f"Oracle database error during billing insertions: {e}"
        logging.error(error_message)
        raise
    except Exception as e:
        error_message = f"An unexpected error occurred during billing insertions: {e}"
        logging.critical(error_message) 
        raise


if __name__ == '__main__':
    billing_docs = [
        {'billing_date': datetime.now(), 'amount': 1000, 'customer_id': 1, 'note': None},
        {'billing_date': datetime.now(), 'amount': 1500, 'customer_id': 2, 'note': None},
        {'billing_date': datetime.now(), 'amount': 1700, 'customer_id': 3, 'note': None},
    ]
    insert_many(billing_docs)Code language: Python (python)

Here are the contents of the billing_headers table after executing the program:

SELECT * FROM billing_headders;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the cursor.execute() and Cursor.executemany() methods to insert one or more rows into a table in the Oracle Database.