Let's see how this is done:
Chapter7/entity.py
class Attribute:
def __init__(self, unique=False, notnull=False,
default=None, affinity=None, validate=None,
displayname=None, primary=False):
self.coldef = (
(affinity+' ' if not affinity is None else '') +
('unique ' if unique else '') +
('not null ' if notnull else '') +
('default %s '%default if not default is None else '')
)
self.validate = validate
self.displayname = displayname
self.primary = primary
The Attribute class is mainly a vehicle to store information about attributes in a structured way. We could have used strings and parsed them, but by using an Attribute class, it is possible to explicitly recognize class variables that are meant to be attributes that are stored as database columns. That way, we can still define class variables that have a different purpose. Also, writing a parser is a lot of work, while checking parameters is a lot easier.
The highlighted code shows that most parameters are used to create a string that can be used as a column definition that is part of a create table statement. The other parameters (displayname and validate) are just stored as is for future reference:
Chapter7/entity.py
class MetaEntity(type):
@classmethod
def __prepare__(metaclass, classname, baseclasses, **kwds):
return collections.OrderedDict()
@staticmethod
def findattr(classes,attribute):
a=None
for c in classes:
if hasattr(c,attribute):
a=getattr(c,attribute)
break
if a is None:
for c in classes:
a = MetaEntity.findattr(c.__bases__,attribute)
if not a is None:
break
return a
def __new__(metaclass,classname,baseclasses,classdict):
def connect(cls):
if not hasattr(cls._local,'conn'):
cls._local.conn=sqlite.connect(cls._database)
cls._local.conn.execute('pragma foreign_keys = 1')
cls._local.conn.row_factory = sqlite.Row
return cls._local.conn
entitydefinition = False
if len(baseclasses):
if not 'database' in classdict:
classdict['_database']=MetaEntity.findattr(
baseclasses,'database')
if classdict['_database'] is None:
raise AttributeError(
'''subclass of AbstractEntity has no
database class variable''')
entitydefinition=True
if not '_local' in classdict:
classdict['_local']=MetaEntity.findattr(
baseclasses,'_local')
classdict['_connect']=classmethod(connect)
classdict['columns']=[
k for k,v in classdict.items()
if type(v) == Attribute]
classdict['sortorder']=[]
classdict['displaynames']={
k:v.displayname if v.displayname else k
for k,v in classdict.items()
if type(v) == Attribute}
classdict['validators']={
k:v.validate for k,v in classdict.items()
if type(v) == Attribute
and not v.validate is None}
classdict['displaynames']['id']='id'
PrimaryKey = Attribute()
PrimaryKey.coldef = 'integer primary key '
PrimaryKey.coldef+= 'autoincrement'
if entitydefinition:
sql = 'create table if not exists '
sql+= classname +' ('
sql+= ", ".join([k+' '+v.coldef
for k,v in [('id',PrimaryKey)]
+list(classdict.items())
if type(v) == Attribute])
sql+= ')'
conn = sqlite.connect(classdict['_database'])
conn.execute(sql)
for k,v in classdict.items():
if type(v) == Attribute:
if v.primary:
classdict['primary']=property(
lambda self:getattr(self,k))
classdict['primaryname']=k
break
if not 'primary' in classdict:
classdict['primary']=property(
lambda self:getattr(self,'id'))
classdict['primaryname']='id'
return type.__new__(metaclass,
classname,baseclasses,classdict)
The metaclass we will use to synchronize the creation of database tables and the creation of entity classes is called MetaEntity. Its __new__() method is where all the action takes place, but there is one important additional method: __prepare__().
The __prepare__() method is called to provide an object that can be used as a class dictionary. The default, as provided by the type class, just returns a regular Python dict object. Here we return an ordered dictionary, a dictionary that will remember the order of its keys as they are entered. This will enable us to use the order in which class variables are declared, for example, to use this as the default order to display columns. Without an ordered dictionary, we wouldn't have any control and would have to supply separate information.
The __new__() method first checks if we are a subclass of MetaEntity by checking whether the list of base classes is non zero (highlighted) as MetaEntity itself does not have a database backend.
Then it checks if the database class variable is defined. If not, we are a specific entity that has a database backend and we try to locate the database class variable in one of our super classes. If we find it, we store it locally; if not, we raise an exception because we cannot function without a reference to a database.
The AbstractEntity class will have a _local class variable defined that holds a reference to thread local storage, and subclasses will have their own _local variable that points to the same thread local storage.
The next step is to gather all sorts of information from all the class variables that refer to Attribute instances. First we collect a list of column names (highlighted). Remember that because we caused the class dictionary to be an ordered dictionary, these column names will be in the order they were defined.
Likewise, we define a list of display names. If any attribute does not have a displayname attribute, its display name will be identical to its column name. We also construct a dictionary of validators, that is, a dictionary indexed by column name that holds a function to validate any value before it is assigned to a column.
Every entity will have an id attribute (and a corresponding column in the database table) that is created automatically without it being explicitly defined. Therefore, we add its displayname separately and construct a special Attribute instance (highlighted).
This coldef attribute of this special Attribute together with the coldef attributes of the other Attribute instances will then be used to compose an SQL statement that will create a table with the proper column definitions.
Finally, we pass the altered and augmented class dictionary together with the original list of base classes and the class name to the __new__() method of the type class which will take care of the actual construction of the class.
The rest of the functionality of any Entity is not implemented by its metaclass, but in the regular way, that is, by providing methods in the class that all entities should derive from: AbstractEntity:
Chapter7/entity.py
class AbstractEntity(metaclass=MetaEntity): _local = threading.local() @classmethod def listids(cls,pattern=None,sortorder=None): sql = 'select id from %s'%(cls.__name__,) args = [] if not pattern is None and len(pattern)>0: for s in pattern: if not (s[0] in cls.columns or s[0]=='id'): raise TypeError('unknown column '+s[0]) sql += " where " sql += " and ".join("%s like ?"%s[0] for s in pattern) args+= [s[1] for s in pattern] if sortorder is None: if not cls.sortorder is None : sortorder = cls.sortorder else: for s in sortorder: if not (s[0] in cls.columns or s[0]=='id'): raise TypeError('unknown column '+s[0]) if not s[1] in ('asc', 'desc') : raise TypeError('illegal sort argument'+s[1]) if not (sortorder is None or len(sortorder) == 0): sql += ' order by ' sql += ','.join(s[0]+' '+s[1] for s in sortorder) cursor=cls._connect().cursor() cursor.execute(sql,args) return [r['id'] for r in cursor] @classmethod def list(cls,pattern=None,sortorder=None): return [cls(id=id) for id in cls.listids( sortorder=sortorder,pattern=pattern)] @classmethod def getcolumnvalues(cls,column): if not column in cls.columns : raise KeyError('unknown column '+column) sql ="select %s from %s order by lower(%s)" sql%=(column,cls.__name__,column) cursor=cls._connect().cursor() cursor.execute(sql) return [r[0] for r in cursor.fetchall()] def __str__(self): return '<'+self.__class__.__name__+': '+", ".join( ["%s=%s"%(displayname, getattr(self,column)) for column,displayname in self.displaynames.items()])+'>' def __repr__(self): return self.__class__.__name__+"(id="+str(self.id)+")" def __setattr__(self,name,value): if name in self.validators : if not self.validators[name](value): raise AttributeError( "assignment to "+name+" does not validate") object.__setattr__(self,name,value) def __init__(self,**kw): if 'id' in kw: if len(kw)>1 : raise AttributeError('extra keywords besides id') sql = 'select * from %s where id = ?' sql%= self.__class__.__name__ cursor = self._connect().cursor() cursor.execute(sql,(kw['id'],)) r=cursor.fetchone() for c in self.columns: setattr(self,c,r[c]) self.id = kw['id'] else: rels={} attr={} for col in kw: if not col in self.columns: rels[col]=kw[col] else: attr[col]=kw[col] name = self.__class__.__name__ cols = ",".join(attr.keys()) qmarks = ",".join(['?']*len(attr)) if len(cols): sql = 'insert into %s (%s) values (%s)' sql%= (name,cols,qmarks) else: sql = 'insert into %s default values'%name with self._connect() as conn: cursor = conn.cursor() cursor.execute(sql,tuple(attr.values())) self.id = cursor.lastrowid def delete(self): sql = 'delete from %s where id = ?' sql%= self.__class__.__name__ with self._connect() as conn: cursor = conn.cursor() cursor.execute(sql,(self.id,)) def update(self,**kw): for k,v in kw.items(): setattr(self,k,v) sets = [] vals = [] for c in self.columns: if not c == 'id': sets.append(c+'=?') vals.append(getattr(self,c)) table = self.__class__.__name__ sql = 'update %s set %s where id = ?' sql%=(table,",".join(sets)) vals.append(self.id) with self._connect() as conn: cursor = conn.cursor() cursor.execute(sql,vals)
AbstractEntity provides a number of methods to provide CRUD functionality:
- A constructor to refer to the existing entities in the database or to create new ones
list()andlistids(), to find instances that match certain criteriaupdate(), to synchronize changed attributes of an entity with the databasedelete(), to delete an entity from the database
It also defines the 'special' Python methods __str__(), __repr__(), and __setattr__() to render an entity in a legible way and to validate the assignment of a value to an attribute.
Obviously, AbstractEntity refers to the MetaEntity metaclass (highlighted). It also defines a _local class variable that refers to thread local storage. The MetaEntity class will make sure this reference (but not its contents) are copied to all subclasses for fast access. By defining it here, we will make sure that all subclasses refer to the same thread local storage, and more importantly, will use the same connection to the database for each thread instead of using a separate database connection for each different entity.
The listids() class method will return a list of IDs of entities that match the criteria in its pattern argument or the IDs of all the entities if no criteria were given. It will use the sortorder argument to return the list of IDs in the required order. Both sortorder and pattern are a list of tuples, each tuple having the column name as its first item. The second item will be a string to match against for the pattern argument or either asc or desc for the sortorder argument, signifying an ascending or descending sort respectively.
The SQL statement to retrieve any matching IDs is constructed by first creating the select part (highlighted), as this will be the same irrespective of any additional restrictions. Next, we check if there are any pattern components specified, and if so, add a where clause with matching parts for each pattern item. The matches we specify use the SQL like operator, which is normally only defined for strings, but SQLite will convert any argument to a string if we use the like operator. Using the like operator will allow us to use SQL wildcards (for example, % ).
The next stage is to check if there were any sort order items specified in the sortorder argument. If not, we use the default sort order stored in the sortorder class variable (which in our current implementation will still be None). If there are items specified, we add an order by clause and add specifications for each sort item. A typical SQL statement will look something like select id from atable where col1 like ? and col2 like ? order by col1 asc.
Finally, we use the _connect() method (that was added by the metaclass) to retrieve a database connection (and establish one, if needed) that we can use to execute the SQL query with and retrieve the list of IDs.
The list() method bears a close resemblance to the listids() method and takes the same arguments. It will, however, return a list of entity instances rather than a list of just the IDs by calling the entity's constructor with each ID as an argument. This is convenient if that was what we wanted to do with those IDs anyway, but a list of IDs is often easier to manipulate. We therefore provide both methods.
Being able to retrieve lists of entities is nice, but we must also have a means to create a new instance and to retrieve all information associated with a record with a known ID. That is where the entity's constructor, in the form of the __init__() method, comes in.
Note
Strictly speaking, __init__() isn't a constructor but a method that initializes an instance after it is constructed.
If a single ID keyword argument is passed to __init__() (highlighted), all columns of the record matching that ID are retrieved and the corresponding arguments (that is, attributes with the same name as a column) are set using the setattr() built-in function.
If more than one keyword argument is passed to __init__(), each should be the name of a defined column. If not, an exception is raised. Otherwise, the keywords and their values are used to construct an insert statement. If there are more columns defined than there are keywords given, this will result in default values to be inserted. The default is usually NULL unless a default argument was specified for the column when the Entity was defined. If NULL is the default and the column has a non null constraint, an exception will be raised.
Because ID columns are defined as autoincrement columns and we do not specify an explicit ID value, the ID value will be equal to the rowid, a value we retrieve as the lastrowid attribute of the cursor object (highlighted).
Retrieving a list of IDs first and then instantiating entities means that we have to retrieve all attributes of each entity with a separate SQL statement. This might have a negative impact on performance if the list of entities is large.
Create a variant of the list() method that will not convert the selected IDs to entity instances one-by one, but will use a single select statement to retrieve all attributes and use those to instantiate entities.