Subscribe

Share

Application Development

Perform Basic CRUD Operations with cx_Oracle, Part 2

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

By Blaine Carter

May/June 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, and delete) operations in Python with the cx_Oracle driver. Part 1 of this series included setup information and examples for create: the C in CRUD. This article, part 2, continues with information and examples on how to perform operations for the R in CRUD—retrieve. For setup information for the complete article series—including links and instructions for downloading the cx_Oracle driver and setting up the sample data—refer to “Perform Basic CRUD Operations Using CX_ORACLE, Part 1.”

Run a Simple Query

With the cx_Oracle driver installed and the setup complete, you will perform a simple query that pulls all the records in no particular order.

First, create a select.py file that includes the following code:

import cx_Oracle
import os
connectString = os.getenv('DB_CONNECT') 
# The environment variable for the connect string:
DB_CONNECT=user/password@database
con = cx_Oracle.connect(connectString)

# Your code here

For the different examples in this article, replace

# Your code here

with the specified code. To run this first simple query, replace

# Your code here

with the following:

# 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 select.py in my Python session, I see

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

Here’s what the code does:

  1. Gets a cursor object from your connection. You will use this cursor to perform your database operations.
  2. Prepares a SQL SELECT statement specifying the columns wanted from the table.
  3. Executes the statement.
  4. Fetches the results into a variable.
  5. Prints the results.

Deeper Dive

Now modify the SELECT statement to order the results by age. When you’re done, the results should be

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

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

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 I want to see only the data for Kim. I want, therefore, to restrict the rows returned by SELECT. This is done with a WHERE clause, and there are several ways to do it.

I could put the WHERE clause in the statement, and it would work:

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

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

It is possible to simply concatenate the value into the statement, but this is very dangerous and opens the code to a SQL injection attack. I won’t be going into detail on SQL injection in this series, but you should, generally, not 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 of different options:

Positional. You can use a positional statement to pass in the values:

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

Note that the :1 and the :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 that the first variable in the statement will be assigned the first of the provided values ('Bob') and the second variable in the statement will be assigned the second value (35).

Named. You can use a statement that passes in the values by name:

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.

Note, in both examples, that you do not wrap the bind variable for :name with quotes. This is handled automatically when the statement is prepared for execution. Here’s an example that passes in values by name:

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

Here’s what the code does:

  1. Gets a cursor object from the connection. You will use this cursor to perform your database operations.
  2. Assigns 'Kim' to person_name.
  3. Prepares a SQL statement, using a bind variable.
  4. With the cursor, executes the query by using the prepared statement.
  5. Fetches the results from the cursor into a variable.
  6. Prints the results.

Another Deeper Dive

Now modify the previous statement and variable to retrieve people older than 30. When you’re done, the results should be

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

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

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)

Some Other Things You Could Try

Here are some other things to try for retrieving the sample data:

  • Join the cx_people and cx_pets tables to retrieve people and their pets.
  • Retrieve only a person’s name and age.
  • Change the result order to display the results 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 you determine whether the problem is with the query or the code.

The next article in this series will cover the U in CRUD: update.

Next Steps

DOWNLOAD cx_Oracle.

GET this article’s code examples from GitHub.

READ Part 1 and Part 3 of this article series.

Photography by Christina Kirschnerova, Unsplash