10.2 Creating a Custom Internal Report

Internal reports typically aggregate data such that the result can be handed off to another PyPedal routine for further processing. To do this, the pedigree is loaded into a table in an SQLite database against which queries are made. This is faster and more flexible than writing reporting routines that loop over the pedigree to construct reports, but it does require some knowledge of the Structured Query Language (SQL; http://www.sql.org/). The canonical example of this kind of report is the passing of the dictionary returned by pyp_reports.meanMetricBy() to pyp_graphics.plot_line_xy() (see 9.1.1). That approach is outlined in code below.

def inbreedingByYear(pedobj):
    curs = pyp_db.getCursor(pedobj.kw['database_name'])

    # Check and see if the pedigree has already been loaded.  If not, do it.
    if not pyp_db.tableExists(pedobj.kw['database_name'], pedobj.kw['dbtable_name']):
        pyp_db.loadPedigreeTable(pedobj)

    MYQUERY = "SELECT birthyear, pyp_mean(coi) FROM %s GROUP BY birthyear \
        ORDER BY birthyear ASC" % (pedobj.kw['dbtable_name'])
    curs.execute(MYQUERY)
    myresult = curs.fetchall()
    result_dict = {}
    for _mr in myresult:
        _level, _mean = _mr
        result_dict[_level] = _mean
    return result_dict

You should always check to see if your pedigree has been loaded into the database before you try and make queries against the pedigree table or your program may crash. inbreedingByYear() returns a dictionary containing average coefficients of inbreeding keyed to birth years. The query result, myresult, is a list of tuples; each tuple in the list corresponds to one row in an SQL resultset. The tuples in myresult are unpacked into temporary variables that are then stored in the dictionary, result_dict (for information on tuples see the Python Tutorial (http://www.python.org/doc/tut/node7.html#SECTION007300000000000000000). If the resultset is empty, result_dict will also be empty. As long as you can write a valid SQL query for the report you'd like to assemble, there is no limitation on the reports that can be prepared by PyPedal.

See About this document... for information on suggesting changes.