Over a million developers have joined DZone.

Basic CRUD Operations Using cx_Oracle, Part 3: Create

In this post, we're going to take a look at the C in CRUD: Create. We will be using the cx_Oracle driver to create some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

In this post, we're going to take a look at the C in CRUDCreate. We will be using the cx_Oracle driver to create some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

DISCLAIMER: PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

We will be using a helper function: get_all_rows(). This is a select statement used to verify that the inserts worked. The select functionality is covered in the R part of this series, so we won't go into the details here.

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()

Add this function to the top of your file.

Resetting the Data

To keep the examples clean and precise, we will reset the data at times.

Create a new file called reset_data.py with the following code and then run it whenever you would like 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 Coulmn
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 Coulmn
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()

Boilerplate Template

The template we will be using is:

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')

For each exercise, replace the "# Your code here" line with your code.

Simple Insert

We will perform a simple insert that adds a single record into the cx_people table. These are the steps performed in the code snippet below.

  • Get a cursor object from our connection. We will use this cursor to perform our database operations.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • Execute the statement using bind variables. (See the R part of this series for an explanation of bind variables.)
  • Commit the transaction.
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:

Image title

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')]

What is a Transaction?

You'll notice in the bullet points above, I said commit the transaction.

When you make execute Data Manipulation Language or DML statements, such as the insert I use in this post, those changes are only visible to your current connection or 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 it "permanent" in the database, and available for everyone else to see (and may possibly change in a future transaction).

Extra Fun 1 & 2

1.  Insert more than 1 row .

Using data for 'Rob', 37, 'I like snakes' and 'Cheryl', 41, 'I like monkey', your results should be:

Image title

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')]

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 cover that below.

2.  Verify that a second connection cannot see your changes till after the commit.

Using data for 'Suzy', 31, 'I like rabbits' and assuming that you did the previous exercise your results should be:

Image title

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.

Answer

I modified the helper function and the get_all_rows calls in the template code to make it a little easier.  If you chose to do this, please revert the template code 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)

Reset the Data

Now is a good time to run reset_data.py.

Using Identity Columns

You may 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 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 here (pdf).

Returning Data After an Insert

Sometimes we need to perform additional operations after an insert using data generated by the database, such as the identity column above. For example, let's add a person and a pet for them. We could run an insert then select the value back using the name. But if the name is not unique, we'll have a problem. This is where the RETURNING clause is helpful.

We will perform an insert that adds a single record into the cx_people table. Then using the returned id, we will add a pet. These are the steps performed in the code snippet below.

  • Get a cursor object from our connection. We will use this cursor to perform our database operations.
  • Create a variable associated with the cursor to receive the returned value. Set its type to cx_Oracle.NUMBER.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the people data.
  • Execute the statement using bind variables returning the id into new_id.
  • Get the value from new_id and assign it to sandy_id.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the pet data.
  • Execute the statement using bind variables including the sandy_id value.
  • Commit the transaction.
  • Print the sandy_id value. (It's a float so we use .rstrip('.0') to make it pretty)
  • Prepare a SQL statement using a bind variable.
  • Execute the statement using sandy_id for the bind variable.
  • Fetch the results from the cursor into a variable.
  • Print the results with a little decoration text.
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(' ')

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

Image title

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 .

Extra Fun 3

3.  Insert Sandy again but return her id and name.

Your results should be:

Image title

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 our new (4, ‘Sandy’...) but the returned id is 4.  It should return the new id each time you run it.

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))

Reset the data

Now is a good time to run reset_data.py.

Insert More Than 1 Row

As mentioned above, when you want to insert multiple rows, running multiple insert statements is inefficient and makes multiple trips to the database, so instead we will use executemany.

We will perform an insert that adds two records into the cx_people table. These are the steps performed in the code snippet below.

  • Create an array populated with our data
  • Get a cursor object from our connection. We will use this cursor to perform our database operations.
  • Set the cursor's bindarraysize to the number of records in our array.
  • Set the cursor's setinputsizes.  This tells the cursor what to expect from our data items. The first and third items are strings so we define the max length, the second is an int so we just use int. This allows the cx_Oracle driver to pre-define the memory needed.
  • Prepare a SQL INSERT statement, specifying the table and columns to insert the data.
  • Execute the statement using bind variables.
  • Commit the transaction.
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()

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

Image title

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 may 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 Things You Could Try

  • 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().

Series Sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
oracle ,python ,scaling mysql ,crud ,database

Published at DZone with permission of Blaine Carter. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}