Update (crUd) using cx_Oracle

July 2, 2015 bcarter 5 Comments

In this post we’re going to take a look at the U in CRUD: Update.

We use the cx_Oracle driver to update some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

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

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the updates worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

def get_all_rows(label, data_type='people'):
 # Query all rows
 cur = con.cursor()
 if (data_type == 'pets'):
    statement = 'select id, name, owner, type from cx_pets order by owner, id'
 else:
    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()

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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, I 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. (Notice this version adds pet data not included in other sections.)

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 8)'
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'),
        (3, 'Princess', 1, 'snake'),
        (4, 'Polly', 1, 'bird'),
        (5, 'Rollo', 1, 'horse'),
        (6, 'Buster', 1, 'dog'),
        (7, 'Fido', 1, 'cat')]
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()

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

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

(3, 'Princess', 1, 'snake'),

(4, 'Polly', 1, 'bird'),

(5, 'Rollo', 1, 'horse'),

(6, 'Buster', 1, 'dog'),

(7, 'Fido', 1, 'cat')]

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, data_type='people'):
 # Query all rows
 cur = con.cursor()
 if (data_type == 'pets'):
    statement = 'select id, name, owner, type from cx_pets order by owner, id'
 else:
    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')

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

def get_all_rows(label, data_type='people'):

# Query all rows

cur = con.cursor()

if (data_type == 'pets'):

statement = 'select id, name, owner, type from cx_pets order by owner, id'

else:

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 update

We will perform a simple update that modifies a single record in 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 UPDATE statement, changing age to 31 for the record with an id of 1.
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 = 'update cx_people set age = :1 where id = :2'
cur.execute(statement, (31, 1))
con.commit()

cur = con.cursor()

statement = 'update cx_people set age = :1 where id = :2'

cur.execute(statement, (31, 1))

con.commit()

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

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

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

Original Data:

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

New Data:

[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

Extra Fun 1

Update Bob’s notes to ‘I like cats’ .

Your results should be:

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

New Data:
[(1, 'Bob', 31, 'I like cats'), (2, 'Kim', 27, 'I like birds')]

Original Data:

[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:

[(1, 'Bob', 31, 'I like cats'), (2, 'Kim', 27, 'I like birds')]

Answer

cur = con.cursor()
statement = 'update cx_people set notes = :1 where id = :2'
cur.execute(statement, ("I like cats", 1))
con.commit()

cur = con.cursor()

statement = 'update cx_people set notes = :1 where id = :2'

cur.execute(statement, ("I like cats", 1))

con.commit()

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get pet data.

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

Make sure your where clause is specific

In the above example, notice that we used the id column in our where clause. For our data set, id is the primary key. You do not always have to use a primary key, but you should make sure you only update the rows you intend to.

Next let’s look at updating multiple rows. We’ll have Bob give his dog Duke to Kim.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob) and a type of ‘dog’.
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 = 'update cx_pets set owner = :1 where owner = :2 and type = :3'
cur.execute(statement, (2, 1, 'dog'))
con.commit()

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where owner = :2 and type = :3'

cur.execute(statement, (2, 1, 'dog'))

con.commit()

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

Original Data:
[(1, 'Duke', 1, 'dog'), (3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (2, 'Pepe', 2, 'bird')]

New Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), <strong>(1, 'Duke', 2, 'dog')</strong>, (2, 'Pepe', 2, 'bird'), <strong>(6, 'Buster', 2, 'dog')</strong>]

Original Data:

[(1, 'Duke', 1, 'dog'), (3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (2, 'Pepe', 2, 'bird')]

New Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

In our example we only used owner and type, assuming that Bob only had one dog, Duke, as it is in our original data. With the new reset data function we added a second dog Buster. This example is intended to demonstrate what may happen when multiple users are working with the same data set.

In our data, the only unique identifier for cx_pets is id. Bob may have two dogs, or even two dogs named Duke. Make sure if you intend to change a specific row you use a unique identifier.

It also helps to…

Verify the number of affected rows

Now lets give Buster back to Bob. This time we will use the unique id column and we will print out the number of rows affected using Cursor.rowcount.

Get a cursor object from our connection. We will use this cursor to perform our database operations.
Prepare a SQL UPDATE statement, changing owner to 1 (Bob) for the records with an id of 6 (Buster).
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 = 'update cx_pets set owner = :1 where id = :2'
cur.execute(statement, (1, 6))
con.commit()
print('Number of rows updated: ' + str(cur.rowcount))
print(' ')

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where id = :2'

cur.execute(statement, (1, 6))

con.commit()

print('Number of rows updated: ' + str(cur.rowcount))

print(' ')

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

Original Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

Number of rows updated: 1

New Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), <strong>(6, 'Buster', 1, 'dog')</strong>, (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Original Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

Number of rows updated: 1

New Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Cursor.rowcount will show you the number of rows affected for insert, update and delete statements and the number of rows returned in a select statement.

Extra Fun 2

Give all birds to Kim that she doesn’t already have and print the number of affected rows .

Your results should be:

Original Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Number of rows updated: 1

New Data:
[(3, 'Princess', 1, 'snake'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), <strong>(4, 'Polly', 2, 'bird')</strong>]

Original Data:

[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Number of rows updated: 1

New Data:

[(3, 'Princess', 1, 'snake'), (5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (4, 'Polly', 2, 'bird')]

Answer

cur = con.cursor()
statement = 'update cx_pets set owner = :1 where type = :2 and owner != :3'
cur.execute(statement, (2, 'bird', 2))
con.commit()
print('Number of rows updated: ' + str(cur.rowcount))
print(' ')

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where type = :2 and owner != :3'

cur.execute(statement, (2, 'bird', 2))

con.commit()

print('Number of rows updated: ' + str(cur.rowcount))

print(' ')

Some other things you could try

Change multiple column values
Preform an update that changes all rows, if the rowcount is greater than 2, rollback the change

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Uncategorized

Kscope15 The Other Days

June 26, 2015 bcarter Leave a comment

I covered Monday in another post. After that, I got really wrapped up in everything going on and didn’t get any more posts up. Now I’m home and I can try to remember and summarize. Forgive me if I forget anything, it’s a whirlwind of activity.

I’ve been exploring other technologies for the past couple years so some of my Oracle skills got rusty and I missed some cool stuff. Fortunately there were plenty of opportunities to do a little catch up.

I really enjoyed every single session I attended. The speakers were very knowledgeable and eager to answer questions.

Just a quick summary:

Mark Doran taught me some interesting Analytic functions.
Maria Colgan showed how to configure your system to get the most out of Oracle Database In-Memory.
Noel Potugal and Jeremy Ashely made me come home and dig out my Raspberry Pi and Arduino boards.
Craig Shallahamer helped me understand Queuing much better, (and even a little bit about how hamburgers cook.)
Melanie Caffrey explained how to set up EBR for simplified Pl/SQL releases, and even got me thinking about some alternate uses that I need to go experiment with.
Kris Rice demonstrated Oracle REST Data Services and answered everything I could think of just as I was thinking of new questions.
Attending the Women In Technology session opened my eyes to some issues I had not thought of.
In a database developer session, speaking with people in the halls and even discussing developer environments on the bus to Nikki Beach, I learned there are plenty of misconceptions about the available tools, such as git.

I met so many interesting people from all over the world that I’m still trying to place conversations with faces.

To be honest, I was mostly excited to go to Kscope15 because it was the first chance to meet a couple people on our Developer Advocates team. I knew some of the sessions would be interesting but probably not related to what I’m doing. That is the kind of being wrong that I like!

Kscope has become one of my top picks. I need to get some decent material ready for the next one so I can make a contribution. It’s going to be difficult to measure up.

Thanks for a great time and I hope to see everyone again soon.

Learning, Python

Insert (Crud) using cx_Oracle

June 26, 2015 bcarter 1 Comment

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.

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

Helper Function

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

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

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

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

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:

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

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 to 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 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:

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

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

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:

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

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): #&lt;&lt; Modified to use passed in connection
 # Query all rows
 cur = connection.cursor() #&lt;&lt; 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)

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,
 ....

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

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:

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

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:

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

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

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

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:

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

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

Uncategorized

KScope15 Monday

June 23, 2015 bcarter Leave a comment

The first two sessions I attended were from Tom Kyte (ask Tom) titled, All (Well, a Lot) about SQL.

Over the years it seems like I get busy working on this critical project or that show stopper bug. New stuff comes out and I skim the titles and decide “I’ll come back and dig in in a few days.” Somehow I rarely go back and dig in. The new features I pick up mostly come from things others put in the code or internet searches to fix an immediate issue.

Watching Tom go through feature after feature it seems like he was reading straight off of my “I’ll get to it” list. Of course, his explanations of the examples are so good that I came away with a better understanding of the things I still don’t know, but at least now I know more about what I don’t know.

Next, I went to Team Development in the Cloud: Introduction to Oracle Developer Cloud Service, by Brian Fry.

Brian gave a great overview of the tools available in the Development Cloud offering. I’ve been using some of them for about a month but like I mentioned above, there were a few that I plan to get too soon. Now, thanks to Brian, I’ll have a head start in figuring them out. But it looks like there’s enough in there, that my list just grew a bit more.

One thing I would like to see is an option to use Gradle, I’m not a big Maven fan, but given everything in the suite I’m sure I can make do with it.

It is nice to just pop an entire development environment up as a service, I can see this saving me so much time.

I’m really looking forward to the IDE in the cloud feature when it comes out. Maybe I can scale down what I require in a laptop.

For my last session of the day I attended Galo Balda’s presentation, Getting Started with Row Pattern Matching in Oracle 12c.

Another thing on my list. I really need to take some time an work on that list.

Galo had several great examples and use cases that really showed me how MATCH_RECOGNIZE can be much more useful than I previously thought. He set a good pace throughout the presentation; fast enough to keep it interesting but it allowed enough time to absorb each example.

I look forward to more from Galo.

I floated in and out of each community event in the evening. Met quite a few people and had a great time.

Discussion

Open Source in the Database: do DBAs resist?

June 15, 2015 bcarter Leave a comment

Following up on a previous post, I wanted to dig a little deeper into the idea that database administrators have a higher resistance to open source in the database than programmers.

Searching the web, I found very little on DBAs being either for or against open source. I decided to call a couple with whom I’ve worked over the years.

First, I spoke with the most experienced DBA I know; he’s been at it longer than anyone I know. He follows a very strict set of rules he’s developed over the years, but I’ve never seen him just say no to a new idea without a good reason.

I assumed he would be open but cautious towards open source.

Note to self: “Stop assuming.”

I explained what I was doing and asked how he felt about running open source PL/SQL in the database. The first thing he said was, “I download all kinds of PL/SQL code. I trust it, because I can go through it and verify it myself.” He also said, “If I can see the source code, I don’t have a problem with it.” He wants to be able to review anything that gets deployed to ‘his’ database

In his long career, the resistance he’s seen to open source has always come from management and/or the legal department.

He has seen the most resistance when he was working for companies who’s product was proprietary database software or companies with policies dictating that all software must have a license and a support contract. When working at companies where the database was mostly used for internal applications, he’s tried to use open source whenever it was available.

Well, that was encouraging!

Next, I spoke with a person I worked with years ago who fits the stereotypical definition of a DBA. He’s very focused, methodical and always keeps “his” databases running smoothly.

He told me that he never really thought about open source but added: “I often download snippets identified through online searches.” We talked for a bit about different licenses and his opinion was always the same: as long as he can review the code before he installs it, that’s fine. Open source vs. proprietary doesn’t matter so much, assuming the specific license works for the project.

Even more encouraging!

Finally, I spoke with a friend who does a little bit of everything. He’s been a system admin, a DBA, and a developer. He is constantly exploring new technologies and programming languages. We’ve had many discussions about open source, so I know he’s not against open source in any environment. I asked him about the resistance he’s encountered when trying to use open source.

He said, “The company environment/policy would determine whether or not I would run OS in the database. If we’re developing a closed source product, we wouldn’t want to include open source that had license terms that require us to open source our software.”

He told me about a coworker who had been “burned by a restrictive open source license on products that he’d used before.” Basically he had included some open source components in a proprietary application, and had not read the license carefully. That license required them to open source the company’s product, which of course was not an option. He had to remove those components and build his own.

Clearly, if and when you are going to incorporate open source code (or any code you don’t own), you’ve got avoid making assumptions, and perform due diligence on the licensing side of things.

Common threads

While I realize that a sample count of three is not sufficient to draw a solid conclusion, common themes were clear:

When installing code in the database, regardless of origin, DBAs want to be able to review the code. It doesn’t matter if the code is open source, proprietary or internally developed. “Trust but verify” always applies.
Company management tends to be more concerned with the open source label than the people who work with the code.
The actual license terms matter, not the fact that it’s open source or proprietary. There are many different types of each.

Thought experiment

Suppose….

We are looking for a PL/SQL package to solve a problem in our application and we find two that should work. One is open source, the other is proprietary and its code is wrapped.
Both come from reputable companies.
Both licenses allow us to use the software without restrictions.
The open source package has an active user community with frequent commits to the project.
The proprietary software has good documentation and is updated regularly.
Company management will approve either choice.
Both are free.

Which would you recommend to company management to use, and why?

Learning, Python

Select (cRud) using cx_Oracle

June 2, 2015 bcarter Leave a comment

In this post, we’re going to take a look at the R in CRUD: Retrieve.

We will be using the cx_Oracle driver to retrieve some data from the database tables, using the connection object created in the Initial Setup section of the first post in this series.

Simple query

We will preform a simple query that pulls all of the records in no particular order. Here are the steps we’ll follow in the code snippet below.

Get a cursor object from our connection. We will use this cursor to preform our database operations.
Prepare a SQL SELECT statement, specifying the columns desired from the table.
Execute the statement.
Fetch the results into a variable.
Print the results.

# Query all rows
cur = con.cursor()
statement = 'select id, name, age, notes from cx_people'
cur.execute(statement)
res = cur.fetchall()
print (res)

# Query all rows

cur = con.cursor()

statement = 'select id, name, age, notes from cx_people'

cur.execute(statement)

res = cur.fetchall()

print (res)

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

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

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

Extra Fun 1

Modify the statement to order by age. When you’re done the results should be:

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

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

Answer

cur = con.cursor()
statement = 'select id, name, age, notes from cx_people order by age'
cur.execute(statement)
res = cur.fetchall()
print (res)

cur = con.cursor()

statement = 'select id, name, age, notes from cx_people order by age'

cur.execute(statement)

res = cur.fetchall()

print (res)

Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

statement = "select id, name, age, notes from cx_people where name = 'Kim'"

1	statement = "select id, name, age, notes from cx_people where name = 'Kim'"

However, we want to choose the name at run time and store it in a variable called person_name. You could accept the value in as an argument or passed into a function, but we’ll just set a variable to keep it simple.

person_name = 'Kim'

1	person_name = 'Kim'

It is possible to simply concatenate the value into the statement.

statement = "select id, name, age, notes from cx_people where name= '" + person_name + "'"

1	statement = "select id, name, age, notes from cx_people where name= '" + person_name + "'"

This is very dangerous and opens our code to a SQL Injection attack. You can follow that link for more information, but we won’t be going into detail in this series. Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into a SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Positional:

cur.execute('select id, name, age, notes from cx_people where name=:1 and age=:2',
 ('Bob', 35))

cur.execute('select id, name, age, notes from cx_people where name = :2 and age = :1',
 ('Bob', 35))

cur.execute('select id, name, age, notes from cx_people where name=:1 and age=:2',

('Bob', 35))

cur.execute('select id, name, age, notes from cx_people where name = :2 and age = :1',

('Bob', 35))

Notice the :1 and :2 are switched in the two examples. With a positional statement the labels do not matter, it could just as well have been :1 and :something. What matters is the first :variable in the statement will be assigned the first of the provided values- ‘Bob’ and the second – 35.

Named:

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',
 {'name':'Bob', 'age':35})

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',
 {'age':35, 'name':'Bob'})

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',

{'name':'Bob', 'age':35})

cur.execute('select id, name, age, notes from cx_people where name = :name and age = :age',

{'age':35, 'name':'Bob'})

With this method the :name variable will be assigned the value of ‘name’ in the provided key value set.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes. This is handled automatically when the statement is prepared for execution.

Example:

Get a cursor object from our connection. We will use this cursor to preform our database operations.
Assign ‘Kim’ to person_name
Prepare an SQL statement using a bind variable
Using the cursor, execute the query using the prepared statement.
Fetch the results from the cursor into a variable.
Print the results.

# Query for Kim
cur = con.cursor()
person_name = 'Kim'
statement = 'select id, name, age, notes from cx_people where name = :name'
cur.execute(statement, {'name':person_name})
res = cur.fetchall()
print (res)

# Query for Kim

cur = con.cursor()

person_name = 'Kim'

statement = 'select id, name, age, notes from cx_people where name = :name'

cur.execute(statement, {'name':person_name})

res = cur.fetchall()

print (res)

This will return only the data for Kim:

[(2, 'Kim', 27, 'I like birds')]

1	[(2, 'Kim', 27, 'I like birds')]

Extra Fun 2

Modify the statement and variable to get the people older than 30. When you’re done the results should be:

[(1, 'Bob', 35, 'I like dogs')]

1	[(1, 'Bob', 35, 'I like dogs')]

Answer

cur = con.cursor()
person_age = 30
statement = 'select id, name, age, notes from cx_people where age &gt; :age'
cur.execute(statement, {'age':person_age})
res = cur.fetchall()
print (res)

cur = con.cursor()

person_age = 30

statement = 'select id, name, age, notes from cx_people where age > :age'

cur.execute(statement, {'age':person_age})

res = cur.fetchall()

print (res)

In this section we took a look at some basic query functionality. When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try

Join the cx_people and cx_pets table to get the people and their pets
Only retrieve the person’s name and age
Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool. This will help determine if the problem is with the query or the code.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Learning, Python

Basic CRUD operations using cx_Oracle

June 2, 2015 bcarter 5 Comments

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the cx_Oracle driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database. Your project might call for an ORM tool such as SQLAlchemy or Pony. No doubt about it, a good ORM can come in very handy. An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project. It may be overkill for some projects. There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, its hard to be satisfied with a black box 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, you are in a better position to choose the right tool for the right job.

Common Setup

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

Creating the Database Objects

The following can be used to setup the initial tables we’ll use. Please make sure you’re connected to a schema in which you can safely execute commands like these.

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
/

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

Making the Connection

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

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

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

We will include this code section with all examples and use the “con” connection object “con” throughout the series.

Cleanup

To clean up the database when you are finished with the series, you just need to drop the two tables. Please make sure you’re connected to the correct schema where you created the tables.

drop table CX_PETS
/

drop table CX_PEOPLE
/

drop table CX_PETS

drop table CX_PEOPLE

Guide to cx_Oracle CRUD Series

In this series, we will learn how to use cx_Oracle connections to create cursor objects that you can use to run SQL (DDL / DML) and PL/SQL procedures and functions in the Oracle Database.

Initial Setup
Create records
Retrieve records
Update records
Delete records

Discussion, Information

I wasn’t always an advocate for open source.

May 26, 2015 bcarter Leave a comment

I saw this on twitter a few days ago. (Disclosure – @sfonplsql is my boss)

The part that caught my attention was

“easy to say “trust open source” but seems like DB mgrs and devs quite reluctant to put OS code in database.”

This attitude isn’t restricted just to databases, I’ve seen it regarding all types of software. In fact there was a time where I didn’t trust an application simply because it was open source. But then…

My thinking started to change a few years ago

(It’s been a few years. I’m making this as accurate as I can remember.)

I was working on a government contract and we were looking for some bug tracking software. We were told it had to be very inexpensive. I suggested a very popular system that also happened to be open source. The software met all of our needs, it was free at the basic level and there was paid support available if we needed it. We started moving the proposal up the approval chain (did I mention government?). Everyone agreed that it would be a great solution.

When it reached the final approval level, however, it was simply rejected. We asked for the reason and were told “It’s open source, we can’t trust it. If you want to use it, there must be a full code review looking for security issues and unknown bugs.”

I’m rarely surprised by the illogical decisions I see some managers make, but this one caught me completely by surprise. By which I mean: when was the last time we checked for security issues in a commercial package? Just because we pay for it, it doesn’t have any?

I began to research proprietary options but of course the big name applications cost a big name price (sure, some of them were worth the price, but we had a small – and unchangeable – budget already allocated for the project.) The only options that fit in our very limited budget were not-ready-for-prime-time applications by companies that just didn’t seem to be putting much effort into maintaining and enhancing their product. They also had smaller install bases and less frequent updates than the original open source solution.

We chose the two that looked most active and sent them in. We were told that either of those would be fine. No review needed.

I set up a meeting with the approval chain.

I projected the code from the open source application on the board, and clicked through a few pages. I said “Here’s the code from the first app we proposed. You can see that it’s fairly complex and it will take a significant amount of time to do the requested code review. We have some talented programmers we can put on this and I’m sure we can do a good review.”

The person who asked for the review replied: “That’s why open source is a bad idea. It would cost too much to do that.” I pointed out that the ability to review, change and even fork the code is what makes open source so valuable. I pointed out that we know next to nothing about the people behind the approved proprietary solutions, we have no way to review the code, and if they shut down we can either limp along on the current revision or migrate to another application.

At this point, I suggested we evaluate the options not based on open source vs proprietary, but how likely it is that the software would be improved moving forward, and what our options would be if the application was abandoned.

Feature wise, the open source product was far ahead already so we looked into support and update frequency.

One of the proprietary options hadn’t had a release in a couple years and the developers hadn’t responded to a forum post in months. This was a bad sign, and we decided to drop that alternative

The other option had a fairly active forum, but there were a lot of posts from users asking for help getting around problems and configuration issues. The documentation was sparse, and judging by the forum posts, had a few inaccuracies. They seemed to be releasing yearly with mostly bug fixes and some minor features. Upgrades cost a small amount each time. Not terrible overall and it would be something we could live with.

The open source product had a very active community with frequent forum posts, pull requests (made and accepted) and a nice selection of add on modules. The documentation wasn’t great, but what was there seemed accurate. It seemed to be going strong and growing.

A couple weeks later, we were using the open source solution, no code review needed (but it was an option if we wanted.)

What’s the point?

The above is what it took to shift my thinking. I’m not advocating only open source. This is not an open source vs proprietary debate. I try hard to stay away from extremes and absolutes.

The point is, ignore the labels, figure out what matters in your situation and make informed decisions.

Now what?

I’ve seen the above scenario play out a few times over the years and it usually ends the same way.

I would like to dig into why some people seem to instinctively mistrust open source. (It’s not just manager types.)

I will follow this up with a couple situations I see every now and then. I’ll also look at the specific situation of open source in the database.

Help me out.

I would like to turn this into a full discussion, so please leave comments with your experiences from either side of the situation. If you’re currently going through this and would like some help, let me know and I’ll go find us some information.

Information, Python

Using Environment Variables for Database Credentials

May 13, 2015 bcarter 6 Comments

This is not a security discussion

Where to store the database credentials for an app is a long-running discussion that depends on many things. What app server is being used, what OS, how sensitive is the data and of course how much time and money is available to invest (bad reason to skip security).

In my opinion, if a hacker can get far enough into your systems to pull down your files (application code, config files or others) they will be able to find the DB credentials for the app.

It comes down to the point, that the information is accessible to your application somehow. If they breach far enough to get your code or config files, they will be able to find and attack that method.

There are many solutions out there and some are quite good so I’m not going to get into the best way to secure a system.

Using environment variables is relatively simple.

Example

Setting environment variables on Linux

export db_connect=user/pw@db

1	export db_connect=user/pw@db

or on Windows

set db_connect=user/pw@db

1	set db_connect=user/pw@db

Then in Python using the os package

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

import cx_Oracle

import os

connectString = os.getenv('db_connect')

con = cx_Oracle.connect(connectString)

Or in Ruby using ENV

require 'oci8'
connectString = ENV['db_connect'] 
con = cx_Oracle.connect(connectString)

require 'oci8'

connectString = ENV['db_connect']

con = cx_Oracle.connect(connectString)

Setting the variables on the command line like this is temporary for the current session. Consult your OS instructions for a more permanent method if needed.

Why?

There are a couple reasons I prefer this method over hard-coding or config files.

Accidental commits

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

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

con = cx_Oracle.connect('user/pw@db')

1	con = cx_Oracle.connect('user/pw@db')

Which would you rather ‘accidentally’ push to GitHub?
I could expand on this but I think that covers it.

Different environments

They are called environment variables for a reason. On your development machine, you may have a DB running in a VirtualBox instance. Your test servers will probably have their own databases. And only certain people should even know the credentials for the Production server.

Using environment variables, there is no need for maintaining multiple versions of config files or worse yet, source code. Each environment is configured independently of the application.

Quick switching

This would be more of a development or test thing. But, if you need to run your code against multiple different databases you would simply change the environment variable and not any config files or source code. Remember, every time you modify the source code, no matter how small of a change, there’s a chance for a mistype to bring it all down.

Deploying to a Platform as a Service (PaaS)

Most PaaS systems will spin resources up and down as needed, including your database. If your application is using a database provided by the PaaS the process of spinning up the database would include creating secure credentials. To simplify the process the PaaS may simply set the environment variables for the credentials and your application never needs to change.

For a bit of extra security, they may automatically change the credentials whenever the system is restarted. You would be able to log onto your server and get the current credentials if you need them, otherwise, they just work.

Leave me a comment if you have any questions or suggestions.

Learning, Python

Problem loading Python driver

May 13, 2015 bcarter Leave a comment

I ran into a small issue last week when I tried the following command:

pip install cx_Oracle

1	pip install cx_Oracle

This returns “Python Pip install Error: Unable to find vcvarsall.bat.” This is not a problem with cx_Oracle, it happens with any pip install that tries to compile.

If you want to skip the rant below and just get the driver loaded, the immediate solution is to download the binary installer and move on. That worked fast and easy.

<Rant>
Before I found the simple solution above, my search found recommendations to install the VS compiler for Python 2.7, upgrade pip, and even to load Visual Studio Express.

BTW, the VS compiler for Python 2.7 took a loooooooong time to install. An unacceptably long time. VS Express added a few other apps to my system that I didn’t want. As far as I could tell in the installer, there was no option to not load them.

In the process of trying these different solutions, none of which worked, I wound up with a lot of extra software that I didn’t want.

Now I am a newb to Python, but not to Windows. I went in and tried to remove the bloatware that came with VS express and the other software, such as multiple versions of VS compiler. As I expected, when I installed VS it modified some existing apps somewhere. So when I removed VS it broke other apps that had been working fine before all of this. Thankfully, I’ve done this enough times before, so I had a system restore point ready.

System restored, moving on.

The problem, as far as I can tell, is that the latest Python tools do not play well with the latest Microsoft tools when you are on a 64 bit OS.
</Rant>

Long term, as I move on to building real Python apps, I found a few workarounds that claim to solve this problem for windows that I could try. But first, I plan to switch over to my Linux install and work there. At this point I’m willing to bet it won’t be such a mess.

Helper Function

Resetting the data

Boilerplate template

Simple update

Extra Fun 1

Reset the data

Boilerplate change

Make sure your where clause is specific

Verify the number of affected rows

Extra Fun 2

Some other things you could try

Share this:

Share this:

Helper Function

Resetting the data

Boilerplate template

Simple insert

What is a transaction?

Extra Fun 1 & 2

Reset the data

Using Identity Columns

Returning data after an insert

Extra Fun 3

Reset the data

Insert more than 1 row

Some things you could try

Share this:

Share this:

Common threads

Thought experiment

Share this:

Simple query

Extra Fun 1

Select specific rows

Positional:

Named:

Example:

Extra Fun 2

Some things you could try

Series sections

Share this:

A good ORM will handle most of your needs.

Why learn to use the driver directly?

Common Setup

Creating the Database Objects

Making the Connection

Cleanup

Guide to cx_Oracle CRUD Series

Share this:

My thinking started to change a few years ago

What’s the point?

Now what?

Help me out.

Share this:

This is not a security discussion

Using environment variables is relatively simple.

Example

Why?

Accidental commits

Different environments

Quick switching

Deploying to a Platform as a Service (PaaS)

Share this:

Share this: