[DB-SIG] Improved support for prepared SQL statements

[INADA Naoki]

On Thu, Dec 18, 2014 at 9:33 PM, M.-A. Lemburg <mal at egenix.com> wrote:
> On 18.12.2014 12:27, INADA Naoki wrote:
>> On Thu, Dec 18, 2014 at 7:39 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>>> On 17.12.2014 19:13, INADA Naoki wrote:
>>>> As I said before, prepared statement is normally bound to connection.
>>>> So `.prepare()` method should be connection's method, not cursor's.
>>>>
>>>> prepared = conn.prepare("SELECT ?+?")
>>>> ...
>>>> cur = conn.cursor()
>>>> cur.execute(prepared, (1, 2))
>>>> cur.fetchone()  # returns (3,)
>>>
>>> I'm not sure which database you are talking about,
>>
>> I'm a developer of MySQL drivers (PyMySQL and mysqlclient).
>> prepared statement is alive as long as connection is alive.
>> We can use one prepared statement multiple times.
>
> So MySQL separates the concepts of a prepared statement and
> a statement which is used to execute a query ?

I can't catch what you mean exactly.
In MySQL, COM_PREPARE returns statement id.

http://dev.mysql.com/doc/internals/en/com-stmt-prepare-response.html#packet-COM_STMT_PREPARE_OK

Cursor's lifetime may be as short as executing one query.
Typical usage is:

_query = "SELECT a, b FROM tbl WHERE id=? AND k<?"

def some_query(con, params):
    cur = con.cursor()
    try:
        cur.execute(_query, params)
        return cur.fetchall()
    finally:
        cur.close()

Then, to reuse prepared statement, connection should cache prepared query.

def some_query(con, params):
    # con manages prepared statement to avoid prepare same statement
multiple times.
    prepared = con.prepare(_query)
    cur = con.cursor()
    try:
        cur.execute(prepared, params)
        return cur.fetchall()
    finally:
        cur.close()

>
>>> but in terms
>>> of concepts, statements are run on cursors, so adding the method
>>> to connections doesn't look right (we dropped this logic when moving
>>> from DB-API 1.0 to 2.0 a long time ago).
>>
>> PEP 249 says:
>>
>>> Cursor Objects
>>>
>>> These objects represent a database cursor, which is used to manage the context of a fetch operation.
>>
>> Preparing statement is not fetching query result.
>
> The DB-API is a bit terse in this respect. The two central concepts
> in the DB-API are connections and cursors:
>
> Connections provide a connection interface to the database and
> encapsulate a transactional view on database operations.
>
> Cursors provide a way to execute SQL statements and fetch the
> corresponding data. Cursors are created on connections and
> bound to these.
>
> Now instead of creating a third concept, that of a prepared
> statement, I think it's better to stick to the above two concepts
> and simply add a method to access the intermediate step of preparing
> a statement on the cursor, which will then get executed on the
> cursor.
>

OK.  In case of MySQL, cursor.prepare() can proxy to connection.prepare()
to reuse prepared statement over cursor lifetime.

But if prepares statement is bound to cursor, why not just adding
`PreparedCusor` type?
I feel there are no need for providing `.prepare()` method separately.


> Since the DB-API tries to provide an API which works for many
> databases, the small glitch with having MySQL use a different
> concept is acceptable, IMO.
>
>>> Also note that the prepare step may need access to the
>>> cursor configuration settings to be correctly interpreted
>>> by the database.
>>
>> I'm not sure which database you are talking about.
>> MySQL has configuration per connection, not per cursor.
>
> My experience is from working with ODBC and ODBC drivers.
> IBM DB2 and MS SQL Server use ODBC as their native database
> interface API. In ODBC, cursors are called "statements" and
> you have two modes of operation:
>
> a) direct execution, which sends the SQL straight to the
>    database
>
> b) prepare + execute, which separates the prepare from the
>    execute step

Doesn't it support b') prepare first, and execute it multiple times after ?

>
> You can configure both connections and cursors in ODBC,
> e.g. the cursor type setting is configured on a per
> cursor basis and this has direct influence on what locking
> mechanisms need to be enabled in the database to run the
> SQL statement:
>
> http://msdn.microsoft.com/en-us/library/ms712631%28v=vs.85%29.aspx
>
> Note that the DB-API is modeled in many aspects after the ODBC
> API and its concepts, since ODBC is an industry standard and
> provides a good common denominator for how database APIs work
> and which concepts they can support.
>
> --
> Marc-Andre Lemburg
> eGenix.com
>
> Professional Python Services directly from the Source  (#1, Dec 18 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/



-- 
INADA Naoki  <songofacandy at gmail.com>