On 18.12.2014 12:27, INADA Naoki wrote:
> On Thu, Dec 18, 2014 at 7:39 PM, M.-A. Lemburg <m...@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 ?
>> 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.
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
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/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
