Database Hints and Tricks

While you can deal with the data as tuples, it's not always the most convenient technique. The issue with tuples is that you need to use a numeric index to retrieve elements. This can make your code unreadable, as this first coding exercise will show. Create datatest.py as shown:

"""
Demonstration of indexed access to data elements.
"""
import mysql.connector
from database import login_info

db = mysql.connector.Connect(**login_info)
cursor = db.cursor()

fmt = "{0:10} {1:10} {2:6}"
print(fmt.format("Animal", "Weight", "Family"))
print("-"*28)
cursor.execute("SELECT * FROM animal")
for animal in cursor.fetchall():
    print(fmt.format(animal[1], animal[3], animal[2]))

This code produces a listing of the animals' names, weights, and families as you might expect:

Animal     Weight Family
----------------------------
Ellie        2350 Elephant
Gerald       1400 Gnu
Gerald        940 Giraffe
Leonard       280 Leopard
Sam            24 Snake
Steve          35 Snake
Zorro         340 Zebra

The code uses a cursor's execute() method to request all animal data, and then iterates over the list of tuples returned by the cursor's fetchall() method. But looking at the last line of the code, it isn't at all obvious that animal[1] is the animal's name, animal[3] is its weight, and animal[2] represents the animal family. Since you know that readability is one of the most important aspects of code, it would be good to allow access to data elements by name. We can do that using various Python features.

One way to do it that will immediately improve the readability of our code, is to use an unpacking assignment in the for loop that iterates over the result set. At the same time, we'll change the SQL to explicitly retrieve only the fields we want. Each element of the (three-element) tuple is stored in its own variable, thanks to the unpacking assignment. This makes the code a bit easier to read, but it does not effect its result at all. This updated version of the code should produce exactly the same output. In datatest.py, type the code below as shown:

"""
Demonstration of indexed access to data elements.
"""
import mysql.connector
from database import login_info

db = mysql.connector.Connect(**login_info)
cursor = db.cursor()

fmt = "{0:10} {1:106} {2:610}"
print(fmt.format("Animal", "Weight", "Family"))
print("-"*28)
cursor.execute("SELECT * FROM animal")
for animal in cursor.fetchall():
    print(fmt.format(animal[1], animal[3], animal[2]))

cursor.execute("SELECT name, weight, family FROM animal")
for name, weight, family in cursor.fetchall():
    print(fmt.format(name, weight, family))

Save and run it. You'll see the same results.

Another way to make the code more comprehensible is to create an object for each row that has attributes with the same names as the columns, to hold the data elements retrieved from the database. Then we'll begin to see that the rows returned from a query are actually data objects. Create animal.py as shown:

"""
animal.py: a class to represent an animal in the database
"""

class Animal:

    def __init__(self, id, name, family, weight):
        self.id = id
        self.name = name
        self.family = family
        self.weight = weight

This class has no tests. We need to write some, quickly! Instead of getting into all the formality of unit tests, we can include a basic self-test. This will allow us to tailor the way an Animal appears when printed, by providing a __repr__() method to meet our own specifications. Modify animal.py as shown:

"""
animal.py: a class to represent an animal in the database
"""

class Animal:

    def __init__(self, id, name, family, weight):
        self.id = id
        self.name = name
        self.family = family
        self.weight = weight

    def __repr__(self):
        return "Animal({0}, '{1}', '{2}', {3})".format(
                self.id, self.name, self.family, self.weight)

if __name__ == "__main__":
    import mysql.connector
    from database import login_info
    db = mysql.connector.Connect(**login_info)
    cursor = db.cursor()
    cursor.execute("SELECT id, name, family, weight FROM animal")
    animals = [Animal(*row) for row in cursor.fetchall()]
    from pprint import pprint
    pprint(animals)

Save and run it. You'll see this:

[Animal(1, 'Ellie', 'Elephant', 2350),
 Animal(2, 'Gerald', 'Gnu', 1400),
 Animal(3, 'Gerald', 'Giraffe', 940),
 Animal(4, 'Leonard', 'Leopard', 280),
 Animal(5, 'Sam', 'Snake', 24),
 Animal(6, 'Steve', 'Snake', 35),
 Animal(7, 'Zorro', 'Zebra', 340)]

Take a closer look:

"""
animal.py: a class to represent an animal in the database
"""
class Animal:

    def __init__(self, id, name, family, weight):
        self.id = id
        self.name = name
        self.family = family
        self.weight = weight

    def __repr__(self):
        return "Animal({0}, '{1}', '{2}', {3})".format(
                self.id, self.name, self.family, self.weight)

if __name__ == "__main__":
    import mysql.connector
    from database import login_info
    db = mysql.connector.Connect(**login_info)
    cursor = db.cursor()
    cursor.execute("SELECT id, name, family, weight FROM animal")
    animals = [Animal(*row) for row in cursor.fetchall()]
    from pprint import pprint
    pprint(animals)

The program now defines the representation of an Animal by implementing a __repr__() method for Animal. The animals list is created in a list comprehension that provides individual arguments to the Animal creation using Python's "*" feature. As we learned earlier, Python's "*" feature takes a tuple or list and turns it into a series of individual arguments, as required by the Animal class's __init__() method. The pprint() function, imported from the pprint module, displays the representation of each list element by calling the __repr__() method.

This test isn't perfect, but it does cover most basic functionality. A silently-passing test is usually better. (Can you think of a way to silence the testing? Consider the exec() function). The test code also shows you one way to create an instance of this class from a row in a database table. Keep in mind that this method depends on the precise order of the fields in the database table, which isn't always a given. Someone might change the structure of the database without your knowledge, which could cause problems.

You can go further by defining a function that returns a tailored class, of which you can create instances to represent each row. To create the class, you would call:

RC = RecordClass("animal", "id name family weight")

Once you create the class, you would create instances of that class by calling the class with values for each of the named columns as follows:

for row in cursor.fetchall(): row_record = RC(*row)

You have the power to go in many different directions with Python objects. As a relative newcomer to the programming scene, you might sometimes find yourself almost paralyzed by the limitless number of options you have. Don't panic. In almost every case, the best way to deal with the quandary is to go ahead and write something. If it needs to be changed later, that's fine—your tests should save you from big mistakes.

Going further, you'll consider the best way to create the Animal objects, and which methods they should have. And what kind of objects should those methods to return? It seems like it would be a good idea to have read() return an Animal instance, but is it appropriate for a method of Animal to return an animal? And what arguments should read() be capable of accepting?

Should readAnimal() be a function instead of an Animal method? How about write()? Or should that be save()? Is there any really important value for that method to return? Maybe the names of the written fields?

Of course, you don't always want every column of every row. Suppose you only wanted to retrieve certain columns; would it help to keep the names of those columns somewhere, and build the column names into the query somehow? That way you could have queries that didn't bring unnecessary data into memory, for example. This is not only possible, it's what we're going to do next!

Python objects have a defined life-cycle which generally begins by calling the type's __new__() method, then calling the __init__() method of the "instance" returned by that. But most objects' behavior is determined by the methods you write. You can write their __new__() and __init__() methods if you like. Once you know what you're doing, you can pretty much install your own logic and have objects behave according to your plan.

So, how would you like a query to behave? Do you want your query to be on just a single table? If not, you will need to generate JOINs—if there are n tables, there must be n-1 JOIN conditions. Do you want to be able to determine which columns from which of the joined tables should be read in, and updated when written? Do you want to be able to read and write those objects, at least by primary key?

There are some generic solutions to these problems, but those frameworks can be intimidating at first. With your knowledge of Python, you already understand some techniques that make the database data easier to handle. It's good to have a range of techniques at your command for different situations. In order to implement those techniques, you'll need to understand how Python can be used to create SQL statements. Check it out:

SELECT column, ... FROM relation WHERE conditions

The relation being queried is often a table, though you can query a join as well. The column list in the statement may consist of simple column names or qualified names. If two or more of the tables in a query possess columns with the same name, these columns can only be referred to using tablename.columname syntax. You don't necessarily need all columns of a table each time you reference a row, so you can make a case for having several different object types for a given table, each using a different set of columns.

Suppose cols is the list of column names you want, table is the name of the table, and there are no other conditions on the data. The SQL statement you'd need to start with is:

"SELECT {0} FROM {1}".format(", ".join( cols ), table)

The rows returned by this query have len(cols) elements, and the name of column n is cols[n].

In this next example, you'll generate the SQL from its component parts, and have a chance to observe how queries can be built. Type the code below into an interactive interpreter session, as shown:

>>> cols ="id name family".split()
>>> ", ".join(cols)
'id, name, family'
>>> table = "animal"
>>> "SELECT {0} FROM {1}".format(", ".join(cols), table)
'SELECT id, name, family FROM animal'
>>> condition1 = "id=7"
>>> conditions = [condition1]
>>> " AND ".join(conditions)
'id=7'
>>> conditions.append("family IS NOT NULL")
>>> " AND ".join(conditions)
'id=7 AND family IS NOT NULL'
>>> "SELECT {0} FROM {1} WHERE {2}".format(
... ", ".join(cols), table, " AND ".join(conditions))
'SELECT id, name, family FROM animal WHERE id=7 AND family IS NOT NULL'
>>>

Let's take a closer look at that last statement:

"SELECT {0} FROM {1} WHERE {2}".format(", ".join(cols), table, " AND ".join(conditions))

The result of this expression is:

'SELECT id, name, family FROM animal WHERE id=7 AND family IS NOT NULL'

When a query joins multiple tables, there is always a chance that a name conflict will occur—the same column name might be defined in multiple tables. If you have enough information about the database, you can predict and avoid such conflicts by using the fully-qualified name table.column. The SQL interpreter will tell you when you make mistakes like this.

Let's say you have the column names and corresponding data items in lists. You can create a Python object for each of the rows retrieved with attributes of the same names as the columns (the column names must be named in acceptable Python style for the scheme to work properly). Earlier, we looked at how attribute assignment works on Python objects. Don't worry if your memory is a bit fuzzy on this. Just focus on this part: if x is some Python object, then the assignment x.name = value is pretty much equivalent to x.__dict__['name'] = value, which can also be expressed as setattr(x, 'name', value).

Now, suppose the column names are "id," "name," and "email," and that you have a (three-element) data row holding a value for each attribute. There are various ways to modify a Python object. The object must be an instance of some user-defined class though, because built-in classes like int and list use a different mechanism to look up attributes. Type the code below as shown into an interactive interpreter console:

>>> COLS = "id name email".split()
>>> data = (1, "Steve Holden", "steve@holdenweb.com")
>>> class row:
...     pass
...
>>> r1 = row()
>>> for col, d in zip(COLS, data):
...   setattr(r1, col, d)
...
>>> dir(r1)
['__doc__', '__module__', 'email', 'id', 'name']
>>> r1.id, r1.name, r1.email
(1, 'Steve Holden', 'steve@holdenweb.com')
>>>

So now you know how to inject arbitrary attributes into a Python object. Writing three lines of code to create the object you want is pretty economical. But when the __dict__ attribute is actually a standard Python dict, it has an update() method, which you can call with either a dict or a sequence of (key, value) pairs as its sole argument. The arguments are added to the original dict, overwriting the values of existing keys and adding new ones as necessary. This means you can achieve the same result even more efficiently. Continue your previous interactive session, typing the code below as shown:

>>> zip(COLS, data)
<zip object at 0x0116F738>
>>> dict(zip(COLS, data))
{'email': 'steve@holdenweb.com', 'id': 1, 'name': 'Steve Holden'}
>>> r2 = row()
>>> r2.__dict__.update(dict(zip(COLS, data)))
>>> r2.email
'steve@holdenweb.com'
>>> dir(r2)
['__doc__', '__module__', 'email', 'id', 'name']
>>> r3 = row()
>>> r3.__dict__.update(zip(COLS, data))
>>> dir(r3)
['__doc__', '__module__', 'email', 'id', 'name']>>>

As the r3 example above demonstrates, the dict.update() method also accepts a sequence of (name, value) tuples as an argument, avoiding the unnecessary creation of a dict. This type of manipulation is common in some applications.

Armed with this knowledge, you can now write a class with a constructor call that takes the column names and data items as arguments, and returns an object with the attributes set. Keep in mind that database column names do not always follow exactly the same rules as Python names, so you might find tables that don't adapt well to this technique. There are often remedies you can apply at the database level to compensate for poor naming choices, but that topic is beyond the scope of this course.