web2py database always locked when trying to update from code

Question

Trying to simply update existing row in database (running on web2py), but always getting database locked error..

Error message:

<class 'sqlite3.OperationalError'> database is locked

My setup

• in models/db.py I create database and it works when using database administration (can insert, update using the web interface)

  db.define_table('mytest', Field('name', 'string'))

• I have added 1 row to mytest, using the web interface (so its not empty)

• in controllers/test.py i have simple code to get first item and try to update the value, there it fails (I open the page is browser and it gives the internal error, with link to error log)

  def index():
     # connect
     db = DAL('sqlite://storage.sqlite',pool_size=10,auto_import=True)
  
     # get first record
     record = db(db.mytest).select().first()
  
     # try to update it.. database locked error here
     record.update_record(name="asdfg")
  
     # just in case needed?
     db.commit()
     db.close()
  
     return "test"
  

Software

• WinPython2.7
• Running win2py.py (2.14.6) manually using Spyder ide
• windows8

What i've tried so far

• Different DAL settings, poolsize, without autoimport..
• Close all web2py admin tools/tabs
• Create new database
• Restart web2py
• Restart pc

---

Error log: http://pastebin.com/2WMWypt6

---

Current workaround: - Create New Application, exact same code seems to work there

---

Solution was: by @GauravVichare - Remove this line from controller (its already defined in db.py)

db = DAL('sqlite://storage.sqlite',pool_size=10,auto_import=True)

Answer 1

Check Whether there is no other connection (to sqlite db) open on your machine, if web2py shell is open, close it.

Check DAL is defined only once or not. Define DAL only in models/db.py, no need to define it again in controller.

Every variable defined in models is visible in controllers.

You must have defined DAL in models/db.py and you are defining once again in controller, so you have two connection open for SQLite db. Thats why you are getting error 'database is locked'.

Answer 2

Try using myRecord instead of Record, since it may be a reserved word.
I know User has given people issues in web2py. I would just tend to stay away from very generic aliases.

Otherwise, is there anything currently in the db? If it is empty you would receive and error.
It might be better to :

myRecord = record = db(db.mytest).select().first()

if myRecord:
  myRecord.update_record(name="asdfg")
else:
  [insert statement here]

Answer 3

My Suggestion is

1.First of all save the code when u make some changes.

2.Aftr saving u r new code try to reload web2py.exe then run web2py so that u wont get Databaselocked error.

3.Dont ever create tables in Sqlite database before.

4.once u start running web2py and starts server and when ever u enter data into forms it automatically creates the tablesin sqlite database.