MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Python’ Category

Basic Python Object

without comments

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.

Written by maclochlainn

October 30th, 2016 at 6:23 pm

Create a Python Module

without comments

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.

Written by maclochlainn

October 19th, 2016 at 11:50 pm

Python for loops

without comments

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.

  1. A range for-loop goes from a low numerical value to a high numerical value, like:
  2. for i in range(0,3):
      print i

    It prints the following range values:

    0
    1
    2
  1. A for-each loop goes from the first to the last item while ignoring indexes, like:
  2. list = ['a','b','c']
    for i in list:
      print i

    It prints the following elements of the list:

    a
    b
    c
  1. A for-loop with enumeration goes from the first to the last item while ignoring indexes, like:
  2. 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.

Written by maclochlainn

October 19th, 2016 at 9:02 pm

Install cx_Oracle for Python

without comments

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.

Written by maclochlainn

October 19th, 2016 at 1:47 am

IT Salary Thought

with 2 comments

During the holidays, I check salaries for my students and the IT industry overall. I’m never surprised by the reality, after all salaries pay for return on skills and effort. Here’s my annual look, which some may find unkind but reality is seldom kind.

Before looking at IT salaries, it seems like a good opportunity to first look at the overall job market for Millennials in the United States. AOL provides a great graphic of the median income for Millennials (those born between 1981 and 1997), which is $18,000 to $43,000 a year:

millennial-median-income-state-map

That’s a stark contrast to Forbes’ statistics on the top college baccalaureate degrees. In fact, the top five with the highest salary are between $58 to $67 thousand a year. They are:

  1. Computer Science ………… $66,800
  2. Engineering ………………… $65,000
  3. Mathematics & Statistics … $60,300
  4. Economics ………………….. $58,600
  5. Finance ……………………… $58,000

Computer science, applied computer science, and information technology are probably lumped into the first category. Information systems, exposure without real skills, is a management degree and probably opens positions equivalent to the business degree at $50 thousand a year. More or less, that’s a nine thousand dollar difference between having real skills and being able to talk the game and supervise technical resources. (The 10 hottest IT skills for 2015 are listed in Computerworld.)

There’s no surprise that Ruby, Objective C (iPhone, iPad, Mac OS X), Python, Java, C++ are at the top of the pyramid. Starting salaries in the Salt Lake area are higher for programmers college than they are for other computer science skill sets. In fact, my informal contacts peg them as starting at $70+ thousand. That’s higher than Forbes average for computer science. Here’s a visual on experienced programmers by language:

2015LanguageSalaries

It seems fair to say that a computer science, applied computer science, and information technology degree with an emphasis in real programming skills is the best bet to pay off student loans. However, some will wait for politicians to do that for them, but really that’s quite unlikely, isn’t it?

Reality is always blunt. Reality also seems to frequently differs from what politicians say. After all, politicians pander to audiences, which generally means they say a great deal of nonsense. Nonsense like economics doesn’t matter, everyone should earn the same regardless of their education, skills, or work ethic. Aldous Huxley said it more elegantly when he said, “That all men are equal is a proposition to which, at ordinary times, no sane human being has ever given his assent.”

Written by maclochlainn

December 21st, 2015 at 6:24 pm

MySQLdb Manage Columns

without comments

Sometimes trying to keep a post short and to the point raises other questions. Clearly, my Python-MySQL Program post over the weekend did raise a question. They were extending the query example and encountered this error:

TypeError: range() integer end argument expected, got tuple.

That should be a straight forward error message because of two things. First, the Python built-in range() function manages a range of numbers. Second, the row returned from a cursor is actually a tuple (from relational algebra), and it may contain non-numeric data like strings and dates.

The reader was trying to dynamically navigate the number of columns in a row by using the range() function like this (where row was a row from the cursor or result set):

    for j in range(row):

Naturally, it threw the type mismatch error noted above. As promised, the following Python program fixes that problem. It also builds on the prior example by navigatung an unknown list of columns. Lines 16 through 31 contain the verbose comments and programming logic to dynamically navigate the columns of a row.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/usr/bin/python
 
# Import sys library.
import MySQLdb
import sys
 
try:
  # Create new database connection.
  db = MySQLdb.connect('localhost','student','student','studentdb')
  # Create a result set cursor.
  rs = db.cursor()
  rs.execute("SELECT item_title, item_subtitle, item_rating FROM item")
  # Assign the query results to a local variable.
  for i in range(rs.rowcount):
    row = rs.fetchone()
    # Initialize variable for printing row as a string.
    data = ""
    # Address an indefinite number of columns.
    count = 0
    for j in range(len(row)):
      # Initialize column value as an empty string.
      datum = ""
      # Replace column values when they exist.
      if str(row[count]) != 'None':
        datum = str(row[count])
      # Append a comma when another column follows.
      if count == len(row) - 1:
        data += datum
      else:
        data += datum + ", "
      count += 1
    # Print the formatted row as a string.
    print data
except MySQLdb.Error, e:
  # Print the error.
  print "ERROR %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

There are a couple Python programming techniques that could be perceived as tricks. Line 24 checks for a not null value by explicitly casting the column’s value to a string and then comparing its value against the string equivalent for a null. The MySQLdb returns a 'None' string for null values by default. The if-block on lines 27 through 30 ensure commas aren’t appended at the end of a row.

While the for-loop with a range works, I’d recommend you write it as a while-loop because its easier to read for most new Python programmers. You only need to replace line 20 with the following to make the change:

20
    while (count < len(row)):

Either approach generates output like:

The Hunt for Red October, Special Collectornulls Edition, PG
Star Wars I, Phantom Menace, PG
Star Wars II, Attack of the Clones, PG
Star Wars II, Attack of the Clones, PG
Star Wars III, Revenge of the Sith, PG-13
The Chronicles of Narnia, The Lion, the Witch and the Wardrobe, PG
RoboCop, , Mature
Pirates of the Caribbean, , Teen
The Chronicles of Narnia, The Lion, the Witch and the Wardrobe, Everyone
MarioKart, Double Dash, Everyone
Splinter Cell, Chaos Theory, Teen
Need for Speed, Most Wanted, Everyone
The DaVinci Code, , Teen
Cars, , Everyone
Beau Geste, , PG
I Remember Mama, , NR
Tora! Tora! Tora!, The Attack on Pearl Harbor, G
A Man for All Seasons, , G
Hook, , PG
Around the World in 80 Days, , G
Harry Potter and the Sorcerer's Stone, , PG
Camelot, , G

As always, I hope this helps those looking for clarity.

Written by maclochlainn

April 13th, 2015 at 10:05 pm

Python-MySQL Program

with 4 comments

This post works through the Python configuration of Fedora instance, and continues the configuration of my LAMP VMware instance. It covers how you add the MySQL-python libraries to the Fedora instance, and provides the students with one more language opportunity for their capstone lab in the database class.

A standard Fedora Linux distribution installs Python 2.7 by default. Unfortunately, the MySQL-python library isn’t installed by default. You can verify the Python version by writing and running the following version.py program before installing the MySQL-python library:

1
2
3
4
5
# Import sys library.
import sys
 
# Print the Python version.
print sys.version

You can run the version.py program dynamically like this from the current working directory:

python version.py

It will print the following:

2.7.5 (default, Nov  3 2014, 14:26:24) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-7)]

If you modify the program by adding the following first line

1
2
3
4
5
6
7
#!/usr/bin/python
 
# Import sys library.
import sys
 
# Print the Python version.
print sys.version

Provided you’ve set the file permissions to read and execute, you can run the program by simply calling version.py like this from the present working directory:

./version.py

You can install the MySQL-python library with the yum utility like this:

yum install -y MySQL-python

It shows you the following output:

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                                  |  12 kB  00:00     
updates                                                     | 4.9 kB  00:00     
updates/20/x86_64/primary_db                                |  13 MB  00:04     
(1/2): updates/20/x86_64/updateinfo                         | 1.9 MB  00:02     
(2/2): updates/20/x86_64/pkgtags                            | 1.4 MB  00:02     
Resolving Dependencies
--> Running transaction check
---> Package MySQL-python.x86_64 0:1.2.3-8.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package              Arch           Version               Repository      Size
================================================================================
Installing:
 MySQL-python         x86_64         1.2.3-8.fc20          fedora          82 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 82 k
Installed size: 231 k
Downloading packages:
MySQL-python-1.2.3-8.fc20.x86_64.rpm                        |  82 kB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : MySQL-python-1.2.3-8.fc20.x86_64                             1/1 
  Verifying  : MySQL-python-1.2.3-8.fc20.x86_64                             1/1 
 
Installed:
  MySQL-python.x86_64 0:1.2.3-8.fc20                                            
 
Complete!

After installing the MySQL-python library, you can call the following mysql_connect.py program from the local directory:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/usr/bin/python
 
# Import sys library.
import MySQLdb
import sys
 
try:
  # Create new database connection.
  db = MySQLdb.connect('localhost','student','student','studentdb')
  # Query the version of the MySQL database.
  db.query("SELECT version()")
  # Assign the query results to a local variable.
  result = db.use_result()
  # Print the results.
  print "MySQL Version: %s " % result.fetch_row()[0]
except MySQLdb.Error, e:
  # Print the error.
  print "ERROR %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

Like the version.py program, set the file permissions to read and execute and call , you can run the program by simply calling mysql_connect.py program like this from the present working directory:

./mysql_connect.py

The mysql_connect.py program displays:

MySQL Version: 5.6.24

After verifying the MySQL connection, you can query actual data with the following mysql_queryset.py program:

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
#!/usr/bin/python
 
# Import sys library.
import MySQLdb
import sys
 
try:
  # Create new database connection.
  db = MySQLdb.connect('localhost','student','student','studentdb')
  # Create a result set cursor.
  rs = db.cursor()
  rs.execute("SELECT item_title FROM item")
  # Assign the query results to a local variable.
  rows = rs.fetchall()
  # Print the results.
  for row in rows:
    print row
except MySQLdb.Error, e:
  # Print the error.
  print "ERROR %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

You call the mysql_queryset.py file from the present working directory like this:

./mysql_queryset.py

It prints the following:

('The Hunt for Red October',)
('Star Wars I',)
('Star Wars II',)
('Star Wars II',)
('Star Wars III',)
('The Chronicles of Narnia',)
('RoboCop',)
('Pirates of the Caribbean',)
('The Chronicles of Narnia',)
('MarioKart',)
('Splinter Cell',)
('Need for Speed',)
('The DaVinci Code',)
('Cars',)
('Beau Geste',)
('I Remember Mama',)
('Tora! Tora! Tora!',)
('A Man for All Seasons',)
('Hook',)
('Around the World in 80 Days',)
("Harry Potter and the Sorcerer's Stone",)
('Camelot',)

You can substantially improve on the behavior of the prior example by handling each row one at a time. The following mysql_query.py program reads through the cursor result set one row at a time:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/usr/bin/python
 
# Import sys library.
import MySQLdb
import sys
 
try:
  # Create new database connection.
  db = MySQLdb.connect('localhost','student','student','studentdb')
  # Create a result set cursor.
  rs = db.cursor()
  rs.execute("SELECT item_title FROM item")
  # Assign the query results to a local variable.
  for i in range(rs.rowcount):
    row = rs.fetchone()
    print row[0]
except MySQLdb.Error, e:
  # Print the error.
  print "ERROR %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  # Close the connection when it is open.
  if db:
    db.close()

You call the mysql_query.py with the following syntax:

./mysql_query.py

It returns the following result set:

The Hunt for Red October
Star Wars I
Star Wars II
Star Wars II
Star Wars III
The Chronicles of Narnia
RoboCop
Pirates of the Caribbean
The Chronicles of Narnia
MarioKart
Splinter Cell
Need for Speed
The DaVinci Code
Cars
Beau Geste
I Remember Mama
Tora! Tora! Tora!
A Man for All Seasons
Hook
Around the World in 80 Days
Harry Potter and the Sorcerer's Stone
Camelot

As always, I hope this helps those looking for this type of solution. The Python tutorial web site teaches you more about the Python Programming Language. You may also find the TutorialsPoint.com site useful while you’re learning and using Python. The MySQLdb User’s Guide teaches more about working writing Python-MySQL library. The MySQLdb implements the Python Database API Specification v2.0.

Written by maclochlainn

April 12th, 2015 at 6:36 pm

Popular Programming Languages

with 6 comments

First of all, Happy New Year!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted programming languages (Python [#5], JavaScript [#6], PHP [#7], Ruby [#8], Perl [#11], HTML [#12])
  • Data languages (SQL [#9], MATLAB [#10], R [#13])

I couldn’t resist including Objective-C because it shows how the iPhone, iPad, and Mac OS impact our daily lives. At the same time, Assembly [#15] is actually more popular than Objective-C. Shell [#17] follows Objective-C. While the Visual Basic [#14] programming language still remains very popular.

There are many “why” questions raised by this list of popular programming languages. The “why” from my perspective deals with what are the market drivers for their popularity. The money drivers I see are as follows:

Business Intelligence (BI) software manages most high-level data analysis tools and they’ll continue to get better over time. However, if SQL has shown us anything over 30 years it’s that ultimately we revert to it to solve problems. The conclusion from the reality of BI probably means the programming languages that develop those tools will continue to rise and so will the underlying data languages.

It’s also interesting to note that nine out of ten of the popular programming languages work with databases, like Oracle, MySQL, PostgreSQL, or SQL Server. While JavaScript doesn’t access the database typically, it’s JSON (JavaScript Object Notation) is supported in all the databases.

Written by maclochlainn

January 1st, 2015 at 9:46 pm

Open World 2012

with 2 comments

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …

Written by maclochlainn

October 4th, 2012 at 12:30 pm

MySQL Workbench Scripts

with 2 comments

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py and .lua file extensions, which would preclude opening a .sql file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.

As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.

After you dismiss (by clicking the x) the non-editable .sql file, you need to click on the Open Script file icon shown below.

This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.

The ReadFile.py below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.

Here’s the ReadFile.py file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print() function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.

Python 2.7 (compatible with MySQL Workbench 5.2):

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

Python 3.0 (not-compatible with MySQL Workbench 5.2)

You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('') statement.

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.

If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.

If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …

Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.

Written by maclochlainn

August 18th, 2012 at 4:57 pm