def data(self, QModelIndex, role=None): v = QSqlTableModel.data(self, QModelIndex, role) if role == Qt.BackgroundRole: index = self.index(QModelIndex.row(), 2) color = QSqlTableModel.data(self, index, Qt.DisplayRole) if color: return QColor(color) return (v)
def data(self, index, role=Qt.DisplayRole):
if role == SortRole:
value = QSqlTableModel.data(self, index, role=Qt.DisplayRole)
try:
return int(value)
except:
return value
else:
return QSqlTableModel.data(self, index, role)
class SqlModel(object):
def __init__(self,databasetype="QSQLITE",databasename="data.db",sqltablename="里程碑"):
db=QSqlDatabase.addDatabase(databasetype)
db.setDatabaseName(databasename)
self.sqltablename=sqltablename
self.__tablemodel()
def __tablemodel(self):
self.model=QSqlTableModel()
self.model.setTable(self.sqltablename)
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
def modelview(self):
self.view=QTableView()
self.view.setModel(self.model)
# modelwidget=QTabWidget()
# modelwidget.addTab(self.view, viewname)
return self.view
def tabadd(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row)
self.view.setCurrentIndex(index)
self.view.edit(index)
def tabdel(self):
index = self.modelview().currentIndex()
print(index)
# self.model.removeRow(index.row())
def tabupdate(self):
self.model.updateRowInTable()
def row(self):
row=self.model.rowCount()
return row
def line(self):
line=self.model.columnCount()
return line
def rowvalue(self):
rowvalue=self.model.insertRowIntoTable(QSqlRecord="")
def data(self):
self.model.data()
def run(self,WindowTitle= "data"):
app = QApplication(sys.argv)
dig=QDialog()
layout=QHBoxLayout()
layout.addWidget(self.modelview())
dig.setLayout(layout)
dig.setWindowTitle(WindowTitle)
dig.resize(430,450)
# dig.hide()
# time.sleep(10)
dig.show()
sys.exit(app.exec_())
class Lodgers(QtWidgets.QMainWindow, Ui_lodgers):
def __init__(self, parent=None):
super(Lodgers, self).__init__(parent)
self.setupUi(self)
sdb = QSqlDatabase.addDatabase('QSQLITE')
sdb.setDatabaseName('baza.db')
sdb.open()
self.model = QSqlTableModel(db=sdb)
self.model.setTable("users")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
self.tableView_1.setModel(self.model)
self.rоом_beds = []
self.pushButton_9.clicked.connect(
self.on_add_record) # Добавить запись
self.pushButton_10.clicked.connect(
self.on_del_record) # Удалить запись
def on_add_record(self):
row = self.tableView_1.selectionModel().currentIndex().row()
if self.tableView_1.selectionModel().currentIndex().row() == -1:
row = 0
self.model.insertRow(row)
else:
row = row + 1
self.model.insertRow(row)
self.model.setData(self.model.index(row, 0), 'ФИО NEW')
self.model.setData(self.model.index(row, 1), '0000')
self.model.setData(self.model.index(row, 2), '0')
self.model.setData(self.model.index(row, 3), '0')
self.model.submit()
def on_del_record(self):
self.model.removeRow(self.tableView_1.currentIndex().row())
self.model.select()
def _getRooms(self):
self.rоом_beds = []
for row in range(self.model.rowCount()):
if self.model.data(self.model.index(row, 2)) == 1:
rоом_bed = str(self.model.data(
self.model.index(row, 1)))[:3], \
str(self.model.data(self.model.index(row, 1)))[3]
self.rоом_beds.append(rоом_bed)
def data(self, index, role=Qt.DisplayRole):
value = super(ImportSqlTableModel, self).data(index)
if index.column() in self.booleanSet:
if role == Qt.CheckStateRole:
return Qt.Unchecked if value == 2 else Qt.Checked
else:
return QVariant()
return QSqlTableModel.data(self, index, role)
def data(self, index, rol):
if rol < Qt.UserRole:
return QSqlTableModel.data(self, index, rol)
sql_kayit = QSqlRecord()
sql_kayit = self.record(index.row())
return sql_kayit.value(rol - Qt.UserRole)
def data(self, index, role):
if role < Qt.UserRole:
return QSqlTableModel.data(self, index, role)
record = self.record(index.row())
if role < Qt.UserRole + self.columnCount():
column = role - Qt.UserRole - 1
return record.value(column)
dataValue = record.value('itemData')
data = deserializeData(dataValue)
if role == self.itemHtmlRole:
return data.get(mimeHtml, '')
if role == self.itemHasImage:
return mimePng in data
return None
class View(QtWidgets.QTableView):
def __init__(self, name, parent=None):
QtWidgets.QTableView.__init__(self, parent)
#设置所有表的基本信息
getDBs = (self.getLocalDB,self.getLocalDB, self.getLocalDB\
,self.getServerDB,self.getLocalDB,\
self.getLocalDB,self.getLocalDB,self.getLocalDB)
#建立基本结构
self.viewId = viewNames.index(name)
self.db = getDBs[self.viewId](name)
self.model = QSqlTableModel(self, self.db)
self.setModel(self.model)
self.show()
print(self.model.lastError().text())
#添加属性
self.setSelectionMode(QtWidgets.QAbstractItemView.MultiSelection)
self.setSelectionBehavior(QtWidgets.QAbstractItemView.SelectRows)
self.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)
def getKeys(self):
indexs = self.selectedIndexes()
rowCnt = int(len(indexs) / self.model.columnCount())
indexs = numpy.array(indexs).reshape(rowCnt, self.model.columnCount())
values = []
values_temp = []
for index in indexs:
values_temp = []
for keyColumn in keyColumns[self.viewId]:
values_temp.append(self.model.data(index[keyColumn]))
if len(keyColumns[self.viewId]) == 1: values.append(values_temp[0])
else: values.append(tuple(values_temp))
return tuple(values)
def filter(self, *args):
if self.viewId == 3:
#如果是examExamServer的话,为了添加is_download,必须把本地exam的ids作为形参输入
keys = getKeysExamExamLocal()
args = list(args)
args.append(keys)
self.query = QSqlQuery(getQuerys[self.viewId](*args), self.db)
self.model.setQuery(self.query)
print(self.model.lastError().text())
self.update()
def getLocalDB(self, dbName):
db = QSqlDatabase.addDatabase("QPSQL", dbName + '%f' % random.random())
#db = QSqlDatabase.addDatabase("QPSQL",dbName)
db.setHostName(config.localDBHost)
db.setPort(config.localDBPort)
db.setDatabaseName(config.localDBName)
db.setUserName(config.localDBUser)
db.setPassword(config.localDBPasswd)
if (db.open() == False):
QtWidgets.QMessageBox.critical(None, "Database Error",
db.lastError().text())
return db
def getServerDB(self, dbName):
db = QSqlDatabase.addDatabase("QPSQL", dbName)
db.setHostName(config.serverDBHost)
db.setPort(config.serverDBPort)
db.setDatabaseName(config.serverDBName)
db.setUserName(config.serverDBUser)
db.setPassword(config.serverDBPasswd)
if (db.open() == False):
QtWidgets.QMessageBox.critical(None, "Database Error",
db.lastError().text())
return db
class TableToTreeModel2(QAbstractItemModel):
"""
Более новый вариант - используем композицию, то есть абстрактная модель
включает в себя sql табличную модель только для работы с базой данных
"""
def __init__(self, m, connection):
QAbstractItemModel.__init__(self, m)
# попробуем сделать композицию
self.dbmodel = QSqlTableModel(self, connection)
self.dbmodel.setEditStrategy(0) # при каждом изменении поля
#print (self.dbmodel.hasIndex(1,12))
self.headers = [
'id', '_buy', 'deadline', 'made', 'significance', 'urgency',
'_children', '_next', '_parents', '_prev', 'season', 'short text',
'tags', 'text'
]
self.rootItem = TreeItem(self.headers)
def setTable(self, tname):
self.dbmodel.setTable(tname)
def select(self):
self.dbmodel.select()
#здесь должны грузиться данные
dct = dict() #словарь айди - список строк с данными
for ind in range(self.dbmodel.rowCount()):
#dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
dct[int(self.dbmodel.data(self.dbmodel.index(ind, 0)))] = [
self.dbmodel.data(self.dbmodel.index(ind, j))
for j in range(self.dbmodel.columnCount())
]
def find_children_and_append(item: TreeItem, dct):
chlist = eval(item.data(6))
for ch in chlist:
tri = TreeItem(dct[ch], item)
if tri.data(6) != '[]':
find_children_and_append(tri, dct)
item.appendChild(tri)
for i in [j for j in dct.values() if j[8] == '[]']:
tri = TreeItem(i, self.rootItem)
find_children_and_append(tri, dct)
self.rootItem.appendChild(tri)
def columnCount(self, QModelIndex_parent=None, *args, **kwargs):
return self.dbmodel.columnCount()
def data(self, index, role):
if not index.isValid():
return None
if role != Qt.DisplayRole:
return None
item = index.internalPointer()
return item.data(index.column())
def flags(self, index):
if not index.isValid():
return Qt.NoItemFlags
return Qt.ItemIsEnabled | Qt.ItemIsSelectable | Qt.ItemIsEditable
def headerData(self, section, orientation, role):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return self.headers[section]
#return self.dbmodel.headerData(section,orientation,role)
def index(self, row, column, parent):
if not self.hasIndex(row, column, parent):
return QModelIndex()
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
childItem = parentItem.child(row)
if childItem:
return self.createIndex(row, column, childItem)
else:
return QModelIndex()
def rowCount(self, parent):
if parent.column() > 0:
return 0
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
return parentItem.childCount()
def parent(self, index):
if not index.isValid():
return QModelIndex()
childItem = index.internalPointer()
parentItem = childItem.parent()
if parentItem == self.rootItem:
return QModelIndex()
return self.createIndex(parentItem.row(), 0, parentItem)
def getIndexById(self, _id):
for i in range(self.dbmodel.rowCount()):
if int(self.dbmodel.data(self.dbmodel.index(i, 0))) == _id:
return self.dbmodel.index(i, 0)
return QModelIndex()
def setData(self, modelIndex, value, int_role=Qt.EditRole):
"""
Функция вызывается при установке данных
:param QModelIndex:
:param QVariant:
:param int_role:
:return:
"""
print(self.dbmodel.record(0).setValue(12, 'sdfsfsdf'))
r = self.dbmodel.record(1)
r.setValue(12, 'Krevedko')
print(self.dbmodel.setRecord(0, r))
print(r)
return 1
class TableToTreeModel2 (QAbstractItemModel):
"""
Более новый вариант - используем композицию, то есть абстрактная модель
включает в себя sql табличную модель только для работы с базой данных
"""
def __init__(self, m, connection):
QAbstractItemModel.__init__(self, m)
# попробуем сделать композицию
self.dbmodel = QSqlTableModel(self, connection)
self.dbmodel.setEditStrategy(0) # при каждом изменении поля
#print (self.dbmodel.hasIndex(1,12))
self.headers=['id',
'_buy',
'deadline',
'made',
'significance',
'urgency',
'_children',
'_next',
'_parents',
'_prev',
'season',
'short text',
'tags',
'text']
self.rootItem = TreeItem (self.headers)
def setTable(self, tname):
self.dbmodel.setTable(tname)
def select(self):
self.dbmodel.select()
#здесь должны грузиться данные
dct = dict () #словарь айди - список строк с данными
for ind in range (self.dbmodel.rowCount()):
#dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
def find_children_and_append (item:TreeItem, dct):
chlist = eval(item.data(6))
for ch in chlist:
tri = TreeItem(dct[ch], item)
if tri.data(6) != '[]':
find_children_and_append(tri,dct)
item.appendChild(tri)
for i in [j for j in dct.values() if j[8]=='[]']:
tri = TreeItem(i, self.rootItem)
find_children_and_append(tri,dct)
self.rootItem.appendChild(tri)
def columnCount(self, QModelIndex_parent=None, *args, **kwargs):
return self.dbmodel.columnCount()
def data(self, index, role):
if not index.isValid():
return None
if role != Qt.DisplayRole:
return None
item = index.internalPointer()
return item.data(index.column())
def flags(self, index):
if not index.isValid():
return Qt.NoItemFlags
return Qt.ItemIsEnabled | Qt.ItemIsSelectable |Qt.ItemIsEditable
def headerData(self, section, orientation, role):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return self.headers[section]
#return self.dbmodel.headerData(section,orientation,role)
def index(self, row, column, parent):
if not self.hasIndex(row, column, parent):
return QModelIndex()
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
childItem = parentItem.child(row)
if childItem:
return self.createIndex(row, column, childItem)
else:
return QModelIndex()
def rowCount(self, parent):
if parent.column() > 0:
return 0
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
return parentItem.childCount()
def parent(self, index):
if not index.isValid():
return QModelIndex()
childItem = index.internalPointer()
parentItem = childItem.parent()
if parentItem == self.rootItem:
return QModelIndex()
return self.createIndex(parentItem.row(), 0, parentItem)
def getIndexById(self, _id):
for i in range (self.dbmodel.rowCount()):
if int (self.dbmodel.data(self.dbmodel.index(i,0)))==_id:
return self.dbmodel.index(i,0)
return QModelIndex()
def setData(self, modelIndex, value, int_role=Qt.EditRole):
"""
Функция вызывается при установке данных
:param QModelIndex:
:param QVariant:
:param int_role:
:return:
"""
print (self.dbmodel.record(0).setValue(12, 'sdfsfsdf'))
r = self.dbmodel.record(1)
r.setValue(12, 'Krevedko')
print (self.dbmodel.setRecord(0,r))
print (r)
return 1
class MainWindow(QMainWindow, Ui_MainWindow):
def __init__(self):
super(MainWindow, self).__init__()
self.folder_path = QDir.current().path()
self.init_ui()
def init_ui(self):
"""Initialize ui."""
self.setupUi(self)
self.move_to_center()
self.init_table_view()
icon = QIcon('subtitle_analyze_gui.ico')
self.setWindowIcon(icon)
movie = QMovie('processing.gif')
self.label.setMovie(movie)
movie.start()
self.label.setVisible(False)
self.tableView.resizeColumnsToContents()
self.tableView.resizeRowsToContents()
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.actionOpen_Folder.triggered.connect(self.open_folder)
self.actionSave_As.triggered.connect(self.save_result)
self.actionExit.triggered.connect(self.close)
self.refreshPushButton.released.connect(self.refresh)
def init_table_view(self):
"""Initialize the table view"""
if not create_connection():
sys.exit(1)
self.model = QSqlTableModel()
initialize_model(self.model)
self.tableView.setModel(self.model)
self.tableView.setSortingEnabled(True)
reflesh_model(self.model)
def move_to_center(self):
"""Move windows to the center of the screen."""
screen = QDesktopWidget().screenGeometry()
size = self.geometry()
self.move((screen.width() - size.width()) / 2,
(screen.height() - size.height()) / 2)
def open_folder(self):
"""Set target folder."""
self.folder_path = QFileDialog.getExistingDirectory(
self, 'Open the folder', QDir.currentPath())
if not self.folder_path:
self.folder_path = QDir.current().path()
def save_result(self):
"""Save analyze result."""
filename = QFileDialog.getSaveFileName(
self, 'Save analyze result',
os.path.join(QDir.current().path(), 'result.csv'), 'File (*.csv)')
with open(filename[0], 'w', encoding='utf-8') as file:
row = self.model.rowCount()
col = self.model.columnCount()
for i in range(row):
text = ''
for j in range(col):
text += str(self.model.data(self.model.index(i, j))) + '\t'
text += '\n'
file.writelines(text)
def analyze_subtitle(self):
"""Analyze subtitles"""
filename_list = get_subtitle_filename_list(self.folder_path, mode='r')
result_dict = single_thread_analyze(filename_list)
for key in result_dict.keys():
add_record(result_dict[key][0], result_dict[key][1],
result_dict[key][2] / 1000, result_dict[key][3])
def refresh(self):
"""Execute analysis task."""
self.label.setVisible(True)
self.refreshPushButton.setEnabled(False)
reflesh_model(self.model)
self.task_thread = TaskThread()
self.task_thread.set_task(self.analyze_subtitle)
self.task_thread.finish_signal.connect(self.process_thread_message)
self.task_thread.start()
def process_thread_message(self, message):
"""Processing thread message."""
reflesh_model(self.model)
self.label.setVisible(False)
self.refreshPushButton.setEnabled(True)
class MainMenu(QMainWindow):
def __init__(self, parent=None):
super(MainMenu, self).__init__(parent)
# ----------------- UI --------------------------#
loadUi("./UI/main_ui.ui", self)
self.setWindowTitle("密码保险柜")
self.setWindowIcon(QIcon("./image/1.ico"))
# ----------------- 按键 --------------------------#
self.pushButtonExit.clicked.connect(QCoreApplication.instance().quit)
self.pushButtonDel.clicked.connect(self.delete)
self.pushButtonAdd.clicked.connect(self.add)
self.refresh.clicked.connect(self.reselect)
# ----------------- db --------------------------#
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("./db/word.db")
# ----------------- model --------------------------#
self.model = QSqlTableModel()
self.model.setTable("warehouse")
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "ID")
self.model.setHeaderData(SITE, Qt.Horizontal, "站点")
self.model.setHeaderData(NAME, Qt.Horizontal, "账号")
self.model.setHeaderData(PASS, Qt.Horizontal, "密码")
self.model.setHeaderData(REMARK, Qt.Horizontal, "备注")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
# ----------------- tableView --------------------------#
self.tableView.setModel(self.model)
self.tableView.horizontalHeader().setStretchLastSection(True)
# ----------------- query --------------------------#
self.query = QSqlQuery()
def contextMenuEvent(self, event):
cmenu = QMenu()
quitAct = cmenu.addAction("退出")
action = cmenu.exec_(self.mapToGlobal(event.pos()))
if action == quitAct:
qApp.quit()
def add(self):
self.query.exec(
"insert into `warehouse` VALUES (NULL ,'example.com','example','example','example')"
)
self.reselect()
rows = self.model.rowCount()
self.tableView.selectRow(rows)
def delete(self):
row = self.tableView.currentIndex().row()
site_id = self.model.data(self.model.index(row, 0))
sql = "delete from `warehouse` where id =" + str(site_id)
self.query.exec(sql)
self.reselect()
def reselect(self):
if self.model.data(self.model.index(self.model.rowCount(), 0)) != "":
self.model.select()
