MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQLdb’ Category

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