Quantcast

need some database API design advice

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

need some database API design advice

Peter Eisentraut-2
I'm looking for some API design advice.

The PostgreSQL developers are currently considering an extension of the
PL/Python database access API [0].  This is, for better or worse, a
lower-level custom Python database access API, but you can build DB-API
on top of that.

We have added a few functions that extract metadata such as column names
and types from a result set object (rv.colnames(), rv.coltypes(); think
cursor.description).  The question was what to do when there is no
resulting row set, because the command was a utility command such as
CREATE TABLE.  Option 1 was that the functions should throw an
exception, because the request is invalid.  Option 2 was that the
functions should return None.  (This is what cursor.description is
specified to do.)  This was objected to because it would require extra
checking for None.  In addition to that, the question relative to option
1 in particular was how to detect whether a result row set exists, to
avoid the exception-throwing calls.  With option 2 you could check for
is None, of course.  There is an ongoing discussion [1] about which ones
of these would be better style.

So, in terms of code, which one of these is "better"?

1a.

rv = plpy.execute("some SQL command")
try:
    output(rv.colnames())
except SomeException:
    output("it's a utility command")

1b.

rv = plpy.execute("some SQL command")
if rv.has_rows():  # some currently nonexisting function to be added
    output(rv.colnames())
else:
    output("it's a utility command")

2.

rv = plpy.execute("some SQL command")
if rv.colnames() is not None:
    output(rv.colnames())
else:
    output("it's a utility command")


[0] http://www.postgresql.org/docs/devel/static/plpython-database.html
[1] http://archives.postgresql.org/message-id/CAK6bCay4yrFJD3po_bCke4ukjjsPLkbf+ad07jZiAU3N6cwUiA@...

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need some database API design advice

M.-A. Lemburg
Peter Eisentraut wrote:

> I'm looking for some API design advice.
>
> The PostgreSQL developers are currently considering an extension of the
> PL/Python database access API [0].  This is, for better or worse, a
> lower-level custom Python database access API, but you can build DB-API
> on top of that.
>
> We have added a few functions that extract metadata such as column names
> and types from a result set object (rv.colnames(), rv.coltypes(); think
> cursor.description).  The question was what to do when there is no
> resulting row set, because the command was a utility command such as
> CREATE TABLE.  Option 1 was that the functions should throw an
> exception, because the request is invalid.  Option 2 was that the
> functions should return None.  (This is what cursor.description is
> specified to do.)  This was objected to because it would require extra
> checking for None.  In addition to that, the question relative to option
> 1 in particular was how to detect whether a result row set exists, to
> avoid the exception-throwing calls.  With option 2 you could check for
> is None, of course.  There is an ongoing discussion [1] about which ones
> of these would be better style.
>
> So, in terms of code, which one of these is "better"?
>
> 1a.
>
> rv = plpy.execute("some SQL command")
> try:
>     output(rv.colnames())
> except SomeException:
>     output("it's a utility command")
>
> 1b.
>
> rv = plpy.execute("some SQL command")
> if rv.has_rows():  # some currently nonexisting function to be added
>     output(rv.colnames())
> else:
>     output("it's a utility command")
>
> 2.
>
> rv = plpy.execute("some SQL command")
> if rv.colnames() is not None:
>     output(rv.colnames())
> else:
>     output("it's a utility command")

When writing code, you typically know whether you are expecting
a result set or not, so you don't run into such problems often.

That said, calling a function is different than accessing an
attribute. You normally expect a function to raise an exception
in case it cannot process your request (due to a missing
result set). The .fetch...() APIs also raise an exception
if no result is present.

Attribute access does not tend to generate exceptions if the
values cannot be set. They simply revert to a default value
(which is None in case of cursor.description).

> [0] http://www.postgresql.org/docs/devel/static/plpython-database.html
> [1] http://archives.postgresql.org/message-id/CAK6bCay4yrFJD3po_bCke4ukjjsPLkbf+ad07jZiAU3N6cwUiA@...

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 11 2012)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2012-04-28: PythonCamp 2012, Cologne, Germany              17 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need some database API design advice

Chris Clark
In reply to this post by Peter Eisentraut-2
On Wednesday 2012-04-11 12:44 (-0700), Peter Eisentraut
<[hidden email]> wrote:

> ....
> So, in terms of code, which one of these is "better"?
>
> 1a.
>
> rv = plpy.execute("some SQL command")
> try:
>      output(rv.colnames())
> except SomeException:
>      output("it's a utility command")
>
> 1b.
>
> rv = plpy.execute("some SQL command")
> if rv.has_rows():  # some currently nonexisting function to be added
>      output(rv.colnames())
> else:
>      output("it's a utility command")
>
> 2.
>
> rv = plpy.execute("some SQL command")
> if rv.colnames() is not None:
>      output(rv.colnames())
> else:
>      output("it's a utility command")

My 2 cents, I don't like any of them :-p

I prefer the last one modified slightly:

    rv = plpy.execute("some SQL command")
    if rv.colnames():
         output(rv.colnames())
    else:
         output("it's a utility command")

I.e. loose the None check. I'm not sure I like making an explicit
function call for the colnames. I.e. I think sticking with the pep249
description attribute is a better solution, the API already exist so
clone that (when possible) in your new api.

....Of course a lot of this is is down to style preferences.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need some database API design advice

Peter Eisentraut-2
On ons, 2012-04-11 at 12:48 -0700, Chris Clark wrote:

> > rv = plpy.execute("some SQL command")
> > if rv.colnames() is not None:
> >      output(rv.colnames())
> > else:
> >      output("it's a utility command")
>
> My 2 cents, I don't like any of them :-p
>
> I prefer the last one modified slightly:
>
>     rv = plpy.execute("some SQL command")
>     if rv.colnames():
>          output(rv.colnames())
>     else:
>          output("it's a utility command")
>
> I.e. loose the None check.

That would misbehave if a result set exists but contains zero columns.
(Sounds strange, but it's possible.)  Which actually now makes me lean
toward throwing an exception.

> I'm not sure I like making an explicit
> function call for the colnames. I.e. I think sticking with the pep249
> description attribute is a better solution, the API already exist so
> clone that (when possible) in your new api.

That would require populating this structure on every call, which would
be expensive for such a low-level API, or turning the attribute into a
fake function, which would be evil.  I think this is best left to the
plpydbapi layer on top of it.


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need some database API design advice

M.-A. Lemburg
Peter Eisentraut wrote:
>> I'm not sure I like making an explicit
>> function call for the colnames. I.e. I think sticking with the pep249
>> description attribute is a better solution, the API already exist so
>> clone that (when possible) in your new api.

I agree with Chris Clark here... doing so saves you complexity in the
dbapi layer you intend to write on top of the low level API.

> That would require populating this structure on every call, which would
> be expensive for such a low-level API, or turning the attribute into a
> fake function, which would be evil.  I think this is best left to the
> plpydbapi layer on top of it.

Using properties you can do this on the fly and only when
needed. If you're writing the API in C, you can also create the
.description tuple on demand and only when needed. You can also
cache it in case you don't want to take the small extra hit
of having to recreate it every time.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 13 2012)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2012-04-28: PythonCamp 2012, Cologne, Germany              15 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need some database API design advice

Chris Clark
On Friday 2012-04-13 14:25 (-0700), M.-A. Lemburg <[hidden email]> wrote:

> Peter Eisentraut wrote:
>>> I'm not sure I like making an explicit
>>> function call for the colnames. I.e. I think sticking with the pep249
>>> description attribute is a better solution, the API already exist so
>>> clone that (when possible) in your new api.
> I agree with Chris Clark here... doing so saves you complexity in the
> dbapi layer you intend to write on top of the low level API.
>
>> That would require populating this structure on every call, which would
>> be expensive for such a low-level API, or turning the attribute into a
>> fake function, which would be evil.  I think this is best left to the
>> plpydbapi layer on top of it.
> Using properties you can do this on the fly and only when
> needed. If you're writing the API in C, you can also create the
> .description tuple on demand and only when needed. You can also
> cache it in case you don't want to take the small extra hit
> of having to recreate it every time.
>

That was what I had in mind.

This is slightly off topic for C but it illustrates why an "if true" is
usually considered more Pythonic than an explicit "is [not] None" check
(for attributes/objects at any rate, not function call results).

Consider:

    class MyClass():
         def __nonzero__(self):
             return False


    x = MyClass()
    if x:
         print 'x is true'
    else:
         print 'x is False'

the "if x" check is usually a inexpensive check. For more python magic
methods, check out the excellent article
http://www.rafekettler.com/magicmethods.html

I think I confused the issue by leaving the function call in the example
when my comment suggested not doing that.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Loading...