@Jesse SQLite has to be seen as a signle file storage. If you open in one process all other processes have to wait until the first process releases lock ownership. The best way to do that, is to put commit or rollback at the end of every access on the db. The code you posted does a single commit when the class is instantiated, if I understand correctly, so the application ends up with very long transactions... hence the lock
2017-02-05 23:12 GMT+01:00 António Ramos <[email protected]>: > (i´m not an expert here but i faced the same problem) > I moved to postgresql and no more database locks. > sqlite does not allow to have scheduler and your code writting to the > database at the same , or at least that was my understanding... > > Regards > António > > > 2017-02-03 14:01 GMT+00:00 Jesse Hunt <[email protected]>: > >> Good afternoon all, >> >> I am experiencing issues while trying to perform operations using my DAL >> from a module. I recently moved code from the default controller to a >> module for better coding practices. The code was running previous to the >> move, but now I experience an error ( <class 'sqlite3.OperationalError'> >> database is locked ) everytime I try to run it. >> >> So far, I have tried: >> >> - various additions of small snippets I thought might help me fix the >> problem from http://www.web2pyslices.com/slice/show/1478/using-modul >> es-in-web2py >> - enabling WAL in SQLite from this thread https://groups.google.c >> om/forum/#!msg/web2py/2IDEb658VDw/f5jv54bOH30J;context- >> place=forum/web2py >> >> <https://groups.google.com/forum/#!msg/web2py/2IDEb658VDw/f5jv54bOH30J;context-place=forum/web2py> >> >> But after searching for the last 3 days, I can not find a solution that >> matches up with my problem. I will post the code relevant to what is >> happening. >> FYI: >> >> - There is a scheduler running >> - The following code is running within a module >> - I executed the WAL statement in the default controller >> >> from gluon import current >> from gluon.dal import DAL, Field >> import pymongo >> from datetime import datetime as dt >> from datetime import date >> >> >> class mongo_functions: >> client = None >> connection = None >> mongo_db = None >> db_name = None >> collection = None >> collection_name = None >> dal_name = None >> dal_db = None >> >> >> def __init__(self, connection=None, db_name=None, collection_name= >> None, dal_name=None): >> >> >> # if settings returns nothing, connect to local DB >> if connection is None: >> self.connection = 'mongodb://localhost:27017/' >> if db_name is None: >> self.db_name = 'test_database' >> if collection_name is None: >> self.collection_name = 'test_s_collection' >> if dal_name is None: >> self.dal_name = 'sqlite://development.sqlite' >> >> # added after looking at slice referenced above >> self.session = current.session >> self.request = current.request >> self.response = current.response >> self.cache = current.cache >> >> >> def open_connection(self): >> self.client = pymongo.MongoClient(self.connection) >> self.mongo_db = self.client[self.db_name] >> self.collection = self.mongo_db[self.collection_name] >> self.dal_db = DAL(self.dal_name) >> print "Connection to Mongo and DAL open" >> >> >> def close_connection(self): >> self.collection = None >> self.mongo_db = None >> self.client.close() >> self.dal_db = None >> >> # the method below refuses to format itself correctly, assume the >> tabulation is fine >> def query_for_service(self, security_name, field, start_date, >> end_date, session_id): >> self.dal_db.define_table('export_service', >> Field('security_name', notnull=True), >> Field('field', notnull=True), >> Field('date', type='date'), >> Field('value'), >> Field('session_id'), >> format='%(name)-(field)-(date)s') >> >> >> queried_document = collection.find_one({'sec_name': security_name, >> 'field': field}) >> # data_set is a list of date/value dictionary objects >> data_set = queried_document['data'] >> >> >> # a data_point is a dictionary object with date and value pairs. >> for data_point in data_set: >> date_object = data_point['date'].date() >> >> >> print start_date >> print end_date >> print date_object >> >> >> export_list = list() >> >> >> if date_object > start_date and date_object < end_date: >> value = data_point['value'] >> >> >> value_dict = {'security_name': security_name, 'field': field, >> 'date': date_object, 'value': value, >> 'session_id': session_id} >> export_list.append(value_dict) >> >> >> self.dal_db.export_service.bulk_insert(export_list) >> self.dal_db.commit() >> >> *Thank you in advance for your time, I very sincerely appreciate it!* >> >> -- >> Resources: >> - http://web2py.com >> - http://web2py.com/book (Documentation) >> - http://github.com/web2py/web2py (Source code) >> - https://code.google.com/p/web2py/issues/list (Report Issues) >> --- >> You received this message because you are subscribed to the Google Groups >> "web2py-users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
