[Biopython-dev] BioSQL test code on Postgres

[Peter]

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?

> Should we be type checking in __delitem__ (line 517) in BioSeqDatabase.py so
> that trying to delete a bioentry_id that is a string throws an appropriate
> error?
>
> Otherwise the BioSQL tests pass on PostGreSQL.
>
> The default DBDRIVER PostgreSQL driver in setup.py should be changed to
> "pyscopg2"
>
> Cheers, Cymon

Thanks,

Peter