CODEWITHSUNDEEP
pythonsqliteapsw
Andy Thompson
Andy Thompson

Reputation: 97

why does apsw cursor.execute(sql, (vars)) break outer for loop

So I have the following code:

with closing(connection.cursor()) as cursor:
    for row in cursor.execute("select *, rowid from positions where status = 'pending'"):
        print(row[11])
        x=2
        if (row[9])!='':
            x=3
        for y in range(x):
            print(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7+y],'position# '+str(row[11]))
            sql="insert into trades VALUES (?,?,?,?,?,?,?,?,?)"
            #cursor.execute(sql, (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7+y],'position# '+str(row[11])))
            sql="update positions set status = 'processed' where rowid = "+str(row[11])
            #cursor.execute(sql)

which produces the following output:

2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 124.07 position# 2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 123.77 position# 2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 123.27 position# 2
3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.13 position# 3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.43 position# 3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.93 position# 3
4
redacted 10000001 2021-10-20T07:54:41+00:00 GBPJPY BUY 157.57 156.50 157.77 position# 4
redacted 10000001 2021-10-20T07:54:41+00:00 GBPJPY BUY 157.57 156.50 158.07 position# 4
redacted 10000001 2021-10-20T07:54:41+00:00 GBPJPY BUY 157.57 156.50 158.57 position# 4

but uncommenting the cursor.execute() calls breaks the outer for loop leading to:

2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 124.07 position# 2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 123.77 position# 2
redacted 10000001 2021-10-19T12:22:37+00:00 CHFJPY SELL 124.27 125.30 123.27 position# 2

and on repeating:

3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.13 position# 3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.43 position# 3
redacted 10000001 2021-10-20T07:53:48+00:00 EURJPY BUY 132.93 131.80 133.93 position# 3

etc. The sql is doing its job but why is the outer for loop broken?

Interestingly there is a similar question for a different database package here with no resolution... Ideas...?

Upvotes: 0

Views: 117

Answers (1)

Andy Thompson
Andy Thompson

Reputation: 97

So following the excellent guidance from DinoCoderSaurus, I now have the follwing, which, incidently, works like a charm...

sqls=["insert into trades VALUES (?,?,?,?,?,?,?,?,?)","update positions set status = 'processed' where rowid = :posid"]
values=[]
posids=[]
with closing(connection.cursor()) as cursor:
    for row in cursor.execute("select *, rowid from positions where status = 'pending'"):
        posid=row[11]
        x=2
        if (row[9])!='':
            x=3
        for y in range(x):
            values.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7+y],'position# '+str(posid)))
            posids.append({'posid':posid})

for value in values:
    with closing(connection.cursor()) as cursor:
        x=0
        cursor.execute(sqls[0], value)
        cursor.execute(sqls[1],posids[x])
        x+=1

CORRECTION So I still messed it up with the 2 cursor calls at the end so this:

sql="insert into trades VALUES (?,?,?,?,?,?,?,?,?);update positions set status = 'processed' where rowid = ?"
values=[]
with closing(connection.cursor()) as cursor:
    for row in cursor.execute("select *, rowid from positions where status = 'pending'"):
        posid=row[11]
        print(posid)
        x=2
        if (row[9])!='':
            x=3
        for y in range(x):
          values.append((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7+y],'position# '+str(posid),posid))
for value in values:
    with closing(connection.cursor()) as cursor:
        cursor.execute(sql, value)

Upvotes: 0

Related Questions