[Biopython-dev] [Bug 1921] New: BioSeqDatabase.load() method fails

[bugzilla-daemon at portal.open-bio.org]

http://bugzilla.open-bio.org/show_bug.cgi?id=1921

           Summary: BioSeqDatabase.load() method fails
           Product: Biopython
           Version: Not Applicable
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: major
          Priority: P2
         Component: BioSQL
        AssignedTo: biopython-dev at biopython.org
        ReportedBy: lpritc at scri.sari.ac.uk


Using Fedora Core 3, MySQL Ver 14.7 Distrib 4.1.11 with BioPython from CVS
head.

On attempting to follow the documentation code at
http://www.biopython.org/docs/biosql/python_biosql_basic.html#htoc10 to
populate a BioSQL database from the example GenBank file, an error was thrown,
with traceback:

  File "/usr/lib/python2.3/site-packages/BioSQL/BioSeqDatabase.py", line 414,
in load
    db_loader.load_seqrecord(cur_record)
  File "/usr/lib/python2.3/site-packages/BioSQL/Loader.py", line 37, in
load_seqrecord
    bioentry_id = self._load_bioentry_table(record)
  File "/usr/lib/python2.3/site-packages/BioSQL/Loader.py", line 251, in
_load_bioentry_table
    self.adaptor.execute(sql, (self.dbid,
  File "/usr/lib/python2.3/site-packages/BioSQL/BioSeqDatabase.py", line 277,
in execute
    self.cursor.execute(sql, args or ())
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in
defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1216, 'Cannot add or update a child row: a foreign key
constraint fails')

This problem had previously been reported under a different configuration on
the BioPython discussion mailing list at
http://www.biopython.org/pipermail/biopython/2005-July/002716.html

The test_BioSQL.py script with the CVS BioPython failed with the same error:

[lpritc at lplinuxdev Tests]$ python test_BioSQL.py
Load SeqRecord objects into a BioSQL database. ... ERROR
Get a list of all items in the database. ... ERROR
Test retrieval of items using various ids. ... ERROR
Make sure Seqs from BioSQL implement the right interface. ... ERROR
Check SeqFeatures of a sequence. ... ERROR
Make sure SeqRecords from BioSQL implement the right interface. ... ERROR
Check that slices of sequences are retrieved properly. ... ERROR
Make sure all records are correctly loaded. ... ERROR
Indepth check that SeqFeatures are transmitted through the db. ... ERROR

======================================================================
ERROR: Load SeqRecord objects into a BioSQL database.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test_BioSQL.py", line 316, in t_load_database
    self.db.load(self.iterator)
  File "/usr/lib/python2.3/site-packages/BioSQL/BioSeqDatabase.py", line 414,
in load
    db_loader.load_seqrecord(cur_record)
  File "/usr/lib/python2.3/site-packages/BioSQL/Loader.py", line 37, in
load_seqrecord
    bioentry_id = self._load_bioentry_table(record)
  File "/usr/lib/python2.3/site-packages/BioSQL/Loader.py", line 251, in
_load_bioentry_table
    self.adaptor.execute(sql, (self.dbid,
  File "/usr/lib/python2.3/site-packages/BioSQL/BioSeqDatabase.py", line 277,
in execute
    self.cursor.execute(sql, args or ())
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in
defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1216, 'Cannot add or update a child row: a foreign key
constraint fails')

The problem seems to stem from the DatabaseLoader._load_bioentry_table() method
in Loader.py - a previous fix attempts to solve a previous problem with the
population of the bioentry.taxon_id field by assigning it the value "0" in the
INSERT SQL statment.  Attempting to do this in a database where the taxon table
is unpopulated is a violation of a foreign key constraint in both the current
BioSQL schema, and the one that ships with BioPython, and throws the error
seen.

I modified the code in DatabaseLoader._load_bioentry_table() so that the INSERT
statement no londer attempts to populate the bioentry.taxon_id field, which is
left to take the default value of NULL.  The diff is below:

226c226
<         taxon_id = "0" # inserted this because the taxon population code is
out of date
---
>         #taxon_id = "0" # inserted this because the taxon population code is out of date
231a232,234
>       # removed taxon_id field, as it was causing difficulties with the
>       # schema  - not inserting a value allows it to default to NULL,
>       # avoiding violation of the foreign key constraint.
235d237
<          taxon_id,
249d250
<          %s,
252d252
<                                    taxon_id,




------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.