Because the primary key is unique, this will match at most a single record, something that is verified after we retrieve the matching records. If we didn't fetch exactly one (1) record, an exception is raised (highlighted).

If everything went well, we initialize the attributes of the instance we are creating with the built-in setattr() function. The columns of the record we retrieved can be accessed by name because we initialized the row_factory attribute of the connection object to a sqlite3.Row. We also stored the names of the columns in the columns class variable and this lets us initialize the instance's attributes with the values of the corresponding column names (highlighted).

Creating a Car instance with:

Car(id=1)

Will result in a SQL statement like this:

select * from Car where Car_id = ?

Where the question mark is a placeholder for the actual value that is passed to the execute() method.

The second branch of the code (starting at the else clause) is executed if no id argument was present. In this case, we separate the keyword names and values and set the attributes of the instance we are creating. The keyword names and values are then used to construct an SQL statement to insert a new row in the table associated with this Entity (highlighted). For example:

Car(make="Volvo", model="C30", licenseplate="12-abc-3")

Will give us:

insert into Car (make,model,licenseplate) values(?,?,?)

The question marks are again placeholders for the values we pass to the execute() method.

If calling the execute() method (highlighted) went well, we initialize the id attribute of the instance we are creating with the value of the lastrowid attribute. Because we defined the primary key as a primary key integer autoincrement column and did not specify it in the insert statement, the primary key will hold a new unique integer and this integer is available as the lastrowid attribute.

Any sqlite3.IntegrityError that might be raised due to the violation of a uniqueness constraint is caught and re-raised as a ValueError with slightly more meaningful text.

The update() method is used to synchronize an instance with the database. It can be used in two ways: we can alter any attributes of an instance first and then call update(), or we may pass keyword arguments to update() to let update() alter the corresponding attributes and synchronize the instance to the database. These two ways may even be combined. Either way, the database will hold the most current values of all attributes corresponding to a column once the update() returns. The following two pieces of code are therefore equivalent:

car.update(make='Peugeot')

And:

car.make='Peugeot'
car.update()

Any keyword arguments we pass to update() should match a column name, otherwise an exception is raised (highlighted).

Chapter5/entity.py

def update(self,**kw):
		for k in kw:
			if not k in self.__class__.columns :
				raise KeyError("unknown column")
		for k,v in kw.items():
			setattr(self,k,v)
		updates=[]
		values=[]
		for k in self.columns:
			updates.append("%s=?"%k)
			values.append(getattr(self,k))
		updates=",".join(updates)
		values.append(self.id)
		sql="update %s set %s where %s_id = ?"%(
		self.__class__.__name__, updates, self.__class__.__name__)
		with self.threadlocal.connection as conn:
			cursor=conn.cursor()
			cursor.execute(sql, values)
			if cursor.rowcount != 1 :
				raise ValueError(
				"number of updated entities not 1 (%d)" %
				cursor.rowcount)

The column names and the values of the corresponding attributes are then used to construct an SQL statement to update records with these values, but only for the single record whose primary key matches the ID of the instance we are updating. The SQL statement might look like this:

update Car set make=?, model=?, licenseplate=? where Car_id = ?

The question marks again are placeholders for the values we pass to the execute() method.

After we execute this statement, we do a sanity check by validating that the number of affected records is indeed one. Just as for an insert statement, this number is available as the rowcount attribute of the cursor object after an update statement (highlighted).

Deleting an instance is implemented by the delete() method of the Entity class and consists primarily of composing an SQL statement that will delete the record with a primary key equal to the id attribute of the instance. The resulting SQL looks like this:

delete from Car where Car_id = ?

Just like in the update() method, we end with a sanity check to verify that just a single record was affected (highlighted). Note that delete() will only remove the record in the database, not the Python instance it is called on. If nothing references this object instance, it will be automatically removed by the Python's garbage collector:

Chapter5/entity.py

def delete(self):
		sql="delete from %s where %s_id = ?"%(
		self.__class__.__name__,self.__class__.__name__)
		with self.threadlocal.connection as conn:
				cursor=conn.cursor()
				cursor.execute(sql,(self.id,))
				if cursor.rowcount != 1 :
					raise ValueError(
					"number of deleted entities not 1 (%d)" %
					cursor.rowcount)

The final method we encounter is the class method list(). This method may be used to retrieve the IDs of all instances of an entity when called without arguments or to retrieve the IDs of instances that match certain criteria passed as arguments. For example:

Car.list()

Will return a list of IDs of all cars in the database, whereas:

Car.list(make='Volvo')

Will return the IDs of all the Volvos in the database.

Chapter5/entity.py

@classmethod
def list(cls,**kw):
		sql="select %s_id from %s"%(cls.__name__,cls.__name__)

		cursor=cls.threadlocal.connection.cursor()
		if len(kw):
				cols=[]
				values=[]
				for k,v in kw.items():
						cols.append(k)
						values.append(v)
				whereclause = " where "+",".join(c+"=?" for c in 
cols)
				sql += whereclause
				cursor.execute(sql,values)
		else:
				cursor.execute(sql)
		for row in cursor.fetchall():
				yield row[0]

The implementation is straightforward and starts off with creating an SQL statement to select all IDs from the table (highlighted). An example would be:

select Car_id from Car

If there were any keyword arguments passed to the list() method, these are then used to construct a where clause that will restrict the IDs returned to those of the records that match. This where clause is appended to our general select statement (highlighted). For example:

select Car_id from Car where make=?

After invocation of the execute() method, we yield all the IDs. By using the yield statement, we have identified the list() method as a generator that will return the IDs found one-by-one rather than in one go. We still can manipulate this generator just like a list if we wish, but for very large result sets, a generator might be a better option as it does consume less memory, for example.

The Relation class is used to manage relations between individual instances of entities. If we have Car entities as well as Owner entities, we might like to define a CarOwner class that provides us with the functionality to identify the ownership of a certain car by a specific owner.

Like entities, generic relations share a lot of common functionality: we must be able to create a new relation between two entities, delete a relation, and list related entities given a primary entity, for example, list all owners of a given car or all cars of a certain owner.

Relations are stored in the database in a table, often called a bridging table, consisting of records with columns that store the IDs of both related entities. When an application starts using a (subclass of) the Relation class, we must verify that the corresponding table exists, and if not, create it.