CODEWITHSUNDEEP
pythonsqlitesqlalchemy
imns
imns

Reputation: 5082

sqlalchemy database table is locked

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.

Upvotes: 5

Views: 4855

Answers (1)

Daniel Stutzbach
Daniel Stutzbach

Reputation: 76745

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.

Upvotes: 3

Related Questions