[Biopython-dev] [Bug 2506] New: SELECT problems on _get_seqfeature_dbxref in Loader.py with postgresql

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

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

           Summary: SELECT problems on _get_seqfeature_dbxref in Loader.py
                    with postgresql
           Product: Biopython
           Version: 1.45
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: blocker
          Priority: P2
         Component: BioSQL
        AssignedTo: biopython-dev at biopython.org
        ReportedBy: andrea at biodec.com
                CC: andrea at biodec.com


Using: 
  - postgres 8.3 or less # the version is not important
  - BioSQL 1.0.0 installed on a postgresql database (on Linux) # the version is
not important
  - python-psycopg 1.1.21-14 or less
  - python-psycopg2 2.0.5.1-6 or less
  - python 2.4.4-2 # not important
  - Biopython CVS version 28/05/08,
    - Loader.py version 1.30
  - "psycopg" or "psycopg2" as BioSeqDatabase.open_database drivers

During insertion in the BioSQL database of a seq_record object derived from a
GenBank Iterator, the procedure _get_seqfeature_dbxref fails with the errror:

Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "/var/lib/python-support/python2.4/BioSQL/BioSeqDatabase.py", line 420,
in load
    db_loader.load_seqrecord(cur_record)
  File "/var/lib/python-support/python2.4/BioSQL/Loader.py", line 50, in
load_seqrecord
    self._load_seqfeature(seq_feature, seq_feature_num, bioentry_id)
  File "/var/lib/python-support/python2.4/BioSQL/Loader.py", line 542, in
_load_seqfeature
    self._load_seqfeature_qualifiers(feature.qualifiers, seqfeature_id)
  File "/var/lib/python-support/python2.4/BioSQL/Loader.py", line 641, in
_load_seqfeature_qualifiers
    seqfeature_id)
  File "/var/lib/python-support/python2.4/BioSQL/Loader.py", line 679, in
_load_seqfeature_dbxref
    self._get_seqfeature_dbxref(seqfeature_id, dbxref_id, rank+1)
  File "/var/lib/python-support/python2.4/BioSQL/Loader.py", line 712, in
_get_seqfeature_dbxref
    result = self.adaptor.execute_and_fetch_col0(sql, (seqfeature_id,
  File "/var/lib/python-support/python2.4/BioSQL/BioSeqDatabase.py", line 295,
in execute_and_fetch_col0
    self.cursor.execute(sql, args or ())
psycopg.ProgrammingError: ERROR:  column "195" does not exist

SELECT seqfeature_id, dbxref_id FROM seqfeature_dbxref WHERE seqfeature_id =
"195" AND dbxref_id = "207739"

The problem is that there is an error in the query format at rows 710 and 711
of the Loader.py in Biopyton/BioSQL:
    709    # Check for an existing record
    710    sql = r'SELECT seqfeature_id, dbxref_id FROM seqfeature_dbxref ' \
    711          r'WHERE seqfeature_id = "%s" AND dbxref_id = "%s"'
because the query has double quotes (") around the values, and
postgres interprets them as Column names and not values.

If you correct the query with single quotes, you correct the error. 
    709    # Check for an existing record
    710    sql = r"SELECT seqfeature_id, dbxref_id FROM seqfeature_dbxref " \
    711          r"WHERE seqfeature_id = '%s' AND dbxref_id = '%s'"


-- 
Configure bugmail: http://bugzilla.open-bio.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.