A simple DB based application

When I started the PyCLIPS project, the intention was mainly to have the flexibility and extensibility of the Python framework and take advantage of a rule-based production engine such as CLIPS. CLIPS would be faster when it comes to writing a complex knowledge base than any RETE implementation written in Python, and give more possibilities than a series of if-then-else statements. As a side effect we get GUIs and other features, such as many ways to implement data persistence.

Here I will describe a simple case where Python acts as the main director between CLIPS and a database. I will imagine a situation close to a real life case, in order to show how the interaction between these three elements can be useful. Please note that, at different times, we move between three separate environments, that is:

  • Python
  • a SQL based RDBMS (we will use SQLite 3, as it comes with Python)
  • CLIPS

Python is the only one that interacts with both the other ones. I also use it to perform some calculations that, thanks to the library, are much easier in Python than in any of the other environments.

So, let's imagine the case of a car insurance company. At least here, car insurance companies use quite complex grids (we could say rule sets, or knowledge bases) to determine the offers for insurance contracts. Such grids take several things in account, such as customer (that is, the supposed driver) age, sex and living place, as well as car details. The insurance company probably keeps their customer data in a complex database and will know details about cars and the cities where they can place contract offers.

In our example, the insurance company will have a quite simple rule set, consisting of the following statements.

Bonuses:

  1. small cities will have a very particular offer: they'll save 35% off the base price, but since this is a really big bonus, no other discount will cumulate with this one;
  2. customers of an age equal or greater than 27 years will save 10%
  3. female customers with small cars (1300cc or less) will save 5%
  4. a 50.00 EUR bonus is for new customers (contracts started in the last 30 days)

Surcharges:

  1. luxury car owners pay 10% more than the other customers
  2. contracts for powerful cars, more than 100KW (134bhp), cost 100.00 EUR more than the others

Note that surcharges will apply to all contracts, including the ones that benefit from the first bonus. Extra costs will have to be applied after the bonuses, in order to avoid reductions on the "power tax".

In our rule set we will depict contracts as facts, so that each fact will hold all information needed to calculate the final price of an insurance contract. The rules will modify the facts where needed.

Translating statement 1 above into a rule, we will have the following in CLIPS:

(defrule city-population "rule for city population"
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-city-population
                ?v_driver-city-population&:(
                    < ?v_driver-city-population 100000))
            (base-price ?v_base-price)
        )
    =>
        (modify ?f
            (final-price (* 0.65 ?v_base-price))
            (no-more-bonus TRUE)
        )
    )

The "no-more-bonus" slot is something that we added to the fact, so that no other rules that apply bonuses will be able to modify this fact anymore. The other rules might be somewhat simpler, although the pattern will be much similar: when some conditions are met, the "final-price" slot is changed - possibly along with some other slots that help a rule not be fired twice: when a fact is modified in place, it's actually retracted and reasserted with modified slots, so the rule set will treat it as a completely new fact. Note that we have used "contract" for the fact relation: this is because we will define a template for the facts that represent contracts. Anyway, from this rule we see that we need at least these slots:

  • driver-city-population, the population of the city where the customer lives
  • base-price, obviously the calculated base price (Python will calculate it)
  • final-price, the final price that CLIPS will report
  • no-more-bonus (a "service" slot)

Another thing must be considered: this rule will have to be one of the first ones to be executed, since it has to inhibit all other bonus rules.

Since all the other rules will modify the final price in place as a function of itself, we will need to set it to the base price for all remaining contracts - that is, facts. We do it in CLIPS:

(defrule reset-price "final price to base price"
    ?f <- (contract
            (no-more-bonus FALSE)
            (base-price ?v_base-price)
            (final-price 0)
        )
    =>
        (modify ?f
            (final-price ?v_base-price)
        )
    )

Here are the other rules for bonuses: they are named in a way that easily associates them to the statements above, and their comment gives a little explanation about them. They are "at the same level", in the sense that their execution order is not important (because of commutativity of multiplication, of course).

(defrule older-driver "bonus for older drivers"
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-age ?a&:(> ?a 26))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* 0.9 ?v_final-price))
            (driver-age 0)  ; don't reevaluate
        )
    )
 
(defrule woman-small-car "bonus for women on small cars"
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-gender FEMALE)
            (car-displacement ?a&:(< ?a 1.3))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* 0.95 ?v_final-price))
            (car-displacement 1000) ; don't reevaluate
        )
    )
 
(defrule new-customer-discount "discount for newly acquired customers"
    ?f <- (contract
            (no-more-bonus FALSE)
            (new-customer TRUE)
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (- ?v_final-price 50))
            (new-customer FALSE)    ; don't reevaluate
        )
    )

From these rules, we notice that we need the following extra slots in our contract representation fact:

  • driver-age, age in years, greater than 26 means 27 or more
  • driver-gender, sex could be either MALE or FEMALE (as SYMBOLs)
  • car-displacement, the displacement of the car expressed in liters (thus it has to be a FLOAT)
  • new-customer, a boolean (TRUE or FALSE) that will state whether or not the contract start date is more recent than 30 days ago

Now, we introduce the extra cost rules: since they will impact all facts, the test for "no-more-bonus" will not be performed.

(defrule luxury-car-extra "price higher for luxury cars"
    ?f <- (contract
            (car-luxury TRUE)
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* ?v_final-price 1.1))
            (car-luxury FALSE)  ; don't reevaluate
        )
    )
 
(defrule power-extra "price higher for power cars"
    ?f <- (contract
            (car-power ?a&:(> ?a 100))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (+ ?v_final-price 100))
            (car-power 0)   ; don't reevaluate
        )
    )

The extra slots that we need, then, are:

  • car-power, the power of the car in KW (the standard here)
  • car-luxury, that assumes a TRUE value when the car is considered a luxury car and FALSE otherwise

Moreover, we will keep track in the fact of its corresponding identifier in the database, with a slot that we will call "contract-id" for clarity. So it will be possible to update the database using the resulting facts from the CLIPS engine. So, considering that a fact more or less "maps" to a contract, we can freeze the structure of a fact into the following template:

(deftemplate contract "structure of a contract"
    (slot contract-id (type INTEGER))
    (slot car-luxury (type SYMBOL) (default FALSE))
    (slot car-power (type INTEGER))
    (slot car-displacement (type NUMBER))
    (slot new-customer (type SYMBOL) (default FALSE))
    (slot contract-class (type INTEGER) (default 15))
    (slot max-refund (type NUMBER) (default 3000000))
    (slot driver-age (type NUMBER))
    (slot driver-gender (type SYMBOL))
    (slot driver-city-population (type NUMBER))
    (slot base-price (type NUMBER))
    (slot final-price (type NUMBER) (default 0))
    (slot no-more-bonus (type SYMBOL) (default FALSE))
    )

which has been referenced throughout the whole rule set, as said above.

Since some of the rules have to be executed before other ones, salience will be used appropriately: in order to keep the system flexible, we will declare salience to be 20 for the first rule, 15 for the second one, 10 for the remaining "bonus" rules and 5 for the extra cost rules.

All the data shown above is likely to be already in the database of the insurance company. We can imagine the database to be an existing instance, structured in a (slightly) normalized way. For instance we will have three tables, two of which are dictionaries based on data not strictly related to the contracts: these two dictionaries are descriptive for a broad number of car models and for the cities served by the company. The CARS table contains information about brand, model, power and displacement, as well as a rather arbitrary attribute about being a luxury car or not. The CITIES table will provide the known population for each served city. These tables are defined as follows:

-- CARS: determines displacement, power and luxury
CREATE TABLE CARS (
    IDENT varchar(10) NOT NULL PRIMARY KEY,
    MAKE varchar(20),
    MODEL varchar(50),
    DISPLACEMENT integer,
    POWER integer,
    LUXURY char(1) DEFAULT 'N'
);
 
-- CITIES: to determine the population
CREATE TABLE CITIES (
    IDENT varchar(30) NOT NULL PRIMARY KEY,
    POPULATION integer
);

IDENT for cars is an alphanumeric code, IDENT for cities is the name of the city in lowercase. The CONTRATS table contains all information about each contract, including customer name, birth date and sex, as well as the contract start date, the contract class (an integer that decreases every year that a customer has no accidents, down to zero, and is raised when a customer has an accident), the city where the customer lives, the insured car and the maximum refund chosen by the customer. The customer name is stored for completeness.

-- CONTRACTS: contains all other data and will hold the final price
CREATE TABLE CONTRACTS (
    IDENT integer PRIMARY KEY AUTOINCREMENT,
    CUSTOMER_NAME varchar(20),
    CUSTOMER_BIRTHDATE varchar(10),
    CUSTOMER_GENDER char(1),
    CUSTOMER_CITY varchar(30),
    CAR_IDENT varchar(10),
    CONTRACT_DATE varchar(10),
    CONTRACT_CLASS integer DEFAULT 15,
    MAX_REFUND integer DEFAULT 2000000,
    BASE_PRICE integer,
    FINAL_PRICE integer
);

CAR_IDENT is a foreign key and is related to CARS.IDENT, CUSTOMER_CITY is another foreign key bound to CITIES.IDENT, BASE_PRICE and FINAL_PRICE will contain, after all calculations, the calculated base price for the contract and the offer made to the customer. Note that dates are expressed as VARCHAR fields: they will hold strings of the form 'YYYY-MM-DD', which are humanly readable and can be easily converted into dates via the Python library. The base price is a simple mathematic formula: it will basically depend on the car, the contract class, the maximum desired refund and some fixed values (derived from fixed costs, possibly). Higher displacements will cause higher costs for various reasons and can reasonably impact linearly on the price. The maximum desired refund is normally something quite high: since it is normally a lot more than the price of an expensive sports car (at least 1000000,00 EUR) it will probably be required just in case of severe injuries (or death) to the offended party, an event that is hopefully unlikely to happen. We expect that the normal refund to be given is for damage to things (mostly cars) so, everything above the lowest possible amount will be taken into account as a function of its order of magnitude, namely a logarithmic function. The company also wants to reward customers that did not have accidents for a long time, so the base price will be lowered of a certain amount quadratically with the decreasing of the insurance (bonus-malus) class. Here is the formula:

    base price = 400.00
                + (20.00 * displacement)
                + (30.00 * log2(max refund))
                - ( 1.00 * ((15 - insurance class) ^ 2))

Well, probably it's not so realistic, but it will serve the case. As a Python programmer, I find it more difficult to perform these calculations in CLIPS, so I'll let Python calculate the base price and provide it to CLIPS.

def base_price(displacement, insuranceclass, maxrefund):
    if insuranceclass > 15:
        insuranceclass = 15
    return round(
             400.0
            + 20.0 * displacement
            + 30.0 * math.log(maxrefund, 2)
            -  1.0 * ((15.0 - insuranceclass) ** 2)
            , 2)

Since the dates are expressed as strings, some functions that provide the number of years or days from a certain "stringized" date to another one might be useful:

def days_from_to(from_date, to_date=None):
    if not to_date:
        to_date_t = datetime.date.today()
    else:
        to_date_t = datetime.datetime.strptime(str(to_date), '%Y-%m-%d').date()
    from_date_t = datetime.datetime.strptime(str(from_date), '%Y-%m-%d').date()
    return (to_date_t - from_date_t).days
 
def years_from_to(from_date, to_date=None):
    if not to_date:
        to_date_t = datetime.date.today()
    else:
        to_date_t = datetime.datetime.strptime(str(to_date), '%Y-%m-%d').date()
    from_date_t = datetime.datetime.strptime(str(from_date), '%Y-%m-%d').date()
    return (to_date_t - from_date_t).days / 365     # yes, not really so

I normally write more "general purpose" functions than what is actually required. In our case, we needed the functions to calculate date deltas to today, but I preferred to calculate generic date differences and assume that if the end date is not given, it has to be today, hence the 'if' statement at the beginning of each. The second function is quite useless, and definitely wrong. Though, in this example, I didn't want to make things more complicated than they are, so I assumed for all years the same number of days, and separated years_from_to() from the other one while waiting for a library function to do the math for me.

In order to make things easier when it comes to extract the data from the database, a view in the database to make all joins explicit comes quite handy: we can create it in the existing database without doing any harm.

-- COMPLETE: a view joining the three tables above
CREATE VIEW COMPLETE
AS SELECT
    CONTRACTS.IDENT AS CONTRACT_ID, CUSTOMER_BIRTHDATE, CUSTOMER_GENDER,
    CUSTOMER_CITY, CAR_IDENT, CONTRACT_DATE, CONTRACT_CLASS, MAX_REFUND,
    BASE_PRICE, FINAL_PRICE, POPULATION, MAKE, MODEL, DISPLACEMENT,
    POWER, LUXURY
FROM CONTRACTS, CITIES, CARS
WHERE
    CAR_IDENT = CARS.IDENT AND CUSTOMER_CITY = CITIES.IDENT;

Assuming that we will retrieve all records from this view, and considering that the template for the CLIPS facts is slightly different from each of its rows, one of the possible actions is to provide a way to traverse the view and, for each row, use the values to create facts consistent with the template - possibly performing the needed calculations (such as the ones involving dates and the base price). Here I use a simple generator that, while retrieving rows, converts them to facts and returns these facts - ready to be asserted.

def get_facts_from_rows(fact_template):
    conn = db.connect("contracts.db")
    conn.row_factory = db.Row
    cur = conn.cursor()
    SQL = "select * from COMPLETE"
    cur.execute(SQL)
    # iterate database rows and return them as facts
    while True:
        r = cur.fetchone()
        if not r:
            conn.close()
            raise StopIteration
        # calculate some more values here to let CLIPS only make decisions
        gender = 'MALE' if r['CUSTOMER_GENDER'].upper() == 'M' else 'FEMALE'
        luxury = 'TRUE' if r['LUXURY'].upper() == 'Y' else 'FALSE'
        new_customer = \
            'TRUE' if days_from_to(r['CONTRACT_DATE']) < 30 else 'FALSE'
        bprice = clips.Integer(base_price(
            r['DISPLACEMENT'], r['CONTRACT_CLASS'], r['MAX_REFUND']))
        driver_age = years_from_to(r['CUSTOMER_BIRTHDATE'])
        # in this dictionary the keys have the values of fact slots, and
        #  the values are cast to CLIPS types, just to be sure
        res = {
            'contract-id': clips.Integer(r['CONTRACT_ID']),
            'car-luxury': clips.Symbol(luxury),
            'car-power': clips.Integer(r['POWER']),
            'car-displacement': clips.Float(r['DISPLACEMENT']),
            'new-customer': clips.Symbol(new_customer),
            'contract-class': clips.Integer(r['CONTRACT_CLASS']),
            'max-refund': clips.Integer(r['MAX_REFUND']),
            'driver-age': clips.Integer(driver_age),
            'driver-gender': clips.Symbol(gender),
            'driver-city-population': clips.Integer(r['POPULATION']),
            'base-price': clips.Integer(bprice),
        }
        f = fact_template.BuildFact()
        f.AssignSlotDefaults()
        for k in res.keys():
            f.Slots[k] = res[k]
        yield f

Maybe I could initialize the connection as a global object, but I thought that it would be better to keep the function as self contained as possible. This can be done either on database that support multiple connections (most server based RDBMSs do, but SQLite cannot, generally) or in cases like this, where while retrieving rows no other DB connection is required. In real life cases, consider using a global connection object. See the documentation of the sqlite3 module for the line about the row factory: it's used only to make this code more readable, so that we can reference columns by their name instead of their index.

Now we are almost done: we only need to write a little "main" program that:

  • retrieves all rows in the database, converts them to facts and asserts each fact
  • executes the CLIPS program on the asserted facts
  • writes the results back to the database

given a cursor on the database, named "cur", it will look like this:

# retrieve and assert all facts
facts = get_facts_from_rows(clips.FindTemplate('contract'))
for f in facts:
    f.Assert()
 
# execute the ruleset aginst the given set of facts
clips.Run()
 
# print the offers to screen, and update the database
li = clips.FactList()
for f in li:
    # skip initial fact
    if f.Relation == 'initial-fact':
        continue
    contract_id = f.Slots['contract-id']
    base_p = f.Slots['base-price']
    final_p = f.Slots['final-price']
    print "Contract n. %s: base %.2f EUR, offer %.2f EUR" % (
        contract_id, base_p, final_p)
 
    SQL = ("update CONTRACTS set BASE_PRICE=%s, FINAL_PRICE=%s "
           "where IDENT=%s" % (base_p, final_p, contract_id))
    cur.execute(SQL)

then the changes will be committed and the connection will be closed. The following listings are the complete programs in CLIPS and Python, as well as a SQL file that create the structure for the database. I also provide a database with some sample data that can be used to recreate the environment I used to test the program, and that provides the following output:

Contract n. 4: base 949.95 EUR, offer 617.47 EUR
Contract n. 3: base 1054.95 EUR, offer 1054.95 EUR
Contract n. 2: base 1046.60 EUR, offer 941.94 EUR
Contract n. 1: base 922.50 EUR, offer 913.28 EUR

A complete dump of the sample database, suitable for SQLite 3, as the fourth listing. To run the sample, save all the files in a directory of your choice, then run the following commands at the shell prompt in that directory:

> sqlite3 contracts.db < contracts.dump
> python insurance.py

The above output will be shown and the database will be updated.

1) CLIPS constructs and rule set (file "constructs.clp")

; template for contract facts
(deftemplate contract "structure of a contract"
    (slot contract-id (type INTEGER))
    (slot car-luxury (type SYMBOL) (default FALSE))
    (slot car-power (type INTEGER))
    (slot car-displacement (type NUMBER))
    (slot new-customer (type SYMBOL) (default FALSE))
    (slot contract-class (type INTEGER) (default 15))
    (slot max-refund (type NUMBER) (default 3000000))
    (slot driver-age (type NUMBER))
    (slot driver-gender (type SYMBOL))
    (slot driver-city-population (type NUMBER))
    (slot base-price (type NUMBER))
    (slot final-price (type NUMBER) (default 0))
    (slot no-more-bonus (type SYMBOL) (default FALSE))
    )
 
;--------------------------------------------------------------------------
; rules to calculate the final price
;--------------------------------------------------------------------------
 
; rule based upon the city population: it has more salience than the
;  other rules since it may cancel other possible bonuses, this rule
;  directly uses the base price because no other bonus rule should
;  have fired thanks to its higher salience
(defrule city-population "rule for city population"
    (declare (salience 20))
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-city-population
                ?v_driver-city-population&:(
                    < ?v_driver-city-population 100000))
            (base-price ?v_base-price)
        )
    =>
        (modify ?f
            (final-price (* 0.65 ?v_base-price))
            (no-more-bonus TRUE)
        )
    )
 
 
; this rule will change all remaining facts (with final-price still to 0)
;  to have the final price set to the base price
(defrule reset-price "final price to base price"
    (declare (salience 15))
    ?f <- (contract
            (no-more-bonus FALSE)
            (base-price ?v_base-price)
            (final-price 0)
        )
    =>
        (modify ?f
            (final-price ?v_base-price)
        )
    )
 
 
; normal *bonus* rules
 
; a driver more than 26 years old has a bonus of 10% on the final price
(defrule older-driver "bonus for older drivers"
    (declare (salience 10))
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-age ?a&:(> ?a 26))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* 0.9 ?v_final-price))
            (driver-age 0)  ; don't reevaluate
        )
    )
 
; a woman on a car of less than 1.3 liter displacement has a 5% bonus
(defrule woman-small-car "bonus for women on small cars"
    (declare (salience 10))
    ?f <- (contract
            (no-more-bonus FALSE)
            (driver-gender FEMALE)
            (car-displacement ?a&:(< ?a 1.3))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* 0.95 ?v_final-price))
            (car-displacement 1000) ; don't reevaluate
        )
    )
 
; new customers (less than 30 days) have a special 50 EUR discount
(defrule new-customer-discount "discount for newly acquired customers"
    (declare (salience 10))
    ?f <- (contract
            (no-more-bonus FALSE)
            (new-customer TRUE)
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (- ?v_final-price 50))
            (new-customer FALSE)    ; don't reevaluate
        )
    )
 
 
; normal *extra cost* rules
 
; luxury cars will pay an extra 10% on calculated price
(defrule luxury-car-extra "price higher for luxury cars"
    (declare (salience 5))
    ?f <- (contract
            (car-luxury TRUE)
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (* ?v_final-price 1.1))
            (car-luxury FALSE)  ; don't reevaluate
        )
    )
 
; powerful cars will pay an extra 100 EUR on calculated price
(defrule power-extra "price higher for power cars"
    (declare (salience 5))
    ?f <- (contract
            (car-power ?a&:(> ?a 100))
            (final-price ?v_final-price)
        )
    =>
        (modify ?f
            (final-price (+ ?v_final-price 100))
            (car-power 0)   ; don't reevaluate
        )
    )
 
 
; end.

2) database tables (file "tables.sql")

-- this creates the base structure for the database
 
-- CARS: determines displacement, power and luxury
CREATE TABLE CARS (
    IDENT varchar(10) NOT NULL PRIMARY KEY,
    MAKE varchar(20),
    MODEL varchar(50),
    DISPLACEMENT integer,
    POWER integer,
    LUXURY char(1) DEFAULT 'N'
);
 
-- CITIES: to determine the population
CREATE TABLE CITIES (
    IDENT varchar(30) NOT NULL PRIMARY KEY,
    POPULATION integer
);
 
-- CONTRACTS: contains all other data and will hold the final price
CREATE TABLE CONTRACTS (
    IDENT integer PRIMARY KEY AUTOINCREMENT,
    CUSTOMER_NAME varchar(20),
    CUSTOMER_BIRTHDATE varchar(10),
    CUSTOMER_GENDER char(1),
    CUSTOMER_CITY varchar(30),
    CAR_IDENT varchar(10),
    CONTRACT_DATE varchar(10),
    CONTRACT_CLASS integer DEFAULT 15,
    MAX_REFUND integer DEFAULT 2000000,
    BASE_PRICE integer,
    FINAL_PRICE integer
);
 
-- COMPLETE: a view joining the three tables above
CREATE VIEW COMPLETE
AS SELECT
    CONTRACTS.IDENT AS CONTRACT_ID, CUSTOMER_BIRTHDATE, CUSTOMER_GENDER,
    CUSTOMER_CITY, CAR_IDENT, CONTRACT_DATE, CONTRACT_CLASS, MAX_REFUND,
    BASE_PRICE, FINAL_PRICE, POPULATION, MAKE, MODEL, DISPLACEMENT,
    POWER, LUXURY
FROM CONTRACTS, CITIES, CARS
WHERE
    CAR_IDENT = CARS.IDENT AND CUSTOMER_CITY = CITIES.IDENT;
 
-- end.

3) the Python program (file "insurance.py")

# insurance.py
 
# we need both CLIPS and math functions
import clips
import math
 
# we will use dates (in days from the epoch) to calculate ranges
import datetime
 
# to use the DB, we will use SQLite, as it comes for free with Python
import sqlite3 as db
 
 
# return the base price, without any discounts or addition, based upon the
#  requirements of the customer, rounded to the second decimal number
def base_price(displacement, insuranceclass, maxrefund):
    if insuranceclass > 15:
        insuranceclass = 15
    return round(
             400.0
            + 20.0 * displacement
            + 30.0 * math.log(maxrefund, 2)
            -  1.0 * ((15.0 - insuranceclass) ** 2)
            , 2)
 
 
# calculate the difference between two dates known in the form 'YYYY-MM-DD':
#  we will use this format to represent dates in the database (no date ops
#  from within the database)
def days_from_to(from_date, to_date=None):
    if not to_date:
        to_date_t = datetime.date.today()
    else:
        to_date_t = datetime.datetime.strptime(str(to_date), '%Y-%m-%d').date()
    from_date_t = datetime.datetime.strptime(str(from_date), '%Y-%m-%d').date()
    return (to_date_t - from_date_t).days
 
def years_from_to(from_date, to_date=None):
    if not to_date:
        to_date_t = datetime.date.today()
    else:
        to_date_t = datetime.datetime.strptime(str(to_date), '%Y-%m-%d').date()
    from_date_t = datetime.datetime.strptime(str(from_date), '%Y-%m-%d').date()
    return (to_date_t - from_date_t).days / 365     # yes, not really so
 
 
# a simple cursor iterator, so that we can retrieve data row by row: see the
#  "tables.sql" file for the database structure; data will be used to directly
#  create facts to be asserted
def get_facts_from_rows(fact_template):
    conn = db.connect("contracts.db")
    conn.row_factory = db.Row
    cur = conn.cursor()
    SQL = "select * from COMPLETE"
    cur.execute(SQL)
    # iterate database rows and return them as facts
    while True:
        r = cur.fetchone()
        if not r:
            conn.close()
            raise StopIteration
        # calculate some more values here to let CLIPS only make decisions
        gender = 'MALE' if r['CUSTOMER_GENDER'].upper() == 'M' else 'FEMALE'
        luxury = 'TRUE' if r['LUXURY'].upper() == 'Y' else 'FALSE'
        new_customer = \
            'TRUE' if days_from_to(r['CONTRACT_DATE']) < 30 else 'FALSE'
        bprice = base_price(
            r['DISPLACEMENT'], r['CONTRACT_CLASS'], r['MAX_REFUND'])
        driver_age = years_from_to(r['CUSTOMER_BIRTHDATE'])
        # in this dictionary the keys have the values of fact slots, and
        #  the values are cast to CLIPS types, just to be sure
        res = {
            'contract-id': clips.Integer(r['CONTRACT_ID']),
            'car-luxury': clips.Symbol(luxury),
            'car-power': clips.Integer(r['POWER']),
            'car-displacement': clips.Float(r['DISPLACEMENT']),
            'new-customer': clips.Symbol(new_customer),
            'contract-class': clips.Integer(r['CONTRACT_CLASS']),
            'max-refund': clips.Integer(r['MAX_REFUND']),
            'driver-age': clips.Integer(driver_age),
            'driver-gender': clips.Symbol(gender),
            'driver-city-population': clips.Integer(r['POPULATION']),
            'base-price': clips.Float(bprice),
        }
        f = fact_template.BuildFact()
        f.AssignSlotDefaults()
        for k in res.keys():
            f.Slots[k] = res[k]
        yield f
 
 
 
# MAIN PROGRAM
 
# initialize the CLIPS environment with our rules and constructs
clips.Load("constructs.clp")
clips.Reset()
 
# dribble to a debug file
#clips.DebugConfig.WatchAll()
#clips.DebugConfig.DribbleOn("debug.out")
 
# retrieve and assert all facts
facts = get_facts_from_rows(clips.FindTemplate('contract'))
for f in facts:
    f.Assert()
 
# execute the ruleset aginst the given set of facts
clips.Run()
 
# print the offers to screen, and update the database
conn = db.connect("contracts.db")
cur = conn.cursor()
li = clips.FactList()
for f in li:
    # skip initial fact
    if f.Relation == 'initial-fact':
        continue
    contract_id = f.Slots['contract-id']
    base_p = f.Slots['base-price']
    final_p = f.Slots['final-price']
    print "Contract n. %s: base %.2f EUR, offer %.2f EUR" % (
        contract_id, base_p, final_p)
 
    SQL = ("update CONTRACTS set BASE_PRICE=%s, FINAL_PRICE=%s "
           "where IDENT=%s" % (base_p, final_p, contract_id))
    cur.execute(SQL)
conn.commit()
conn.close()
 
 
# end.

4) the sample database dump (file "contracts.dump")

BEGIN TRANSACTION;
CREATE TABLE CARS (
    IDENT varchar(10) NOT NULL PRIMARY KEY,
    MAKE varchar(20),
    MODEL varchar(50),
    DISPLACEMENT integer,
    POWER integer,
    LUXURY char(1) DEFAULT 'N'
);
INSERT INTO "CARS" VALUES('meA160','mercedes','A160 CDI',1.7,55,'N');
INSERT INTO "CARS" VALUES('meA190','mercedes','A190',1.7,92,'Y');
INSERT INTO "CARS" VALUES('meA210','mercedes','A210 EVO',1.7,103,'Y');
INSERT INTO "CARS" VALUES('meE200','mercedes','E200 CDI',2.15,100,'Y');
INSERT INTO "CARS" VALUES('meE350','mercedes','E350',3.5,200,'Y');
INSERT INTO "CARS" VALUES('meSK200K','mercedes','SLK200 K',1.8,120,'Y');
INSERT INTO "CARS" VALUES('meSK300','mercedes','SLK300',3,170,'Y');
INSERT INTO "CARS" VALUES('opCR13D','opel','Corsa 1.3 CDTI',1.3,55,'N');
INSERT INTO "CARS" VALUES('opCR17D','opel','Corsa 1.7 CDTI',1.7,75,'N');
INSERT INTO "CARS" VALUES('opCR16X','opel','Corsa 1.6 OPC',1.6,141,'N');
INSERT INTO "CARS" VALUES('opAS20X','opel','Astra 2.0 OPC',2,177,'Y');
INSERT INTO "CARS" VALUES('opVE18','opel','Vectra 1.8',1.8,90,'N');
INSERT INTO "CARS" VALUES('opVE32V6','opel','Vectra 3.2',3.2,155,'Y');
INSERT INTO "CARS" VALUES('opVE28X','opel','Vectra 2.8 OPC',2.8,188,'Y');
INSERT INTO "CARS" VALUES('fi500b12','fiat','Fiat 500 1.2',1.2,40,'N');
INSERT INTO "CARS" VALUES('fi500d13','fiat','Fiat 500 1.3D',1.3,55,'N');
INSERT INTO "CARS" VALUES('fi500b14','fiat','Fiat 500 1.4',1.4,60,'N');
INSERT INTO "CARS" VALUES('fiGP12','fiat','Grande Punto 1.2',1.2,50,'N');
INSERT INTO "CARS" VALUES('fiGP14','fiat','Grande Punto 1.4',1.4,88,'N');
INSERT INTO "CARS" VALUES('fiGP14A','fiat','Grande Punto 1.4 A',1.4,114,'N');
INSERT INTO "CARS" VALUES('fiPA12','fiat','Panda 1.2',1.2,45,'N');
INSERT INTO "CARS" VALUES('fdFS14','ford','Fiesta 1.4',1.4,71,'N');
INSERT INTO "CARS" VALUES('fdFS16','ford','Fiesta 1.6',1.6,88,'N');
INSERT INTO "CARS" VALUES('fdMO16','ford','Mondeo 1.6',1.6,81,'N');
INSERT INTO "CARS" VALUES('fdMO18','ford','Mondeo 2.0',2,107,'N');
INSERT INTO "CARS" VALUES('fdMO25','ford','Mondeo 2.5',2.5,162,'Y');
INSERT INTO "CARS" VALUES('fdMU40s','ford','Mustang Base',4,157,'Y');
INSERT INTO "CARS" VALUES('fdMU46GT','ford','Mustang GT',4.6,224,'Y');
CREATE TABLE CITIES (
    IDENT varchar(30) NOT NULL PRIMARY KEY,
    POPULATION integer
);
INSERT INTO "CITIES" VALUES('siena',54000);
INSERT INTO "CITIES" VALUES('rome',2706000);
INSERT INTO "CITIES" VALUES('milan',1296000);
INSERT INTO "CITIES" VALUES('florence',366000);
INSERT INTO "CITIES" VALUES('viterbo',61000);
CREATE TABLE CONTRACTS (
    IDENT integer PRIMARY KEY AUTOINCREMENT,
    CUSTOMER_NAME varchar(20),
    CUSTOMER_BIRTHDATE varchar(10),
    CUSTOMER_GENDER char(1),
    CUSTOMER_CITY varchar(30),
    CAR_IDENT varchar(10),
    CONTRACT_DATE varchar(10),
    CONTRACT_CLASS integer DEFAULT 15,
    MAX_REFUND integer DEFAULT 2000000,
    BASE_PRICE integer,
    FINAL_PRICE integer
);
INSERT INTO "CONTRACTS" VALUES(1,'Mario Rossi','1965-03-28','M','rome','meE200','2006-07-15',1,6000000,'','');
INSERT INTO "CONTRACTS" VALUES(2,'Antonio Verdi','1975-06-12','M','florence','opCR17D','2003-01-20',10,2500000,'','');
INSERT INTO "CONTRACTS" VALUES(3,'Giovanna Bianchi','1987-10-03','F','milan','fi500b14','2008-01-01',14,2000000,'','');
INSERT INTO "CONTRACTS" VALUES(4,'Fabio Bruni','1952-04-30','M','siena','opVE18','2009-01-25',3,4000000,'','');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('CONTRACTS',4);
CREATE VIEW COMPLETE
AS SELECT
    CONTRACTS.IDENT AS CONTRACT_ID, CUSTOMER_BIRTHDATE, CUSTOMER_GENDER,
    CUSTOMER_CITY, CAR_IDENT, CONTRACT_DATE, CONTRACT_CLASS, MAX_REFUND,
    BASE_PRICE, FINAL_PRICE, POPULATION, MAKE, MODEL, DISPLACEMENT,
    POWER, LUXURY
FROM CONTRACTS, CITIES, CARS
WHERE
    CAR_IDENT = CARS.IDENT AND CUSTOMER_CITY = CITIES.IDENT;
COMMIT;

I hope that this small tutorial will help to understand how PyCLIPS can be used to create rule based applications involving more complex systems than the CLIPS engine alone.

F.