[issue22956] Improved support for prepared SQL statements

[mike bayer]

mike bayer added the comment:

prepared statements are, in proportion to the typical speed issues in Python (see my comparison benchmark at https://mail.python.org/pipermail/db-sig/2014-December/006147.html) a fairly small optimization that the DBAPI already allows for in an implicit sense, via an internal statement cache - the execute() method of DBAPI (see https://www.python.org/dev/peps/pep-0249/#id14) allows for this optimization.   

Therefore an application that wishes to use this optimization with a participating DBAPI only need to maintain a reference to the cursor, and continue to use that same cursor for the same statement - pretty much identically to how it would be used in the explicit prepare step.     

An explicit prepare step should be no more intrusive than an optional flag sent along to cursor(), as MySQL-connector does, see http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-cursor.html.      

The DBAPI does not use objects to represent statements.   In JDBC, there is a Statement and PreparedStatement object, but because JDBC has no explicit sense of a "cursor", these are in fact just cursor objects (see https://mail.python.org/pipermail/db-sig/2014-December/006168.html for my description of this).

Therefore we are really just talking about a potentially modified cursor class, and in Python we can just use a flag, there's no need for heavy-handed Java-esque concepts like new classes. 

If one really wishes there were a PreparedStatement class, using the flag approach one can have it with very simple code that IMO does not belong in the DBAPI:

class PreparedStatement(object):
    def __init__(self, connection, statement):
        self.cursor = connection.cursor(prepared=True)
        self.statement = statement

    def execute(self, params):
        self.cursor.execute(self.statement, params)

    def fetchall(self):
        return self.cursor.fetchall()

    # ... etc

----------

_______________________________________
Python tracker <report at bugs.python.org>
<http://bugs.python.org/issue22956>
_______________________________________