class MainWindow(QtWidgets.QMainWindow):
def __init__(self) -> None:
super().__init__()
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.dbpath = DEFAULT_DATABASE_PATH
self.qmainwindow = QtWidgets.QMainWindow()
# define buttons vehaviour
self.button_actions: Dict[str, QtWidgets.QAction] = {}
self.define_buttons()
# UI variables
self.label = QtWidgets.QLabel()
self.combo_box = QtWidgets.QComboBox()
self.table_model = QSqlRelationalTableModel()
self.table_view = QtWidgets.QTableView()
# create window and show it
self.draw()
self.qmainwindow.show()
def draw(self) -> None:
self.qmainwindow.setObjectName("MainWindow")
self.qmainwindow.resize(800, 600)
self.qmainwindow.setWindowTitle("Database manager")
widget = QtWidgets.QWidget(self.qmainwindow)
widget.setObjectName("widget")
self.qmainwindow.setCentralWidget(widget)
self.toolbar = self.qmainwindow.addToolBar("ToolBar")
for _, action in self.button_actions.items():
self.toolbar.addAction(action)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 10), QtCore.QSize(105, 16)))
_.setText("Database path: ")
self.label = QtWidgets.QLabel(widget)
self.label.setGeometry(
QtCore.QRect(QtCore.QPoint(110, 10), QtCore.QSize(750, 16)))
self.label.setText(self.dbpath)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 30), QtCore.QSize(105, 16)))
_.setText("Choosen table: ")
self.combo_box = QtWidgets.QComboBox(self.qmainwindow)
self.combo_box.setGeometry(
QtCore.QRect(QtCore.QPoint(100, 55), QtCore.QSize(200, 16)))
self.combo_box.currentIndexChanged.connect(self.update_table)
_ = QtWidgets.QLabel(widget)
_.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 65), QtCore.QSize(105, 16)))
_.setText("Table")
self.table_view = QtWidgets.QTableView(self.qmainwindow)
self.table_view.setObjectName("tableView")
self.table_view.setGeometry(
QtCore.QRect(QtCore.QPoint(10, 115), QtCore.QSize(780, 475)))
self.table_view.setShowGrid(False)
def define_buttons(self) -> None:
def _exit_action() -> None:
QtWidgets.qApp.quit()
def _show_dialog() -> None:
self.dbpath, _ = QtWidgets.QFileDialog.getOpenFileName(
self, "Open file")
self.label.setText(self.dbpath)
def _load_database() -> None:
self.db.setDatabaseName(self.dbpath)
self.db.open()
# update selectors
self.combo_box.clear()
for tbl in self.db.tables():
self.combo_box.addItem(tbl)
self.update_table()
def _save_database() -> None:
self.table_model.submitAll()
def _insert_row() -> None:
self.table_model.insertRows(self.table_model.rowCount(), 1)
def _remove_row() -> None:
for idx in self.table_view.selectedIndexes():
self.table_model.removeRows(idx.row(), 1)
self.button_actions.update(
{"exit": QtWidgets.QAction("Exit", self.qmainwindow)})
self.button_actions["exit"].triggered.connect(_exit_action)
self.button_actions.update({
"dbpath":
QtWidgets.QAction("Set database path", self.qmainwindow)
})
self.button_actions["dbpath"].triggered.connect(_show_dialog)
self.button_actions.update(
{"load": QtWidgets.QAction("Load database", self.qmainwindow)})
self.button_actions["load"].triggered.connect(_load_database)
self.button_actions.update(
{"save": QtWidgets.QAction("Save database", self.qmainwindow)})
self.button_actions["save"].triggered.connect(_save_database)
self.button_actions.update(
{"insert": QtWidgets.QAction("Insert row", self.qmainwindow)})
self.button_actions["insert"].triggered.connect(_insert_row)
self.button_actions.update(
{"remove": QtWidgets.QAction("Remove row", self.qmainwindow)})
self.button_actions["remove"].triggered.connect(_remove_row)
def update_table(self) -> None:
self.table_model = QSqlRelationalTableModel()
tablename = self.combo_box.currentText()
self.table_model.setTable(tablename)
# foreign key logic
if tablename == "goods":
self.table_model.setRelation(2,
QSqlRelation("units", "id", "unit"))
self.table_model.setRelation(
3, QSqlRelation("categories", "id", "name"))
elif tablename == "employees":
self.table_model.setRelation(
2, QSqlRelation("positions", "id", "position"))
elif tablename == "vendors":
self.table_model.setRelation(
2, QSqlRelation("ownerships", "id", "ownership"))
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.table_view.setModel(self.table_model)
self.table_view.setItemDelegate(QSqlRelationalDelegate(
self.table_view))
# define header width
self.table_view.horizontalHeader().setStretchLastSection(False)
self.table_view.resizeColumnsToContents()
self.table_view.horizontalHeader().setMinimumSectionSize(50)
self.table_view.horizontalHeader().setStretchLastSection(True)
class MyWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
# Собираем окно
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'database/database.sqlite3'
self.ui.path_db.setText(self.db_path)
# Собираем кнопки
self.save_db = QAction(QIcon('icon/save.png'), 'Save', self)
self.open_db_file = QAction(QIcon('icon/open-db.png'), 'Open', self)
self.add_row = QAction(QIcon('icon/add.png'), 'Add row', self)
self.del_row = QAction(QIcon('icon/del.png'), ' Del row', self)
self.ui.toolBar.addAction(self.save_db)
self.ui.toolBar.addAction(self.open_db_file)
self.ui.toolBar.addAction(self.add_row)
self.ui.toolBar.addAction(self.del_row)
self.db = None
self.table_model = None
# Подключаемся к БД
self.open_db()
# действуем по триггерам
self.add_row.triggered.connect(self.add_row_action)
self.del_row.triggered.connect(self.del_row_action)
self.save_db.triggered.connect(self.save_change_db)
self.open_db_file.triggered.connect(self.open_db_file_action)
self.ui.comboBox.currentIndexChanged.connect(self.show_table)
def open_db(self):
# Подключение к БД
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
self.show_table()
def get_tables_name(self):
self.ui.comboBox.clear()
for table_name in self.db.tables():
self.ui.comboBox.addItem(table_name)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.comboBox.currentText()
if table == 'goods':
self.create_goods_table_model()
elif table == 'employees':
self.create_employees_table_model()
else:
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit'))
self.table_model.setRelation(3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(2, QSqlRelation('positions', 'position_id', 'position'))
self.table_model.select()
def add_row_action(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def del_row_action(self):
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def open_db_file_action(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.path_db.setText(self.db_path)
self.db.close()
self.open_db()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
class MyWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'database\sklad_db.sqlite3'
self.ui.path_db.setText(self.db_path)
self.save_db = QAction(QIcon('icon/save.png'), 'Сохранить', self)
self.find_db_file = QAction(QIcon('icon/open_db.png'), 'Открыть БД',
self)
self.add_rec = QAction(QIcon('icon/add.png'), 'Добавить запись', self)
self.del_rec = QAction(QIcon('icon/del.png'), 'Удалить запись', self)
self.ui.toolBar.addAction(self.save_db)
self.ui.toolBar.addAction(self.find_db_file)
self.ui.toolBar.addAction(self.add_rec)
self.ui.toolBar.addAction(self.del_rec)
self.db = None
self.table_model = None
self.open_db()
self.add_rec.triggered.connect(self.add_record_action)
self.del_rec.triggered.connect(self.del_record_action)
self.save_db.triggered.connect(self.save_change_db)
self.find_db_file.triggered.connect(self.find_db_file_action)
self.ui.comboBox.currentIndexChanged.connect(self.show_table)
def open_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_table_name()
self.show_table()
def get_table_name(self):
self.ui.comboBox.clear()
table_name_ru = None
for table_name in self.db.tables():
if table_name == 'goods':
table_name_ru = 'Товары'
elif table_name == 'categories':
table_name_ru = 'Категории'
elif table_name == 'units':
table_name_ru = 'Единица измерения'
elif table_name == 'employees':
table_name_ru = 'Персонал'
elif table_name == 'positions':
table_name_ru = 'Должности'
elif table_name == 'vendors':
table_name_ru = 'Поставщики'
self.ui.comboBox.addItem(table_name_ru)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.comboBox.currentText()
if table == 'Товары':
self.create_goods_table_model()
elif table == 'Персонал':
self.create_employees_table_model()
else:
if table == 'Единица измерения':
table = 'units'
elif table == 'Категории':
table = 'categories'
elif table == 'Должности':
table = 'positions'
elif table == 'Поставщики':
table = 'vendors'
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(2, QSqlRelation('units', 'unit_id',
'unit'))
self.table_model.setRelation(
3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(
2, QSqlRelation('positions', 'position_id', 'position'))
self.table_model.select()
def add_record_action(self):
self.ui.statusbar.clearMessage()
self.table_model.insertRows(self.table_model.rowCount(), 1)
def del_record_action(self):
self.ui.statusbar.clearMessage()
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
#print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def find_db_file_action(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.path_db.setText(self.db_path)
self.db.close()
self.open_db()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(
f'{self.table_model.lastError().text()}')
class MainForm(QDialog):
def __init__(self):
super(MainForm, self).__init__()
self.assetModel = QSqlRelationalTableModel(self)
self.assetModel.setTable("assets")
self.assetModel.setRelation(CATEGORYID,
QSqlRelation("categories", "id", "name"))
self.assetModel.setSort(ROOM, Qt.AscendingOrder)
self.assetModel.setHeaderData(ID, Qt.Horizontal, "ID")
self.assetModel.setHeaderData(NAME, Qt.Horizontal, "Name")
self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal, "Category")
self.assetModel.setHeaderData(ROOM, Qt.Horizontal, "Room")
self.assetModel.select()
self.assetView = QTableView()
self.assetView.setModel(self.assetModel)
self.assetView.setItemDelegate(AssetDelegate(self))
self.assetView.setSelectionMode(QTableView.SingleSelection)
self.assetView.setSelectionBehavior(QTableView.SelectRows)
self.assetView.setColumnHidden(ID, True)
self.assetView.resizeColumnsToContents()
assetLabel = QLabel("A&ssets")
assetLabel.setBuddy(self.assetView)
self.logModel = QSqlRelationalTableModel(self)
self.logModel.setTable("logs")
self.logModel.setRelation(ACTIONID,
QSqlRelation("actions", "id", "name"))
self.logModel.setSort(DATE, Qt.AscendingOrder)
self.logModel.setHeaderData(DATE, Qt.Horizontal, "Date")
self.logModel.setHeaderData(ACTIONID, Qt.Horizontal, "Action")
self.logModel.select()
self.logView = QTableView()
self.logView.setModel(self.logModel)
self.logView.setItemDelegate(LogDelegate(self))
self.logView.setSelectionMode(QTableView.SingleSelection)
self.logView.setSelectionBehavior(QTableView.SelectRows)
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
self.logView.resizeColumnsToContents()
self.logView.horizontalHeader().setStretchLastSection(True)
logLabel = QLabel("&Logs")
logLabel.setBuddy(self.logView)
addAssetButton = QPushButton("&Add Asset")
deleteAssetButton = QPushButton("&Delete Asset")
addActionButton = QPushButton("Add A&ction")
deleteActionButton = QPushButton("Delete Ac&tion")
editActionsButton = QPushButton("&Edit Actions...")
editCategoriesButton = QPushButton("Ed&it Categories...")
quitButton = QPushButton("&Quit")
for button in (addAssetButton, deleteAssetButton, addActionButton,
deleteActionButton, editActionsButton,
editCategoriesButton, quitButton):
if MAC:
button.setDefault(False)
button.setAutoDefault(False)
else:
button.setFocusPolicy(Qt.NoFocus)
dataLayout = QVBoxLayout()
dataLayout.addWidget(assetLabel)
dataLayout.addWidget(self.assetView, 1)
dataLayout.addWidget(logLabel)
dataLayout.addWidget(self.logView)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(addAssetButton)
buttonLayout.addWidget(deleteAssetButton)
buttonLayout.addWidget(addActionButton)
buttonLayout.addWidget(deleteActionButton)
buttonLayout.addWidget(editActionsButton)
buttonLayout.addWidget(editCategoriesButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(dataLayout, 1)
layout.addLayout(buttonLayout)
self.setLayout(layout)
#self.connect(self.assetView.selectionModel(),
#SIGNAL(("currentRowChanged(QModelIndex,QModelIndex)")),
#self.assetChanged)
self.assetView.selectionModel().currentRowChanged.connect(
self.assetChanged)
addAssetButton.clicked.connect(self.addAsset)
deleteAssetButton.clicked.connect(self.deleteAsset)
addActionButton.clicked.connect(self.addAction)
deleteActionButton.clicked.connect(self.deleteAction)
editActionsButton.clicked.connect(self.editActions)
editCategoriesButton.clicked.connect(self.editCategories)
quitButton.clicked.connect(self.done)
self.assetChanged(self.assetView.currentIndex())
self.setMinimumWidth(650)
self.setWindowTitle("Asset Manager")
def done(self, result=1):
query = QSqlQuery()
query.exec_("DELETE FROM logs WHERE logs.assetid NOT IN"
"(SELECT id FROM assets)")
QDialog.done(self, 1)
def assetChanged(self, index):
if index.isValid():
record = self.assetModel.record(index.row())
#print(index.row())
id = record.value("id")
self.logModel.setFilter("assetid = {0}".format(id))
else:
self.logModel.setFilter("assetid = -1")
#self.logModel.reset() # workaround for Qt <= 4.3.3/SQLite bug
#self.logModel.beginResetModel()
self.logModel.select()
self.logView.horizontalHeader().setVisible(
self.logModel.rowCount() > 0)
if PYQT_VERSION_STR < "4.1.0":
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
#self.logModel.endResetModel()
def addAsset(self):
row = (self.assetView.currentIndex().row()
if self.assetView.currentIndex().isValid() else 0)
QSqlDatabase.database().transaction()
self.assetModel.insertRow(row)
index = self.assetModel.index(row, NAME)
self.assetView.setCurrentIndex(index)
assetid = 1
query = QSqlQuery()
query.exec_("SELECT MAX(id) FROM assets")
if query.next():
assetid = query.value(0)
query.prepare("INSERT INTO logs (assetid, date, actionid) "
"VALUES (:assetid, :date, :actionid)")
query.bindValue(":assetid", assetid + 1)
query.bindValue(":date", QDate.currentDate())
query.bindValue(":actionid", ACQUIRED)
query.exec_()
QSqlDatabase.database().commit()
#self.logModel.select()
self.assetView.edit(index)
def deleteAsset(self):
index = self.assetView.currentIndex()
if not index.isValid():
return
QSqlDatabase.database().transaction()
record = self.assetModel.record(index.row())
assetid = record.value(ID)
logrecords = 1
query = QSqlQuery(
"SELECT COUNT(*) FROM logs WHERE assetid = {0}".format(assetid))
if query.next():
logrecords = query.value(0)
msg = ("<font color=red>Delete</font><br><b>{0}</b>"
"<br>from room {1}").format(record.value(NAME),
record.value(ROOM))
if logrecords > 1:
msg += (", along with {0} log records".format(logrecords))
msg += "?"
if (QMessageBox.question(self, "Delete Asset", msg, QMessageBox.Yes
| QMessageBox.No) == QMessageBox.No):
QSqlDatabase.database().rollback()
return
#query.exec_("DELETE FROM logs WHERE assetid = {0}"
# .format(assetid))
#use model API
self.logModel.setFilter("assetid={0}".format(assetid))
self.logModel.select()
if self.logModel.rowCount() > 0:
self.logModel.removeRows(0, self.logModel.rowCount())
self.logModel.submitAll()
self.assetModel.removeRow(index.row())
self.assetModel.submitAll()
QSqlDatabase.database().commit()
self.assetModel.select()
self.assetChanged(self.assetView.currentIndex())
def addAction(self):
index = self.assetView.currentIndex()
if not index.isValid():
return
QSqlDatabase.database().transaction()
record = self.assetModel.record(index.row())
assetid = record.value(ID)
row = self.logModel.rowCount()
self.logModel.insertRow(row)
self.logModel.setData(self.logModel.index(row, ASSETID), assetid)
self.logModel.setData(self.logModel.index(row, DATE),
QDate.currentDate())
QSqlDatabase.database().commit()
index = self.logModel.index(row, ACTIONID)
self.logView.setCurrentIndex(index)
self.logView.edit(index)
def deleteAction(self):
index = self.logView.currentIndex()
if not index.isValid():
return
record = self.logModel.record(index.row())
action = record.value(ACTIONID)
if action == "Acquired":
QMessageBox.information(
self, "Delete Log",
"The 'Acquired' log record cannot be deleted.<br>"
"You could delete the entire asset instead.")
return
when = str(record.value(DATE))
if (QMessageBox.question(self, "Delete Log",
"Delete log<br>{0} {1}?".format(when, action),
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.No):
return
self.logModel.removeRow(index.row())
self.logModel.submitAll()
self.logModel.select()
def editActions(self):
form = ReferenceDataDlg("actions", "Action", self)
form.exec_()
def editCategories(self):
form = ReferenceDataDlg("categories", "Category", self)
form.exec_()
class Window(QMainWindow):
def __init__(self):
super().__init__()
self.ui = mainform.Ui_MainWindow()
self.ui.setupUi(self)
self.ui.retranslateUi(self)
self.db_path = r'db/db_test.sqlite3'
self.ui.lbl_full_path_db.setText(self.db_path)
self.ui.btn_db_open.clicked.connect(self.db_file_open)
self.ui.btn_add.clicked.connect(self.db_add)
self.ui.btn_add.setEnabled(False)
self.ui.btn_del.clicked.connect(self.db_del)
self.ui.btn_del.setEnabled(False)
self.ui.save_changes.clicked.connect(self.save_change_db)
self.ui.save_changes.setEnabled(False)
self.ui.mnu_exit.triggered.connect(self.close)
self.ui.tablelist.setEnabled(False)
self.db = None
self.db_model = None
self.open_db()
self.ui.tablelist.currentIndexChanged.connect(self.show_table)
def open_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
# self.show_table()
def get_tables_name(self):
self.ui.tablelist.clear()
for table_name in self.db.tables():
self.ui.tablelist.addItem(table_name)
def db_file_open(self):
self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0]
self.ui.lbl_full_path_db.setText(self.db_path)
self.db.close()
self.open_db()
self.ui.tablelist.setEnabled(True)
self.ui.btn_add.setEnabled(True)
self.ui.btn_del.setEnabled(True)
self.ui.save_changes.setEnabled(True)
self.ui.btn_db_open.setEnabled(False)
def show_table(self):
self.table_model = QSqlRelationalTableModel()
table = self.ui.tablelist.currentText()
if table == 'goods':
self.create_goods_table_model()
elif table == 'employees':
self.create_employees_table_model()
elif table == 'units':
self.create_units_table_model()
elif table == 'vendors':
self.create_vendors_table_model()
elif table == 'positions':
self.create_positions_table_model()
elif table == 'categories':
self.create_categories_table_model()
else:
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableView
view.setModel(self.table_model)
view.hideColumn(0)
view.setItemDelegate(QSqlRelationalDelegate(view))
def db_add(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def db_del(self):
rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
def create_goods_table_model(self):
self.table_model.setTable('goods')
self.table_model.setRelation(
2, QSqlRelation('units', 'unit_id', 'unit_name'))
self.table_model.setRelation(
3, QSqlRelation('categories', 'category_id', 'category_name'))
self.table_model.setRelation(
4, QSqlRelation('vendors', 'vendor_id', 'vendor_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название продукта')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal,
'Категория продукта')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Поставщик')
self.table_model.select()
def create_units_table_model(self):
self.table_model.setTable('units')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Единица измерения')
self.table_model.select()
def create_employees_table_model(self):
self.table_model.setTable('employees')
self.table_model.setRelation(
2, QSqlRelation('positions', 'position_id', 'position_name'))
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'ФИО сотрудника')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_positions_table_model(self):
self.table_model.setTable('positions')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Должность')
self.table_model.select()
def create_vendors_table_model(self):
self.table_model.setTable('vendors')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Наименование')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Форма собственности')
self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес')
self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Телефон')
self.table_model.setHeaderData(5, QtCore.Qt.Horizontal,
'Электронная почта')
self.table_model.select()
def create_categories_table_model(self):
self.table_model.setTable('categories')
self.table_model.setHeaderData(1, QtCore.Qt.Horizontal,
'Название категории')
self.table_model.setHeaderData(2, QtCore.Qt.Horizontal,
'Описание категории')
self.table_model.select()
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Изменения сохранены')
else:
self.ui.statusbar.showMessage(
f'{self.table_model.lastError().text()}')
class Client:
def __init__(self):
# init GUI
self.table_name = None
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
self.ui = Ui_MainWindow()
self.ui.setupUi(MainWindow)
MainWindow.show()
# parameters
self.db_path = None
self.db = None
# events
self.ui.openDbPushButton.clicked.connect(self.open_database)
self.ui.saveDbPushButton.clicked.connect(self.save_change_db)
self.ui.addRowPushButton.clicked.connect(self.add_row)
self.ui.deleteRowPushButton.clicked.connect(self.del_row)
self.ui.tablesListView.itemDoubleClicked.connect(self.change_table)
sys.exit(app.exec_())
def open_database_dialog(self):
try:
self.db_path = QFileDialog.getOpenFileName(self.ui.MainWindow, "Open file", '.')[0]
print(self.db_path)
self.ui.path_db.setText(self.db_path)
self.db.close()
except Exception as e:
pass
def open_database(self):
try:
self.open_database_dialog()
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_path)
self.db.open()
self.get_tables_name()
self.show_table()
except Exception as err:
print(err)
def get_tables_name(self):
self.ui.tablesListView.clear()
for table_name in self.db.tables():
self.ui.tablesListView.addItem(table_name)
def change_table(self, name):
self.table_name = self.ui.tablesListView.currentItem().text()
self.show_table()
def show_table(self):
self.table_model = QSqlRelationalTableModel()
if self.table_name:
table = self.table_name
else:
table = [str(self.ui.tablesListView.item(i).text()) for i in range(self.ui.tablesListView.count())][0]
self.table_model.setTable(table)
self.table_model.select()
self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
view = self.ui.tableViewerTableView
view.setModel(self.table_model)
view.setItemDelegate(QSqlRelationalDelegate(view))
def add_row(self):
self.table_model.insertRows(self.table_model.rowCount(), 1)
def save_change_db(self):
if self.table_model.submitAll():
self.ui.statusbar.showMessage('Changes were saved')
else:
self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
def del_row(self):
rs = list(map(lambda x: x.row(), self.ui.tableViewerTableView.selectedIndexes()))
print(rs)
for i in rs:
self.table_model.removeRows(i, 1)
self.ui.statusbar.showMessage("Row was deleted")