[DB-SIG] Improved support for prepared SQL statements

M.-A. Lemburg mal at egenix.com
Wed Dec 17 22:25:20 CET 2014

On 17.12.2014 20:37, Tony Locke wrote:
> On the question of extending the DB-API to accommodate prepared
> statements, from my narrow point of view as a maintainer of pg8000
> it's unnecessary, as pg8000 uses prepared statements for everything
> anyway. In later versions of Postgres, the query plan is potentially
> updated every time a prepared statement is executed, as opposed to the
> old days when the query plan for a prepared statement was fixed.
> So (again from my limited point of view) it seems there isn't any
> reason ever to use anything other than a prepared statement.


Just clarify: some databases don't need the prepare step
on the client side, since they do (mostly) everything on the
server side. Doing a prepare step will either not change
anything, or worse, add an unnecessary network roundtrip.

With others you always have to run a prepare step, since
the clients needs the parsed data to convert/handle

For this reason, ODBC provides both modes of operation.

The optional .prepare() method we're discussing here
is only be useful for the latter class of databases,
since the DB-API currently doesn't provide a way to say
"prepare this SQL for execution, but don't execute it
just yet".

> Regards,
> Tony.
> On 17 December 2014 at 17:54, Michael Bayer <mike_mp at zzzcomputing.com> wrote:
>>> On Dec 17, 2014, at 10:41 AM, M.-A. Lemburg <mal at egenix.com> wrote:
>>> Another possibility is to write a layer on top of the DB-API
>>> to abstract the underlying queries away from the application
>>> and only have the layer provide dedicated methods for the
>>> things the application needs, such as query method for specific
>>> details or inserting application objects into the database.
>>> This is the approach we usually take in our projects, since
>>> it provides better separation of the application logic from the
>>> database logic than using ORMs usually provides. It's also possible
>>> to use such an abstraction layer on top of an ORM, if you want
>>> to the ORM to deal with abstracting away database backend details.
>> When I read this quickly, it seems to make sense, but when I really try to imagine what this means, I come out at the same place every time: if you’ve written a “method for inserting application objects into the database”, you’ve written an ORM.   Databases don’t store “application objects”, they store rows.  So there has to be “ORM” in there.
>>>> I am looking for higher service levels without following the software
>>>> design directions from object-relational managers like SQLAlchemy
>>>> and SQLObject.
>> replying to Markus -
>> SQLAlchemy’s ORM is only an optional component of the SQLAlchemy database toolkit overall.  If you would like a very mature and well proven SQL abstraction layer that does not provide any design directions whatsoever (not to mention much better performance than the ORM), please consider SQLAlchemy Core: http://docs.sqlalchemy.org/en/rel_0_9/core/index.html.
>> _______________________________________________
>> DB-SIG maillist  -  DB-SIG at python.org
>> https://mail.python.org/mailman/listinfo/db-sig

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Dec 17 2014)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> mxODBC Plone/Zope Database Adapter ...       http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2014-12-11: Released mxODBC Plone/Zope DA 2.2.0   http://egenix.com/go67

::::: Try our 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

More information about the DB-SIG mailing list