[Biopython-dev] BioSQL test code on Postgres

[Cymon Cox]

Hi P.,

On 31 August 2010 11:38, Peter <biopython at maubp.freeserve.co.uk> wrote:

> On Tue, Aug 31, 2010 at 10:11 AM, Cymon Cox <cy at cymon.org> wrote:
> > Hi Peter,
> >
> > 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"
> > ...
> >
> > Cheers, Cymon
>
> I think I get it now - the bioentry_id is an integer (in all the schemas),
> and PostgreSQL throws an error due to the type mismatch (we are
> comparing it to a string) while MySQL and SQLite just return no
> matches. How's this?:
>
>
> http://github.com/biopython/biopython/commit/050963bd3bbd6653101306eed9aab6c629cf9375
>


Sure - nice and simple.

Or catching the exceptions, this'll work:

diff --git a/BioSQL/BioSeqDatabase.py b/BioSQL/BioSeqDatabase.py
index 45c0774..57d6ab9 100644
--- a/BioSQL/BioSeqDatabase.py
+++ b/BioSQL/BioSeqDatabase.py
@@ -533,9 +533,15 @@ class BioSeqDatabase:
         """Check if a primary (internal) id is this namespace (sub
database)."""
         sql = "SELECT COUNT(bioentry_id) FROM bioentry " + \
               "WHERE biodatabase_id=%s AND bioentry_id=%s;"
-        return bool(self.adaptor.execute_and_fetch_col0(sql,
-                                                        (self.dbid,
value))[0])
-
+        try:
+            return bool(self.adaptor.execute_and_fetch_col0(sql,(self.dbid,
value))[0])
+        except (self.adaptor.conn.DataError,
+                self.adaptor.conn.DatabaseError), e:
+            if "invalid input syntax for integer" in e.__str__():
+                return False
+            else:
+                raise
+
     def __iter__(self):
         """Iterate over ids (which may not be meaningful outside this
database)."""
         #TODO - Iterate over the cursor, much more efficient


With either correction, the test will pass with the PyGreSQL driver as well.

Cheers, C.