While Python is an interpreted language, it is a very popular programming language. You may ask yourself why it is so popular? The consensus answer points to several factors. For example, Python is a robust high-level programming language that lets you:
You find Python developers throughout the IT enterprise. Development, release engineering, IT operations, and support teams all use Python to solve problems. Business intelligence and data scientists use Python because it’s easy to use.
Developers don’t generally write end-user applications in Python. They mostly use Python as a scripting language. Python provides a simple syntax that lets developers get complex things done quickly. Python also provides you with a vast set of libraries that you can leverage to solve problems. Those libraries often simplify how you analyze complex data or automate repetitive tasks.
This article explains how to use the Python programming language with the Oracle database. It shows you how to install and use the cx_Oracle library to query data. Part 2 will cover how you write queries with multiple bind variables that use sequences, dynamic queries that use dictionaries, queries formatting dynamic column sets, and insert, update, and delete statements. Part 3 will cover how the cx_Oracle libraries let you call and use PL/SQL stored functions and procedures. Part 4 will cover how to work with advanced data types--collections and large objects--in the Oracle database.
The article has two parts:
The audience for this article should know the basics of writing a Python program. If you’re completely new to Python, you may want to get a copy of Eric Matthes’ Python Crash Course: A Hands-On, Project-Based Introduction to Programming. More experienced developers with shell scripting backgrounds may prefer Al Sweigart’s Automate the Boring Stuff with Python.
This article uses Python 2.7, which appears to be the primary commercial version of Python in most organizations. At least, it’s what most vendors ship with Linux distros. It also happens to be the Python distro on Fedora Linux.
The first step requires that you test the current version of Python on your operating system (OS). For the purpose of this paper, you use the student user account. The student user is in the sudoer list, which gives the account super user privileges.
You can find the Python version by opening a Terminal session and running the following command:
[student@localhost ~]$ python -V
You can download the current version of the cx_Oracle library at the Python Software Foundation’s web site. At the time of writing, the current version of the cx_Oracle is the cx_Oracle 5.2.1 version. The cx_Oracle library is available for download as a Red Hat Package Manager (RPM) module.
You download the cx_Oracle-5.2.1-11g-py26-1.x86_64.rpm to the /tmp directory or to a sudoer-enabled user’s downloads directory. Let’s assume you download the RPM into the /tmp directory. After you download the RPM, you can install it with the yum utility with this syntax:
yum install -y /tmp/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm
The cx_Oracle library depends on the Oracle Client software, which may or may not be installed. Python installs without a problem but Python raises a runtime error when you try to use the cx_Oracle library without installing the prerequisite Oracle Client software. You can check whether the Oracle Client is installed by using the following syntax at the Linux command-line:
rpm –qa oracle-instantclient11.2-basic
If the oracle-instantclient11.2-basic library isn’t installed, the command returns nothing. If the oracle-instantclient11.2-basic library is installed it returns the following:
Assuming you don’t have the Oracle Client software installed, you should download it from Oracle’s Instant Client Downloads web page. After you download the RPM, you install the Oracle 11g Release 2 Client software with the following syntax:
yum install -y /tmp/oracle-instantclient11.2-basic-126.96.36.199.0-1.x86_64.rpm
You now have the necessary software installed and configured to run and test Python programs that work with the Oracle database. Python uses a standard path configuration to look for Python modules or libraries. You can see that set of path values by connecting to the Python IDLE environment, which is the runtime environment. The IDLE environment is very much like the SQL*Plus environment.
You connect to the Python IDLE environment by typing the following:
It opens the Python IDLE environment. It should display the following:
Python 2.7.5 (default, Apr 10 2015, 08:09:05) [GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>>
You import the sys library and then you can print the path elements with the following command:
>>> import sys >>> print sys.path
It should print the following for Python 2.7 in Fedora Linux:
['', '/usr/lib64/python27.zip', '/usr/lib64/python2.7', '/usr/lib64/python2.7/plat-linux2', '/usr/lib64/python2.7/lib-tk', '/usr/lib64/python2.7/lib-old', '/usr/lib64/python2.7/lib-dynload', '/usr/lib64/python2.7/site-packages', '/usr/lib64/python2.7/site-packages/gtk-2.0', '/usr/lib/python2.7/site-packages']
You can now test whether the Python environment works by typing the following commands in the IDLE environment:
>>> import cx_Oracle>>> db = cx_Oracle.connect("student/student@xe")>>> print db.version
The other two sections require you to test components inside Python files. That means you need to supplement the default Python path variable. You do that by adding values to the Python environment variable.
The following adds the /home/student/Code/python directory to the Python path variable:
export set PYTHONPATH=/home/student/Code/python
Next, we create a connection.py file, which holds the following:
# Import the Oracle library. import cx_Oracle try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # Print a message. print "Connected to the Oracle " + db.version + " database." except cx_Oracle.DatabaseError, e: error, = e.args print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message finally # Close connection. db.close()
The import statement adds the cx_Oracle library to the program scope. The cx_Oracle library’s connect function takes either the user name and password, or the user name, password, and TNS alias.
The except block differs from what you typically see. The code value maps to the SQLCODE value and the message value maps to the SQLERRM value.
You can test the connection.py file as follows in the /home/student/Code/python directory:
It prints the following:
Connected to the Oracle 188.8.131.52.0 database.
This section has shown you how to setup the cx_Oracle library, and how you can test the cx_Oracle library with Python programs.
The prior section shows you how to connect to an Oracle instance and how to verify the driver version of the cx_Oracle library. Like most ODBC and JDBC libraries, Python first creates a connection. Then, you need to create a cursor inside the connection.
The basicCursor.py program creates a connection and a cursor. The cursor holds a static SQL SELECT statement. The SELECT statement queries a string literal from the pseudo dual table.
# Import the Oracle library. import sys import cx_Oracle try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # Create a cursor. cursor = db.cursor() # Execute a query. cursor.execute("SELECT 'Hello world!' FROM dual") # Read the contents of the cursor. for row in cursor: print (row) except cx_Oracle.DatabaseError, e: error, = e.args print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message finally: # Close cursor and connection. cursor.close() db.close()
The connect function assigns a database connection to the local db variable. The cursor function returns a cursor and assigns it to the local cursor variable. The execute function dispatches the query to Oracle’s SQL*Plus and returns the result set into a row element of the local cursor variable. The for-each loop reads the row element from the cursor variable and prints one row at a time. Since the cursor only returns a string literal, there’s only one row to return.
You test the program with this syntax:
The next basicTable.py program queries the item table. The item table holds a number of rows of data. The code returns each row inside a set of parentheses. This is a sequence of strings in Python, and the number of strings is set by the number of columns retrieved by the SELECT-list.
# Import the Oracle library. import cx_Oracle try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # Create a cursor. cursor = db.cursor() # Execute a query. cursor.execute("SELECT item_title " + ", item_rating " + "FROM item " + "WHERE item_type = " " (SELECT common_lookup_id " + " FROM common_lookup " + " WHERE common_lookup_type = 'DVD_WIDE_SCREEN')") # Read the contents of the cursor. for row in cursor: print (row, row) except cx_Oracle.DatabaseError, e: error, = e.args print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message finally: # Close cursor and connection. cursor.close() db.close()
The SQL query is split across several lines by using the + operator. The + operator concatenates strings, and it lets you format a long query statement. The range for loop returns tuples from the cursor. The tuples are determined by the SELECT-list of the query.
The query returns the following type of results:
('Casino Royale', 'PG-13') ... ('Star Wars - Episode I', 'PG') ('Star Wars - Episode II', 'PG') ('Star Wars - Episode III', 'PG-13') ('Star Wars - Episode IV', 'PG') ('Star Wars - Episode V', 'PG') ('Star Wars - Episode VI', 'PG')
At this point, you know how to work with static queries. The next example shows you how to work with dynamic queries. The difference between a static and dynamic query is that an element of the string changes.
You have two options for creating dynamic strings. The first lets you glue a string inside a query. Gluing is descriptive but the process is concatenating. The last query uses the + symbol to glue or concatenate strings together into a complete SQL statement. The second lets you embed one or more bind variables in a string. As a rule, you should use bind variables because they avoid SQL injection risks.
The following is the basicDynamicTable.py script
# Import the Oracle library. import cx_Oracle sRate = 'PG-13' try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # Define a dynamic statment. stmt = "SELECT item_title, item_rating FROM item WHERE item_rating = :rating" # Create a cursor. cursor = db.cursor() # Execute a statement with a bind variable. cursor.execute(stmt, rating = sRate) # Read the contents of the cursor. for row in cursor: print (row, row) except cx_Oracle.DatabaseError, e: error, = e.args print >> sys.stderr, "Oracle-Error-Code:", error.code print >> sys.stderr, "Oracle-Error-Message:", error.message finally: # Close cursor and connection. cursor.close() db.close()
You need to assign a dynamic SQL statement to a local string variable. The :rating bind variable is preceded with a colon (:). The execute function takes a string variable with the dynamic SQL statement. Then, you provide a name and value pair. The name needs to match the bind variable in the dynamic SQL statement. The value needs to map to a local Python variable.
The query should return a full list from the item table for the two item_title and item_rating columns:
('Casino Royale', 'PG-13') ... ('Harry Potter and the Goblet of Fire', 'PG-13') ('Harry Potter and the Order of the Phoenix', 'PG-13') ('The Lord of the Rings - Fellowship of the Ring', 'PG-13') ('The Lord of the Rings - Two Towers', 'PG-13') ('The Lord of the Rings - The Return of the King', 'PG-13') ('The Lord of the Rings - The Return of the King', 'PG-13')
This article should have shown you how to effectively work static and dynamic queries. You can find the scripts for this part on the github.com server. You would continue reading in Part 2.