Python SQLite3 / Nested cursor.execute

Question

Consider with this following piece of code:

for count in range(28, -1, -1):
    crsr.execute("SELECT board, win1, win2 FROM positions WHERE balls = ?", (count,))
    response = crsr.fetchall()
    print count, len(response)
    for possibility in response:
        internal = possibility[0]
        player = count & 1
        victor = 1 - player
        opponent = 2 - player
        victory = possibility[opponent]
        if victory:
            crsr.execute("UPDATE positions SET result = ? WHERE board = ?", (victor, internal))
        else:
            subsequent = derive((internal, count))
            for derived in subsequent:
                external = reduce(derived[0])
                crsr.execute("SELECT result FROM positions WHERE board = ?", (external,))
                colour = crsr.fetchall()
                if colour[0][0] == player:
                    victor = player
                    break
            crsr.execute("UPDATE positions SET result = ? WHERE board = ?", (victor, internal))

Consider with the line:

response = crsr.fetchall()

Whenever that there are as much as 10⁷ rows in response, the above statement returns a memory error, even on a system with 8 GB of RAM.

So, I decided that I would change with the following piece of code:

for count in range(28, -1, -1):
    crsr.execute("SELECT board, win1, win2 FROM positions WHERE balls = ?", (count,))
    response = crsr.fetchall()
    print count, len(response)
    for possibility in response:
        internal = possibility[0]

to:

for count in range(28, -1, -1):
    crsr.execute("SELECT COUNT(board) FROM positions WHERE balls = ?", (count,))
    sum = crsr.fetchall()
    total = sum[0][0]
    print count, total
    crsr.execute("SELECT board, win1, win2 FROM positions WHERE balls = ?", (count,))
    for possibility in range(total):
        response = crsr.fetchone()
        internal = response[0]

Now that the line:

response = crsr.fetchone()

makes use of the crsr variable for performing with SQLite3 selection query for every iteration of possibility in range(total).

There are already other crsr statements in the same 'for' loop:

crsr.execute("UPDATE positions SET result = ? WHERE board = ?", (victor, internal))

with that statement occurring twice, and

crsr.execute("SELECT result FROM positions WHERE board = ?", (external,)).

with that statement occurring once.

So, whenever the crsr variable from the line: response = crsr.fetchall() changes with every iteration of possibility in range(total), will it not conflict with the other crsr statements already in the same 'for' loop?

We cannot create with other cursor variables for executing with different SQLite3 queries, because crsr is defined by using crsr = connection.cursor() for a specific database file, as soon as it is initialized (whichever is spline.db, in this particular case).

So, I would like to know that if there are any other alternative solutions available for it whichever are efficient enough quite directly.

Your explanation why you can't create multiple cursors does not make sense. Why can't you call connection.cursor() multiple times? — CL.
– CL., Commented Apr 28, 2018 at 10:36
Thank you for your response. So if I quite directly call crsr1 = connection.cursor() and crsr2 = connection.cursor() and if I am executing crsr1.execute(query1) and crsr2.execute(query2) inside of the same 'for' loop itself, then will crsr1 and crsr2 not quite directly both conflict with each other, of themselves? — V. Raman
– V. Raman, Commented Apr 28, 2018 at 14:27

CL. · Accepted Answer · 2018-04-28 16:59:30Z

A result set is part of the cursor object, so whenever you call execute(), any previous query on the same cursor object is aborted. The only way to avoid this is to use fetchall() to read all result rows before the next query is executed.

To be able to execute multiple simultaneous queries, you must use multiple cursors. Simply call connection.cursor() multiple times.

Please note that you must not modify a table that you are still reading from (even if you are using multiple cursors); changed rows might be skipped or read twice by the read cursor. If you cannot use fetchall(), put the results of the first query into a temporary table:

crsr1.execute("CREATE TEMP TABLE temp_pos(board, win1, win2)")

for count in ...:
    crsr1.execute("INSERT INTO temp_pos SELECT board, win1, win2 ...")

    crsr1.execute("SELECT board, win1, win2 FROM temp_pos")
    for row in crsr1:
        if ...:
            crsr2.execute("UPDATE positions ...")
        else:
            crsr2.execute("SELECT ... FROM positions ...")
            ...
    crsr1.execute("DELETE FROM temp_pos")

crsr1.execute("DROP TABLE temp_pos")

Collectives™ on Stack Overflow

Python SQLite3 / Nested cursor.execute

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related