Database Access in Python

Relational Databases

• Data is held in tables

  • Tables are columns and rows

• Data is manipulated using SQL (Structured Query Language)

  SELECT

  Queries the database

  INSERT, UPDATE, DELETE

  Add, change and remove data items

A Database Server

• A database server is a set of processes that manages the data stored in tables, and allows other processes to connect to it.
  • also called a relational database management system (RDBMS)
• A single database server may manage many databases, each of which may have many tables.

Database Users

• A database manages access to data using user accounts
• User accounts are granted permission to retrieve data, create tables, etc.
• Database user accounts are different to operating system user accounts.

MySQL

• An Open Source database management system
• Extensively used in industry
• Available in shared hosting plans

You and MySQL

• You have mysql on your computer

• To connect:

  > mysql -u root -p
  (enter password)
  

• The command mysql starts a process that connects to the MySQL server. - this is called a client process

A simple query

To return the user you connected as:

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Python and MySQL

• MySQLdb is the standard module to connect Python to MySQL

• Try it:

  >>> import MySQLdb
  >>> db = MySQLdb.connect(user="root", passwd="coi")
  >>> curs = db.cursor()
  >>> curs.execute("select user()")
  >>> curs.fetchall()
  (('root@localhost',),)
  

Cursors

• A query evaluates to a rowset, which has rows and columns (like a table).
• A cursor is used by the client to process these rows
• .fetchone() gets the next row from the cursor
• .fetchall() gets all remaining rows

cursor.execute('SELECT ...')
while 1:
      row = cursor.fetchone()
      if row == None:
         break
      # process row...

cursor.execute('SELECT ...')
for row in cursor:
      # process row...

Parameterization

• SQL uses the where clause to filter the rows that are returned:

  SELECT field1, field2
  FROM table
  WHERE field1 = 'value'
  

• Often you want to filter for something the user has asked for

  • orders for a specific customer
  • courses offered by a department

Parameterization (2)

• To do this, you make value a parameter:

  cursor.execute(
     """SELECT field1, field2
     FROM table
     WHERE field1 = %s""",
     ('value',))
  

  • %s is a placeholder, which is replaced with the values you pass in
  • note there are no quotes

Changing data

• INSERT - Add new records
  • INSERT INTO people VALUES ('Euler', 'Leonhard')
• UPDATE - Change existing records
  • UPDATE people SET age = age+1 WHERE id = 247
• DELETE - Remove records
  • DELETE FROM people WHERE id = 3

Transactions

• Databases are often organized with related data split across several tables.
• A transaction ensures that a piece of work with a number of steps either all commits or is all undone (rollback).
• This prevents an error mid-way through a piece of work from leaving the database in an inconsistent state.

Error Handling

• Connection
  • database is down
  • passwords are incorrect
  • network to db is broken
• Interactions
  • Data that fails constraints
  • Disks full - can't insert

Error Handling

The database can fail at any time - including between you reading the last result and closing the connections

MySQL does not throw an exception here, but it's not forbidden by the spec.

Trapping DB Exceptions

Exceptions are not immediately obvious from a backtrace:

>>> curs.execute("select * from foo")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
...
_mysql_exceptions.ProgrammingError: (1146, "Table 'meps.foo' doesn't exist")

DB Exceptions

This is better:

from MySQLdb import DatabaseError, ProgrammingError

So is this (avoids importing them all):

try:
   ...
except db.ProgrammingError, err:
   ...

Resource Leaks

db = connect(...)
try:
    curs = db.cursor()
    try:
        try:
            curs.execute(...)
            db.commit()
        except:
            db.rollback()
            raise
    finally:
        curs.close()
finally:
   db.close()

Context Managers

• That's too much typing

• Python 2.6 includes context managers

  • In 2.5, from __future__ import with_statement

• Now you can write:

  with db:
      curs.execute(...)
  

• Remember this is an extension.

Exercises

See handout

def rebase_rec(itr):
    rec = []
    for line in itr:
        m = re.match(r"<([1-8])>(.*)", line)
        if m:
            field, val = int(m.group(1)), m.group(2)
            rec.append(val)
            if field == 8:
                yield tuple(rec)
                rec = []