Python Sqlite3 Database Locked, QWidget
Question: I'm getting a `sqlite3.OperationalError: database is locked` when I try to
update the database from a pyqt widget.

The main window shows a list of items in a database. When an item is double
clicked a widget will pop up with options to modify a value in the same row of
the database.

I have 4 separate .py files; (I have removed most of the code)

`DBmanager.py` that contains all the functions for interacting with the
database.

    
    
    class DatabaseUtility:
            def __init__(self, databaseFile):
            self.db = databaseFile 
            self.conn = sqlite3.connect(self.db)
            self.c = self.conn.cursor()  
    
        def ChangeItemQuantity(self, itemName, incramentQuantity):
            try:
                # Change given item quantity in database
                self.c.execute('''
                     SELECT quantity
                     FROM items
                     WHERE itemName=?
                     ''',(itemName,))
                print(itemName)
                print(incramentQuantity)
                current_quantity = self.c.fetchone()
                print(current_quantity[0])
                new_quantity = current_quantity[0] + incramentQuantity
                self.c.execute('''
                     UPDATE items
                     SET quantity = ?
                     WHERE itemName=?
                     ''',(new_quantity, itemName))
                self.conn.commit()
            except Exception as error:
                # Rollback any changes if something goes wrong.
                self.conn.rollback()
                raise error
    
        def __del__(self):
            """Commit and close database connection when the class is terminated."""
            # pass
            self.conn.commit()
            self.c.close()
            self.conn.close()
    

`StartGui.py` that contains the functions for the GUI.

    
    
    import sys
    from PyQt4 import QtCore, QtGui
    from guiFormat import Ui_MainWindow
    from itemWidget import Ui_itemWidget
    from DBmanager import DatabaseUtility
    
    
    class MainWindow(QtGui.QMainWindow):
        def __init__(self, parent=None):
            QtGui.QWidget.__init__(self, parent)
            self.ui = Ui_MainWindow()
            self.ui.setupUi(self)
            self.db = 'testdb.db'
            self.dbUtil = DatabaseUtility(self.db)
            self.ui.updateButton.clicked.connect(self.populateTable_default)
            self.ui.updateButton.clicked.connect(self.dbUtil.UpdateDatabase)
            self.ui.searchButton.clicked.connect(self.populateTable_search)
            self.ui.tableWidget.doubleClicked.connect(self.open_item_widget)
            self.ui.tableWidget.resizeRowsToContents()
    
            # Populate table on initial startup
            self.populateTable_default()
    
        def open_item_widget(self):
            self.item_widget = ItemWidget(self)
            # self.item_widget.show()
            column_count = self.ui.tableWidget.columnCount()
            self.item_widget.ui.tableWidget.setColumnCount(column_count)
            self.item_widget.ui.tableWidget.setRowCount(1)
            column_names = self.dbUtil.GetColumns('items')
            self.item_widget.ui.tableWidget.setHorizontalHeaderLabels(column_names)
            row = self.ui.tableWidget.currentRow()
            for column in range(column_count):
                x = self.ui.tableWidget.item(row, column).text()
                item_data = QtGui.QTableWidgetItem(str(x))
                self.item_widget.ui.tableWidget.setItem(0, column, item_data)
            self.item_widget.show()
    
    
    class ItemWidget(QtGui.QWidget):
        def __init__(self, parent=None):
            self.db = 'testdb.db'
            self.dbUtil2 = DatabaseUtility(self.db)
            QtGui.QWidget.__init__(self, parent)
            self.ui = Ui_itemWidget()
            self.ui.setupUi(self)
            self.ui.cancelOkButtonBox.rejected.connect(self.close)
            self.ui.cancelOkButtonBox.accepted.connect(self.submit_changes)
    
        def submit_changes(self):
            # Get item Name.
            item_name = self.ui.tableWidget.item(0,0).text()
            # Get number from increment box.
            quant_increment = self.ui.QuantSpinBox.value()
            alert_incrament = self.ui.alertSpinBox.value()
            print('Changing quantity...', item_name, quant_increment)
            self.dbUtil2.ChangeItemQuantity(item_name, quant_increment)
    
            self.close()
    
    
    
    ##======================================================================================================================
    ##======================================================================================================================
    if __name__ == "__main__":
        dbUtil = DatabaseUtility('testdb.db')
        app = QtGui.QApplication(sys.argv)
        mainwindow = MainWindow()
        mainwindow.show()
        # itemwidget = ItemWidget()
        # itemwidget.show()
        sys.exit(app.exec_())
    

`Mainwindow.py` is an export from pyqt designer for the main window.

`Itemwidget.py` is also an export from pyqt designer for the item widget.

Thank you for all your help.


Answer: The `sqlite3` documentation says:

> When a database is accessed by multiple connections, and one of the
> processes modifies the database, the SQLite database is locked until that
> transaction is committed.

Since you have two connections, one opened by `MainWindow` and the other by
`ItemWidget`, one likely cause of your problem is just what the doc says. One
connection tries to update the database when changes made by the other
connection is not committed. This won't happen with the `ChangeItemQuantity`
method you are showing as the change it makes is immediately committed, but
obviously `DatabaseUtility` has other methods you are not showing, like
`UpdateDatabase`. If they make changes that are not immediately committed,
that could be the problem.

Let `MainWindow` and `ItemWidget` share one connection and see if the problem
goes away. You can do this by making `conn` a shared attribute of
`DatabaseUtility` instances, with no change to callers:

    
    
    class DatabaseUtility:
    
        conn = None
    
        def __init__(self, databaseFile):
            self.db = databaseFile
            if self.__class__.conn is None:
                self.__class__.conn = sqlite3.connect(self.db)
            self.c = self.conn.cursor()
    

Also, although this seems less likely to be the problem, if some other process
has an open transaction on the same SQLite database, the database will be
locked for as long as that transaction is active. Some apps have the bad habit
of maintaining an active transaction it seems. Whenever I open a database with
[SQLite Browser](http://sqlitebrowser.org/), I can't do anything to the
database from other programs (maybe I can turn that off, but I never bothered
to figure out how...)

