Archive for the ‘Python’ Category
Python & Oracle 1
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_Oraclewith 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.
Read list of a dictionaries
My students wanted a quick example of how to read a list of a dictionaries in Python. So, here it is:
#!/usr/bin/python # Declare list of dictionaries. cakes = [{'cake':"vanilla",'frosting':"chocolate"} ,{'cake':"chocolate",'frosting':"vanilla"}] # Read the list of dictionaries. for lkey, lvalue in enumerate(cakes): print lvalue['cake'] + " with " + lvalue['frosting'] + " frosting." |
Naturally, a list can contain many things and you should ensure each value you read is a dictionary before trying to read it as a dictionary. At least, I’d suggest you check.
Hope this answers the how.
Installing PIP for Python
If you’re on a Mac running macOS Sierra, you can install PIP to add packages. PIP stands for either of the following:
- PIP installs Packages
- PIP installs Python
You use the following to install the PIP utility:
sudo easy_install pip |
It should return the following:
Searching for pip Reading https://pypi.python.org/simple/pip/ Best match: pip 9.0.1 Downloading https://pypi.python.org/packages/11/b6/abcb525026a4be042b486df43905d6893fb04f05aac21c32c638e939e447/pip-9.0.1.tar.gz#md5=35f01da33009719497f01a4ba69d63c9 Processing pip-9.0.1.tar.gz Writing /tmp/easy_install-ryxjDg/pip-9.0.1/setup.cfg Running pip-9.0.1/setup.py -q bdist_egg --dist-dir /tmp/easy_install-ryxjDg/pip-9.0.1/egg-dist-tmp-l6_Jjt /System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/distutils/dist.py:267: UserWarning: Unknown distribution option: 'python_requires' warnings.warn(msg) warning: no previously-included files found matching '.coveragerc' warning: no previously-included files found matching '.mailmap' warning: no previously-included files found matching '.travis.yml' warning: no previously-included files found matching '.landscape.yml' warning: no previously-included files found matching 'pip/_vendor/Makefile' warning: no previously-included files found matching 'tox.ini' warning: no previously-included files found matching 'dev-requirements.txt' warning: no previously-included files found matching 'appveyor.yml' no previously-included directories found matching '.github' no previously-included directories found matching '.travis' no previously-included directories found matching 'docs/_build' no previously-included directories found matching 'contrib' no previously-included directories found matching 'tasks' no previously-included directories found matching 'tests' creating /Library/Python/2.7/site-packages/pip-9.0.1-py2.7.egg Extracting pip-9.0.1-py2.7.egg to /Library/Python/2.7/site-packages Adding pip 9.0.1 to easy-install.pth file Installing pip script to /usr/local/bin Installing pip2.7 script to /usr/local/bin Installing pip2 script to /usr/local/bin Installed /Library/Python/2.7/site-packages/pip-9.0.1-py2.7.egg Processing dependencies for pip Finished processing dependencies for pip |
After you install PIP, you can use PIP to add custom packages to the Python environment. The
sudo pip install easygui |
You get the following warning and installation:
The directory '/Users/michaelmclaughlin/Library/Caches/pip/http' or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag. The directory '/Users/michaelmclaughlin/Library/Caches/pip' or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag. Collecting easygui Downloading easygui-0.98.1-py2.py3-none-any.whl (90kB) 100% |████████████████████████████████| 92kB 1.0MB/s Installing collected packages: easygui Successfully installed easygui-0.98.1 |
After installing the easygui Python library, you can change to the root directory to confirm the installation of the easygui Python library with the following command:
find . -name easygui* 2>/dev/null |
It returns the following:
./Library/Python/2.7/site-packages/easygui ./Library/Python/2.7/site-packages/easygui/easygui.py ./Library/Python/2.7/site-packages/easygui/easygui.pyc ./Library/Python/2.7/site-packages/easygui-0.98.1.dist-info |
You can connect to Python 2.7 in a Terminal session. Then, you use the easygui library to run a Hello World! message box with the following commands in the Python shell:
import easygui easy gui.msgbox("Hello World!") |
It will raise the following image:
Hopefully, this helps a few folks.
Python variable not defined
While working with a programming example for my students, I ran into an interesting run-time error when I changed their approach to importing Python’s random module. Here’s the raised error message:
Traceback (most recent call last): File "windowBouncingBalls.py", line 84, in <module> speed = [choice([-2,2]), choice([-2,2])] NameError: name 'choice' is not defined |
You raise the missing choice identifier when two things occur. The first thing requires you to use a standard import statement, like the following example, and the second thing requires you to continue to reference the identifier as “choice“.
import random |
You can avoid the error by making the import of random like this:
from random import * |
Or, you can leave the ordinary import statement and fully qualify the choice identifier with the random module name, like this:
speed = [random.choice([-2,2]), random.choice([-2,2])] |
As always, I hope this helps those who encounter a similar problem.
Install PyGame on Fedora
The PyGame library is a wonderful tool for building games with Python. It lets you accomplish a great deal by simply managing events. You need to understand how to use Python functions, modules, and events to build games with this Python library.
You can download and install the PyGame library with the yum utility like this:
yum install -y pygame |
It should generate the following list when you install it as the root user:
Loaded plugins: langpacks, refresh-packagekit Available Packages pygame.x86_64 1.9.1-14.fc20 fedora [root@localhost ~]# yum install -y pygame Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package pygame.x86_64 0:1.9.1-14.fc20 will be installed --> Processing Dependency: numpy for package: pygame-1.9.1-14.fc20.x86_64 --> Processing Dependency: libportmidi.so.0()(64bit) for package: pygame-1.9.1-14.fc20.x86_64 --> Processing Dependency: libSDL_ttf-2.0.so.0()(64bit) for package: pygame-1.9.1-14.fc20.x86_64 --> Processing Dependency: libSDL_mixer-1.2.so.0()(64bit) for package: pygame-1.9.1-14.fc20.x86_64 --> Processing Dependency: libSDL_image-1.2.so.0()(64bit) for package: pygame-1.9.1-14.fc20.x86_64 --> Running transaction check ---> Package SDL_image.x86_64 0:1.2.12-7.fc20 will be installed ---> Package SDL_mixer.x86_64 0:1.2.12-5.fc20 will be installed --> Processing Dependency: libmikmod for package: SDL_mixer-1.2.12-5.fc20.x86_64 ---> Package SDL_ttf.x86_64 0:2.0.11-4.fc20 will be installed ---> Package numpy.x86_64 1:1.8.2-2.fc20 will be installed --> Processing Dependency: python-nose for package: 1:numpy-1.8.2-2.fc20.x86_64 ---> Package portmidi.x86_64 0:217-9.fc20 will be installed --> Running transaction check ---> Package libmikmod.x86_64 0:3.3.6-3.fc20 will be installed ---> Package python-nose.noarch 0:1.3.0-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: pygame x86_64 1.9.1-14.fc20 fedora 2.1 M Installing for dependencies: SDL_image x86_64 1.2.12-7.fc20 fedora 41 k SDL_mixer x86_64 1.2.12-5.fc20 fedora 91 k SDL_ttf x86_64 2.0.11-4.fc20 fedora 22 k libmikmod x86_64 3.3.6-3.fc20 updates 142 k numpy x86_64 1:1.8.2-2.fc20 updates 3.0 M portmidi x86_64 217-9.fc20 fedora 26 k python-nose noarch 1.3.0-1.fc20 fedora 272 k Transaction Summary ================================================================================ Install 1 Package (+7 Dependent packages) Total download size: 5.7 M Installed size: 21 M Downloading packages: (1/8): SDL_image-1.2.12-7.fc20.x86_64.rpm | 41 kB 00:00 (2/8): SDL_mixer-1.2.12-5.fc20.x86_64.rpm | 91 kB 00:00 (3/8): portmidi-217-9.fc20.x86_64.rpm | 26 kB 00:00 (4/8): SDL_ttf-2.0.11-4.fc20.x86_64.rpm | 22 kB 00:00 (5/8): libmikmod-3.3.6-3.fc20.x86_64.rpm | 142 kB 00:00 (6/8): numpy-1.8.2-2.fc20.x86_64.rpm | 3.0 MB 00:02 (7/8): pygame-1.9.1-14.fc20.x86_64.rpm | 2.1 MB 00:01 (8/8): python-nose-1.3.0-1.fc20.noarch.rpm | 272 kB 00:00 -------------------------------------------------------------------------------- Total 1.7 MB/s | 5.7 MB 00:03 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : SDL_ttf-2.0.11-4.fc20.x86_64 1/8 Installing : SDL_image-1.2.12-7.fc20.x86_64 2/8 Installing : portmidi-217-9.fc20.x86_64 3/8 Installing : libmikmod-3.3.6-3.fc20.x86_64 4/8 Installing : SDL_mixer-1.2.12-5.fc20.x86_64 5/8 Installing : python-nose-1.3.0-1.fc20.noarch 6/8 Installing : 1:numpy-1.8.2-2.fc20.x86_64 7/8 Installing : pygame-1.9.1-14.fc20.x86_64 8/8 Verifying : pygame-1.9.1-14.fc20.x86_64 1/8 Verifying : SDL_mixer-1.2.12-5.fc20.x86_64 2/8 Verifying : python-nose-1.3.0-1.fc20.noarch 3/8 Verifying : libmikmod-3.3.6-3.fc20.x86_64 4/8 Verifying : 1:numpy-1.8.2-2.fc20.x86_64 5/8 Verifying : portmidi-217-9.fc20.x86_64 6/8 Verifying : SDL_image-1.2.12-7.fc20.x86_64 7/8 Verifying : SDL_ttf-2.0.11-4.fc20.x86_64 8/8 Installed: pygame.x86_64 0:1.9.1-14.fc20 Dependency Installed: SDL_image.x86_64 0:1.2.12-7.fc20 SDL_mixer.x86_64 0:1.2.12-5.fc20 SDL_ttf.x86_64 0:2.0.11-4.fc20 libmikmod.x86_64 0:3.3.6-3.fc20 numpy.x86_64 1:1.8.2-2.fc20 portmidi.x86_64 0:217-9.fc20 python-nose.noarch 0:1.3.0-1.fc20 Complete! |
I hope this helps folks install the software.
Install EasyGUI on Fedora
The EasyGUI library is a nice tool for developing GUI applications. It doesn’t require you to know event-driven programming to write basic GUI applications because it’s based on Python functions.
You can download and install the EasyGUI library with yum utility like this:
yum install -y python-easygui |
It should generate the following list:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 2.8 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package python-easygui.noarch 0:0.96-7.fc20 will be installed --> Processing Dependency: tkinter for package: python-easygui-0.96-7.fc20.noarch --> Processing Dependency: python-setuptools for package: python-easygui-0.96-7.fc20.noarch --> Running transaction check ---> Package python-setuptools.noarch 0:1.4.2-1.fc20 will be installed ---> Package tkinter.x86_64 0:2.7.5-16.fc20 will be installed --> Processing Dependency: libtk8.5.so()(64bit) for package: tkinter-2.7.5-16.fc20.x86_64 --> Processing Dependency: libtcl8.5.so()(64bit) for package: tkinter-2.7.5-16.fc20.x86_64 --> Processing Dependency: libTix.so()(64bit) for package: tkinter-2.7.5-16.fc20.x86_64 --> Running transaction check ---> Package tcl.x86_64 1:8.5.14-1.fc20 will be installed ---> Package tix.x86_64 1:8.4.3-11.fc20 will be installed ---> Package tk.x86_64 1:8.5.14-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: python-easygui noarch 0.96-7.fc20 fedora 481 k Installing for dependencies: python-setuptools noarch 1.4.2-1.fc20 updates 413 k tcl x86_64 1:8.5.14-1.fc20 fedora 1.9 M tix x86_64 1:8.4.3-11.fc20 fedora 253 k tk x86_64 1:8.5.14-1.fc20 fedora 1.4 M tkinter x86_64 2.7.5-16.fc20 updates 316 k Transaction Summary ================================================================================ Install 1 Package (+5 Dependent packages) Total download size: 4.7 M Installed size: 13 M Downloading packages: (1/6): python-setuptools-1.4.2-1.fc20.noarch.rpm | 413 kB 00:00 (2/6): python-easygui-0.96-7.fc20.noarch.rpm | 481 kB 00:00 (3/6): tkinter-2.7.5-16.fc20.x86_64.rpm | 316 kB 00:00 (4/6): tix-8.4.3-11.fc20.x86_64.rpm | 253 kB 00:01 (5/6): tcl-8.5.14-1.fc20.x86_64.rpm | 1.9 MB 00:01 (6/6): tk-8.5.14-1.fc20.x86_64.rpm | 1.4 MB 00:03 -------------------------------------------------------------------------------- Total 1.5 MB/s | 4.7 MB 00:03 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : 1:tcl-8.5.14-1.fc20.x86_64 1/6 Installing : 1:tk-8.5.14-1.fc20.x86_64 2/6 Installing : 1:tix-8.4.3-11.fc20.x86_64 3/6 Installing : tkinter-2.7.5-16.fc20.x86_64 4/6 Installing : python-setuptools-1.4.2-1.fc20.noarch 5/6 Installing : python-easygui-0.96-7.fc20.noarch 6/6 Verifying : 1:tk-8.5.14-1.fc20.x86_64 1/6 Verifying : tkinter-2.7.5-16.fc20.x86_64 2/6 Verifying : 1:tix-8.4.3-11.fc20.x86_64 3/6 Verifying : 1:tcl-8.5.14-1.fc20.x86_64 4/6 Verifying : python-easygui-0.96-7.fc20.noarch 5/6 Verifying : python-setuptools-1.4.2-1.fc20.noarch 6/6 Installed: python-easygui.noarch 0:0.96-7.fc20 Dependency Installed: python-setuptools.noarch 0:1.4.2-1.fc20 tcl.x86_64 1:8.5.14-1.fc20 tix.x86_64 1:8.4.3-11.fc20 tk.x86_64 1:8.5.14-1.fc20 tkinter.x86_64 0:2.7.5-16.fc20 Complete! |
You can then test the EasyGUI library with the following three lines of code inside the IDLE interpreter:
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. >>> import easygui >>> flavor = easygui.enterbox("What flavor of bum do you like?") >>> if easygui.msgbox("You like " + flavor + " gum.") == 'OK': ... print "OK button clicked ..." ... OK button clicked ... |
The easygui.enterbox call displays the image below. Enter “Peppermint” in the entry box and click the OK button to assign the “Peppermint” string literal to the flavor variable.
The easygui.msgbox call displays the message below:
When you click the OK button, the program returns an “OK” string to the Python code. It prints the string “OK button clicked …” string:
As always, I hope this helps those looking for instructions and a quick way to play with Python and GUI applications.
Basic Python Object
One of my students wanted a quick example of a Python object with getters and setters. So, I wrote a little example that I’ll share.
You define this file in a physical directory that is in your $PYTHONPATH, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | # Define Coordinate class. class Coordinate: # The method that initializes the Coordinate class. def __init__ (self, x, y): self.x = x self.y = y # Gets the x value from the instance. def getX (self): return self.x # Gets the y value from the instance. def getY (self): return self.y # Sets the x value in the instance. def setX (self, x): self.x = x # Sets the y value in the instance. def setY (self, y): self.y = y # Prints the coordinate pair. def printCoordinate(self): print (self.x, self.y) |
Assuming the file name is Coordinate.py, you can put it into the Python Idle environment with the following command:
from Coordinate import Coordinate |
You initialize the class, like this:
g = Coordinate(49,49) |
Then, you can access the variables of the class or it’s methods as shown below:
# Print the retrieved value of x from the g instance of the Coordinate class. print g.getX() # Print the formatted and retrieved value of x from the g instance of the Coordinate class. print "[" + str(g.getX()) + "]" # Set the value of x inside the g instance of the Coordinate class. print g.setX(39) # Print the Coordinates as a set. g.printCoordinate() |
You would see the following results:
49 [49] (39,49) |
As always, I hope that helps those looking for a solution.
Create a Python Module
Sometime formal programming documentation is less than clear. At least, it’s less than clear until you’ve written your first solution. The Modules section of the Python language is one of those that takes a few moments to digest.
Chapters 22 and 23 in Learning Python gives some additional details but not a clear step-by-step approach to implementing Python modules. This post is designed to present the steps to write, import, and call a Python module. I figured that it would be helpful to write one for my students, and posting it in the blog seemed like the best idea.
I wrote the module to parse an Oracle version string into what we’d commonly expect to see, like the release number, an “R”, a release version, and then the full version number. The module name is more or less equivalent to a package name, and the file name is effectively the module name. The file name is strVersionOracle.py, which makes the strVersionOracle the module name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | # Parse and format Oracle version. def formatVersion(s): # Split string into collection. list = s.split(".") # Iterate through the result set. for i, l in enumerate(list): if i == 0 and list[i] == "11": label = str(l) + "g" elif i == 0 and list[i] == "12": label = label + str(l) + "c" elif i == 1: label = label + "R" + list[i] + " (" + s + ")" # Return the formatted string. return label |
You can put this in any directory as long as you add it to the Python path. There are two Python paths to maintain. One is in the file system and the other is in Python’s interactive IDLE environment. You can check the contents of the IDLE path with the following interactive commands:
import sys print sys.path |
It prints the following:
['', '/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 append to the IDLE path using the following command:
sys.path.append("/home/student/Code/python") |
After putting the module in the runtime path, you can test the code in the IDLE environment:
1 2 3 | import cx_Oracle db = cx_Oracle.connect("student/student@xe") print strVersionOracle.formatVersion(db.version) |
Line 3 prints the result by calling the formatVersion function inside the strVersionOracle module. It prints the following:
11gR2 (11.2.0.2.0) |
You can test the program outside of the runtime environment with the following oracleConnection.py file. It runs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # Import the Oracle library. import cx_Oracle import strVersionOracle try: # Create a connection. db = cx_Oracle.connect("student/student@xe") # Print a message. print "Connected to the Oracle " + strVersionOracle.formatVersion(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() |
You can call the formatVersion() function rather than a combination of module and function names when you write a more qualified import statement on line 3, like:
3 | from strVersionOracle import formatVersion |
Then, you can call the formatVersion() function like this on line 10:
10 | print "Connected to the Oracle " + formatVersion(db.version) + " database." |
It works because you told it to import a function from a Python module. The first example imports a module that may contain one to many functions, and that style requires you to qualify the location of functions inside imported modules.
The oracleConnection.py program works when you call it from the Bash shell provided you do so from the same directory where the oracleConnection.py and strVersionOracle.py files (or Python modules) are located. If you call the oracleConnection.py file from a different directory, the reference to the library raises the following error:
Traceback (most recent call last): File "oracleConnection.py", line 3, in <module> import strVersionOracle ImportError: No module named strVersionOracle |
You can fix this error by adding the directory where the strVersionOracle.py file exists, like
export set PYTHONPATH=/home/student/Code/python |
Then, you can call successfully the oracleConnection.py file from any directory:
python oracleConnection.py |
The program will connect to the Oracle database as the student user, and print the following message to the console:
Connected to the Oracle 11gR2 (11.2.0.2.0) database. |
I hope this helps those trying to create and use Python modules.
Python for loops
It’s always interesting to explain a new programming language to students. Python does presents some challenges to that learning process. I think for-loops can be a bit of a challenge until you understand them. Many students are most familiar with the traditional for loop like Java:
for (i = 0; i < 5; i++) { ... } |
Python supports three types of for-loops – a range for loop, a for-each expression, and a for-loop with enumeration. Below are examples of each of these loops.
- A range for-loop goes from a low numerical value to a high numerical value, like:
for i in range(0,3): print i |
It prints the following range values:
0 1 2 |
- A for-each loop goes from the first to the last item while ignoring indexes, like:
list = ['a','b','c'] for i in list: print i |
It prints the following elements of the list:
a b c |
- A for-loop with enumeration goes from the first to the last item while ignoring indexes, like:
list = ['a','b','c'] for i, e in enumerate(list): print "[" + str(i) + "][" + list[i] + "]" |
The i represents the index values and the e represents the elements of a list. The str() function casts the numeric value to a string.
It prints the following:
[0][a] [1][b] [2][c] |
This should help my students and I hope it helps you if you’re trying to sort out how to use for loops in Python.
Install cx_Oracle for Python
This shows you how to install the cx_Oracle library for Python 2.7 on Fedora Linux. If Fedora has it on the server you can download it with the following yum command:
yum install -y cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm |
Currently, you’ll get the following failure because it’s not available in the Fedora repository:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00:00 mysql-tools-community | 2.5 kB 00:00:00 mysql56-community | 2.5 kB 00:00:00 pgdg93 | 3.6 kB 00:00:00 updates/20/x86_64/metalink | 2.3 kB 00:00:00 No package cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm available. Error: Nothing to do |
You can download the cx_Oracle library from the Python web site. The cx_Oracle documentation qualifies module interfaces, objects, and connections. Assuming your Linux user’s name is student, you download the cx_Oracle library into the /home/student/Downloads directory. Then, you use the su or sudo command to become the root user.
As the root user, run the following yum command:
yum install -y ~student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm |
You should see the following output:
Loaded plugins: langpacks, refresh-packagekit Examining /home/student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm: cx_Oracle-5.2.1-1.x86_64 Marking /home/student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package cx_Oracle.x86_64 0:5.2.1-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================= Package Arch Version Repository Size ======================================================================================= Installing: cx_Oracle x86_64 5.2.1-1 /cx_Oracle-5.2.1-11g-py27-1.x86_64 717 k Transaction Summary ======================================================================================= Install 1 Package Total size: 717 k Installed size: 717 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : cx_Oracle-5.2.1-1.x86_64 1/1 Verifying : cx_Oracle-5.2.1-1.x86_64 1/1 Installed: cx_Oracle.x86_64 0:5.2.1-1 Complete! |
After you install the cx_Oracle-5.2.1-1.x86_64 package, you can find the installed files with this rpm command:
rpm -ql cx_Oracle-5.2.1-1.x86_64 |
It lists:
/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info /usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/PKG-INFO /usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/SOURCES.txt /usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/dependency_links.txt /usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/top_level.txt /usr/lib64/python2.7/site-packages/cx_Oracle.so /usr/share/doc/cx_Oracle-5.2.1 /usr/share/doc/cx_Oracle-5.2.1/BUILD.txt /usr/share/doc/cx_Oracle-5.2.1/README.txt /usr/share/doc/cx_Oracle-5.2.1/samples /usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseChangeNotification.py /usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseShutdown.py /usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseStartup.py /usr/share/doc/cx_Oracle-5.2.1/samples/ReturnLongs.py /usr/share/doc/cx_Oracle-5.2.1/samples/ReturnUnicode.py /usr/share/doc/cx_Oracle-5.2.1/samples/RowsAsInstance.py /usr/share/doc/cx_Oracle-5.2.1/test /usr/share/doc/cx_Oracle-5.2.1/test/3kArrayDMLBatchError.py /usr/share/doc/cx_Oracle-5.2.1/test/3kNumberVar.py /usr/share/doc/cx_Oracle-5.2.1/test/3kStringVar.py /usr/share/doc/cx_Oracle-5.2.1/test/ArrayDMLBatchError.py /usr/share/doc/cx_Oracle-5.2.1/test/BooleanVar.py /usr/share/doc/cx_Oracle-5.2.1/test/Connection.py /usr/share/doc/cx_Oracle-5.2.1/test/Cursor.py /usr/share/doc/cx_Oracle-5.2.1/test/CursorVar.py /usr/share/doc/cx_Oracle-5.2.1/test/DateTimeVar.py /usr/share/doc/cx_Oracle-5.2.1/test/IntervalVar.py /usr/share/doc/cx_Oracle-5.2.1/test/LobVar.py /usr/share/doc/cx_Oracle-5.2.1/test/LongVar.py /usr/share/doc/cx_Oracle-5.2.1/test/NCharVar.py /usr/share/doc/cx_Oracle-5.2.1/test/NumberVar.py /usr/share/doc/cx_Oracle-5.2.1/test/ObjectVar.py /usr/share/doc/cx_Oracle-5.2.1/test/SessionPool.py /usr/share/doc/cx_Oracle-5.2.1/test/SetupTest.sql /usr/share/doc/cx_Oracle-5.2.1/test/StringVar.py /usr/share/doc/cx_Oracle-5.2.1/test/TestEnv.py /usr/share/doc/cx_Oracle-5.2.1/test/TimestampVar.py /usr/share/doc/cx_Oracle-5.2.1/test/test.py /usr/share/doc/cx_Oracle-5.2.1/test/test3k.py /usr/share/doc/cx_Oracle-5.2.1/test/test_dbapi20.py /usr/share/doc/cx_Oracle-5.2.1/test/uArrayDMLBatchError.py /usr/share/doc/cx_Oracle-5.2.1/test/uConnection.py /usr/share/doc/cx_Oracle-5.2.1/test/uCursor.py /usr/share/doc/cx_Oracle-5.2.1/test/uCursorVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uDateTimeVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uIntervalVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uLobVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uLongVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uNumberVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uObjectVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uSessionPool.py /usr/share/doc/cx_Oracle-5.2.1/test/uStringVar.py /usr/share/doc/cx_Oracle-5.2.1/test/uTimestampVar.py |
After you installed the software, you can test whether inside Python’s IDLE environment with the import command, like this:
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. >>> import cx_Oracle Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory |
This error indicates that Oracle Client software isn’t installed, which is true in this case. I only installed the Oracle Database 11g Express Edition. You need to download the Oracle Client software and install it as the root user.
You download the Oracle Client software from the Oracle web site. Assuming your Linux user’s name is student, you download the cx_Oracle library into the /home/student/Downloads directory. Then, you use the su or sudo command to become the root user.
As the root user, run the following yum command:
yum install -y ~student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm |
You should see the following output:
Loaded plugins: langpacks, refresh-packagekit
Examining /home/student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
Marking /home/student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient11.2-basic.x86_64 0:11.2.0.4.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package
Arch Version
Repository Size
================================================================================
Installing:
oracle-instantclient11.2-basic
x86_64 11.2.0.4.0-1
/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64 179 M
Transaction Summary
================================================================================
Install 1 Package
Total size: 179 M
Installed size: 179 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
Installing : oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64 1/1
Verifying : oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64 1/1
Installed:
oracle-instantclient11.2-basic.x86_64 0:11.2.0.4.0-1
Complete! |
You can create a Python program that checks your ability to connect to the Oracle database, like the following oracleConnection.py file:
# 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 cursor. db.close() |
You can run this from the Linux command line with the following syntax:
python oracleConnection.py |
It should return the following string:
Connected to the Oracle 11.2.0.2.0 database. |
Now, you can create a Python program that reads data from the Oracle database. The following oracleString.py file reads a string literal from the pseudo table dual:
# 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 '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() |
You can run this from the Linux command line with the following syntax:
python oracleString.py |
It should return the following string:
Hello world! |
Now, you can create a Python program that reads actual table data from the Oracle database (assuming you have a copy of my video store database). The following oracleTable.py file reads a string literal from the pseudo table dual:
# 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_subtitle FROM item") # 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 can run this from the Linux command line with the following syntax:
python oracleTable.py |
It should return the following strings (only a subset of the returned values):
("Harry Potter and the Sorcer's Stone", 'Two-Disc Special Edition')
('Harry Potter and the Chamber of Secrets', 'Two-Disc Special Edition')
('Harry Potter and the Prisoner of Azkaban', 'Two-Disc Special Edition')
('Harry Potter and the Chamber of Secrets', None)
('Harry Potter and the Goblet of Fire', 'Widescreen Edition')
('Harry Potter and the Goblet of Fire', 'Two-Disc Special Edition')
('Harry Potter and the Order of the Phoenix', 'Widescreen Edition')
('The Lord of the Rings - Fellowship of the Ring', 'Widescreen Edition')
('The Lord of the Rings - Fellowship of the Ring', 'Platinum Series Special Extended Edition')
('The Lord of the Rings - Two Towers', 'Widescreen Edition')
('The Lord of the Rings - Two Towers', 'Platinum Series Special Extended Edition')
('The Lord of the Rings - The Return of the King', 'Widescreen Edition')
('The Lord of the Rings - The Return of the King', 'Platinum Series Special Extended Edition')
('Star Wars - Episode I', 'The Phantom Menace')
('Star Wars - Episode II', 'Attack of the Clones')
('Star Wars - Episode III', 'Revenge of the Sith')
('Star Wars - Episode IV', 'A New Hope')
('Star Wars - Episode V', 'The Empire Strikes Back')
('Star Wars - Episode VI', 'Return of the Jedi') |
As always, I hope this helps others who want to work with Python and the Oracle database.
Subscribe via RSS