Reputation: 4374
I have some business logic that iterates many many times and needs to perform a simple query every time. Rather than make a call to the db every time I would like to store the SELECT statements as an array of strings or something similar and then execute all of the statements at once after the loop. Is this possible with python and sqlite?
Upvotes: 2
Views: 6671
Reputation: 180060
The documentation says:
execute()
will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Useexecutescript()
if you want to execute multiple SQL statements with one call.
However, executescript()
does not allow you to access all the results.
To get multiple query results, you have to do the loop yourself:
def execute_many_selects(cursor, queries):
return [cursor.execute(query).fetchall() for query in queries]
SQLite is an embedded library, so there is no client/server communication overhead when doing multiple database calls.
Upvotes: 3
Reputation: 881403
I suspect you'd be better off if you work out a "larger" query and then decompose the result set after retrieving the information.
In other words, rather than three calls to the database (one each for Alice, Betty and Claire), use something like:
select stuff from a_table
where person in ('Alice', 'Betty', 'Claire')
and then process the actual data taking person
into account.
Obviously, that will only work in the case where you can figure out the query before executing any of the person-based actions, but it looks like that's the case anyway, based on your question.
Upvotes: 1