[Biopython-dev] BioSQL test code on Postgres

[Cymon Cox]

Hi Peter,

On 30 August 2010 19:21, Peter <biopython at maubp.freeserve.co.uk> wrote:

> On Mon, Aug 30, 2010 at 6:36 PM, Cymon Cox <cy at cymon.org> wrote:
> > Hi Folks,
> >
> > The current test code in test_BioSQL.py fails on PostgreSQL;
> >
> > ERROR: Check list, keys, length etc
> > ----------------------------------------------------------------------
> > Traceback (most recent call last):
> >  File "/home/cymon/git/biopython-github-master/Tests/test_BioSQL.py",
> line
> > 187, in test_get_db_items
> >    del db["non-existant-name"]
> >  File "/home/cymon/git/biopython-github-master/BioSQL/BioSeqDatabase.py",
> > line 519, in __delitem__
> >    if key not in self:
> >  File "/home/cymon/git/biopython-github-master/BioSQL/BioSeqDatabase.py",
> > line 539, in __contains__
> >    (self.dbid, value))[0])
> >  File "/home/cymon/git/biopython-github-master/BioSQL/BioSeqDatabase.py",
> > line 423, in execute_and_fetch_col0
> >    self.execute(sql, args or ())
> >  File "/home/cymon/git/biopython-github-master/BioSQL/BioSeqDatabase.py",
> > line 404, in execute
> >    self.dbutils.execute(self.cursor, sql, args)
> >  File "/home/cymon/git/biopython-github-master/BioSQL/DBUtils.py", line
> 33,
> > in execute
> >    cursor.execute(sql, args or ())
> > DataError: invalid input syntax for integer: "non-existant-name"
> > LINE 1: ...M bioentry WHERE biodatabase_id=1 AND
> bioentry_id=E'non-exis...
> >
> > Because when trying to delete a bioentry_id that is a string type, ie.
> > "non-existant-name" (line 188 on test_BioSQL.py),  postgres throws an
> error
> > rather than returning a long (0,1) as in sqlite (and presumably MySQL (I
> > havent tried it)).
>
> This (test_get_db_items) is once the unit tests added since Biopython 1.54,
> while I was working on making the BioSQL objects act more like
> dictionaries.
> I think the SQL statements for the __contains__ method (and others added
> recently) may need single quotes round the %s placeholders. Does that work?
>


Nope. The bioentry_id parameter is already being passed as a string -
psycopg automatically converts python objects into SQL literals (see
http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters
).

Here is the same error using the psql interface:

biosqldb=# select count(bioentry_id) from bioentry where biodatabase_id=1
and bioentry_id='non-existant';
ERROR:  invalid input syntax for integer: "non-existant"
LINE 1: ...m bioentry where biodatabase_id=1 and bioentry_id='non-exist...

biosqldb=# \d bioentry;
                                     Table "public.bioentry"
     Column     |          Type          |
Modifiers
----------------+------------------------+-------------------------------------------------------
 bioentry_id    | integer                | not null default
nextval('bioentry_pk_seq'::regclass)

Cheers, Cymon