4.4.2 Output to Databases

PyPedal can write NewPedigree pedigrees to ASDx-formatted SQLite tables using the NewPedigree::savedb() method. The following program will result in the creation of a table named "test_save" in the database "test_pypedal_save".

test.kw['database_name'] = 'test_pypedal_save'
test.kw['dbtable_name'] = 'test_save'
test.savedb()

The pedigree is saved to the database and table specified in the database_name and dbtable_name variables. If the specified table already exists then the records from the current pedigree will be added to the table. If you want to overwrite the existing database then you need to pass "drop=True" in the call to methodsavedb:

test.savedb(drop=True)

While this may strike some users as unexpected behavior it is based on the idea that you should never lose data unexpectedly. If you use only the defaults you will never lose data. However, if you try and save a renumbered pedigree on top of another renumbered pedigree you will run into problems because animal IDs are used as primary keys, and you will have constraint violations.

It is possible to save pedigrees to a databse with a format different than that exected by PyPedal, but it is a little harder than changeing pedigree input. Changing the output table requires that you change the NewPedigree class's savesb method. Consider the code that begins on or about line 590 of pyp_newclasses.py in savesb:

591  sql = 'create table %s ( \
592      animalName   varchar(128) primary key, \
593      sireName     varchar(128), \
594      damName      varchar(128), \
595      sex          char(1) \
596      );' % ( self.kw['database_table'] )

You need to rewrite this statement to add or remove columns; the SQL for a tavble that includes all fields in a NewAnimal object may be found in the createPedigreeTable function of the pyp_db module. Note that not every field in a NewAnimal object has a corresponding pedigree format code. Consult Table 3.5.1 for appropriate codes.

Once you've updated the SQL used to create your custom pedigree table you will also need to change the code which prepares the input for writing to the database. This code is found on or about about line 617 in savesb:

617  sql = "INSERT INTO %s ( animalName, sireName, damName, sex ) VALUES ('%s', '%s', '%s', '%s')" % ( self.kw['database_table'], an, si, da, p.sex )

Note that you will need to make appropriate casts yourself. For example, you will want to cast things that are stored as integers or reals to make sure that you don't violate a column constraint. You must enclose strings in single-quotation marks ('') yourself.

By default PyPedal saves pedigrees in "ASD" format. If you want to save your pedigree in "asd" format then you will need to change the code on or about line 610 from:

610  an = p.name
611  si = p.sireName
612  da = p.damName

to:

610  an = p.animalID
611  si = p.sireID
612  da = p.damID

It is better to add a new method supporting your output format to NewAnimal rather than changing the savedb method.

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