Inserting Data Into Table from Python

Summary: in this tutorial, you will learn how to use the Python cx_Oracle 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 cx_Oracle
import config as cfg
from datetime import datetime


def insert_billing(billing_date, amount, customer_id, note):
    """
    Insert a row to the billing_headers table
    :param billing_date:
    :param amount:
    :param customer_id:
    :param note:
    :return:
    """
    # construct an insert statement that add a new row to the billing_headers table
    sql = ('insert into billing_headers(billing_date, amount, customer_id, note) '
        'values(:billing_date,:amount,:customer_id,:note)')

    try:
        # establish a new connection
        with cx_Oracle.connect(cfg.username,
                            cfg.password,
                            cfg.dsn,
                            encoding=cfg.encoding) as connection:
            # create a cursor
            with connection.cursor() as cursor:
                # execute the insert statement
                cursor.execute(sql, [billing_date, amount, customer_id, note])
                # commit work
                connection.commit()
    except cx_Oracle.Error as error:
        print('Error occurred:')
        print(error)


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

In the insert_billing() 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 config.py module:

If you have not followed the previous tutorial, you can create the config.py module with the following code:

username = 'OT'
password = '<password>'
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'
Code language: Python (python)

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_billing(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 cx_Oracle
import config as cfg
from datetime import datetime


def insert_billings(billings):
    """
    insert multiple billings
    :param billings: a list of billings
    :return:
    """
    # construct an insert statement that add a new row to the billing_headers table
    sql = ('insert into billing_headers(billing_date, amount, customer_id, note) '
        'values(:billing_date,:amount,:customer_id,:note)')

    try:
        # establish a new connection
        with cx_Oracle.connect(cfg.username,
                            cfg.password,
                            cfg.dsn,
                            encoding=cfg.encoding) as connection:
            # create a cursor
            with connection.cursor() as cursor:
                # execute the insert statement
                cursor.executemany(sql, billings)
                # commit work
                connection.commit()
    except cx_Oracle.Error as error:
        print('Error occurred:')
        print(error)


if __name__ == '__main__':
    billing_docs = [
        (datetime.now(),1000, 1, None),
        (datetime.now(), 1500, 2, None),
        (datetime.now(), 1700, 3, None),
    ]
    # insert multiple billings
    insert_billings(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.