{"id":3019,"date":"2019-11-23T03:25:09","date_gmt":"2019-11-23T10:25:09","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=3019"},"modified":"2025-05-31T19:00:54","modified_gmt":"2025-06-01T02:00:54","slug":"inserting-data","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/python-oracle\/inserting-data\/","title":{"rendered":"Inserting Data Into Table from Python"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the python-oracledb API to insert data into a table from an Oracle Database.<\/p>\n\n\n\n

Inserting a single row into the table #<\/a><\/h2>\n\n\n\n

The following code illustrates how to insert a new row into the billing_headers<\/code> table:<\/p>\n\n\n

import<\/span> oracledb\nfrom<\/span> connect import<\/span> connect\nfrom<\/span> datetime import<\/span> datetime\nimport<\/span> logging\n\n\ndef<\/span> insert<\/span>(billing_date: datetime, amount: float, customer_id: int, note: str)<\/span> -> bool:<\/span>\n\n    sql = '''INSERT INTO billing_headers(billing_date, amount, customer_id, note) \n            VALUES(:billing_date, :amount, :customer_id, :note)'''<\/span>\n\n    try<\/span>:\n        with<\/span> connect() as<\/span> connection:\n            with<\/span> connection.cursor() as<\/span> cursor:\n                params = {\n                    'billing_date'<\/span>: billing_date,\n                    'amount'<\/span>: amount,\n                    'customer_id'<\/span>: customer_id,\n                    'note'<\/span>: note\n                }\n                cursor.execute(sql, params)\n                connection.commit()\n                return<\/span> True<\/span>\n    except<\/span> oracledb.Error as<\/span> e:\n        error_message = f\"Oracle database error during billing insertion for customer_id {customer_id}<\/span>: {e}<\/span>\"<\/span>\n        logging.error(error_message)\n        raise<\/span>\n    except<\/span> Exception as<\/span> e:\n        error_message = f\"An unexpected error occurred during billing insertion for customer_id {customer_id}<\/span>: {e}<\/span>\"<\/span>\n        logging.critical(error_message) \n        raise<\/span>\n\n\nif<\/span> __name__ == '__main__'<\/span>:\n    insert(datetime.now(), 1200<\/span>, 1<\/span>, None<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In the insert()<\/code> function:<\/p>\n\n\n\n
Second, connect to the Oracle Database<\/a> with the information provided by the connect.py<\/code> module.<\/p>\n\n\n\n
Third, create a Cursor<\/code> object from the Connection<\/code> object and execute the insert<\/code> statement.<\/p>\n\n\n\n
Finally, commit the transaction by using the Connection.commit()<\/code> method:<\/p>\n\n\n
connection.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The following function call inserts a new row into the billing_headers<\/code> table:<\/p>\n\n\n
insert(datetime.now(), 1200<\/span>, 1<\/span>, None<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
After executing the program, you can query the billing_headers<\/code> table using any Oracle client tool such as SQL*Plus and SQL Developer.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> billing_headders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"python<\/figure>\n\n\n\n
It worked as expected.<\/p>\n\n\n\n
Inserting multiple rows into the table #<\/a><\/h2>\n\n\n\n
If you want to insert multiple rows into a table once, you can use the Cursor.executemany()<\/code> method.<\/p>\n\n\n\n
The Cursor.executemany()<\/code> is more efficient than calling the Cursor.execute()<\/code> method multiple times because it reduces network transfer and database load.<\/p>\n\n\n\n
The following code illustrates how to insert multiple rows into the billing_headers<\/code> table:<\/p>\n\n\n
import<\/span> oracledb\nfrom<\/span> connect import<\/span> connect\nfrom<\/span> datetime import<\/span> datetime\nimport<\/span> logging\n\n\ndef<\/span> insert_many<\/span>(billings: list)<\/span> -> bool:<\/span>\n\n    sql = '''INSERT INTO billing_headers(billing_date, amount, customer_id, note) \n            VALUES(:billing_date, :amount, :customer_id, :note)'''<\/span>\n\n    try<\/span>:\n        with<\/span> connect() as<\/span> connection:\n            with<\/span> connection.cursor() as<\/span> cursor:\n                \n                cursor.executemany(sql, billings)\n                connection.commit()\n                return<\/span> True<\/span>\n    except<\/span> oracledb.Error as<\/span> e:\n        error_message = f\"Oracle database error during billing insertions: {e}<\/span>\"<\/span>\n        logging.error(error_message)\n        raise<\/span>\n    except<\/span> Exception as<\/span> e:\n        error_message = f\"An unexpected error occurred during billing insertions: {e}<\/span>\"<\/span>\n        logging.critical(error_message) \n        raise<\/span>\n\n\nif<\/span> __name__ == '__main__'<\/span>:\n    billing_docs = [\n        {'billing_date'<\/span>: datetime.now(), 'amount'<\/span>: 1000<\/span>, 'customer_id'<\/span>: 1<\/span>, 'note'<\/span>: None<\/span>},\n        {'billing_date'<\/span>: datetime.now(), 'amount'<\/span>: 1500<\/span>, 'customer_id'<\/span>: 2<\/span>, 'note'<\/span>: None<\/span>},\n        {'billing_date'<\/span>: datetime.now(), 'amount'<\/span>: 1700<\/span>, 'customer_id'<\/span>: 3<\/span>, 'note'<\/span>: None<\/span>},\n    ]\n    insert_many(billing_docs)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Here are the contents of the billing_headers<\/code> table after executing the program:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> billing_headders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"python<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the cursor.execute()<\/code> and Cursor.executemany()<\/code> methods to insert one or more rows into a table in the Oracle Database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
First, construct an insert<\/a><\/code> statement that inserts a new row into the billing_headers<\/code> table. This insert<\/a><\/code> statement uses the named bind variables.<\/p>\n\n\n\n