An interface for parameterised SQL statements (working with placeholders) is provided by the execute() method from the Cursor class at the moment.
https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

I assume that the "SQL Statement Object" from the SQLite C interface is reused there already.
http://sqlite.org/c3ref/stmt.html

I imagine that it will be more efficient occasionally to offer also a base class like "prepared_statement" so that the parameter specification does not need to be parsed for every passed command.
I suggest to improve corresponding preparation and compilation possibilities.

The low-hanging fruit of executemany() reusing the prepared statement is of course taken. Also, there is a statement cache that is being used transparently.

I am against exposing the statement directly via the API.

Are you really against benefits from reusing of existing application programming interfaces for the explicit preparation and compilation of SQL statements?

It seems that other software contributors like Marc-Andre Lemburg and Tony Locke show more constructive opinions.
https://mail.python.org/pipermail/db-sig/2014-December/006133.html
https://www.mail-archive.com/db-sig@python.org/msg01829.html
http://article.gmane.org/gmane.comp.python.db/3784

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

As Gerhard said in msg233384, there is already a statement cache. sqlite3_prepare_v2() is only called if the statement is not found in the cache.

Current behaviour:
>>> import sqlite3
>>> cx = sqlite3.connect(":memory:")
>>> cu = cx.cursor()
>>> cu.execute("select 1")  # sqlite3_prepare_v2() called
>>> cu.execute("select 1")  # sqlite3_prepare_v2() is _not_ called


Suggesting to close this issue.