multiple prepared statements

[BjornB]

The pyodbc intro mentions that a prepared statement will be efficient
if it is run multiple times with no changes to the statement.

A common scenario when doing batch updates is the following:
SELECT to check if exists, and then either INSERT or UPDATE

This will never run the same statements twice in a row, and will
therefore be somewhat inefficient as I understand it.
How could one deal with this? Multiple cursors if supported by the
database?

The db APIs of other programming languages sometimes return handles to
prepared statements. These are then executed. This idea does not seem
to exist in pyodbc.

[Michael Kleehammer]

I agree it would be a handy idea.  Perhaps a "Cursor.prepare()" statement could be added that returns an "prepared statement" object?  Let me think about how I would like to use the feature, syntax-wise. Suggestions are welcome.

[jeremia...@gmail.com]

ps = Cursor.prepare("select user_name from users where user_id=?")

ps.execute('bob')

ps.executemany(['bob', 'bill'])

Or maybe

Cursor.execute(ps, 'bob')

Cursor.executemany(ps, ['bob','bill'])

[Michael Kleehammer]

Agreed.

Solution 1 may be available for you now: If your database allows multiple HSTMTs per connection, you can simply create a 2nd cursor and switch between them.  There is even a way to query this using getInfo, but I don't remember the value off hand.  WIth SQL Server 2005+, you need to add "MARS_Connection=yes" to your connection string.

Each Cursor represents/wraps an HSTMT and you can only prepare one statement per HSTMT.  Therefore, there really isn't a way to manage multiple prepared statement that is any better than the solution above.

However, the largest pyodbc slowdown for INSERT statements actually comes from the fact that pyodbc has to call SQLDescribeCol for all NULL values.  (You must tell ODBC the type of data you are inserting, but there is no type in a dynamic language.  There is also no type that can always be inserted into all columns that I could choose.  For example, SQL Server will not allow a NULL string to be put into a binary field -- there is no conversion it says.)

Internally, the types of columns are tracked so that multiple inserts with the same statement do not require another SQLDescribeCol call.  One long outstanding request is to track the types for the last N statements instead of just the last 1.