MacLochlainns Weblog

It was a forced upgrade to run TurboTax. The upgrade was simple because I work on a Mac Pro 2012 (with 64 GB of memory and 12 TB of storage. As you can tell from Apple’s support article, you must upgrade the video card.

I bought the SAPPHIRE Radeon PULSE RX 580 8 GB GDDR5 but when I went to install it after upgrading to macOS 10.13 (High Sierra), there was a catch. The original mother board supports a six socket power supply, which Apple failed to mention in their support article. That meant that I had to order a StarTech.com PCI Express 6 pin to 8 pin Power Adapter Cable.

While I still prefer Apple, I don’t appreciate policy of removing devices from their supported parts list. Does Tim really need to sell new Apple devices that badly that he wants to obsolete hardware when the audience can’t support it by disallowing 3rd party vendors from purchasing parts? I know it’s six years old machine but it also isn’t an entry level machine because it costs more than $6,000 with the memory.

While Python is an interpreted language, Python is a very popular programming language. You may ask yourself why it is so popular? The consensus answers to why it’s so popular points to several factors. For example, Python is a robust high-level programming language that lets you:

• Get complex things done quickly
• Automate system and data integration tasks
• Solve complex analytical problems

You find Python developers throughout the 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 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 let 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 insert, update, and delete data in the Oracle database, and how you call and use PL/SQL stored functions and procedures.

The article has two parts:

• How you install and use cx_Oracle with the Oracle database
• How you query data statically or dynamically

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

How you install and use cx_Oracle with the Oracle database

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

It displays:

Python 2.7.5

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

However, the most current version is now 7.0. You want the following file on Fedora 64-bit Linux, which can be found at the Python Package Index web site:

cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl

A wheel file requires that you use the pip utility (make sure to upgrade to the current version), like:

sudo pip install cx_Oracle-7.0.0-cp27-cp27mu*.whl

It should print the following to the console:

Processing ./cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl                                            
Installing collected packages: cx-Oracle                                                                  
Successfully installed cx-Oracle-7.0.0

The cx_Oracle library depends on the Oracle Client software, which may or may not be installed. It installs without a problem but would raise a runtime error when using the Python software. You can check whether cx_Oracle is installed with the following syntax:

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:

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64

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-11.2.0.4.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:

python

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 environment works by typing the following commands in the IDLE environment:

>>> import cx_Oracle
db = cx_Oracle.connect("student/student@xe")
print db.version

It prints:

11.2.0.2.0

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, which is $PYTHONPATH.

The following adds the /home/student/Code/python directory to the Python path variable:

export set PYTHONPATH=/home/student/Code/python

Next, we create an 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:

python connection.py

It prints the following:

Connected to the Oracle 11.2.0.2.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.

How you query data statically or dynamically

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 software, Python first creates a connection. Then, you need to create a cursor inside a 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[0]) 
 
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:

python basicConnection.py

It prints:

Hello world!

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.

# 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[0], row[1]) 
 
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. 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[0], row[1]) 
 
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 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 on the github.com server.

Anyone using the August 2018 Fedora image should note that I neglected to put the right transaction_upload2.csv file in the /u01/app/oracle/upload directory. You can fix that by navigating to the Lab 12 Instructions web page and click on the zip file link to download the correct file. You will see the following dialog asking whether you want to open the file with the Ark utility, click OK to continue:

After clicking OK to open in Ark, you will see the following Ark dialog:

Click on the Home option in the Places dialog to the left, then click the Downloads option. You should see the following dialog before you click the Extract button.

Open a Konsole session and become the root superuser with the following command:

su - root

Change directory to Lab8_Final_CSV_Files directory where you extracted the transaction_upload2.csv file, like this:

cd /home/student/Downloads/Lab8_Final_CSV_Files

Copy the transaction_upload2.csv file to the /u01/app/oracle/upload directory with the following command:

cp /home/student/Downloads/Lab8_Final_CSV_Files/transaction_upload2.csv /u01/app/oracle/upload/.

Change directory to the /u01/app/oracle/upload directory and run the following long list (ll) command:

ll

You should see the following:

-rw-r--r--. 1 oracle dba      80 Aug 23 22:13 character.csv
drwxr-xr-x. 2 oracle dba    4096 Aug 23 20:44 preproc
drwxr-xr-x. 2 oracle dba    4096 Aug 23 23:35 textfile
-rw-r--r--. 1 oracle dba  128700 Dec  4 15:46 transaction_upload2.csv
-rw-r--r--. 1 oracle dba 1739520 Aug 23 22:04 transaction_upload.csv

The transaction_upload2.csv file contains a value of 3 for the created_by and last_updated_by user values. There shouldn’t be a value of 3 in the system_user_id column of the system_user table. The transaction_upload2.csv file should contain a value of 1002 for the created_by and last_updated_by user values.

You can modify the transaction_upload2.csv file once you’ve put it in the correct directory as the root user with the following command:

cat transaction_upload2.csv | sed -e 's/\,3\,/\,1002\,/g' > x; cp x transaction_upload2.csv; rm x

The new image will correct this problem.