The sqlite3 module is wonderful, but the one advantage that C/C++ coders have using that system is the ability to use precompiled/prepared SQL statements.  Some SQL databases like Postgresql allow you to precompile statements using special SQL statements (e.g. PREPARE), so there is no need to change the python DBI for those database systems.  But unfortunately, sqlite3 only offers a C/C++ function, which returns a handle to the prepared statement.  I have sought a way to call this from the python DBI, but I simply don't think it's exposed.

Since my application interleaves several SQL commands, depending on the results of the last iteration, I can't immediately use executemany() to obtain maximum performance on my code.  Precompiled statements offer me the best opportunity to optimize.  I can prepare the 2 or 3 most expensive queries in my loop and call them in whatever order I need.  There are some estimates that prepared statements can accelerate complex queries by an order of magnitude, as the parser and optimizers don't need to come into play for every call.

Would you consider an extension to the sqlite3 DBI to expose prepared statements?  Thanks.

The sqlite3 module already uses prepared statements. Quoting from the documentation:

"The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 100 statements."

Okay, I missed that in the documentation.  I was looking for a handle to a
prepared statement.  I suppose it's hashing on the text of the SQL statement
to determine equivalence?

I'm willing to retract the request.  I need to restructure my code a little
bit to take advantage of this feature.

On Sat, Sep 17, 2011 at 5:47 PM, poq <report@bugs.python.org> wrote:

>
> poq <poq@gmx.com> added the comment:
>
> The sqlite3 module already uses prepared statements. Quoting from the
> documentation:
>
> "The sqlite3 module internally uses a statement cache to avoid SQL parsing
> overhead. If you want to explicitly set the number of statements that are
> cached for the connection, you can set the cached_statements parameter. The
> currently implemented default is to cache 100 statements."
>
> ----------
> nosy: +poq
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue12993>
> _______________________________________
>

This can be closed.