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:
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:
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:
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.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.