The problem here is that SQLite has to deal with conflicts arising due to concurrent access by multiple threads i.e., SQLite database created and accessed by one thread cannot allow another thread to access it. This may result from following scenarios:
- global connection objects are created which are then accessed later by different threads
- connection objects are not closed properly between different connections
Its always recommended that an ORM is used to deal with databases and efficiently manage their connection lifecycles. For Sqlite, the most widely used ORM is SqlAlchemy. Using an ORM can probably fix the issue.
However, for very simple applications, where using an ORM is just an overkill, you can tweak the way connection is created to the Sqlite database by allowing concurrent access. This can be done by setting check_same_thread parameter to False while establishing the connection:
def initDB(self, file_path):
self.file_path = file_path
self.cx = sqlite3.connect(file_path, check_same_thread=False)
self.cx.execute(self.create_table_str)
self.cx.execute(self.create_detail_table_str)
print("init the table strucutre successfully")
Having said that, setting up Sqlite connection this way lays responsibility to handle concurrency on the application instead of the database and user should ensure that write operations to the database are serialized in order to avoid any dirty writes/updates.
Note: When using sqlalchemy, its important to use the right libraries and code segregation. I have particularly found this post helpful as well.
