2

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?

secondbreakfast
  • 4,194
  • 5
  • 47
  • 101

2 Answers2

3

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. Use executescript() 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.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • isn't this just the same as doing them one at a time? – secondbreakfast Jun 02 '16 at 00:41
  • You put an array of queries in, and get an array of results out. This is exactly what you asked for. (And even if there were a database call for this: what the database would be doing internally would be exactly the same.) – CL. Jun 02 '16 at 07:24
1

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Well what im doing is finding the points that are within a certain range of a single point. So first I query for all the points, then loop through them and query for the points that are within range of the current point. – secondbreakfast Jun 02 '16 at 00:43
  • 1
    @iMassakre, since you already *have* all the points in your data set, it makes little sense to go and get a subset of them again. I'd use the *one* query, use it to load up whatever data structures you need for efficient comparisons, then just use that to find close points. – paxdiablo Jun 02 '16 at 01:12
  • but how could I query against a list/array? The whole reason im putting them in a db is so that I can query them – secondbreakfast Jun 02 '16 at 01:41
  • 1
    @iMassakre, you shouldn't *need* to requery them if you've placed them in a suitable data structure. You will already *have* the required information. Keep in mind this is probably a moot point if you stick with SQLite since that's a local DBMS. If you were to move to a client/server DB, it would probably be more suitable to try avoiding many DB calls. – paxdiablo Jun 02 '16 at 02:29
  • 1
    @iMassakre You should have asked for a solution to the *actual* problem. (Or maybe not, because it appears to be [a duplicate](http://stackoverflow.com/q/3695224/11654).) – CL. Jun 02 '16 at 07:26
  • @CL I have done much research on this topic and tried many different approaches to solve my particular problem. I have not come across this answer yet, thanks for sharing! It looks very similar to my current approach (the 4 points/rectangle). My biggest problem is that I am dealing with nearly 7 million points of data and need the fastest possible solution. – secondbreakfast Jun 02 '16 at 12:10