def __init__(self):
super(Ajout_Competence, self).__init__()
self.setupUi(self)
self.model = QtSql.QSqlRelationalTableModel()
self.model.setTable('competences')
self.model.setRelation(
1, QtSql.QSqlRelation("operations", "code_op", "code_op"))
self.model.setRelation(
0, QtSql.QSqlRelation("employes", "matricule", "matricule"))
#self.model.setRelation(0,QtSql.QSqlRelation("employe", "matricule", "nom"))
#self.model.setRelation(0,QtSql.QSqlRelation("employe", "matricule", "prenom"))
self.model.select()
self.relationModel = self.model.relationModel(1)
self.op_cmp_aj.setModel(self.relationModel)
self.op_cmp_aj.setModelColumn(self.relationModel.fieldIndex("code_op"))
self.relationModel2 = self.model.relationModel(0)
self.mat_cmp_aj.setModel(self.relationModel2)
self.mat_cmp_aj.setModelColumn(
self.relationModel2.fieldIndex("matricule"))
#self.mat_cmp_aj.currentIndexChanged.connect(self.add_info)
self.annuler_cmp.clicked.connect(self.toListe)
#self.initialiser_aj_cmp.clicked.connect(self.initialise)
self.enregistrer_cmp.clicked.connect(self.add)
def show_visitor(self):
selected_building_id = self.get_selected_building_id()
selected_city_id = self.get_selected_city_id()
try:
manager = visitor_manager.Visitor_manager(
selected_building_id, selected_city_id,
self.comboBoxVisitor.currentText(),
self.lineEditVisitor.text(), self.loader)
manager.add_visitor()
except:
return
self.table_special_model.setEditStrategy(
models.SqlTableModel.OnFieldChange)
self.table_special_model.setTable('characters')
self.set_special_table_header()
self.table_special_model.setFilter(
'visiting_id = {0}'.format(selected_building_id))
self.table_special_model.setRelation(
12, QtSql.QSqlRelation('cities', 'id', 'name'))
self.table_special_model.setRelation(
13, QtSql.QSqlRelation('buildings', 'id', 'name'))
self.table_special_model.select()
self.tableViewSpecial.resizeColumnsToContents()
def set_full_character_table(self):
self.table_content_model.setEditStrategy(
models.SqlTableModel.OnFieldChange)
self.table_content_model.setTable('characters')
self.set_view_table_header('characters')
self.table_content_model.setRelation(
12, QtSql.QSqlRelation('cities', 'id', 'name'))
self.table_content_model.setRelation(
13, QtSql.QSqlRelation('buildings', 'id', 'name'))
self.table_content_model.select()
self.tableViewContent.resizeColumnsToContents()
def model_setup(self):
self.logger.info("Initializing Model")
tie_model = QtSql.QSqlRelationalTableModel(self)
tie_model.setTable("ties")
tie_model.setHeaderData(1, QtCore.Qt.Horizontal, "Team 1")
tie_model.setHeaderData(2, QtCore.Qt.Horizontal, "Team 2")
tie_model.setHeaderData(3, QtCore.Qt.Horizontal, "Event")
tie_model.setHeaderData(4, QtCore.Qt.Horizontal, "Winner")
tie_model.setRelation(1, QtSql.QSqlRelation("teams", "id", "Name"))
tie_model.setRelation(2, QtSql.QSqlRelation("teams", "id", "Name"))
tie_model.setRelation(4, QtSql.QSqlRelation("teams", "id", "Name"))
tie_model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
tie_model.select()
self.model = tie_model
self.model.dataChanged.connect(self.update_min_width)
def __init__(self, parent=None):
model = QtSql.QSqlRelationalTableModel()
model.setTable(u'ПиццаЗаказ')
model.setRelation(1, QtSql.QSqlRelation(u"Пицца", u"код", u"название"))
model.setHeaderData(1, QtCore.Qt.Horizontal, u"Наименование пиццы",
QtCore.Qt.DisplayRole)
model.setRelation(2, QtSql.QSqlRelation(u"Заказ", u"код", u"статус"))
model.setHeaderData(2, QtCore.Qt.Horizontal, u"Статус заказа",
QtCore.Qt.DisplayRole)
model.setRelation(3, QtSql.QSqlRelation(u"Клиенты", u"код", u"адрес"))
model.setHeaderData(3, QtCore.Qt.Horizontal, u"Адрес доставки",
QtCore.Qt.DisplayRole)
model.select()
super(PageOrders, self).__init__(model, appname, parent)
def __init__(self, tableName, newDB, parent=None):
super(PersonEditor, self).__init__(parent)
if newDB:
self.createTable()
#self.model = QtSql.QSqlTableModel(self)
self.model = QtSql.QSqlRelationalTableModel(self)
self.model.setTable(tableName)
self.model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
#self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.model.setRelation(3, QtSql.QSqlRelation('gender', 'id', 'name'))
for c in range(self.model.columnCount()):
self.model.setHeaderData(c, QtCore.Qt.Horizontal, self.tr(self.model.record().fieldName(c)))
self.model.select()
self.view = QtWidgets.QTableView()
self.view.setModel(self.model)
self.view.setItemDelegate(QtSql.QSqlRelationalDelegate(self.view))
self.view.resizeColumnsToContents()
buttons = Buttons(self)
mainLayout = QtWidgets.QVBoxLayout()
mainLayout.addWidget(self.view)
mainLayout.addWidget(buttons.buttonBox)
self.setLayout(mainLayout)
def __inti__(self, parent=None):
#QtWidgets.QWidget.__init__(self, parent)
#self.resize(300, 100)
app = QtWidgets.QApplication(sys.argv)
window = QtWidgets.QWidget()
window.setWindowTitle("QRelationalSqlTableModel")
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName('hl.db3')
con.open()
stm = QtSql.QSqlRelationalTableModel(parent=window)
stm.setTable('good')
stm.setSort(1, QtCore.Qt.AscendingOrder)
# Задаем для поля категории связь с таблицей списка категорий
stm.setRelation(3, QtSql.QSqlRelation('category', 'id', 'catname'))
stm.select()
stm.setHeaderData(1, QtCore.Qt.Horizontal, 'Название')
stm.setHeaderData(2, QtCore.Qt.Horizontal, 'Кол-во')
stm.setHeaderData(3, QtCore.Qt.Horizontal, 'Категория')
vbox = QtWidgets.QVBoxLayout()
tv = QtWidgets.QTableView()
tv.setModel(stm)
tv.hideColumn(0)
tv.setColumnWidth(1, 150)
tv.setColumnWidth(2, 60)
tv.setColumnWidth(3, 150)
vbox.addWidget(tv)
btnAdd = QtWidgets.QPushButton("&Добавить запись")
btnAdd.clicked.connect(addRecord)
vbox.addWidget(btnAdd)
btnDel = QtWidgets.QPushButton("&Удалить запись")
btnDel.clicked.connect(delRecord)
vbox.addWidget(btnDel)
def STMR(self,table,filt='',us=''):
self.stmR.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.stmR.setTable(table)
if filt !='':
print("'"+filt+"'"+'='+"'"+us+"'")
self.stmR.setFilter(filt+'='+"'"+us+"'")
self.stmR.setRelation(1, QtSql.QSqlRelation('bands','ID','nameB'))
self.stmR.select()
return self.stmR
def set_building_table(self, city_id):
self.table_content_model.setEditStrategy(
models.SqlTableModel.OnFieldChange)
self.table_content_model.setTable('buildings')
self.set_view_table_header('buildings')
self.table_content_model.setFilter('city_id = {0}'.format(city_id))
self.table_content_model.setRelation(
4, QtSql.QSqlRelation('cities', 'id', 'name'))
self.table_content_model.select()
self.tableViewContent.resizeColumnsToContents()
def get_sql_model():
db = QtSql.QSqlDatabase('QSQLITE')
db.setDatabaseName('db/chinook.db')
db.open()
model = QtSql.QSqlRelationalTableModel(db=db)
model.setTable('tracks')
model.setRelation(2, QtSql.QSqlRelation('albums', 'AlbumId', 'Title'))
model.setRelation(3,
QtSql.QSqlRelation('media_types', 'MediaTypeId', 'Name'))
model.setRelation(4, QtSql.QSqlRelation('genres', 'GenreId', 'Name'))
model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
model.select()
# model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
# model.setHeaderData(1, QtCore.Qt.Horizontal, "First name")
# model.setHeaderData(2, QtCore.Qt.Horizontal, "Last name")
return model
def __init__(self, parent):
QtSql.QSqlRelationalTableModel.__init__(self, parent)
self.header = [
'ID', 'Directory', 'Field', 'Component Name', 'Units', 'Scale',
'Offset', 'Attribute', 'Tags'
]
self.setTable('environment')
self.setJoinMode(QtSql.QSqlRelationalTableModel.LeftJoin)
self.setRelation(
7, QtSql.QSqlRelation('ref_env_attributes', 'code', 'code'))
self.setRelation(4,
QtSql.QSqlRelation('ref_speed_units', 'code', 'code'))
self.setRelation(
3,
QtSql.QSqlRelation('components', 'component_name',
'component_name'))
self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.select()
def setViewModel(tblName=None,
filtro=None,
lstVal2Rel=None,
lstRelTblName=None,
lstNewNames=None,
sort=0):
'''
Funcao para settar (ahahahaha) ou configurar o modelo, usando o built in relationTable.
Tem como objectivo facilitar o mapeamento e visualizacao de dados, o modelo tem como
princinpal objectivo o uso na vizulizacao de dados em mapper e Views.
O Metodo tem a habilida de ser util em passando todos paramentros ou nao mais o paramentro
fundamental e tblName.
Args:
tblName = Nome da tabela aqual queremos ligar na base de dados, (Obrigatorio)
filtro = filtro ou where e como desejamaos filtrar, (opicional)
lstVal2Rel = os valores que desejamos fazer a relacao, (opicional)
lstRelTblName = a tabela aqual desejamaos nos relacionar (obrigatorio se o campo anterior foi passado)
lstNewNames = os novos nomes que irao aparecer no header (opicional)
sort = como desejamos organizar a tabela por padrao e sempre o primeiro elemento e pode ser mudado (opicional)
dictModel['val2Rel'] = lstVal2Rel
dictModel["relTblName"] = lstRelTblName
dictModel['newNames'] = lstNewNames
'''
bOK = False
try:
Model = QSqlRelationalTableModel()
Model.setTable(tblName)
if filtro is not None: #verificacao do Filtro
Model.setFilter(filtro)
if lstVal2Rel is not None and lstRelTblName is not None: #verificacao da lista de valores relacionais
for idx, val in enumerate(lstVal2Rel):
if val is not None:
Model.setRelation(
idx,
QtSql.QSqlRelation(lstRelTblName[idx], val[0], val[1]))
Model.setJoinMode(QSqlRelationalTableModel.LeftJoin)
if lstNewNames is not None:
for idx, val in enumerate(
lstNewNames): #verificao dos Novos nomes na tabela
Model.setHeaderData(idx, Qt.Horizontal, val)
Model.setSort(sort, Qt.AscendingOrder)
bOK = Model.select()
if not bOK:
msg.error(txt="Erro na configuracao do Modelo",
verbTxt=str(Model.lastError().text()))
return (bOK, Model)
else:
return (bOK, Model)
except TypeError as TE:
msg.error(txt="Erro na configuracao do Modelo", verbTxt=str(TE))
return (bOK, None)
def show_selected_family(self):
selected_character = self.get_characters_from_db(
'id', self.get_selected_character_id())
selected_character = selected_character[0]
selected_family_id = selected_character.family_id
self.table_special_model.setEditStrategy(
models.SqlTableModel.OnFieldChange)
self.table_special_model.setTable('characters')
self.set_special_table_header()
self.table_special_model.setFilter(
'family_id = {0}'.format(selected_family_id)) # Parent
self.table_special_model.setRelation(
12, QtSql.QSqlRelation('cities', 'id', 'name'))
self.table_special_model.setRelation(
13, QtSql.QSqlRelation('buildings', 'id', 'name'))
self.table_special_model.select()
self.tableViewSpecial.resizeColumnsToContents()
def createModel(self):
self.model = QtSql.QSqlRelationalTableModel()
self.model.setTable("agent")
self.model.setHeaderData(0, Qt.Horizontal, "name")
self.model.setHeaderData(1, Qt.Horizontal, "address")
self.model.setHeaderData(2, Qt.Horizontal, "phone")
self.model.setHeaderData(2, Qt.Horizontal, "region")
self.model.setRelation(
2, QtSql.QSqlRelation("regions", "regionId", "region"))
self.model.select()
def __init__(self, log, cfg, db, api, tbl_name, name, parent=None):
self.log = log
self.cfg = cfg
self.db = db
self.api = api
self.tbl_name = tbl_name
self.o_name = name
self.log.info("Конструктор окна отгрузки")
QtWidgets.QWidget.__init__(self, parent)
self.setupUi(self)
self.printer = QtPrintSupport.QPrinter()
self.printer.setResolution(203)
self.stm = QtSql.QSqlRelationalTableModel(self)
self.stm.setTable(self.tbl_name)
self.stm.setHeaderData(0, QtCore.Qt.Horizontal, 'Модель')
self.stm.setHeaderData(1, QtCore.Qt.Horizontal, 'Размер')
self.stm.setHeaderData(2, QtCore.Qt.Horizontal, 'GTIN')
self.stm.setHeaderData(3, QtCore.Qt.Horizontal, 'Сырье')
self.stm.setHeaderData(4, QtCore.Qt.Horizontal, 'Количество')
self.stm.setHeaderData(5, QtCore.Qt.Horizontal, 'Напечатано')
self.stm.setHeaderData(6, QtCore.Qt.Horizontal, 'Доступно')
self.stm.setRelation(0, QtSql.QSqlRelation("GTINS", 'GTIN', 'MODEL'))
self.stm.setRelation(1, QtSql.QSqlRelation("GTINS", 'GTIN', 'SIZE'))
self.stm.setSort(3, QtCore.Qt.AscendingOrder)
self.stm.select()
self.table.setModel(self.stm)
self.table.setColumnWidth(2, 180)
self.list, self.models = self.readGtins()
self.model.addItems(self.list)
self.model.activated.connect(self.buildSizes)
self.btnAdd.clicked.connect(self.add)
self.btnDelete.clicked.connect(self.delete)
self.prn.clicked.connect(self.doPrn)
def createModel(self):
self.model = QtSql.QSqlRelationalTableModel()
self.model.setTable("Books")
self.model.setHeaderData(0, Qt.Horizontal, "BookId")
self.model.setHeaderData(1, Qt.Horizontal, "Title")
self.model.setHeaderData(2, Qt.Horizontal, "Author")
self.model.setRelation(
2, QtSql.QSqlRelation("Authors", "AuthorId", "Name"))
self.model.select()
def setViewModel(self):
self.Model = QSqlRelationalTableModel()
self.Model.setTable(self.TblName)
self.Model.setFilter(self.filtro)
for idx, val in enumerate (self.dictModel['val2Rel']):
if val is not None:
self.Model.setRelation(idx, QtSql.QSqlRelation(self.dictModel["relTblName"][idx], val[0], val[1]))
self.Model.setSort(0, Qt.AscendingOrder)
for idx, val in enumerate (self.dictModel['newNames']):
self.Model.setHeaderData(idx, Qt.Horizontal, val)
bOK = self.Model.select()
if not bOK:
self.msg.error(txt="Erro na configuracao do Modelo", verbTxt=str(self.Model.lastError().text()))
def load(self, db):
self.db = db
if self.name not in self.db.tables():
self.create(self.db)
self.model = SqlRelationalTableModel(self.parentRef.model, None, db)
self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.model.setTable(self.name)
parentIndex = self.model.fieldIndex('parentid')
self.model.setRelation(parentIndex,
QtSql.QSqlRelation(self.parentName, 'id', 'value'))
self.reload()
def ModelTableViewLink (MyTableView, MyTableName):
MyModel = MyTableModel()
MyModel.ConfigTable(MyTableName,cfgDB.DicTable[MyTableName][0],
cfgDB.DicTable[MyTableName][1],
cfgDB.DicTable[MyTableName][2])
for row in cfgDB.DicJoin[MyTableName]:
(NumColumn,(SqlTable, SqlKey, SqlDesc)) = row
MyModel.setRelation(NumColumn,QtSql.QSqlRelation(SqlTable, SqlKey, SqlDesc))
MyTableView.setModel(MyModel)
MyTableView.setSortingEnabled(True)
if len(cfgDB.DicJoin[MyTableName])>0:
MyTableView.setItemDelegate(QtSql.QSqlRelationalDelegate(MyTableView))
MyTableView.show()
def setViewModel(tblName=None, filtro=None, lstVal2Rel=None, lstRelTblName=None,lstNewNames=None, sort=0):
'''
Funcao para configurar o modelos[model] Relacionais.
Tem como objectivo facilitar visualizacao de dados.
Este Modelo aconcelho a usar para #dict Style
Args:
tblName = Nome da tabela aqual queremos ligar na base de dados, (Obrigatorio)
filtro = filtro ou where e como desejamaos filtrar, (opicional)
lstVal2Rel = os valores que desejamos fazer a relacao, (opicional)
lstRelTblName = a tabela aqual desejamaos nos relacionar (obrigatorio se o campo anterior foi passado)
lstNewNames = os novos nomes que irao aparecer no header (opicional)
sort = como desejamos organizar a tabela por padrao e sempre o primeiro elemento e pode ser mudado (opicional)
dictModel['val2Rel'] = lstVal2Rel
dictModel['relTblName'] = lstRelTblName
dictModel['newNames'] = lstNewNames
'''
bOK = False
try:
Model = QSqlRelationalTableModel()#Cria o object QSqlRelationalTableModel
Model.setTable(tblName) #Atribuimos uma tabela ao objecto criado
if filtro is not None: #Verifica se Lhe foi atribuido um Filtro
Model.setFilter(filtro) #Caso tenha sido atribuido Colocamos o filtro
if lstVal2Rel is not None and lstRelTblName is not None: #Vericamos se as listas de valores e tabelas relacionais estao vazias
for idx, val in enumerate (lstVal2Rel):#Caso nao estejam vazias
if val is not None:
Model.setRelation(idx, QtSql.QSqlRelation(lstRelTblName[idx], val[0], val[1])) #atribuimos uma relacao a cada valor com a devida Tabela a que pertence
Model.setJoinMode(QSqlRelationalTableModel.LeftJoin)#Identificamos o tipo de relacao
if lstNewNames is not None:#Verificamos se lhe foi atribuida uma lista de novos nome[headers]
for idx, val in enumerate (lstNewNames): #Levamos para cada nome
Model.setHeaderData(idx, Qt.Horizontal, val)#Atribuimos a cada valos no nosso objecto
Model.setSort(sort, Qt.AscendingOrder) #Organizado em ordem crescente
bOK = Model.select() #Metodo para executar todas mudancas (select), que se todos os campos acima foram correctamentes prenchenchos retoma Verdade caso contrario Falso
if not bOK: #Se a Excucao foi Falsa entao mostramos a menssagem de error.
msg.error(txt="Erro na configuracao do Modelo", verbTxt=str(Model.lastError().text()))
return (bOK, Model)
else:
return (bOK, Model)#Se correr tudo bem Retorna Verdade e o Modelo Criaado
except TypeError as TE:#Se a Excucao foi Falsa entao mostramos a menssagem de error, isto e nos casos mais extremos.
msg.error(txt="Erro na configuracao do Modelo", verbTxt=str(TE))
return (bOK, None)
def config_measure_table(self, a_db_name: str):
self.display_db_connection.setDatabaseName(a_db_name)
res = self.display_db_connection.open()
assert res, "Can't open database {0}!".format(a_db_name)
self.display_db_model.setTable("measures")
self.display_db_model.setRelation(
MeasureColumn.DEVICE_SYSTEM,
QtSql.QSqlRelation("system", "id", "name"))
for column in range(self.display_db_model.columnCount()):
self.display_db_model.setHeaderData(column, QtCore.Qt.Horizontal,
MEASURE_COLUMN_TO_NAME[column])
self.sort_proxy_model.setSourceModel(self.display_db_model)
self.ui.measures_table.setModel(self.sort_proxy_model)
# Чтобы был приятный цвет выделения
self.ui.measures_table.setItemDelegate(
NonOverlappingDoubleClick(self.ui.measures_table))
self.ui.measures_table.selectionModel().currentChanged.connect(
self.current_selection_changed)
self.ui.measures_table.selectionModel().modelChanged.connect(
self.current_selection_changed)
self.ui.measures_table.selectionModel().selectionChanged.connect(
self.current_selection_changed)
self.ui.measures_table.horizontalHeader().restoreState(
self.settings.get_last_header_state(self.__class__.__name__))
self.ui.measures_table.setColumnHidden(MeasureColumn.ID, True)
header_context = qt_utils.TableHeaderContextMenu(
self, self.ui.measures_table, True)
self.ui.measures_table.horizontalHeader().setSectionsMovable(True)
self.ui.measures_table.customContextMenuRequested.connect(
self.show_table_custom_menu)
self.update_table()
return header_context
def setupUi(self):
self.centralwidget = QtWidgets.QWidget(self)
hBox = QtWidgets.QHBoxLayout(self.centralwidget)
self.treeView = QtWidgets.QTreeView(self.centralwidget)
self.treeView.setRootIsDecorated(False)
self.treeView.setSortingEnabled(True)
self.treeView.setAlternatingRowColors(True)
self.treeView.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)
self.treeView.setSelectionMode(QtWidgets.QAbstractItemView.ExtendedSelection)
self.treeView.header().setStretchLastSection(True)
hBox.addWidget(self.treeView)
self.setCentralWidget(self.centralwidget)
header = FilterHeader(self.treeView)
self.treeView.setHeader(header)
# self.treeView.sortByColumn(2, QtCore.Qt.DescendingOrder)
# StatusBar
self.statusBar = QtWidgets.QStatusBar()
self.setStatusBar(self.statusBar)
# ToolBar
exitAct = QtWidgets.QAction(
"Exit", self, shortcut="Ctrl+Q", triggered=QtWidgets.qApp.quit
)
newRecordAct = QtWidgets.QAction(
"New", self, shortcut="Ctrl+N", triggered=self.new_record
)
editRecordAct = QtWidgets.QAction(
"Edit", self, shortcut="Return", triggered=self.edit_record
)
refreshAct = QtWidgets.QAction(
"Refresh", self, shortcut="F5", triggered=self.refresh
)
self.toolbar = self.addToolBar("Exit")
self.toolbar.addAction(exitAct)
self.toolbar.addSeparator()
self.toolbar.addAction(newRecordAct)
self.toolbar.addAction(editRecordAct)
self.toolbar.addSeparator()
self.toolbar.addAction(refreshAct)
modelTable.setTable(MY_TABLE)
modelTable.setHeaderData(0, QtCore.Qt.Horizontal, "row id")
modelTable.setHeaderData(1, QtCore.Qt.Horizontal, "ID")
modelTable.setHeaderData(2, QtCore.Qt.Horizontal, "Last Name")
modelTable.setHeaderData(3, QtCore.Qt.Horizontal, "First Name")
modelTable.setHeaderData(4, QtCore.Qt.Horizontal, "Country")
modelTable.setRelation(4, QtSql.QSqlRelation("country", "id", "name"))
modelTable.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
self.treeView.setModel(
modelTable
) # display data of the SQLTableModel into the QTreeView
if not TRACE:
self.treeView.setColumnHidden(0, True)
# enable human sorting
proxy = HumanProxyModel(self)
proxy.setSourceModel(modelTable)
self.treeView.setModel(proxy)
# enable filtering
header.setFilterBoxes(modelTable.columnCount())
header.filterActivated.connect(self.handleFilterActivated)
# update counters
global g_total_count, g_hits_count
g_total_count = modelTable.rowCount()
g_hits_count = g_total_count
self.updateStatus()
def delRecord():
stm.removeRow(tv.currentIndex().row())
stm.select()
app = QtWidgets.QApplication(sys.argv)
window = QtWidgets.QWidget()
window.setWindowTitle("QRelationalSqlTableModel")
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName('data.sqlite')
con.open()
stm = QtSql.QSqlRelationalTableModel(parent=window)
stm.setTable('good')
stm.setSort(1, QtCore.Qt.AscendingOrder)
# Задаем для поля категории связь с таблицей списка категорий
stm.setRelation(3, QtSql.QSqlRelation('category', 'id', 'catname'))
stm.select()
stm.setHeaderData(1, QtCore.Qt.Horizontal, 'Название')
stm.setHeaderData(2, QtCore.Qt.Horizontal, 'Кол-во')
stm.setHeaderData(3, QtCore.Qt.Horizontal, 'Категория')
vbox = QtWidgets.QVBoxLayout()
tv = QtWidgets.QTableView()
tv.setModel(stm)
tv.setItemDelegateForColumn(3, QtSql.QSqlRelationalDelegate(tv))
tv.hideColumn(0)
tv.setColumnWidth(1, 150)
tv.setColumnWidth(2, 60)
tv.setColumnWidth(3, 150)
vbox.addWidget(tv)
btnAdd = QtWidgets.QPushButton("&Добавить запись")
btnAdd.clicked.connect(addRecord)
def on_u_catalog_btn(parent, db_path):
def on_add_user():
# Добавляем строку
um.insertRow(um.rowCount())
def on_del_user():
# Удаляем строку
um.removeRow(umv.currentIndex().row())
# Выполняем повторное считывание данных в модель,
# чтобы убрать пустую "мусорную" запись
um.select()
def on_data_change(um):
um.submitAll()
# Считаем количество записей после сохранения
query = QtSql.QSqlQuery()
query.exec("SELECT COUNT(*) AS new_count FROM users")
query.next()
recordCount = query.record()
new_count_int = query.value(recordCount.indexOf("new_count"))
if old_count_int != new_count_int:
msg_lbl = QLabel(
'<span style="color:green">Добавлен новый пользователь</span>')
parent.sm_list_widget.clear()
parent.item = QListWidgetItem()
parent.sm_list_widget.addItem(parent.item)
parent.sm_list_widget.setItemWidget(parent.item, msg_lbl)
# Устанавливаем соединение с базой данных
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName(db_path)
con.open()
# Создаем окно
parent.u_table_window = QWidget()
parent.u_table_window.setWindowModality(QtCore.Qt.ApplicationModal)
parent.u_table_window.setWindowTitle("Справочник 'Пользователи'")
parent.u_table_window.resize(440, 300)
# Создаем модель
um = QtSql.QSqlRelationalTableModel(parent=parent.u_table_window)
# Связываем таблицу с моделью
um.setTable('users')
um.setSort(0, QtCore.Qt.AscendingOrder)
# Устанавливаем связь с внешней таблицей
um.setRelation(4, QtSql.QSqlRelation('rules', 'rule', 'rule'))
# Считываем данные в модель
um.select()
# Задаем заголовки для столбцов модели
um.setHeaderData(1, QtCore.Qt.Horizontal, 'Логин')
um.setHeaderData(2, QtCore.Qt.Horizontal, 'Пароль')
um.setHeaderData(3, QtCore.Qt.Horizontal, 'Имя')
um.setHeaderData(4, QtCore.Qt.Horizontal, 'Права')
# При изменении данных выполняем сохранение
# Считаем количество записей до сохранения
query = QtSql.QSqlQuery()
query.exec("SELECT COUNT(*) AS old_count FROM users")
query.next()
recordCount = query.record()
old_count_int = query.value(recordCount.indexOf("old_count"))
query.finish()
um.dataChanged.connect(lambda: on_data_change(um))
# Создаем виджет-таблицу
umv = QTableView()
# Связываем модель с виджетом-таблицей
umv.setModel(um)
umv.setItemDelegate(QtSql.QSqlRelationalDelegate(umv))
umv.hideColumn(0)
# Создаем контейнер
users_vbox = QVBoxLayout()
users_vbox.addWidget(umv)
# Создаем кнопки и размещаем их в контейнеры
btnAdd = QPushButton("Добавить пользователя")
btnAdd.clicked.connect(on_add_user)
users_vbox.addWidget(btnAdd)
btnDel = QPushButton("Удалить пользователя")
btnDel.clicked.connect(on_del_user)
users_vbox.addWidget(btnDel)
# Размещаем контейнер в окне
parent.u_table_window.setLayout(users_vbox)
parent.u_table_window.show()
screen = QDesktopWidget().screenGeometry()
x = int((screen.width() - parent.u_table_window.width()) / 2)
y = int((screen.height() - parent.u_table_window.height()) / 2)
parent.u_table_window.move(x, y)
app = QtWidgets.QApplication(sys.argv)
window = QtWidgets.QWidget()
window.setWindowTitle('Список друзей')
con1 = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con1.setDatabaseName('mydb.sqlite')
con1.open()
stm = QtSql.QSqlRelationalTableModel(parent=window)
stm.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
stm.setTable('Contacts')
# stm.setSort(1, QtCore.Qt.AscendingOrder)
stm.setRelation(1, QtSql.QSqlRelation('Users', 'UserId', 'Name'))
stm.setRelation(2, QtSql.QSqlRelation('Users', 'UserId', 'Name'))
stm.dataChanged.connect(stm.submitAll)
stm.select()
stm.setHeaderData(1, QtCore.Qt.Horizontal, 'Пользователь')
stm.setHeaderData(2, QtCore.Qt.Horizontal, 'Друг')
vbox = QtWidgets.QVBoxLayout()
tv = QtWidgets.QTableView()
tv.setModel(stm)
tv.setItemDelegateForColumn(1, QtSql.QSqlRelationalDelegate(tv))
tv.setItemDelegateForColumn(2, QtSql.QSqlRelationalDelegate(tv))
tv.hideColumn(0)
tv.setColumnWidth(1, 150)
tv.setColumnWidth(2, 150)
vbox.addWidget(tv)
btnAdd = QtWidgets.QPushButton('Добавить запись')
def on_n_catalog_btn(parent, db_path):
def on_add_item():
nm.insertRow(nm.rowCount())
nm_row_count = nm.rowCount()
img_btn = QPushButton("Выбрать")
img_btn.setStyleSheet(
"border-width: 1px;"
"border-style: solid;"
"border-color: dimgray;"
"border-radius: 4px;"
"background-color: silver;")
img_btn.clicked.connect(on_img_choose)
img_btn.setFixedSize(90, 25)
cell = nm.index(nm_row_count - 1, 4)
nmv.setIndexWidget(cell, img_btn)
def on_del_item():
nm.removeRow(nmv.currentIndex().row())
nm.select()
empty_lbl = QLabel()
parent.setCentralWidget(empty_lbl)
parent.cdw.setWidget(empty_lbl)
parent.ldw_tree_model.removeRows(0, parent.ldw_tree_model.rowCount())
con = sqlite3.connect(db_path)
cur = con.cursor()
sql_cats_select = """\
SELECT DISTINCT item_cat FROM nomenclature
"""
cur.execute(sql_cats_select)
arr = cur.fetchall()
for cat in arr:
sql_items_select = "SELECT item_name FROM nomenclature \
WHERE item_cat = ?"
cur.execute(sql_items_select, [cat[0]])
items = cur.fetchall()
parent_item = QStandardItem(cat[0])
parent.ldw_tree_model.appendRow(parent_item)
j = 0
for item in items:
child_item = QStandardItem(item[0])
parent_item.setChild(j, 0, child_item)
j = j + 1
parent.ldw_tree.setModel(parent.ldw_tree_model)
def on_img_choose():
cur_index = nmv.currentIndex().row()
numb_txt = nm.index(cur_index, 0).data()
item_form_class.on_form_open(parent.nom_table_window, numb_txt, db_path, parent)
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName(db_path)
con.open()
parent.nom_table_window = QWidget()
parent.nom_table_window.setWindowModality(QtCore.Qt.ApplicationModal)
parent.nom_table_window.setWindowTitle("Номенклатура товаров")
parent.nom_table_window.resize(760, 250)
nm = QtSql.QSqlRelationalTableModel(parent=parent.nom_table_window)
nm.setTable('nomenclature')
nm.setSort(0, QtCore.Qt.AscendingOrder)
nm.setRelation(2, QtSql.QSqlRelation('units', 'unit', 'unit'))
nm.setRelation(
3,
QtSql.QSqlRelation(
'categories',
'category',
'category'))
nm.select()
nmv = QTableView()
nmv.setModel(nm)
nmv.setItemDelegate(QtSql.QSqlRelationalDelegate(nmv))
nmv.setColumnWidth(0, 130)
nmv.setColumnWidth(1, 320)
nmv.setColumnWidth(2, 50)
nmv.setColumnWidth(3, 130)
nmv.setColumnWidth(4, 90)
# Задаем заголовки для столбцов модели
nm.setHeaderData(0, QtCore.Qt.Horizontal, 'Номенклатурный №')
nm.setHeaderData(1, QtCore.Qt.Horizontal, 'Название')
nm.setHeaderData(2, QtCore.Qt.Horizontal, 'Ед. изм.')
nm.setHeaderData(3, QtCore.Qt.Horizontal, 'Категория')
nm.setHeaderData(4, QtCore.Qt.Horizontal, 'Изображение')
items_vbox = QVBoxLayout()
items_vbox.addWidget(nmv)
nm_row_count = nm.rowCount()
for index in range(nm_row_count):
img_btn = QPushButton("Выбрать")
img_btn.setStyleSheet(
"border-width: 1px;"
"border-style: solid;"
"border-color: dimgray;"
"border-radius: 4px;"
"background-color: silver;")
img_btn.clicked.connect(on_img_choose)
img_btn.setFixedSize(90, 25)
cell = nm.index(index, 4)
nmv.setIndexWidget(cell, img_btn)
btnAdd = QPushButton("Добавить товар")
btnAdd.clicked.connect(on_add_item)
items_vbox.addWidget(btnAdd)
btnDel = QPushButton("Удалить товар")
btnDel.clicked.connect(on_del_item)
items_vbox.addWidget(btnDel)
parent.nom_table_window.setLayout(items_vbox)
parent.nom_table_window.show()
# Считаем количество записей до сохранения
query = QtSql.QSqlQuery()
query.exec("SELECT COUNT(*) AS old_count FROM nomenclature")
query.next()
recordCount = query.record()
old_count_int = query.value(recordCount.indexOf("old_count"))
query.finish()
print(nm.dataChanged.connect(nm.submitAll))
screen = QDesktopWidget().screenGeometry()
x = int((screen.width() - parent.nom_table_window.width()) / 2)
y = int((screen.height() - parent.nom_table_window.height()) / 2)
parent.nom_table_window.move(x, y)
def on_e_catalog_btn(parent, db_path):
def on_add_employee():
em.insertRow(em.rowCount())
def on_del_employee():
em.removeRow(emv.currentIndex().row())
em.select()
def on_data_change(em):
em.submitAll()
# Считаем количество записей после сохранения
query = QtSql.QSqlQuery()
query.exec("SELECT COUNT(*) AS new_count FROM employee")
query.next()
recordCount = query.record()
new_count_int = query.value(recordCount.indexOf("new_count"))
if old_count_int != new_count_int:
msg_lbl = QLabel(
'<span style="color:green">Добавлен новый сотрудник</span>')
parent.sm_list_widget.clear()
parent.item = QListWidgetItem()
parent.sm_list_widget.addItem(parent.item)
parent.sm_list_widget.setItemWidget(parent.item, msg_lbl)
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName(db_path)
con.open()
parent.employee_table_window = QWidget()
parent.employee_table_window.setWindowModality(
QtCore.Qt.ApplicationModal)
parent.employee_table_window.setWindowTitle("Список сотрудников")
parent.employee_table_window.resize(240, 250)
em = QtSql.QSqlRelationalTableModel(
parent=parent.employee_table_window)
em.setTable('employee')
em.setSort(0, QtCore.Qt.AscendingOrder)
em.setRelation(
2,
QtSql.QSqlRelation(
'positions',
'position',
'position'))
em.select()
em.setHeaderData(1, QtCore.Qt.Horizontal, 'ФИО')
em.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность')
emv = QTableView()
emv.setModel(em)
emv.setItemDelegate(QtSql.QSqlRelationalDelegate(emv))
emv.hideColumn(0)
vbox = QVBoxLayout()
vbox.addWidget(emv)
btnAdd = QPushButton("Добавить сотрудника")
btnAdd.clicked.connect(on_add_employee)
vbox.addWidget(btnAdd)
btnDel = QPushButton("Удалить сотрудника")
btnDel.clicked.connect(on_del_employee)
vbox.addWidget(btnDel)
parent.employee_table_window.setLayout(vbox)
parent.employee_table_window.show()
# Считаем количество записей до сохранения
query = QtSql.QSqlQuery()
query.exec("SELECT COUNT(*) AS old_count FROM employee")
query.next()
recordCount = query.record()
old_count_int = query.value(recordCount.indexOf("old_count"))
query.finish()
em.dataChanged.connect(lambda: on_data_change(em))
screen = QDesktopWidget().screenGeometry()
x = int((screen.width() - parent.employee_table_window.width()) / 2)
y = int((screen.height() - parent.employee_table_window.height()) / 2)
parent.employee_table_window.move(x, y)
def _init_relations(self, table_name: str):
if table_name == DataBaseTables.Schedule.value:
self.model.setRelation(
1,
QtSql.QSqlRelation(
"groups",
"id",
"name"
)
)
self.model.setHeaderData(
1,
Qt.Horizontal,
'groups.name'
)
self.model.setRelation(
3,
QtSql.QSqlRelation(
"subjects",
"id",
"name"
)
)
self.model.setHeaderData(
3,
Qt.Horizontal,
'subjects.name'
)
elif table_name == DataBaseTables.Subjects.value:
self.model.setRelation(
2,
QtSql.QSqlRelation(
"lectures",
"id",
"name"
)
)
self.model.setHeaderData(
2,
Qt.Horizontal,
'lectures.name'
)
self.model.setRelation(
4,
QtSql.QSqlRelation(
"teachers",
"id",
"name"
)
)
self.model.setHeaderData(
4,
Qt.Horizontal,
'teachers.name'
)
def __init__(self):
"""MainWindow constructor.
Code in this method should define window properties,
create backend resources, etc.
"""
super().__init__()
# Code starts here
self.stack = qtw.QStackedWidget()
self.setCentralWidget(self.stack)
# Connect to the database
db = qts.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('coffee.db')
if not db.open():
qtw.QMessageBox.critical(
None, 'DB Connection Error',
'Could not open database file: '
f'{db.lastError().text()}')
sys.exit(1)
# Check for missing tables
required_tables = {'roasts', 'coffees', 'reviews'}
missing_tables = required_tables - set(db.tables())
if missing_tables:
qtw.QMessageBox.critical(
None, 'DB Integrity Error',
'Missing tables, please repair DB: '
f'{missing_tables}')
sys.exit(1)
# Create the models
self.reviews_model = qts.QSqlTableModel()
self.reviews_model.setTable('reviews')
self.coffees_model = qts.QSqlRelationalTableModel()
self.coffees_model.setTable('coffees')
self.coffees_model.setRelation(
self.coffees_model.fieldIndex('roast_id'),
qts.QSqlRelation('roasts', 'id', 'description')
)
self.coffees_model.setEditStrategy(0)
self.coffees_model.dataChanged.connect(print)
self.coffee_list = qtw.QTableView()
self.coffee_list.setModel(self.coffees_model)
self.stack.addWidget(self.coffee_list)
self.coffees_model.select()
#self.show()
#return
self.show_list()
# Inserting and deleting rows.
toolbar = self.addToolBar('Controls')
toolbar.addAction('Delete Coffee(s)', self.delete_coffee)
toolbar.addAction('Add Coffee', self.add_coffee)
self.coffee_list.setItemDelegate(qts.QSqlRelationalDelegate())
#self.show()
#return
# The coffee form
self.coffee_form = CoffeeForm(
self.coffees_model,
self.reviews_model
)
self.stack.addWidget(self.coffee_form)
self.coffee_list.doubleClicked.connect(
self.coffee_form.show_coffee)
self.coffee_list.doubleClicked.connect(
lambda: self.stack.setCurrentWidget(self.coffee_form))
toolbar.addAction("Back to list", self.show_list)
# Code ends here
self.show()
app = QtWidgets.QApplication(sys.argv)
window = QtWidgets.QWidget()
window.setWindowTitle("QRelationalSqlTableModel")
con = QtSql.QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName('hl.db3')
con.open()
stm = QtSql.QSqlRelationalTableModel(parent=window)
stm.setTable('library')
stm.setSort(1, QtCore.Qt.AscendingOrder)
# Задаем для поля категории связь с таблицей списка категорий
stm.setRelation(
10,
QtSql.QSqlRelation('author_name', 'author_secondname', 'author_otchestvo',
'name_book', 'book_yearprint', 'publishing',
'locate_storage', 'on_storage', 'quarcode_add',
'date_bookadd'))
stm.select()
stm.setHeaderData(1, QtCore.Qt.Horizontal, 'Имя')
stm.setHeaderData(2, QtCore.Qt.Horizontal, 'Фамилия')
stm.setHeaderData(3, QtCore.Qt.Horizontal, 'Отчество')
stm.setHeaderData(4, QtCore.Qt.Horizontal, 'Название книги')
stm.setHeaderData(5, QtCore.Qt.Horizontal, 'Год издания')
stm.setHeaderData(6, QtCore.Qt.Horizontal, 'Издательство')
stm.setHeaderData(7, QtCore.Qt.Horizontal, 'Место в библиотеке')
stm.setHeaderData(8, QtCore.Qt.Horizontal, 'На месте ли книги')
stm.setHeaderData(9, QtCore.Qt.Horizontal, 'QR')
stm.setHeaderData(10, QtCore.Qt.Horizontal, 'Дата добавления')
#stm.setHeaderData(11, QtCore.Qt.Horizontal, 'Категория')
vbox = QtWidgets.QVBoxLayout()
tv = QtWidgets.QTableView()
