[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 21.12.2014 20:46, Michael Bayer wrote:
> M.-A. Lemburg <mal at egenix.com> wrote:
>>> However, getting back to the issue of prepared statements, I will note that
>>> specific to MySQL, MySQL’s own DBAPI driver, MySQL-connector-Python, does in
>>> fact tie the prepared statement to the cursor:
>>> http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html,
>>> most expediently as a flag to the connection.cursor() call, “cursor =
>>> connection.cursor(prepared=True)”, which is a shortcut for using their
>>> CursorPrepared cursor class.     
>>>
>>> I actually find MySQL-connector’s API quite appealing here: "The first time
>>> you pass a statement to the cursor's execute() method, it prepares the
>>> statement. For subsequent invocations of execute(), the preparation phase is
>>> skipped if the statement is the same.”
>>
>> That's a standard DB-API optimization feature, not special to MySQL.
> 
> I see no mention of the “prepare” keyword at https://www.python.org/dev/peps/pep-0249/#cursor …

I was referring to this part:

>>> "The first time you pass a statement to the cursor's execute() method, it prepares the
>>> statement. For subsequent invocations of execute(), the preparation phase is
>>> skipped if the statement is the same.”

This is the same as standard DB-API:

c = connection.cursor()
stmt = 'select * from mytable where id = ?'
c.execute(stmt, [8989])
result = c.fetchone()
c.execute(stmt, [1212])
result = c.fetchone()

The first execute will prepare and run the stmt. The second will
reuse the already perpared stmt and simply run it against a different
set of parameters.

>> Michael, I think you've misunderstood the proposal. The idea is
>> to expose the prepare step which normally happens implicitly,
>> as an explicit optional intermediate step. The main DB-API
>> functionality is not affected by the proposal.
>>
>> We're only trying to find a standard for database modules to adapt
>> to, which are already exposing the prepare step. Just like we've
>> done several times in the past with other extensions which
>> several modules implemented in sometimes slightly different
>> ways.
> 
> Right.  I’m referring to the explicit API of MySQL-connector which introduces a “prepare” keyword argument to connection.cursor() as one possible option, one that already works well without the need for extra method calls and bigger changes in use.

>From what I read, the keyword parameter merely enables
the above optimization.

It also doesn't provide a way to prepare a statement without
executing it and again, we're not trying to make things more
complicated, only provide access to intermediate implicit steps.

>> The aim here is to prevent different semantics from causing
>> surprises with the database module users.

I would like to hear back from some other module authors,
esp. those who are not targeting MySQL/MariaDB, since we've
already had lots of feedback for those databases.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 21 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/