Error while performing multiple operations on database

user_13398643 0
Viewed 105

I have to loop over all rows in the database and have to update them at some location. I have written this code:

import sqlite3
con=sqlite3.connect("Models.db")
c=con.cursor()
c1=con.cursor()
c.execute("CREATE TABLE ABC(id INTEGER PRIMARY KEY, COLVAL text)")
///inserted a few rows........
for row in c.execute("SELECT * FROM ABC"):
  if $(any condition): 
     with con: 
       c1.execute("UPDATE ABC SET COLUMN1=(:COLVAL) WHERE id=(:id)",{'COLVAL':'XYZ','id':row[0]})

but I get an error:

OperationalError: database is locked

in the second query, c1.execute("UPDATE ABC SET COLUMN1=(:COLVAL) WHERE id=(:id)",{'COLVAL':'XYZ','id':row[0]})

I do not want to fetch all rows at a time and apply for a loop, as there is a lot of data. Just one row at a time which in my case:

"for row in c.execute("SELECT * FROM ABC")"

line is doing.

Does anyone know the solution for it?

sqlite sql python
1 Answers

Basically you can loop within a list such as c.execute("SELECT * FROM abc").fetchall()

c.execute("CREATE TABLE IF NOT EXISTS abc(id INT PRIMARY KEY, column1 TEXT)")
///inserted a few rows........
for row in c.execute("SELECT * FROM abc").fetchall():
    if $(any condition):
        c.execute("UPDATE abc SET column1=(?) WHERE id=(?)",('XYZ',row[0],))
con.commit()    
con.close()

Additionally;

  • add IF NOT EXISTS to the DDL Statement
  • replace parameter placeholders with question marks as being securer
  • no need to open more than one cursor
  • don't miss commiting the DML Statement at the end

extra tip : use executemany as

c.executemany("UPDATE abc SET column1=(?) WHERE id=(?)",[('XYZ',row[0],)])

instead of execute for the DML statement for the multiple data as being more performant. Indeed, you don't need for this current case as only concerns with only one row for each id value

user_58413060
Related