vendredi 19 février 2016

Python Sqlite3 Database Locked, QWidget

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.

Aucun commentaire:

Enregistrer un commentaire