sqlalchemy database table is locked

Question

I am trying to select all the records from a sqlite db I have with sqlalchemy, loop over each one and do an update on it. I am doing this because I need to reformat ever record in my name column.

Here is the code I am using to do a simple test:

   def loadDb(name):    

        sqlite3.connect(name)    
        engine = create_engine('sqlite:///'+dbPath(), echo=False)      
        metadata = MetaData(bind=engine)

        return metadata

    db = database("dealers.db")
    metadata = db.loadDb()
    dealers = Table('dealers', metadata, autoload=True)

    dealer = dealers.select().order_by(asc(dealers.c.id)).execute()

    for d in dealer:
        u = dealers.update(dealers.c.id==d.id)
        u.execute(name="hi")

        break

I'm getting the error:

sqlalchemy.exc.OperationalError: (OperationalError) database table is locked u'UPDATE dealers SET name=? WHERE dealers.id = ?' ('hi', 1)

I'm very new to sqlalchemy and I'm not sure what this error means or how to fix it. This seems like it should be a really simple task, so I know I am doing something wrong.

Answer 1

With SQLite, you can't update the database while you are still performing the select. You need to force the select query to finish and store all of the data, then perform your loop. I think this would do the job (untested):

dealer = list(dealers.select().order_by(asc(dealers.c.id)).execute())

Another option would be to make a slightly more complicated SQL statement so that the loop executes inside the database instead of in Python. That will certainly give you a big performance boost.