Perform Basic CRUD Operations Using CX_ORACLE, Part 1

Here’s how to use Python for CRUD operations in Oracle Database.

By Blaine Carter

February 15, 2018

Python is a powerful open source language, and the CX_ORACLE driver gives your Python application access to the full power of Oracle Database.

In this article series, I’m going to take a look at how to perform CRUD (create, retrieve, update, delete) operations in Python using the cx_Oracle driver. This initial article will include setup instructions for the series as well as coverage of how to perform operations for the C in CRUD: create.

Tool Up or Drive On

A good object-relational mapping (ORM) tool can handle many of the repetitive processes for writing code to interface with a database. Your Python project might call for an ORM tool such as SQLAlchemy or Pony, for example. An ORM tool will typically have a function for passing raw SQL, if needed, so you might not need to go straight to the cx_Oracle Python driver.

But an ORM tool brings its own, different complexity to a project, so it might be overkill for some projects. There are also times when an ORM tool might not be able to help with a specific task, or its application to your requirements becomes so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black-box development approach. You want to know more about how your tools work and you want to have options, just in case. Martin Fowler said, “Mapping to a relational database involves lots of repetitive, boiler-plate code. A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations in Python, you are in a better position to choose the right repetition-busting tool for the right job.

Common Setup and Cleanup

All examples in this series will use the same database objects and connection information.

Note: Always make sure you’re connected to a schema in which you can safely execute CRUD commands.

Create the database objects. Use the following code to set up the initial database tables for this series.

CREATE TABLE cx_people (
 id NUMBER GENERATED BY DEFAULT AS identity,
 name VARCHAR2(20),
 age NUMBER,
 notes VARCHAR2(100)
)
/

ALTER TABLE CX_PEOPLE
ADD CONSTRAINT PK_CX_PEOPLE PRIMARY KEY ("ID")
/

CREATE TABLE CX_PETS (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 name VARCHAR2(20),
 owner NUMBER,
 type VARCHAR2(100)
)
/

ALTER TABLE CX_PETS ADD CONSTRAINT PK_CX_PETS PRIMARY KEY ("ID")
/

ALTER TABLE CX_PETS ADD CONSTRAINT FK_CX_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "CX_PEOPLE" ("ID")
/

INSERT INTO cx_people (name, age, notes)
 VALUES ('Bob', 35, 'I like dogs')
/

INSERT INTO cx_people (name, age, notes)
 VALUES ('Kim', 27, 'I like birds')
/

INSERT INTO cx_pets (name, owner, type)
 VALUES ('Duke', 1, 'dog')
/

INSERT INTO cx_pets (name, owner, type)
 VALUES ('Pepe', 2, 'bird')
/

COMMIT
/

Make the connection. Use the following steps, illustrated in the code shown below the steps, to set up the connection to Oracle Database.

1. Import the cx_Oracle driver.
2. Import the os module used to read the environment variable.
3. Get the connection string from the environment variable.
4. Create the connection object.

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)

Include this connection code section in all examples and use the con connection object throughout the series. In this article, the connection code will be part of the insert.py file, which is described in the “Create the Boilerplate Template” section.

Clean up the database. To clean up the database when you are finished with this article series, use the following commands to drop the two tables.

Note: Make sure you’re connected to the schema where you created the tables for this series.

drop table CX_PETS
/

drop table CX_PEOPLE
/

In this section, I’m going to take a look at the C in CRUD.

I will be using the cx_Oracle driver to create some data in the database tables, using the connection object created in the earlier “Common Setup and Cleanup” section.

Note: Review all the example code first and run it only if you are sure it will not cause any problems with your system.

Use a helper function. The example code in this article uses a SELECT statement—the get_all_rows()helper function (shown below)—to verify that the database inserts worked. I will cover this function in the “R” (retrieve) part of this series.

def get_all_rows(label):
 # Query all rows
 cur = con.cursor()
 statement = 'select id, name, age, notes
                from cx_people order by id'
 cur.execute(statement)
 res = cur.fetchall()
 print(label + ': ')
 print (res)
 print(' ')
 cur.close()

You’ll add this function to the top of your insert.py file (which is described in the “Create the Boilerplate Template” section).

Reset the data. To keep the examples clean and precise, I suggest that you reset the data at times.

To reset the data, create a new file called reset_data.py, include the following code, and then run it whenever you want to reset the data.

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Delete rows
statement = 'delete from cx_pets'
cur.execute(statement)

# Reset Identity Column
statement = 'alter table cx_pets modify id generated BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Delete rows
statement = 'delete from cx_people'
cur.execute(statement)

# Reset Identity Column
statement = 'alter table cx_people modify id generated BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Insert default rows
rows = [(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_people(id, name, age, notes) values (:1, :2, :3, :4)", rows)
con.commit()

# Insert default rows
rows = [(1, 'Duke', 1, 'dog'), (2, 'Pepe', 2, 'bird')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_pets (id, name, owner, type) values (:1, :2, :3, :4)", rows)
con.commit()

cur.close()

Create the boilerplate template. Create an insert.py template that includes the following:

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)

def get_all_rows(label):
 # Query all rows
 cur = con.cursor()
 statement = 'select id, name, age, notes
                from cx_people order by id'
 cur.execute(statement)
 res = cur.fetchall()
 print(label + ': ')
 print (res)
 print(' ')
 cur.close()

get_all_rows('Original Data')

# Your code here

get_all_rows('New Data')

The template includes the connection and helper function code described earlier. For each exercise in this article, you will replace the # Your code here line in the insert.py template with your code.

Do a simple insert. Now perform a simple insert that adds a single record to the cx_people table. Replace the

# Your code here

line in insert.py with the following code and run it in Python:

cur = con.cursor()
statement = 'insert into cx_people(name, age, notes)
                         values (:2, :3, :4)'
cur.execute(statement, ('Sandy', 31, 'I like horses'))
con.commit()

When I run this code in my Python session, I see the following:

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds'),
(3, 'Sandy', 31, 'I like horses')]

Here’s what the code does:

1. Gets a cursor object from the connection. This cursor will perform database operations.
2. Prepares a SQL INSERT statement, specifying the table and columns in which to insert the data.
3. Executes the statement using bind variables. (The “R” part of this series will include an explanation of bind variables.)
4. Commits the transaction.

What is a transaction? You’ll notice that in the explanation of the code above, I said the code commits the transaction.

When you execute Data Manipulation Language (DML) statements, such as the INSERT I use in this article, those changes are visible only to your current connection or database session.

Those changes will not be visible to other sessions (even another session connected to the same schema in which the changes were made) until you commit your changes. That step makes the transaction “permanent” in the database and available for everyone else to see (and possibly change in a future transaction).

Deeper Dives

Now edit the previous code block to try to insert more than one row, adding data for 'Rob', 37, 'I like snakes' and 'Cheryl', 41, 'I like monkeys'. Your results should be the following:

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27,
'I like birds'), (3, 'Sandy', 31, 'I like horses')]

New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes')]

What does the successful code look like? Here’s the answer:

cur = con.cursor()
statement = 'insert into cx_people(name, age, notes)
                         values (:2, :3, :4)'
cur.execute(statement, ('Cheryl', 41, 'I like monkeys'))
cur.execute(statement, ('Rob', 37, 'I like snakes'))
con.commit()

This method will work for inserting many rows at once, but there is a better way. I’ll cover that in the “Insert More Than One Row” section.

Now verify that a second connection cannot see your changes until after the commit.

Using the data

'Suzy', 31, 'I like rabbits'

and assuming that you did the previous exercise, your results should be the following:

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes')]

New connection after insert:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes')]

Same connection:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes'), (6, 'Suzy', 31, 'I like rabbits')]

New connection after commit:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes'), (6, 'Suzy', 31, 'I like rabbits')]

New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Cheryl', 41, 'I like monkeys'), (5, 'Rob', 37,
'I like snakes'), (6, 'Suzy', 31, 'I like rabbits')]

Notice that after the insert, the connection that made the insert can see Suzy, but the second connection can’t. After the commit, both connections see Suzy.

What does the successful code look like? Here’s the answer:

I modified the helper function and the get_all_rows calls in the template code to make it a little easier. If you choose to make these modifications, please revert the template code back to the original version after this exercise.

def get_all_rows(label, connection): #<< Modified to use passed in connection
 # Query all rows
 cur = connection.cursor() #<< cursor from passed in connection
 statement = 'select id, name, age, notes from cx_people order by id'
 cur.execute(statement)
 res = cur.fetchall()
 print(label + ': ')
 print (res)
 print(' ')
 cur.close()

get_all_rows('Original Data', con)

# Make a second connection
con2 = cx_Oracle.connect(connectString)

cur = con.cursor()
statement = 'insert into cx_people(name, age, notes) values (:2, :3, :4)'
cur.execute(statement, ('Suzy', 31, 'I like rabbits'))

get_all_rows('New connection after insert', con2)
get_all_rows('Same connection', con)

con.commit()

get_all_rows('New connection after commit', con2)

get_all_rows('New Data', con)

Now is a good time to run reset_data.py.

Using Identity Columns

You might have noticed that the id column is not passed in, but is automatically set sequentially. Prior to Oracle Database 12c, this was accomplished using a sequence and a trigger.

In Oracle Database 12c, this can be accomplished by using an identity column.

CREATE TABLE cx_people (
 id NUMBER GENERATED BY DEFAULT AS identity,
 ....

You can find more information on identity columns in this document.

Returning data after an insert. Sometimes developers need to perform additional operations after an insert using data generated by the database, such as the identity column. Let’s explore that by adding a person and adding a pet for each person.

You could run an insert and then select the value by using the name. But if the name is not unique, you’ll have a problem. This is where the RETURNING clause is helpful.

Let’s perform an insert that adds a single record to the cx_people table. Then using the returned id column, let’s add a pet. Replace the

# Your code here

line in insert.py with the following code and run it in Python:

cur = con.cursor()

new_id = cur.var(cx_Oracle.NUMBER)

statement = 'insert into cx_people(name, age, notes)
               values (:1, :2, :3) returning id into :4'
cur.execute(statement, ('Sandy', 31, 'I like horses',
new_id))

sandy_id = new_id.getvalue()

pet_statement = 'insert into cx_pets (name, owner, type)
                             values (:1, :2, :3)'
cur.execute(pet_statement, ('Big Red', sandy_id, 'horse'))

con.commit()

print('Our new value is: ' + str(sandy_id).rstrip('.0'))

sandy_pet_statement = 'select name, owner, type
                        from cx_pets
                       where owner = :owner'
cur.execute(sandy_pet_statement, {'owner': sandy_id})
res = cur.fetchall()
print('Sandy\'s pets: ')
print (res)
print(' ')

Here’s what the code does:

• Gets a cursor object from the connection. This cursor will perform database operations.
• Creates a variable associated with the cursor to receive the returned value. Its type is set to cx_Oracle.NUMBER.
• Prepares a SQL INSERT statement, specifying the table and columns in which to insert the people data.
• Executes the statement using bind variables, returning the id value into new_id.
• Gets the value from new_id and assigns it to sandy_id.
• Prepares a SQL INSERT statement, specifying the table and columns in which to insert the pet data.
• Executes the statement using bind variables, including the sandy_id value.
• Commits the transaction.
• Prints the sandy_id value. (It’s a float so it uses .rstrip('.0') to make it pretty.)
• Prepares a SQL statement using a bind variable.
• Executes the statement using sandy_id for the bind variable.
• Fetches the results from the cursor into a variable.
• Prints the results with a little decoration text.

When I run this code in my Python session, I see this:

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27,
'I like birds')]

Our new value is: 3
Sandy's pets:
[('Big Red', 3, 'horse')]

New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27,
'I like birds' (3, 'Sandy', 31, 'I like horses')]

Notice the new value, the owner in Sandy’s pets, and Sandy’s id in the new data are all 3.

Another Deeper Dive

Now edit the previous code block to try to insert Sandy again, but return her id and name.

Your results should be

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses')]

Our new id is: 4 name: Sandy
New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like
birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Sandy', 31, 'I like horses')]

Notice that 3, 'Sandy'… is still there along with the new 4, 'Sandy'…, but the returned id is now 4. It should return the new id each time you run it.

What does the successful code look like? Here’s the answer:

cur = con.cursor()

new_id = cur.var(cx_Oracle.NUMBER)
new_name = cur.var(cx_Oracle.STRING)

statement = 'insert into cx_people(name, age, notes)
              values (:1, :2, :3)
              returning id, name into :4, :5'
cur.execute(statement, ('Sandy', 31, 'I like horses',
                         new_id, new_name))

sandy_id = new_id.getvalue()
sandy_name = new_name.getvalue()

con.commit()

print('Our new id is: ' + str(sandy_id).rstrip('.0') +
'name: ' + str(sandy_name))

Now is a good time to run reset_data.py.

Insert More Than One Row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database. Instead of using multiple inserts, let’s use executemany.

You will perform an insert that adds two records into the cx_people table. Replace the

# Your code here

line in insert.py with the following code and run it in Python:

rows = [('Sandy', 31, 'I like horses'), ('Suzy', 29,
'I like rabbits')]
cur = con.cursor()
cur.bindarraysize = 2
cur.setinputsizes(20, int, 100)
statement = 'insert into cx_people(name, age, notes)
                         values (:1, :2, :3)'
cur.executemany(statement, rows)
con.commit()

Here’s what the code does:

• Creates an array populated with the data.
• Gets a cursor object from the connection. This cursor will perform the database operations.
• Sets the cursor’s bindarraysize to the number of records in the array.
• Sets the cursor’s setinputsizes. This tells the cursor what to expect from the data items. The first and third items are strings, so the values define the maximum length. The second is an integer, so the value is int. This allows the cx_Oracle driver to predefine the memory needed.
• Prepares a SQL INSERT statement, specifying the table and columns in which to insert the data.
• Executes the statement using bind variables.
• Commits the transaction.

When I run this code in my Python session, I see this:

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27,
'I like birds')]

New Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27,
'I like birds'), (3, 'Sandy', 31, 'I like horses'),
(4, 'Suzy', 29, 'I like rabbits')]

There might be an easy way to use the returning option with executemany, but after searching the web for a while, the methods I found were complicated enough that I won’t go over them here. It seems to be easier to just use a PL/SQL function, which is also a topic for another time.

Some Other Things You Could Try

Here are some other things to try using executemany.

• Loop through an array of people and insert each one returning its id. Using that id, add multiple pets with executemany.
• Create a large array of people. Time the difference between looping through single inserts and using executemany.

The next article in this series will cover the R in CRUD: retrieve.

Next Steps

DOWNLOAD cx_Oracle.

GET this article's code examples from Github.

READ Part 2 and Part 3 of this article series.