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 107 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.
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")