Lawrence Technological University
College of Arts and Science
Department of Mathematics and Computer Sciences

Handouts

Survival guide to Python used with SQL

Step 1 in learning to use Python and database systems, is to start Python in interactive mode and play with it. This is done by typing Python on the command line. Of course you need a computer with Python installed. If you have a Mac running OS X, the command line is accessed from the Terminal Window Utility. You can also simply connect to your account on mscs.ltu.edu You might want to install it on Windows laptop. Directions for doing this are in the handout on Perl, Python and Ruby.

Remember that blocks in Python are all the lines indented the same after a : in a flow control statement. Thus Python is the only one of the scripting languages descended from C where whitespace is important to the compiler. For help with basic, non database, programming tasks in Python see the Phrasebook in the handouts.

A sample interactive session in Python on a Windows laptop that also has MySQL and PostgreSQL installed and has the datasets of Exercises 5.1-5 from Ramakrishnan loaded into both MySQL and PostgreSQL. The datasets and directions for loading them are in the handout on Ramakrishnan's Exercises 5.1 through 5.5.

ActivePython 2.3.2 Build 232 (ActiveState Corp.) based on
Python 2.3.2 (#49, Nov 13 2003, 10:34:54) [MSC v.1200 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> # 1st how do you get out of this?
...
>>> exit
'Use Ctrl-Z plus Return to exit.'
>>> print "Hello, World"
Hello, World
>>> while i > 0:
...   print "Hello, World"
...   i = i - 1
...
Hello, World
Hello, World
Hello, World
Hello, World
Hello, World
>>> if i:
...   print "Hello, World"
... else:
...   print "Done with greetings"
...
Done with greetings
>>> # But, remember a string in Python is just another array
...
>>> greeting = 'Hello, World'
>>> for i in range(5):
...   for c in greeting:
...     print c,
...   print
...
H e l l o ,   W o r l d
H e l l o ,   W o r l d
H e l l o ,   W o r l d
H e l l o ,   W o r l d
H e l l o ,   W o r l d>>> # try MySQL first
...
>>> import MySQLdb
>>> dbh = MySQLdb.connect(db='ex5',user='user-id',passwd='dba-pw')
>>> qry = 'select * from parts'
>>> c= dbh.cursor()
>>> c.execute(qry)
9L
>>> for row in c.fetchall():
...   print row
...
(1L, 'Left Handed Bacon Stretcher Cover', 'Red')
(2L, 'Smoke Shifter End', 'Black')
(3L, 'Acme Widget Washer', 'Red')
(4L, 'Acme Widget Washer', 'Silver')
(5L, 'I Brake for Crop Circles Sticker', 'Translucent')
(6L, 'Anti-Gravity Turbine Generator', 'Cyan')
(7L, 'Anti-Gravity Turbine Generator', 'Magenta')
(8L, 'Fire Hydrant Cap', 'Red')
(9L, '7 Segment Display', 'Green')
>>> # now PostgreSQL
...
>>> from pyPgSQL import PgSQL
>>> dbh2 = PgSQL.connect(host='localhost',database='mcs5303',user='user-id',
...                      password='dba-pw')
>>> c= dbh2.cursor()
>>> c.execute(qry)
>>> for row in c.fetchall():
...   print row
...
[1, 'Left Handed Bacon Stretcher Cover', 'Red']
[2, 'Smoke Shifter End', 'Black']
[3, 'Acme Widget Washer', 'Red']
[4, 'Acme Widget Washer', 'Silver']
[5, 'I Brake for Crop Circles Sticker', 'Translucent']
[6, 'Anti-Gravity Turbine Generator', 'Cyan']
[7, 'Anti-Gravity Turbine Generator', 'Magenta']
[8, 'Fire Hydrant Cap', 'Red']
[9, '7 Segment Display', 'Green']
>>> # say hello before we leave
...
>>> import Tkinter
>>> widget = Tkinter.Label(None,text='Hello, World')
>>> widget.pack()
>>> widget.mainloop()
>>> # back in Python after closing the window
...
>>> ^Z
Tk version of Hello, World

By looking at the handout on Perl, Python, Ruby and databases on mscs.ltu.edu, you can see that the Python syntax is essentially the same. (The End-of-File changes from CTRL-Z on Windows and Open VMS, to CTRL-D on Unix varieties.)

For connecting to Oracle from the computers in M 215 refer to the handout on Perl, Python and Ruby.

Getting the part number from the user as you might to work with the dataset for Exercise 5.2 from Ramakrishnan

  1. Command line user interface. With Active State Python installed on Windows, you can run the following program by
    c:\mcs5303> python partno.py
    
    or just
    c:\mcs5303> partno.py
    
    You can run the program on a Unix like system (e.g. mscs.ltu.edu) by
    user-id@gibbon:~$ python partno.py
    
    or make the script executable by finding Python
    user-id@gibbon:~$ which python
    /usr/bin/python
    
    and then add the line
    #! /user/bin/python
    
    as the 1st line of partno.py, and then modify the file permissions
    user-id@gibbon:~$ ls -l partno.py
    -rw-r--r--    1 user-id user-id      728 Oct 28 13:31 partno.py
    user-id@gibbon:~$ chmod 755 partno.py
    user-id@gibbon:~$ ls -l partno.py
    -rwxr-xr-x    1 user-id user-id      728 Oct 28 13:31 partno.py
    
    so it can be run by
    user-id@gibbon:~$ ./partno.py
    
    # partno.py
    # ask a user what part they want to consider
    import MySQLdb
    dbh = MySQLdb.connect(db='ex5',user='user-id',passwd='dba-pw')
    qry = 'select pid,pname,color from parts'
    c= dbh.cursor()
    c.execute(qry)
    desc = {}
    print 'Please select one from this list'
    for row in c.fetchall():
      if row[2]:
        color = '(' + row[2] + ')'
      else:
        color = ""
      pid = int(row[0])
      desc[pid] = row[1] + ' ' + color
      print "%2d  %s" % (pid,desc[pid])
    try:
      pid = int(raw_input('Enter the number of your selection ==> '))
      if desc.has_key(pid):
        print "You selected %d, %s" % (pid, desc[pid])
      else:
        print "I'm sorry, we don't have a part like that"
    except:
      print "I'm sorry, you must enter a number"
    
    In any event the output should be something like
    Please select one from this list
     1  Left Handed Bacon Stretcher Cover (Red)
     2  Smoke Shifter End (Black)
     3  Acme Widget Washer (Red)
     4  Acme Widget Washer (Silver)
     5  I Brake for Crop Circles Sticker (Translucent)
     6  Anti-Gravity Turbine Generator (Cyan)
     7  Anti-Gravity Turbine Generator (Magenta)
     8  Fire Hydrant Cap (Red)
     9  7 Segment Display (Green)
    Enter the number of your selection ==> 2
    You selected 2, Smoke Shifter End (Black)
    
  2. Windows user interface. Python has at least 2 pathways that may interest you for desktop windowing user interfaces. If your need is only for the Windows platform and you already understand the MFC framework for C++, you can use the PyC... wrapper classes for MFC that come with Active State's Python Win32 extentions. The more portable Tk framework also works with Mac OS X and Unix X-windows systems. Because most of the current development in Tcl/Tk is also at Active State, Tk seems like the more stable choice. If you need to do a lot of windowing work, you will want to look at the help files that come from Active State for lots of nice examples of how to use classes in Python with Tkinter.
    # partnotk.py
    # ask a user what part they want to consider
    import MySQLdb
    from Tkinter import *
    def inventory():
      desc = {}
      try:
        dbh = MySQLdb.connect(db='ex5',user='user-id',passwd='dba-pw')
        qry = 'select pid,pname,color from parts'
        c= dbh.cursor()
        c.execute(qry)
        for row in c.fetchall():
          if row[2]:
            color = '(' + row[2] + ')'
          else:
            color = ""
          pid = int(row[0])
          desc[pid] = row[1] + ' ' + color
      except:
        desc = {}
        print "Fatal Database failure"
      return desc
    def find(partlist,answer):
      part = partlist.get(partlist.curselection())
      answer.configure(state=NORMAL)
      answer.delete(1.0,END)
      answer.insert(END,"You requested %s" % part)
      answer.configure(state=DISABLED)
    # Entry
    desc = inventory()
    # Build window
    root = Tk()
    Label(root,text='Part Search').pack()
    listframe = Frame(root)
    listframe.pack()
    partlist = Listbox(listframe,relief=SUNKEN,height=5,selectmode=SINGLE,width=-1)
    # fill the list box
    pids = desc.keys()
    pids.sort()
    for pid in (pids):
      partlist.insert(END,"%d, %s" % (pid,desc[pid]))
    partlist.select_set(0)
    listscroll = Scrollbar(listframe,command=partlist.yview)
    partlist.configure(yscrollcommand=listscroll.set)
    listscroll.pack(side=RIGHT,fill=Y)
    partlist.pack(side=LEFT,expand=YES,fill=BOTH)
    # a read only box that your user can copy to the windows clipboard
    answer = Text(root,height=3,width=60)
    answer.insert(END,'Select a part and click "Find"')
    answer.configure(state=DISABLED)
    answer.pack()
    Button(root,text='Find',command=(lambda: find(partlist,answer))).pack()
    root.mainloop()
    
    partnotk.py running
  3. Web browser user interface. First a program to make an HTML form with a menu of parts. This example is on a Windows platform.
    # genpartno.py
    # program to generate the HTML to help a user select a part
    import MySQLdb
    dbh = MySQLdb.connect(db='ex5',user='user-id',passwd='dba-pw')
    qry = 'select pid,pname,color from parts'
    c= dbh.cursor()
    c.execute(qry)
    f = open(
        "\\program files\\apache group\\apache2\\htdocs\\mcs5303\\partno.html",
        "w")
    f.write("""<html>
      <head><title>partno.html</title></head>
      <body>
        <h1>MCS 5303 Part Search</h1>
        <form action="../cgi-bin/partno.cgi" method=POST>
        <p>Part <select name=pid>\n""")
    for row in c.fetchall():
      if row[2]:
        color = '(' + row[2] + ')'
      else:
        color = ""
      pid = int(row[0])
      f.write("<option value=%d> %s %s\n" % (pid,row[1], color))
    f.write("""    </select>
        <input type=submit value="Find">
        </form>
      </body>
    </html>\n""")
    f.close()
    
    Part search Web form

    Then a GCI program to anwser the form

    #! \python23\python.exe
    # partno.cgi
    import cgi, MySQLdb
    # announce the beginning of an http: reply
    print "Content-type: text/html\n\n"
    # begin the HTML page
    print """<html><head>
      <title>partno.cgi</title>
    </head>
    <body>
    <h1>MCS 5303 Part Search</h1>"""
    #parse key-value pairs
    form = cgi.FieldStorage()
    if form.has_key('pid'):
      pid = form['pid'].value
      try:
        dbh = MySQLdb.connect(db='ex5',user='user-id',passwd='dba-pw')
        qry = "select pname,color from parts where pid = '%d'" % int(pid)
        c= dbh.cursor()
        c.execute(qry)
        row = c.fetchone()
        if row:
          if row[1]:
            color = '(' + row[1] + ')'
          else:
            color = ""
          print "<p>You asked for part #%d, %s %s" % (int(pid),row[0],color)
        else:
          print "<p>Sorry, your request was lost.  Please go back and try again";
      except:
        print "<p>Sorry, the database is down.  Please try again later."
    else:
      print "<p>Please go back and select a part!"
    print "</body></html>"
    
    Answer to Part search Web form

A subroutine example. If there were a table "kit" added to the Exercise 5.2 dataset, with columns:
kit-part-id, component-part-id, component-quantity, ...
then a simple subroutine to test if any part-id is a kit, by checking if at least 1 row has that value

def is_kit(dbh,pid):
  qry = "select count(*) from kit where kid='%s'" % str(pid)
  c = dbh.cursor()
  c.execute(qry)
  row = c.fetchone()
  return int(row.[0])

Revised October 30, 2004