Python and Oracle – Inserting Data

In order to insert data into an Oracle database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are then bound in to the SQL statement when it is executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-except-finally’ block to catch any errors that may arise and close the database connection at the end.

import datetime
import oracledb

# Database connection variable.
connect = None

try:

    # Connect to database.
    connect = oracledb.connect('DemoUN/DemoPW@localhost:1521/freepdb1')

except oracledb.Error as e:

    # Confirm unsuccessful connection and stop program execution.
    print("Database connection unsuccessful.")
    quit()

# Cursor to execute query.
cursor = connect.cursor()

# Query parameters.
firstname = "Fiona"
lastname = "Jones"
title = "Miss"
dob = "1985-05-19"

# SQL to insert data into the person table.
sqlInsert = """ 
     INSERT INTO person (firstname, lastname, title, dob)
     VALUES (:firstname, :lastname, :title, TO_DATE(:dob, 'yyyy-mm-dd'))"""

try:

    # Execute query and commit changes.
    cursor.execute(sqlInsert, [firstname, lastname, title, dob])
    connect.commit()

    # Confirm successful addition of person information.
    print("Person information added successfully.")

except oracledb.Error as e:

    # Confirm error adding person information and stop program execution.
    print("Error adding person information.")
    quit()

finally:

    # Close the cursor and database connection.
    cursor.close()
    connect.close()

The contents of the ‘person’ table now looks as follows.

id	firstname	lastname	title	dob
1	Bob	Smith	Mr	1980-01-20
2	George	Jones	Mr	1997-12-15
3	Fred	Bloggs	Mr	1975-05-07
4	Alan	White	Mr	1989-03-20
5	Fiona	Jones	Miss	1985-05-19

Python and Oracle – Inserting Data

More