Let us first define for ourselves how we want to use an Entity class, because the interface we create must match as closely as possible the things we would like to express in our code. The following example shows what we have in mind (available as carexample.py):
Chapter5/carexample.py
from entity import Entity
class Car(Entity): pass
Car.threadinit('c:/tmp/cardatabase.db')
Car.inittable(make="",model="",licenseplate="unique")
mycar = Car(make="Volvo",model="C30",licenseplate="12-abc-3")
yourcar = Car(make="Renault",model="Twingo",licenseplate="ab-cd-12")
allcars = Car.list()
for id in allcars:
car=Car(id=id)
print(car.make, car.model, car.licenseplate)
The idea is to create a Car class that is a subclass of Entity. We therefore have to take the following steps:
- Import the
Entityclass from theentitymodule. - Define the
Carclass. The body of this class is completely empty as we simply inherit all functionality from theEntityclass. We could, of course, augment this with specific functionality, but in general, this shouldn't be necessary. - Initialize a connection to the database. Before we can work with the
Carinstances, an application has to initialize a connection to the database for each thread. In this example, we do not create extra threads, so there is just the single main thread of the application that needs a connection to the database. We create one here with thethreadinit()method (highlighted). - Make sure an appropriate table with the necessary columns exists in the database. Therefore, we call the
inittable()method with arguments that specify the attributes of our entity with possibly extra information on how to define them as columns in a database table. Here we define three columns:make, model, andlicenseplate. Remember that SQLite doesn't need explicit typing, somakeandmodelare passed as arguments with just an empty string as the value. Thelicenseplateattribute, however, is adorned with auniqueconstraint in this example.
Now we can work with Car instances, as illustrated in the lines that create two different objects or in the last few lines that retrieve the IDs of all Car records in the database and instantiate Car instances with those IDs to print the various attributes of a Car.
That is the way we would like it to work. The next step is to implement this.
The previous example showed how we could derive the Car class from Entity and use it. But what does that Entity class look like?
The definition for the Entity class starts off with defining a class variable threadlocal and a class method threadinit() to initialize this variable with an object that holds data that is local to each thread (the full code is available as entity.py).
If this threadlocal object does not yet have a connection attribute, a new connection to the database is created (highlighted) and we configure this connection by setting its row_factory attribute to sqlite.Row, as this will enable us to access columns in the results by name.
We also execute a single pragma foreign_keys=1 statement to enable the enforcing of foreign keys. As we will see, when we discuss the implementation of relations, this is vital in maintaining a database without dangling references. This pragma must be set for each connection separately; therefore, we put it in the thread initialization method.
Chapter5/entity.py
import sqlite3 as sqlite
import threading
class Entity:
threadlocal = threading.local()
@classmethod
def threadinit(cls,db):
if not hasattr(cls.threadlocal,'connection') or
cls.threadlocal.connection is None:
cls.threadlocal.connection=sqlite.connect(db)
cls.threadlocal.connection.row_factory = sqlite.Row
cls.threadlocal.connection.execute("pragma foreign_
keys=1")
else:
pass #print('threadinit thread has a connection
object already')
Next is the inittable() method. This should be called once to verify that the table necessary for this entity already exists or to define a table with suitable columns if it doesn't. It takes any number of keyword arguments. The names of the keywords correspond to the names of the columns and the value of such a keyword may be an empty string or a string with additional attributes for the column, for example, unique or an explicit type like float.
Note
Although SQLite allows you to store a value of any type in a column, you may still define a type. This type (or more accurately, affinity) is what SQLite tries to convert a value to when it is stored in a column. If it doesn't succeed, the value is stored as is. Defining a column as float, for example, may save a lot of space. More on these affinities can be found on http://www.sqlite.org/datatype3.html.
Chapter5/entity.py
@classmethod
def inittable(cls,**kw):
cls.columns=kw
connection=cls.threadlocal.connection
coldefs=",".join(k+' '+v for k,v in kw.items())
sql="create table if not exists %s (%s_id integer primary
key autoincrement, %s);"%(cls.__name__,cls.__name__,coldefs)
connection.execute(sql)
connection.commit()
The column definitions are stored in the columns class variable for later use by the __init__() method and joined together to a single string. This string, together with the name of the class (available in the __name__ attribute of a (possibly derived) class) is then used to compose a SQL statement to create a table (highlighted).
Besides the columns that we defined based on the keyword arguments, we can also create a primary key column that will be filled with a unique integer automatically. This way, we ensure that we can refer to each individual row in the table later on, for example, from a bridging table that defines a relation.
When we take our previous car example, we see that a Python statement like:
Car.inittable(make="",model="",licenseplate="unique")
Is converted to the following SQL statement:
create table if not exists Car ( Car_id integer primary key autoincrement, make , licenseplate unique, model );
Note that the order in which we pass the keyword arguments to the inittable() method is not necessarily preserved as these arguments are stored in a dict object, and regular dict objects do not preserve the order of their keys.
Note
Sometimes preserving the order of the keys in a dictionary is very desirable. In this case, column order doesn't matter much, but Python does have an OrderedDict class available in its collections module (see http://docs.python.org/library/collections.html#collections.OrderedDict) that we could have used. However, this would prevent us from using keywords to define each column.
Also note that there isn't any form of sanity checking implemented: anything may be passed as a value for one of the column definitions. Judging whether that is anything sensible is left to SQLite when we pass the SQL statement to the database engine with the execute() method.
This method will raise an sqlite3.OperationalError if there is a syntax error in the SQL statement. However, many issues are simply ignored. If we pass an argument like licenseplate="foo", it would happily proceed, assuming foo to be a type it doesn't recognize, so it is simply ignored! If the execution didn't raise an exception, we finish by committing our changes to the database.
Silently ignoring things passed as arguments is not considered a good habit. Without explicit checking, a developer might not even know he/she has done something wrong, something that might backfire later.
How would you implement code to restrict the value to a limited set of directives?
Hint: Types and constraints in a SQL column definition mostly consist of single words. You could check each word against a list of allowable types, for example.