<div dir="ltr"><div><div><div><div><div><div>Let me (as the maintainer of a competing product) be the one to say this:<br></div><br>If prepared statements were to become standardized (either by an extension of PEP-249 or by a new Python3 compatible PEP) it should be simply a codification of the existing API used by mxodbc. (see <a href="http://www.egenix.com/products/python/mxODBC/mxODBC.pdf">mxODBC.pdf</a>). It is a good system, well thought out, simple, proven, and already implemented for numerous databases.<br>
<br></div>Here is a brief description:<br><br></div>A cursor "X" has a method "X.prepare()" which does nothing except create side effects. The required side effect is to set an attribute "X.command" which is simply a reference to its first positional argument. It may optionally also have the side effect of sending an appropriate query preparation statement to its database engine. An optional third side effect is that it may also populate the "X.description" array (my anticipated implementation will not do that). <br>
<br></div>If the first positional argument of a subsequent "X.execute()" statement also refers to the same query string, then the package may use the prepared statement to query the database. If the strings are different, it merely starts over as if no "X.prepare()" had been executed.<br>
<br></div>In other words, the following three code snippets will have exactly the same effect (except for timing):<br><br></div><div><code><br></div><div>crsr = some_connection.cursor()<br></div><div><br></div><div>
# prepared statement<br></div><div><div>crsr.prepare("select * from SomeSQLtable")<br></div><div>crsr.execute(crsr.command)<br><br></div><div># the way we do it now<br></div><div>crsr.execute("select * from SomeSQLtable")<br>
<br></div><div># an otherwise harmless waste of time<br></div><div>crsr.prepare("select * from ThisIsNotTheTableYouAreLookingForMoveAlong")<br>crsr.execute("select * from SomeSQLtable")<br></div><div>
</code><br>
<br></div><div>The only standard needed is: "If a .prepare() method is provided, it must define .command, and set .description to either None, or a valid result from the query."<br></div></div><div class="gmail_extra">
<br></div><div class="gmail_extra"><code><br></div><div class="gmail_extra"># this would be a compliant driver snippet<br></div><div class="gmail_extra">class MySqlApi(object):<br> def prepare(self, sql):<br></div>
<div class="gmail_extra"> self.command = sql<br></div><div class="gmail_extra"> self.description = None<br></div><div class="gmail_extra"></code><br>--<br></div><div class="gmail_extra">Vernon Cole<br>
<br></div><div class="gmail_extra"><div class="gmail_quote">On Mon, Apr 7, 2014 at 3:21 PM, Peter Eisentraut <span dir="ltr"><<a href="mailto:peter_e@gmx.net" target="_blank">peter_e@gmx.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
On 3/24/14, 12:53 PM, Daniele Varrazzo wrote:<br>
> - is there enough consensus - not only in the Python world - about how<br>
> to implement a prepared statements interface on a db driver?<br>
<br>
In other languages, there is often a parameter on the connection handle<br>
saying whether (the analogue of) a prepare+execute call should be only a<br>
parameter substitution or a full prepared statement. I think this is<br>
due to the historical confusion between these aspects and therefore not<br>
a good idea for a new API. In Python, adding an explicit prepare<br>
function and having that actually prepare in all cases sounds like a<br>
decent idea to me.<br>
<br>
_______________________________________________<br>
DB-SIG maillist - <a href="mailto:DB-SIG@python.org">DB-SIG@python.org</a><br>
<a href="https://mail.python.org/mailman/listinfo/db-sig" target="_blank">https://mail.python.org/mailman/listinfo/db-sig</a><br>
</blockquote></div><br></div></div>