class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.table = QTableView()
self.model = QSqlTableModel(db=db)
self.table.setModel(self.model)
# tag::titles[]
self.model.setTable("Track")
column_titles = {
"Name": "Name",
"AlbumId": "Album (ID)",
"MediaTypeId": "Media Type (ID)",
"GenreId": "Genre (ID)",
"Composer": "Composer",
}
for n, t in column_titles.items():
idx = self.model.fieldIndex(n)
self.model.setHeaderData(idx, Qt.Horizontal, t)
self.model.select()
# end::titles[]
self.model.select()
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(self.table)
def UseSqlQuery(db, query, columns):
model = QSqlTableModel(db=db)
model.setQuery(query)
for column in columns:
if column[hcol_idx.DISPLAY_NAME]:
model.setHeaderData(model.fieldIndex(column[hcol_idx.DB_NAME]),
Qt.Horizontal, column[hcol_idx.DISPLAY_NAME])
return model
class SQLite(QSqlDatabase):
"""
Класс для инициализации и открытия базы данных каталога, полученный из Slot'а (openFile)
"""
def __init__(self, path=None):
"""
Инициализация экземпляра класса
:param path: путь до каталога, полученный изиз Slot'а (openFile)
"""
super().__init__()
self.path = path
self.width = 0
self.heigh = 0
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.path)
self.db.open()
self.window = QWidget()
self.window.setWindowTitle("Каталог книг")
self.conn = sqlite3.connect(self.path)
cursor = self.conn.cursor()
sql = f'select * from sqlite_master where type = "table"' # получение имени таблицы(первая в списке), к которой будет
# осуществлено подключение
cursor.execute(sql)
self.search_result = cursor.fetchall()[0][1]
self.model = QSqlTableModel(parent=self.window, db=self.db)
self.model.setTable(self.search_result)
self.db_record = QSqlRecord(self.db.record(self.search_result))
self.tableView = QTableView()
def on(self):
"""
Метод для подключения и отображения подключенного каталога
:return: виджет self.window
"""
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.select()
self.model.setHeaderData(-1, Qt.Horizontal,
self.db_record.fieldName(0))
vbox = QVBoxLayout()
self.tableView.setModel(self.model)
self.tableView.resizeColumnsToContents()
self.tableView.resizeRowsToContents()
for i in range(self.model.columnCount() + 2):
self.width += self.tableView.columnWidth(i)
for j in range(self.model.rowCount() + 1):
self.heigh += self.tableView.rowHeight(j)
self.tableView.resize(self.width + 50, self.heigh + 50)
vbox.addWidget(self.tableView)
self.window.setLayout(vbox)
self.window.resize(self.tableView.width() + 30,
self.tableView.height() + 120)
return self.window
def create_model() -> QSqlTableModel:
"""Create and return the model to use with the playlist table view."""
model = QSqlTableModel()
model.setTable("playlist")
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
model.setHeaderData(1, Qt.Horizontal, "Title", Qt.DisplayRole)
model.setHeaderData(2, Qt.Horizontal, "Artist", Qt.DisplayRole)
model.setHeaderData(3, Qt.Horizontal, "Album", Qt.DisplayRole)
model.setHeaderData(4, Qt.Horizontal, "Genre", Qt.DisplayRole)
model.setHeaderData(5, Qt.Horizontal, "Date", Qt.DisplayRole)
# Default is a descending sort, which leads to an inconsistency given media is appended
model.setSort(0, Qt.AscendingOrder)
model.select() # Force-update the view
return model
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.table = QTableView()
self.model = QSqlTableModel(db=db)
self.table.setModel(self.model)
# tag::titles[]
self.model.setTable("Track")
self.model.setHeaderData(1, Qt.Horizontal, "Name")
self.model.setHeaderData(2, Qt.Horizontal, "Album (ID)")
self.model.setHeaderData(3, Qt.Horizontal, "Media Type (ID)")
self.model.setHeaderData(4, Qt.Horizontal, "Genre (ID)")
self.model.setHeaderData(5, Qt.Horizontal, "Composer")
self.model.select()
# end::titles[]
self.model.select()
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(self.table)
class Stats(Ui_armus):
def __init__(self):
# 从文件中加载UI定义
# qfile=QFile(r'E:\Pycharm\armus1\ui\armus1.ui')
# qfile.open(QFile.ReadOnly)
# # qfile_stats.close()
# # 从 UI 定义中动态 创建一个相应的窗口对象
# # 注意：里面的控件对象也成为窗口对象的属性了
# # 比如 self.ui.button , self.ui.textEdit
# # loader=QUiLoader()
# self.ui=QUiLoader().load(qfile)
# self.ui.show()
super(Stats,self).__init__()
# self.ui=Ui_armus()
# self.tableView.setModel()
db=QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('universitys.db')
self.spider=Data_Spider()#爬虫连接
self.session=DBSession()#数据库连接
self.setupUi(self)
# self.query=QSqlQuery(db)
self.init_connect_db()
# self.model = QStandardItemModel() # 存储任意结构数据
# self.model=QSqlQueryModel()
self.comboBox.activated[str].connect(self.orderinfo)
self.pushButton.clicked.connect(self.add_college)
self.pushButton_2.clicked.connect(self.update_college)
self.pushButton_4.clicked.connect(self.spiders_info)
self.pushButton_5.clicked.connect(self.spider_info)
# self.pushButton_3.clicked.connect(self.help_info)
self.comboBox_2.activated[str].connect(self.link_collegeurl)
#初始化连接数据库
def init_connect_db(self):
self.model = QSqlTableModel()
self.tableView.setModel(self.model)
self.model.setTable('notifications')
self.model.setHeaderData(0, Qt.Horizontal, "全文链接")
self.model.setHeaderData(1, Qt.Horizontal, "讲座标题")
self.model.setHeaderData(2, Qt.Horizontal, "报告大学")
self.model.setHeaderData(3, Qt.Horizontal, "报告人")
self.model.setHeaderData(4, Qt.Horizontal, "报告地点")
self.model.setHeaderData(5, Qt.Horizontal, "举办时间")
self.model.setHeaderData(6, Qt.Horizontal, "发布时间")
# self.model.setFilter('college="华南理工大学软件学院"')
self.model.select()
#排序筛选信息
def orderinfo(self,text):
if text=="按举办时间排序":
# print('按举办时间排序')
self.model.setFilter('title like "%%密码%" or title like "%%安全%" or title like "%security%"')
self.model.setSort(5,Qt.DescendingOrder)#按举办时间降序
self.model.select()
elif text=='按发布时间排序':
# print('按发布时间排序')
self.model.setFilter('title like "%%密码%" or title like "%%安全%" or title like "%security%"')
self.model.setSort(6, Qt.DescendingOrder)#按发布时间降序
self.model.select()
else:
self.model.setFilter('url like "%%"')
self.model.select()
# QMessageBox.critical(NULL, "critical", "Content", QMessageBox.Yes, QMessageBox.Yes) #带按键
def add_college(self): #添加学校网页
url=self.lineEdit_7.text()
college=self.lineEdit.text()
nextpage=self.lineEdit_2.text()
url_xpath=self.lineEdit_3.text()
text_xpath=self.lineEdit_4.text()
notify_time=self.lineEdit_5.text()
self.spider.set_college_url(url)
self.spider.set_college(college)
self.spider.set_next_xpath(nextpage)
self.spider.set_url_xpath(url_xpath)
self.spider.set_text_xpath(text_xpath)
self.spider.set_notify_time_xpath(notify_time)
self.spider.set_title_word()
self.spider.insert_seed(self.session)
def update_college(self):
list_college=self.session.query(Seed).all()
num=self.comboBox_2.count()
colleges=[]
for i in range(num):
colleges.append(self.comboBox_2.itemText(i))
for row in list_college:
if row.college not in colleges:
self.comboBox_2.addItem(row.college)
def link_collegeurl(self,text):
college=self.session.query(Seed).filter(Seed.college==text).first()
if college!=None:
self.lineEdit_6.setText(college.start_url)
self.model.setFilter('college="%s"'%(text))
self.model.select()
else:
self.lineEdit_6.setText('')
self.model.select()
def spiders_info(self):
self.spider.universities_spider()
MESSAGE = "更新/爬取学术信息完成"
msgBox = QMessageBox(QMessageBox.Question,
"提示", MESSAGE,
QMessageBox.NoButton, self)
msgBox.addButton("确定", QMessageBox.AcceptRole)
msgBox.exec_()
def spider_info(self):
text=self.comboBox_2.currentText()
college = self.session.query(Seed).filter(Seed.college == text).first()
self.spider.university_spider(college)
MESSAGE = "更新/爬取学术信息完成"
msgBox = QMessageBox(QMessageBox.Question,
"提示", MESSAGE,
QMessageBox.NoButton, self)
msgBox.addButton("确定", QMessageBox.AcceptRole)
msgBox.exec_()
class ManageSettings(QMainWindow):
def __init__(self, parent):
super(ManageSettings, self).__init__(parent)
self.setWindowTitle("Paraméterek kezelése")
widget = QWidget()
main_layout = QHBoxLayout()
widget.setLayout(main_layout)
self.setCentralWidget(widget)
self.table_view = QTableView()
main_layout.addWidget(self.table_view)
self.model = QSqlTableModel(db=db)
self.model.setTable("settings")
self.model.select()
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setHeaderData(1, Qt.Horizontal, "Paraméter")
self.model.setHeaderData(2, Qt.Horizontal, "Érték")
self.table_view.setModel(self.model)
self.table_view.setSortingEnabled(True)
self.table_view.hideColumn(0)
self.table_view.resizeColumnsToContents()
self.model.dataChanged.connect(self.valtozott)
gomb_layout = QVBoxLayout()
main_layout.addLayout(gomb_layout)
self.apply_button = QPushButton("Módosítások alkalmazása")
self.cancel_button = QPushButton("Módosítások elvetése")
gomb_layout.addWidget(self.apply_button)
gomb_layout.addWidget(self.cancel_button)
self.space = QSpacerItem(0, 0, QSizePolicy.Minimum,
QSizePolicy.Expanding)
gomb_layout.addItem(self.space)
self.setFixedSize(400, 600)
tb = self.addToolBar("File")
exit = QAction(QIcon("images/door--arrow.png"), "Kilépés", self)
tb.addAction(exit)
tb.actionTriggered[QAction].connect(self.toolbarpressed)
self.apply_button.clicked.connect(self.valtozas_mentese)
self.cancel_button.clicked.connect(self.valtozas_elvetese)
def toolbarpressed(self, a):
if a.text() == "Kilépés":
self.close()
def valtozott(self):
self.apply_button.setStyleSheet('background-color: green;')
self.cancel_button.setStyleSheet('background-color: red;')
def valtozas_mentese(self):
self.model.submitAll()
self.apply_button.setStyleSheet('')
self.cancel_button.setStyleSheet('')
def valtozas_elvetese(self):
self.model.revertAll()
self.apply_button.setStyleSheet('')
self.cancel_button.setStyleSheet('')
class ManageMembers(QMainWindow):
def __init__(self, parent):
super(ManageMembers, self).__init__(parent)
self.setWindowTitle("Tagok kezelése")
widget = QWidget()
main_layout = QHBoxLayout()
widget.setLayout(main_layout)
self.setCentralWidget(widget)
self.table_view = QTableView()
main_layout.addWidget(self.table_view)
self.model = QSqlTableModel(db=db)
self.model.setTable("members")
self.model.select()
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.setHeaderData(1, Qt.Horizontal, "Vezetéknév")
self.model.setHeaderData(2, Qt.Horizontal, "Utónév")
self.model.setHeaderData(3, Qt.Horizontal, "Született")
self.model.setHeaderData(4, Qt.Horizontal, "Ir.szám")
self.model.setHeaderData(5, Qt.Horizontal, "Helység")
self.model.setHeaderData(6, Qt.Horizontal, "Cím")
self.model.setHeaderData(7, Qt.Horizontal, "Telefon")
self.model.setHeaderData(8, Qt.Horizontal, "E-mail")
self.model.setHeaderData(9, Qt.Horizontal, "Tagság kezdete")
self.model.setHeaderData(10, Qt.Horizontal, "Aktív")
# self.model.setFilter('vezeteknev Like "Czi%"')
self.table_view.setModel(self.model)
self.table_view.hideColumn(0)
self.table_view.resizeColumnsToContents()
# Ha ez engedélyezve, akkor a model-nél beállított sort nem működik, ez felülírja
# Enélkül működik a model-es beállítás
self.table_view.setSortingEnabled(True)
# Ha engedélyezve van a fejléc szerinti rendezés, akkor UTÁNA meg lehet adni az alap sorrendet
self.table_view.sortByColumn(1, Qt.AscendingOrder)
self.model.dataChanged.connect(self.valtozott)
gomb_layout = QVBoxLayout()
main_layout.addLayout(gomb_layout)
self.delete_button = QPushButton("&Tag törlése")
self.add_button = QPushButton("&Új tag")
self.apply_button = QPushButton("Módosítások alkalmazása")
self.cancel_button = QPushButton("Módosítások elvetése")
gomb_layout.addWidget(self.delete_button)
gomb_layout.addWidget(self.add_button)
gomb_layout.addWidget(self.apply_button)
gomb_layout.addWidget(self.cancel_button)
self.space = QSpacerItem(0, 0, QSizePolicy.Minimum,
QSizePolicy.Expanding)
gomb_layout.addItem(self.space)
self.setFixedSize(1000, 800)
tb = self.addToolBar("File")
exit = QAction(QIcon("images/door--arrow.png"), "Kilépés", self)
tb.addAction(exit)
excel = QAction(QIcon("images/excel.png"), "Excel", self)
tb.addAction(excel)
tb.actionTriggered[QAction].connect(self.toolbarpressed)
self.delete_button.clicked.connect(self.tag_torles)
self.add_button.clicked.connect(self.tag_hozzadas)
self.apply_button.clicked.connect(self.valtozas_mentese)
self.cancel_button.clicked.connect(self.valtozas_elvetese)
def tag_hozzadas(self):
self.form_window = UjtagFormDialog()
self.form_window.setWindowTitle("Új tag felvétele")
if self.form_window.exec_():
record = self.model.record()
record.remove(record.indexOf('id'))
for i in range(len(self.form_window.mezo_ertekek)):
record.setValue(i, self.form_window.mezo_ertekek[i].text())
# print(i, record.value(i))
if self.model.insertRecord(-1, record):
self.model.submitAll()
self.apply_button.setStyleSheet('')
self.cancel_button.setStyleSheet('')
else:
db.rollback()
def tag_torles(self):
if len(self.table_view.selectedIndexes()) > 0:
self.model.removeRow(self.table_view.selectedIndexes()[0].row())
self.model.submitAll()
else:
reply = QMessageBox.question(None, 'Hiba!',
'Törlés előtt jelöljön ki egy sort!',
QMessageBox.Ok)
def toolbarpressed(self, a):
if a.text() == "Kilépés":
self.close()
if a.text() == "Excel":
data = []
for i in range(self.model.rowCount()):
sor = []
for j in range(self.model.columnCount()):
if isinstance(self.model.record(i).value(j), QDate):
sor.append(
self.model.record(i).value(j).toString(
"yyyy-MM-dd"))
else:
sor.append(self.model.record(i).value(j))
data.append(sor)
p.save_as(array=data, dest_file_name="tagok.xlsx")
def valtozott(self):
self.apply_button.setStyleSheet('background-color: green;')
self.cancel_button.setStyleSheet('background-color: red;')
def valtozas_mentese(self):
self.model.submitAll()
self.apply_button.setStyleSheet('')
self.cancel_button.setStyleSheet('')
def valtozas_elvetese(self):
self.model.revertAll()
self.apply_button.setStyleSheet('')
self.cancel_button.setStyleSheet('')
