Using Databases in Python

Using the Façade Pattern

No. 12

Sometimes you start working with some data or library and find that there are so many methods or functions that it is easy to get confused in all the clutter. It would be nicer if the system had just a few classes and methods to manage. This is where the Façade pattern comes in handy.

It allows you to simplify this complexity by providing a simplified interface to all these subsystems. This simplification may in some cases reduce the flexibility of the underlying classes, but usually provides all the function needed for all but the most sophisticated users. These users can still, of course, access the underlying classes and methods.

Fortunately, we don’t have to write a complex system to provide an example of where a Façade can be useful. Python provides a set of classes that connect to databases using an interface called ODBC (Open Database Connectivity). You can connect to any database for which the manufacturer has provided a ODBC connection class -- almost every database on the market.

A database is essentially a set of tables in which columns in one table are related to some data in another table, such as stores, foods and prices. You create queries that produce results computed from those tables. The queries are written in SQL, the standard structured query language, and the results are usually a new table containing rows computed from the other tables.

The Python interface is more procedural than object oriented and can be simplified using a few objects based on the four data boxes shown above.

We started down this path using the popular MySQL database, a full-fledged industrial strength database that you can download and use for free. You can install it and run it on your laptop or run it on a server where a number of people can access the data. However, for simpler cases where you don’t need to share the data, a colleague suggested we also should look at the Sqlite database. In both cases, these databases run on nearly all computing platforms and Python provides drivers to connect to them. Each Sqlite database is a separate file on your computer, it is not embedded in some complex management system and you can easily send that file to other when that is useful.

The Grocery Database

For this example, we are going to create a database of three tables: the stores, the foods and the prices at each store for each food. We created these simple tables in the MySQL Workbench, or the analogous SqliteStudio program.

Our Database class, then, simply wraps a database connection and keeps the database cursor variable.

class Database():
    def __init__(self, *args):
        self._db = MySQLdb.connect(args[0], args[1], 
                                   args[2], args[3])
        self.host=args[0]   # host
        self.userid=args[1] # userid
        self.pwd = args[2]  # password
        self.dbname = args[3] # database name
        self._cursor = self._db.cursor() # save the cursor

    def commit(self):
        self._db.commit()

    def create(self, dbname):
        self._cursor.execute("drop database if exists "+dbname)
        self._cursor.execute("Create database "+ dbname)
        self._dbname = dbname
        self._db=MySQLdb.connect(self.host, self.userid, 
                                 self.pwd, dbname)
        self._cursor.execute("use "+dbname)
        self._cursor= self._db.cursor()

    def getName(self):
        return self._dbname

    @property
    def cursor(self):
        return self._cursor

    def getTables(self):
        tquery = Query(self.cursor, "show tables")
        res = tquery.execute()
        rows = res.getRows()
        # create array of table objects
        self.tables = []
        for r in rows:
            self.tables.append(Table(self._db, r))
        return self.tables

   We also create a Table object which represents a table and its columns.

class Table():
    def __init__(self, db, name):
        self.cursor = db.cursor()
        self.db = db
        self.tname = name   # first of tuple
        self.colList=[]     # list of column names generated
        self._primarystring = ""

    def getColumns(self):
        print(self.tname)
        sql="show columns from "+ "".join(self.tname)
        tquery = Query(self.cursor, sql)
        self.columns = tquery.execute().getRows()
        return self.columns

    @property
    def name(self):     # gets table name
        return self.tname

    # get contents of a column
    def getColumnContents(self, cname):
        query = Query(self.cursor, "select " + cname + " from "
                      + self.tname[0])
        results = query.execute()
        return results.getRows()

And, you will note that within these two classes we have illustrated the use of the Query and Results objects.  Our Table object also contains methods for creating columns and populating the table as well.

The Grocery app

With this in hand, we can now create an application that accesses the groceries database and displays the tables, columns, contents and even runs a query to show the sorted prices for any given item.

Note that while these are the names of real grocery stores, the grocery prices are totally fictitious.

The SQLite Version

There are only very small changes in the Database and Table code for Sqlite. And to illustrate the great power of classes, we can create a derived class from Database with some slight changes to the methods. For example, connecting to a sqlite database just means specifying a filename. And Sqlite does not have a “show tables” SQL command, but you can still get the table names from a master table within the database file:

class SqltDatabase(Database):
    def __init__(self, *args):
        self._db = sqlite3.connect(args[0])
        self._dbname = args[0]
        self._cursor = self._db.cursor()

    def commit(self):
        self._db.commit()

    def create(self, dbname):
        pass
    def getTables(self):
        tbQuery = Query(self.cursor,
                 "select name from sqlite_master where type='table'")

        # create array of table objects
        rows = tbQuery.execute().getRows()
        for r in rows:
            self.tables.append(SqltTable(self._db, r))
        return self.tables

Changes to the derived SqltTable class are likewise pretty simple, and the Groceries app using Sqlite runs and looks exactly the same as the one above for the MySQL version.

So, in conclusion, our Façade pattern allows us to represent database access in four classes, and to access other database my at most require small changes in a derived Database (and maybe Table) class.

Code on Github

You can access all of the code for these examples on Github, under jameswcooper/newsletter/Façade.

The programs are

• DBObjects.py – all 4 classes for both MySQL and Sqlite databases    

• GroceryDisplay.py – Displays the grocery tables using the MySQL database

• MakeDatabase.py – Creates the groceries database in MySQL

• MakeSQLite.py – Creates the groceries database in SQLite

• GroceryDispLite.py – Displays the grocery tables using the SQLite database

• Groceries.db – The Sqlite database file itself.

Subscribe to this newsletter so you don’t miss a single Monday’s issue!