[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

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
parameters.

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
eGenix.com

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
               http://www.egenix.com/company/contact/