def songProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_artist.name FROM fk_song_artist '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE fk_song_artist.id_song = ' +
str(self.tableView_Song_List.currentIndex().sibling(
self.tableView_Song_List.currentIndex().row(), 0).data()))
model.setHeaderData(0, Qt.Horizontal, "Artist Name")
self.tableView_Song_Properties.setModel(model)
pass
class QueryDialog(QDialog):
"""a dialog to choose an item from a query
"""
choice = pyqtSignal(str)
def __init__(self, query):
super().__init__()
self.query = query
self.create_model()
self.init_UI()
def create_model(self):
"""creates the model as QSqlQueryModel,
using the given query
"""
self.model = QSqlQueryModel()
q = QSqlQuery()
q.exec_(self.query)
self.model.setQuery(q)
def init_UI(self):
"""setup the UI
"""
layout = QVBoxLayout()
self.setLayout(layout)
self.resize(200,200)
self.title = "Choose an existing project"
self.list = QListView(self)
layout.addWidget(self.list)
self.list.setModel(self.model)
self.list.setWhatsThis("Choose a project by clicking on it")
self.btn = QPushButton("Accept", self)
layout.addWidget(self.btn)
self.btn.clicked.connect(self.on_btn_clicked)
self.btn.setWhatsThis("Click here to accept your selection (works only if a project has been selected)")
def on_btn_clicked(self):
"""when self.btn is clicked, accept the choice and emit it as self.choice
"""
selected = self.list.selectedIndexes()
if selected:
index = selected[0]
chosen = self.model.data(index, Qt.DisplayRole)
self.choice.emit(chosen)
self.close()
self.choice.emit("")
self.close()
class SalesForm(QDialog):
def __init__(self, db):
super(QWidget, self).__init__()
self.ui = Ui_SalesDialog()
self.ui.setupUi(self)
self.db = db
self.queryModel = QSqlQueryModel()
self.ui.lstSales.setModel(self.queryModel)
self.onUpdateSales()
def onUpdateSales(self):
query = "SELECT sales.id, slabs.name, slabs.num, strftime('%d-%m-%Y', datetime(sales.date, 'unixepoch')) as date, sales.amount, sales.buyer, sales.notes FROM `slabs`, `sales` on slabs.id = sales.slabid;"
print(query)
self.queryModel.setQuery(query, self.db)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
self.query_open = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
WHERE project_status = 'Open'
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.query_all = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.q = QSqlQuery()
self.log.debug("\t=> Done!")
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def create_model(self):
"""creates the table model
"""
q = QSqlQuery(self.query + " " + self.filter)
self.model = QSqlQueryModel()
q.exec_(self.query)
self.model.setQuery(q)
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
def create_model(self):
"""creates the model as QSqlQueryModel,
using the given query
"""
self.model = QSqlQueryModel()
q = QSqlQuery()
q.exec_(self.query)
self.model.setQuery(q)
def attractionView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_attraction')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Attraction_List.setModel(model)
pass
def competitorView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_competitor')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Competitor_List.setModel(model)
pass
def __init__(self, db):
super(QWidget, self).__init__()
self.ui = Ui_SalesDialog()
self.ui.setupUi(self)
self.db = db
self.queryModel = QSqlQueryModel()
self.ui.lstSales.setModel(self.queryModel)
self.onUpdateSales()
def carregarTableByEditora(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery(
"SELECT"
" titulo, editoras.editora,"
" qtde_estoque, vl_unitario, consignado"
" FROM livros"
" INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id"
" WHERE editoras.editora LIKE '%s'" %
(self.ui.txtPesquisar.text() + "%"))
model = QSqlTableModel(self, db)
model.setQuery(query.query())
model.select()
self.ui.tvLivros.setModel(model)
self.ui.tvLivros.show()
db.close()
def setData(self, index, value, role):
if index.column() != 2:
return False
primaryKeyIndex = QSqlQueryModel.index(self, index.row(), 1)
id = self.data(primaryKeyIndex)
self.clear()
ok = self.setState(id, str(value))
self.refresh()
return ok
def nozzleInit(parent):
parent.nozzleMapper = QDataWidgetMapper(parent)
parent.nozzleModel = QSqlQueryModel(parent)
thickness = parent.thicknessLbl.text()
parent.nozzleModel.setQuery("SELECT DISTINCT nozzle FROM cut_chart \
WHERE thickness = '{}'".format(thickness))
parent.nozzleMapper.setModel(parent.nozzleModel)
parent.nozzleMapper.addMapping(parent.nozzleLbl, 0, b'text')
parent.nozzleMapper.toLast()
parent.nozzleLast = parent.nozzleMapper.currentIndex()
parent.nozzleMapper.toFirst()
def Edit(self):
edtxt, edok = QInputDialog.getText(
self, 'Редактирование',
'Введите ID препарата, который вы хотите изменить:')
if edok:
text1, ok1 = QInputDialog.getText(self, 'Редактирование',
'Введите название препарата. ')
if ok1:
text2, ok2 = QInputDialog.getText(
self, 'Редактирование', 'Введите описание препарата. ')
if ok2:
text3, ok3 = QInputDialog.getText(
self, 'Редактирование', 'Введите цену препарата. ')
if ok3:
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName("db.db")
db.open()
queryAdd = QSqlQuery()
queryAdd.prepare(
"UPDATE medications SET mName=( ? ), mDescription=( ? ), mPrice=( ? ) WHERE mId=(?) "
)
queryAdd.bindValue(0, text1)
queryAdd.bindValue(1, text2)
queryAdd.bindValue(2, text3)
queryAdd.bindValue(3, edtxt)
queryAdd.exec()
qry = QSqlQuery()
qry = db.exec("SELECT mId AS 'ID',"
"mName AS 'Название',"
"mDescription AS 'Описание',"
"mPrice AS 'Цена' FROM medications")
tabmodel = QSqlQueryModel()
self.ui.tableView_2.setModel(tabmodel)
tabmodel.setQuery(qry)
self.ui.tableView_2.setColumnWidth(2, 310)
header = self.ui.tableView_2.horizontalHeader()
Lheader = self.ui.tableView_2.verticalHeader()
Lheader.setVisible(False)
header.setStretchLastSection(True)
self.ui.tableView_2.show
db.close()
def queryCache(self, SPath, manageLocalStorage):
projectModel = QSqlQueryModel()
if self.db.open():
query = QSqlQuery(self.db)
# print("in queryCache")
# print(SPath)
query.prepare(
"select SPathRec, Title, Artist from cache WHERE SPath=:SPath "
)
query.bindValue(":SPath", SPath)
query.exec_()
projectModel.setQuery(query)
# print("num of row returned queryCache:")
# print(query.numRowsAffected())
# print("query row count")
if projectModel.rowCount() == 0:
print("get recommendation, nothing is in Cache for this song")
recommendedSongsPathList = []
getRecom = GetRecommendation(manageLocalStorage)
relevantSongDict = getRecom.fetchRelevantSongOffline(SPath)
if SPath:
recommendedSongsPathList = getRecom.predict(
SPath, relevantSongDict)
else:
print("problem with SongPath so can't call predict")
# if playing song is also recommended then remove it
if SPath in recommendedSongsPathList:
recommendedSongsPathList.remove(SPath)
# for item in recommendedSongsPathList:
# print(item)
# build cache
if self.writeCache(recommendedSongsPathList, SPath):
print("re Querying ")
projectModel.clear()
requery = QSqlQuery(self.db)
requery.prepare(
"select SPathRec, Title, Artist from cache WHERE SPath=:SPath "
)
requery.bindValue(":SPath", SPath)
requery.exec_()
projectModel.setQuery(requery)
# print("row Count After re query ")
# print(projectModel.rowCount())
else:
print("Query failed")
return projectModel
class sdb:
def __init__(self):
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.model = QSqlQueryModel() # 1
self.connect()
def connect(self):
# print(QSqlDatabase.drivers())
gl._init()
self.db.setHostName(gl.get_value('HOSTNAME'))
self.db.setPort(gl.get_value('PORT'))
self.db.setDatabaseName(gl.get_value('DBNAME'))
self.db.setUserName(gl.get_value('USRNAME'))
self.db.setPassword(gl.get_value('PWD'))
# self.db.setHostName('111.231.82.68')
# self.db.setPort(8000)
# self.db.setDatabaseName('suggestion')
# self.db.setUserName('pentairSuggest')
# self.db.setPassword('Zhao.jiayun_0217')
if not self.db.open():
print(self.db.lastError().text())
def close(self):
self.db.close()
print("db closed")
def exec(self, sql):
self.model.setQuery(sql)
return self.model
def query(self, sql):
query = QSqlQuery(self.db)
query.exec(sql)
return query
def querycheck(self, sql):
query = QSqlQuery(self.db)
return query.exec(sql)
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(800, 600)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget)
self.verticalLayout.setObjectName("verticalLayout")
self.tv = tableView = QtWidgets.QTableView(self.centralwidget)
self.tv.setObjectName("tableView")
self.verticalLayout.addWidget(self.tv)
MainWindow.setCentralWidget(self.centralwidget)
self.model = None
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("test.db")
if db.open():
print("open DB success.")
query = QSqlQuery()
query.prepare("insert into user ('nom','prenom' )values ('Bauer', 'Jack')")
# query.exec_("create table person(id int primary key, name varchar(20), address varchar(30))")
query.exec()
# query.exec_("insert into user values('Jack', 'shanghai')")
# query.exec_("insert into user values('Alex', 'chengdu')")
# query.prepare("SELECT * FROM user")
# query.exec()
self.model = QSqlQueryModel()
self.model.setQuery("SELECT * FROM user LIMIT 10,10")
self.model.setHeaderData(0,QtCore.Qt.Horizontal,"Nom")
self.model.setHeaderData(1,QtCore.Qt.Horizontal,"Prénom")
self.model.setHeaderData(2,QtCore.Qt.Horizontal,"ID")
# self.tv.setHorizontalHeaderLabels(QString("Nom;Prénom;ID").split(";"))
# self.model.setHeaderData(1, Qt.Horizontal, "Prénom")
self.tv.setModel(self.model)
self.model.setQuery("SELECT * FROM user LIMIT 5")
# self.tv.setModel(self.model)
db.close()
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def setTableView(self):
print('*** step2 SetTableView')
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName("67.209.xxx.xxx")
self.db.setDatabaseName("db_sharedbike")
self.db.setUserName("root")
self.db.setPassword("xxxxxxxxx")
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "订单编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "用户编号")
self.queryModel.setHeaderData(2, Qt.Horizontal, "自行车编号")
self.queryModel.setHeaderData(3, Qt.Horizontal, "下单时间")
self.queryModel.setHeaderData(4, Qt.Horizontal, "结账时间")
def thicknessInit(parent):
parent.thicknessMapper = QDataWidgetMapper(parent)
parent.thicknessModel = QSqlQueryModel(parent)
material = parent.materialLbl.text()
parent.thicknessModel.setQuery("SELECT DISTINCT gauge FROM cut_chart \
WHERE material = '{}'".format(material))
parent.thicknessMapper.setModel(parent.thicknessModel)
parent.thicknessMapper.addMapping(parent.thicknessLbl, 0, b'text')
parent.thicknessMapper.toLast()
parent.thicknessLast = parent.thicknessMapper.currentIndex()
parent.thicknessMapper.toFirst()
nozzleInit(parent)
def model(self, prefix=None):
""" recreate the model each call; opening a new window will not
be needed to use the recent completions
"""
if prefix is None:
query_nav = QSqlQuery(
"select host || path from navigation " +
"order by count desc",
self.litedb)
else: # CFG02
query_nav = QSqlQuery(
"select host || path from navigation " +
"where prefix = '{}' ".format(prefix) +
"order by count desc",
self.litedb)
ret_model = QSqlQueryModel()
ret_model.setQuery(query_nav) # AB01
return ret_model
def do_browse(self):
if self.first_run:
return
model = QSqlQueryModel()
filterStr = ""
if not self.first_run and self.browsed_continent_code is not None:
filterStr = f"WHERE countries.continent_code = '{self.browsed_continent_code}'"
if self.browsed_country_code is not None:
filterStr = f"{filterStr} AND runways.country_code = '{self.browsed_country_code}'"
if self.browsed_region_code is not None:
filterStr = f"{filterStr} AND runways.region_code = '{self.browsed_region_code}'"
sql = f'''
SELECT
runways.airport_code as airport_code,
runways.location as location,
runways.airport_name as airport_name,
all_airports.municipality as municipality,
regions.name as region_name,
countries.name as country_name
FROM runways
INNER JOIN all_airports
ON all_airports.code = runways.airport_code
INNER JOIN regions
ON all_airports.region_code = regions.code
INNER JOIN countries
ON countries.code = regions.country_code
INNER JOIN continents
ON continents.code = countries.continent_code
{filterStr}
ORDER BY runways.airport_code, runways.location;
'''
model.setQuery(sql)
self.ui.tableView.setModel(model)
self.set_table_col_sizes()
def updateAllPage(self, *args):
if args == ([], [], []) or args == ():
self.query.exec(
"SELECT FILENAME FROM FileLibrary WHERE SUFFIX != ('{}')".
format(""))
#如果是目录，会被排除掉
model = QSqlQueryModel()
model.setQuery(self.query)
self.allFileTab.setModel(model)
else:
core.FileOperator.unionQuery(self.query, args[0], args[1], args[2])
model = QSqlQueryModel()
model.setQuery(self.query)
self.allFileTab.setModel(model)
def remove_rule(self, rule):
query = QSqlQueryModel()
query.setQuery("delete from rules "\
"where id={}"\
.format(rule.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def btn3f(self):
conn = sqlite3.connect('company.db')
curs = conn.cursor()
curs.execute("select name from sqlite_master where type='table'")
tabs = QTabWidget()
table_name = 'stock_price'
table_model = QSqlQueryModel()
for i, column_data in enumerate(curs.description):
table_model.setHeaderData(i, Qt.Horizontal, column_data[0])
table_model.setQuery(f"select * from {table_name}")
table_view = QTableView()
table_view.setModel(table_model)
tabs.addTab(table_view, table_name)
screen = QWidget()
content = QVBoxLayout()
line1 = QHBoxLayout()
line1.addWidget(tabs)
content.addLayout(line1)
self.k = popup3()
self.k.setGeometry(800, 350, 400, 200)
self.k.setWindowTitle("Stock Prices")
self.k.setLayout(content)
self.k.show()
def _createModel():
oi_get_query = QSqlQuery()
oi_get_query.prepare("""select symbol
,desc
,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2)
,globex_volume
,volume
,open_interest
,change
from oi_reports
where symbol = "EUR"
order by cast(oi_date as int) desc
;
""")
#oi_get_query.addBindValue(symbol)
oi_get_query.exec()
queryModel = QSqlQueryModel()
queryModel.setQuery(oi_get_query)
headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change']
for columnIndex, header in enumerate(headers):
queryModel.setHeaderData(columnIndex, Qt.Horizontal, header)
#queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol')
#queryModel.setHeaderData(1, Qt.Horizontal, 'Description')
#queryModel.setHeaderData(2, Qt.Horizontal, 'Date')
#queryModel.setHeaderData(3, Qt.Horizontal, 'Globex')
#queryModel.setHeaderData(4, Qt.Horizontal, 'Volume')
#queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest')
#queryModel.setHeaderData(6, Qt.Horizontal, 'Change')
return queryModel
class TabTableNonEditable(InvertedTable):
"""an inverted table presenting a QSqlQueryModel
"""
def __init__(self, log, db, tab_nr, query, headers = None, myfilter = ""):
super().__init__(log, db)
self.nr = tab_nr
self.filter = myfilter
self.headers = headers
self.query = query
self.create_model()
self.invert_model()
self.add_headers()
def create_model(self):
"""creates the table model
"""
q = QSqlQuery(self.query + " " + self.filter)
self.model = QSqlQueryModel()
q.exec_(self.query)
self.model.setQuery(q)
def add_headers(self):
"""adds headers
"""
if self.headers:
for i in self.headers:
column = self.headers[i]
self.model.setHeaderData(i, Qt.Horizontal, column, Qt.DisplayRole)
def refresh(self, myfilter = ""):
"""refreshes the displayed data after data changes in the model
"""
if myfilter:
self.filter = myfilter
self.model.setQuery(self.query + " where " + self.filter)
def gameProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name '
'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id '
'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE t_song.id = ' +
str(self.tableView_Game_List.currentIndex().sibling(
self.tableView_Game_List.currentIndex().row(), 3).data()))
model.setHeaderData(0, Qt.Horizontal, "Song Name")
model.setHeaderData(1, Qt.Horizontal, "Song Type")
model.setHeaderData(2, Qt.Horizontal, "Anime Name")
model.setHeaderData(3, Qt.Horizontal, "Anime Serie")
model.setHeaderData(4, Qt.Horizontal, "Artist Name")
self.tableView_Game_Properties.setModel(model)
pass
def loaddatatv(self, text):
try:
connString = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + server + ";DATABASE=" + database + ";UID=" + user + ";PWD=" + pwd
db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName(connString)
db.open()
qry = QSqlQuery(db)
qry.prepare("select * from LDA2 where Nom like '%" + text +
"%' or Prénom like '%" + text +
"%' or NuméroDemandeurEmploi like '%" + text + "%'")
qry.exec()
model = QSqlQueryModel()
model.setQuery(qry)
self.tvDatas.setModel(model)
self.tvDatas.resizeColumnsToContents()
self.tvDatas.horizontalHeader().setStyleSheet(
"QHeaderView::section {background-color:#5B9BD5;color:white}")
self.tvDatas.setShowGrid(True)
self.tvDatas.verticalHeader().setVisible(False)
self.tvDatas.setAlternatingRowColors(True)
self.tvDatas.setStyleSheet(
"alternate-background-color: #DDEBF7; background:white;border: 1px solid black;"
)
self.tvDatas.show()
except pyodbc.Error as err:
msg = QMessageBox()
msg.setWindowTitle("LDA")
msg.setText(err.__str__())
msg.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel)
r = msg.exec_()
if r == QMessageBox.Ok:
msg.close()
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误，错误信息\n" + self.qryModel.lastError().text())
return
self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount())
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.qryModel)
self.mapper.addMapping(self.ui.dbSpinEmpNo, 0)
self.mapper.addMapping(self.ui.dbEditName, 1)
self.mapper.addMapping(self.ui.dbComboSex, 2)
self.mapper.addMapping(self.ui.dbEditBirth, 3)
self.mapper.addMapping(self.ui.dbComboProvince, 4)
self.mapper.addMapping(self.ui.dbComboDep, 5)
self.mapper.addMapping(self.ui.dbSpinSalary, 6)
self.mapper.toFirst()
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
def threadClassInit(parent):
parent.classMapper = QDataWidgetMapper(parent)
parent.classModel = QSqlQueryModel(parent)
form = parent.threadFormLbl.text()
classSelect = "SELECT DISTINCT class FROM internal_threads \
WHERE form = '{}'".format(form)
parent.classModel.setQuery(classSelect)
parent.classMapper.setModel(parent.classModel)
parent.classMapper.addMapping(parent.threadClassLbl, 0, b'text')
parent.classMapper.toLast()
parent.classLast = parent.classMapper.currentIndex()
parent.classMapper.toFirst()
threadSizeInit(parent)
def rtSizeInit(parent):
parent.rtSizeMapper = QDataWidgetMapper(parent)
parent.rtSizeModel = QSqlQueryModel(parent)
form = parent.rtFormLbl.text()
classSelect = "SELECT DISTINCT size FROM tap \
WHERE form = '{}'".format(form)
parent.rtSizeModel.setQuery(classSelect)
parent.rtSizeMapper.setModel(parent.rtSizeModel)
parent.rtSizeMapper.addMapping(parent.rtSizeLbl, 0, b'text')
parent.rtSizeMapper.toLast()
parent.rtSizeLast = parent.rtSizeMapper.currentIndex()
parent.rtSizeMapper.toFirst()
rtInfoInit(parent)
def setTableView(self):
print('*** step2 SetTableView')
self.db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
self.db.setDatabaseName('data/all.db')
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(1, Qt.Horizontal, "电话")
self.queryModel.setHeaderData(2, Qt.Horizontal, "购买记录")
self.queryModel.setHeaderData(3, Qt.Horizontal, "购买时间")
self.queryModel.setHeaderData(4, Qt.Horizontal, "员工工号")
self.queryModel.setHeaderData(5, Qt.Horizontal, "消费商品金额")
self.tableView.setWordWrap(True)
def setMatplotView(self):
print('*** step2 setMatplotView')
# self.db = QSqlDatabase.addDatabase('QSQLITE')
# # 设置数据库名称
# self.db.setDatabaseName('./db/database.db')
# # 打开数据库
# self.db.open()
# 声明查询模型
# 加载所有曲线数据
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.queryModel.setHeaderData(3, Qt.Horizontal, "年龄")
self.queryModel.setHeaderData(4, Qt.Horizontal, "院系")
def setTableView(self):
print('*** step2 SetTableView')
self.db = QSqlDatabase.addDatabase('QMYSQL')
# 设置数据库名称
self.db.setDatabaseName('pyqttest')
self.db.setUserName("root")
self.db.setPassword("123456")
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 设置表格表头
self.queryModel.setHeaderData(0, Qt.Horizontal, "编号")
self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.queryModel.setHeaderData(3, Qt.Horizontal, "年龄")
self.queryModel.setHeaderData(4, Qt.Horizontal, "院系")
def open_db(self):
db_file, filter = \
QFileDialog.getOpenFileName(self, "Open Database", ".",
"All files (*);;SQLite databases (*.db)")
conn = sqlite3.connect(db_file)
curs = conn.cursor()
curs.execute("select name from sqlite_master where type='table'")
tabs = QTabWidget()
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(db_file)
db.open()
for table_data in curs:
table_name = table_data[0]
table_model = QSqlQueryModel()
# for i, column_data in enumerate(curs.description):
# table_model.setHeaderData(i, Qt.Horizontal, column_data[0])
table_model.setQuery(f"select * from {table_name}")
table_view = QTableView()
table_view.setModel(table_model)
tabs.addTab(table_view, table_name)
self.setCentralWidget(tabs)
def update_dsc(self, thing, dsc):
query = QSqlQueryModel()
query.setQuery("UPDATE things "\
"SET dsc = '{}' "\
"WHERE id={}"
.format(dsc, thing.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def __init__(self, cesta_nazvu):
super().__init__()
self.setupUi(self)
self.cesta_nazvu = cesta_nazvu
# # nacte nazev aktualniho projektu ze souboru "nazev.txt"
# with open(cesta_nazvu) as n:
# nazev=n.readlines()
#
# nazev=nazev[0]
# cesta_inv=cesta_nazvu[::-1] #invertuje cestu
# pozice=cesta_inv.find('/') #najde poradi lomitka
# cesta_konecna=cesta_nazvu[0:len(cesta_nazvu)-pozice] #udela cestu adresare bez nazvu souboru
#
# databaze=cesta_konecna+nazev #vytvori cestu+nazev databaze
#otevreni databaze
db1 = QSqlDatabase.addDatabase("QSQLITE", "db1")
# db = QSqlDatabase.addDatabase("")
db1.setDatabaseName(cesta_nazvu)
db1.open()
# vytvori model databaza a nacte data
projectModel1 = QSqlQueryModel()
# projectModel.setQuery('select Stanovisko,Orientace, Delka,Zenitka, Smer, Kod from gps_sour',db)
projectModel1.setQuery(
'select Stanovisko,Orientace,Delka,Zenitka,Smer, Kod from mereni',
db1)
self.tableView.setModel(projectModel1)
# self.tableView.setColumnWidth(1,5)
db1.close()
del projectModel1
del db1
QSqlDatabase.removeDatabase("db1")
self.show()
self.exec()
def get_model(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
self.db.open()
model = QSqlQueryModel()
query_str = """ select name, count(name) as counts from domains group by name order by counts desc
"""
model.setQuery(query_str, db=self.db)
model.setHeaderData(0, Qt.Horizontal, "Word")
model.setHeaderData(1, Qt.Horizontal, "Count")
return model
def setModel(self, model: QtSql.QSqlQueryModel):
if len(self._list) == 0:
raise Exception("显示列集合为空，请先使用append()方法添加列信息！")
if not isinstance(model, QtSql.QSqlQueryModel):
raise Exception("model参数必须为QSqlQueryModel或其子类的实例！")
self.__model = model
record = model.record()
for vc in self._list:
at = vc.fieldIndex
if at != -1:
field = record.field(at)
vc.jpFieldType = getFieldType(self.db, field.typeID())
if vc.formatString is None:
self._setViewColumnAlighAndFormatString(vc)
def __init__(self, parent=None):
super().__init__(parent)
self.label = QtWidgets.QLabel()
self.button_left = QtWidgets.QPushButton('<')
self.button_right = QtWidgets.QPushButton('>')
blayout = QtWidgets.QHBoxLayout()
blayout.addWidget(self.button_left)
blayout.addWidget(self.button_right)
layout = QtWidgets.QVBoxLayout(self)
layout.addWidget(self.label)
layout.addLayout(blayout)
self.mapper = QtWidgets.QDataWidgetMapper(self)
self.db = create_db()
self.model = QSqlQueryModel(self)
self.model.setQuery('SELECT * FROM test')
self.mapper.setModel(self.model)
self.mapper.addMapping(self.label, 0, b'text')
self.mapper.toFirst()
self.button_left.clicked.connect(self.mapper.toPrevious)
self.button_right.clicked.connect(self.mapper.toNext)
def sptmSizeInit(parent):
parent.sptmMapper = QDataWidgetMapper(parent)
parent.sptmModel = QSqlQueryModel(parent)
parent.sptmModel.setQuery('SELECT * FROM sptm')
parent.sptmMapper.setModel(parent.sptmModel)
parent.sptmMapper.addMapping(parent.sptmSizeLbl, 0, b'text')
parent.sptmMapper.addMapping(parent.sptmDiaLbl, 1, b'text')
parent.sptmMapper.addMapping(parent.sptmCrestLbl, 2, b'text')
parent.sptmMapper.addMapping(parent.sptmMaxDepthLbl, 3, b'text')
parent.sptmMapper.addMapping(parent.sptmFlutesLbl, 4, b'text')
parent.sptmMapper.addMapping(parent.sptmNeckDiaLbl, 5, b'text')
parent.sptmMapper.toLast()
parent.sptmLast = parent.sptmMapper.currentIndex()
parent.sptmMapper.toFirst()
def Nexpired2(self):
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName("db.db")
db.open()
qry = QSqlQuery()
now = datetime.datetime.now()
Date = now.strftime("%Y-%m-%d")
DateNow = str(Date)
qry.prepare(
"SELECT bDateEx AS 'Срок годности', bId AS 'ID партии', mName AS 'Название' FROM batch, medications WHERE bDateEx > ( ? ) and bDateS is null and b_mId= mId ORDER BY bDateEx ASC"
)
qry.bindValue(0, DateNow)
qry.exec()
tabmodel = QSqlQueryModel()
self.ui.tableView.setModel(tabmodel)
tabmodel.setQuery(qry)
#self.ui.tableView.setColumnWidth( 2, 310 )
header = self.ui.tableView.horizontalHeader()
Lheader = self.ui.tableView.verticalHeader()
Lheader.setVisible(False)
header.setStretchLastSection(True)
self.ui.tableView.show
db.close()
def thing_by_id(self, idx):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where id={}"\
.format(idx),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def thing_by_name(self, name):
query = QSqlQueryModel()
query.setQuery("select * from things "\
"where name='{}'"\
.format(name),
db=self.db)
if query.rowCount() == 0:
return None
return self.thing_by_sqlrecord(query.record(0))
def populateFilmCombo(self, filmNumber=None):
editor = self.uiFilmNumberCombo
model = QSqlQueryModel(self)
model.setQuery(
"SELECT DISTINCT {0} FROM {1} ORDER BY {2}".format(
'filmnummer', 'film', 'filmnummer'), self.dbm.db)
tv = QTableView()
editor.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
editor.setModel(model)
editor.setModelColumn(0)
editor.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
# tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
#FIXME PyQt5 AutoCompletion
#editor.setAutoCompletion(True)
if filmNumber:
editor.setCurrentIndex(editor.findText(filmNumber))
self.populateAvailableImagesCombo()
else:
editor.setCurrentIndex(-1)
editor.currentIndexChanged.connect(self.populateAvailableImagesCombo)
def setData(self, index, value, role=Qt.EditRole):
if role == Qt.EditRole:
mycolumn = index.column()
if mycolumn in self.editables:
(query, filter_cols) = self.editables[mycolumn]
values = [value]
for col in filter_cols:
filter_value = self.index(index.row(), col).data()
values.append(filter_value)
q = QSqlQuery(query.format(*values))
result = q.exec_()
if result:
self.query().exec_()
else:
print(self.query().lastError().text())
return result
return QSqlQueryModel.setData(self, index, value, role)
def __init__(self):
super(QMainWindow, self).__init__()
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.txtNumber.setValidator(QIntValidator())
self.ui.txtName.textChanged.connect(self.onFilterChanged)
self.ui.txtNumber.textChanged.connect(self.onFilterChanged)
self.ui.btnNewMarble.clicked.connect(self.onNewMarble)
self.ui.btnDeleteMarble.clicked.connect(self.onDeleteMarble)
self.ui.btnSell.clicked.connect(self.onSell)
self.ui.lstMarble.doubleClicked.connect(self.onMarbleDoubleClicked)
self.ui.btnViewSales.clicked.connect(self.showSales)
self.queryModel = QSqlQueryModel()
self.ui.lstMarble.setModel(self.queryModel)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('inventory.db')
self.db.open()
self.execSql('''PRAGMA foreign_keys = ON;''')
self.execSql('''CREATE TABLE IF NOT EXISTS `slabs` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`num` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`dimL` INTEGER NOT NULL,
`dimW` INTEGER NOT NULL,
`dimH` INTEGER NOT NULL,
`notes` TEXT NOT NULL
);''')
self.execSql('''CREATE TABLE IF NOT EXISTS `sales` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`slabid` INTEGER NOT NULL,
`date` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`leftover` INTEGER NOT NULL,
`buyer` TEXT NOT NULL,
`notes` TEXT NOT NULL,
FOREIGN KEY(slabid) REFERENCES slabs(id)
);''')
self.onFilterChanged()
self.ui.lstMarble.setColumnHidden(0, True)
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def reloadGameCompetitors(self, id_attraction):
model_Game_Competitors = QSqlQueryModel()
model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor ON '
't_point.id_competitor = t_competitor.id '
'WHERE t_point.id_competitor_take = 1 '
'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL '
'SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor '
'ON t_point.id_competitor_take = t_competitor.id '
'WHERE t_point.id_competitor_take != 1 '
'AND t_point.id_attraction = ' + id_attraction + ') '
'GROUP BY name ORDER BY point DESC')
model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points")
model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Game_Competitors.setModel(model_Game_Competitors)
pass
def executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(model.query().numRowsAffected())
)
self.table.resizeColumnsToContents()
self.updateActions()
def flags(self, index):
flags = QSqlQueryModel.flags(self, index)
if index.column() == 2:
flags |= Qt.ItemIsEditable
return flags
def songView(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name '
'FROM t_song '
'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id')
model.setHeaderData(0, Qt.Horizontal, "Song ID")
model.setHeaderData(1, Qt.Horizontal, "Song Name")
model.setHeaderData(2, Qt.Horizontal, "Song File")
model.setHeaderData(3, Qt.Horizontal, "Song Type")
model.setHeaderData(4, Qt.Horizontal, "Anime Name")
model.setHeaderData(5, Qt.Horizontal, "Anime Serie")
self.tableView_Song_List.setModel(model)
pass
class OrderController(QObject):
def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow):
super().__init__()
self.form = form
self.orderTable = orderTable
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.mainwindow = mainwindow
form.currentRecordChanged.connect(self.recordChanged)
form.recordInserted.connect(lambda: self.recordChanged(None))
form.recordDeleted.connect(lambda: self.recordChanged(None))
if form.only_select:
self.addButton.setEnabled(False)
self.editButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def _hiddingHack(self, val):
ui = self.mainwindow.ui
if val is True:
ui.client_hack.setCurrentWidget(ui.client_hide_page)
ui.shop_hack.setCurrentWidget(ui.shop_hide_page)
ui.emp_hack.setCurrentWidget(ui.emp_hide_page)
else:
ui.client_hack.setCurrentWidget(ui.client_ok_page)
ui.shop_hack.setCurrentWidget(ui.shop_ok_page)
ui.emp_hack.setCurrentWidget(ui.emp_ok_page)
def tableSelectionChanged(self, cur, prev):
if self.form.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def selectRow(self, row):
self.form.selectRow(row)
def update(self):
self.form.update()
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class MainWindow(QMainWindow):
def __init__(self):
super(QMainWindow, self).__init__()
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.txtNumber.setValidator(QIntValidator())
self.ui.txtName.textChanged.connect(self.onFilterChanged)
self.ui.txtNumber.textChanged.connect(self.onFilterChanged)
self.ui.btnNewMarble.clicked.connect(self.onNewMarble)
self.ui.btnDeleteMarble.clicked.connect(self.onDeleteMarble)
self.ui.btnSell.clicked.connect(self.onSell)
self.ui.lstMarble.doubleClicked.connect(self.onMarbleDoubleClicked)
self.ui.btnViewSales.clicked.connect(self.showSales)
self.queryModel = QSqlQueryModel()
self.ui.lstMarble.setModel(self.queryModel)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('inventory.db')
self.db.open()
self.execSql('''PRAGMA foreign_keys = ON;''')
self.execSql('''CREATE TABLE IF NOT EXISTS `slabs` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`num` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`dimL` INTEGER NOT NULL,
`dimW` INTEGER NOT NULL,
`dimH` INTEGER NOT NULL,
`notes` TEXT NOT NULL
);''')
self.execSql('''CREATE TABLE IF NOT EXISTS `sales` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`slabid` INTEGER NOT NULL,
`date` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`leftover` INTEGER NOT NULL,
`buyer` TEXT NOT NULL,
`notes` TEXT NOT NULL,
FOREIGN KEY(slabid) REFERENCES slabs(id)
);''')
self.onFilterChanged()
self.ui.lstMarble.setColumnHidden(0, True)
def onFilterChanged(self):
conditions = []
if self.ui.txtNumber.text() != '':
conditions.append('`num` = %d' % (int(self.ui.txtNumber.text()), ))
if self.ui.txtName.text() != '':
conditions.append("`name` LIKE '%%%s%%'" % (self.ui.txtName.text(), ))
conditionsStr = ''
if len(conditions) != 0:
conditionsStr = ' WHERE ' + ' AND '.join(conditions)
query = "SELECT id, name, num, amount, (dimH||'x'||dimL||'x'||dimW) as dimensions, notes FROM `slabs`" + conditionsStr + ' ORDER BY `name`, `num` ASC;'
print(query)
self.queryModel.setQuery(query, self.db)
def onNewMarble(self):
editMarble = EditMarble()
while True:
result = editMarble.exec()
if result == 0:
return
try:
name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues()
break
except Exception as e:
QMessageBox.critical(self, "Error", "Invalid values entered: " + str(e))
query = '''SELECT `id`, `amount`, `notes` FROM `slabs` WHERE `name`='%s' AND `num`=%d AND `dimL`=%d AND `dimW`=%d AND `dimH`=%d''' % (sqlEscape(name), number, dimL, dimW, dimH)
result = self.execSql(query)
if result == None:
return #TOOD: error dialog? or maybe we should just try to insert?
if result.next() and result.value(2) == notes:
slabId = result.value(0)
previousAmount = result.value(1)
result.finish()
del result
result = None
query = '''UPDATE `slabs` SET `amount`=%d WHERE `id`=%d''' % (previousAmount + amount, slabId)
self.execSql(query)
return
del result
query = '''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`)
VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes))
self.execSql(query)
def onDeleteMarble(self):
if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected
QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first")
return
rowIndex = self.ui.lstMarble.selectedIndexes()[0].row()
primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
query = '''DELETE FROM `slabs` WHERE `id` == %d''' % (primaryKey, )
self.execSql(query)
def getSlabData(self, primaryKey):
query = '''SELECT `name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes` FROM `slabs` WHERE id=%d''' % (primaryKey, )
sqlQuery = QSqlQuery(query, self.db);
if not sqlQuery.next(): # user supplied an invalid primary key
QMessageBox.critical(self, "Error", 'slab with id %d was not found' % (primaryKey,))
raise Exception('slab with id %d was not found' % (primaryKey,))
name = sqlQuery.value(0)
number = sqlQuery.value(1)
amount = sqlQuery.value(2)
dimL = sqlQuery.value(3)
dimW = sqlQuery.value(4)
dimH = sqlQuery.value(5)
notes = sqlQuery.value(6)
return name, number, amount, dimL, dimW, dimH, notes
def onMarbleDoubleClicked(self, modelIndex):
rowIndex = modelIndex.row()
primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(primaryKey)
editMarble = EditMarble()
editMarble.setValues(name, number, amount, dimL, dimW, dimH, notes)
result = editMarble.exec()
if result == 0:
return
name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues()
self.execSql('''UPDATE `slabs` SET `name`='%s', `num`=%d, `amount`=%d, `dimL`=%d, `dimW`=%d, `dimH`=%d, `notes`='%s'
WHERE id = %d;''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes), primaryKey))
def execSql(self, query):
print(query)
sqlquery = QSqlQuery(query, self.db)
#self.db.exec(query)
if sqlquery.lastError().number() != -1: #TODO: print these in a dialog box
QMessageBox.critical(self, "Error", 'ERROR OCCURRED WHILE EXECUTING STATEMENT: ' + query + "\n" + \
'Database Text:' + sqlquery.lastError().databaseText() + \
'Databse Driver:' + sqlquery.lastError().driverText())
return None
self.onFilterChanged()
return sqlquery
def onSell(self):
if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected
QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first")
return
rowIndex = self.ui.lstMarble.selectedIndexes()[0].row()
slabPrimaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(slabPrimaryKey)
sell = SellDialog(name, number, amount)
result = sell.exec()
if result == 0:
return
amountSold, amountOfLeftovers, buyer, datetime, notes = sell.getValues()
leftoverQueries = []
while amountOfLeftovers > 0:
newMarble = EditMarble()
newMarble.setValues(name, number, amountSold, dimL, dimW, dimH, notes)
newMarble.setMaxAmount(amountOfLeftovers)
result = newMarble.exec()
if result == 0:
return
newName, newNumber, newAmount, newDimL, newDimW, newDimH, newNotes = newMarble.getValues()
leftoverQueries.append('''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`)
VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(newName), newNumber, newAmount, newDimL, newDimW, newDimH, sqlEscape(newNotes)))
amountOfLeftovers = amountOfLeftovers - newAmount
queryUpdate = '''UPDATE `slabs` SET `amount`=`amount`-%d WHERE id = %d;''' % (amountSold, slabPrimaryKey)
querySale = '''INSERT INTO `sales` (`slabid`, `date`, `amount`, `leftover`, `buyer`, `notes`)
VALUES(%d, %d, %d, 0, '%s', '%s');''' % (slabPrimaryKey, datetime, amountSold, buyer, notes)
self.execSql('BEGIN TRANSACTION;')
self.execSql(queryUpdate)
self.execSql(querySale)
for leftoverQuery in leftoverQueries:
self.execSql(leftoverQuery)
self.execSql('COMMIT;')
self.onFilterChanged()
def showSales(self):
sales = SalesForm(self.db)
sales.exec()
class ProjectAlleles(FilterableTable):
"""a widget to display all alleles of one project,
with their most important data
"""
changed_allele = pyqtSignal(str, int, str)
change_view = pyqtSignal(int)
def __init__(self, log, mydb):
super().__init__(log, mydb, add_color_proxy = (4,5))
self.proxy.setFilterKeyColumn(2)
self.filter_cb.setCurrentIndex(2)
self.header_lbl.setText("Alleles:")
self.table.verticalHeader().hide()
self.table.customContextMenuRequested.connect(self.open_menu)
self.project = ""
self.filter(self.project)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def filter(self, project):
self.project = project
self.proxy.layoutAboutToBeChanged.emit()
self.proxy.setFilterKeyColumn(0)
self.proxy.setFilterFixedString(project)
self.proxy.layoutChanged.emit()
for col in [0, 6, 7]:
self.table.hideColumn(col)
@pyqtSlot()
def refresh(self):
"""refreshes the table's content
"""
self.log.debug("\tRefreshing ProjectView's allele list...")
self.model.setQuery(self.model.query().lastQuery())
@pyqtSlot(QPoint)
def open_menu(self, pos):
"""provides a context menu
"""
try:
menu = QMenu()
open_allele_act = menu.addAction("Open Allele View")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
sample = self.proxy.data(self.proxy.index(row, 6))
allele_nr = int(self.proxy.data(self.proxy.index(row, 7)))
if action == open_allele_act:
self.changed_allele.emit(sample, allele_nr, self.project)
self.change_view.emit(4)
self.log.debug("ProjectAlleles emitted changed_allele to {} #{} ({}) & change_view to AlleleView".format(sample, allele_nr, self.project))
except Exception as E:
self.log.exception(E)
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
# 创建数据库
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) )
print('query sql=' + szQuery )
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage )
self.currentPageLabel.setText( szCurrentText )
#设置按钮是否可用
if self.currentPage == 1 :
self.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex);
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus();
model.setHeaderData(0, Qt.Horizontal, "Серія")
model.setHeaderData(1, Qt.Horizontal, "Проміжна")
model.setHeaderData(2, Qt.Horizontal, "Готова")
def createView(title, model):
view = QTableView()
view.setModel(model)
view.setWindowTitle(title)
return view
if __name__ == '__main__':
import sys
app = QApplication(sys.argv)
if not createConnection():
sys.exit(1)
# model = QSqlTableModel()
model = QSqlQueryModel()
model.setQuery("SELECT Id, PrepData, ReadyData FROM PData")
initializeModel(model)
view1 = createView("Table Model (View 1)", model)
view1.show()
sys.exit(app.exec_())
