class Db(QObject): #QObject а не object для pyqtSignal
""" основная работа с базой SQLITE """
databaseOpened = pyqtSignal(str) #сигналы открытия, закрытия, модификации базы (полный путь текущей базы)
databaseClosed = pyqtSignal(str)
databaseUpdated = pyqtSignal(str)
def __init__(self):
super(Db, self).__init__()
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.IsNtr=False;
self.last_error=''
self.lottery_config=self.LotteryConfig()
self.path = ''
self.isClosed = True
self.configmodel = QSqlTableModel()
self.historymodel =QSqlTableModel()
def __initialized_history_model(self):
self.historymodel.setTable("history")
self.historymodel.select()
self.historymodel.setHeaderData(DRAWNUMBER_COLUMN_INDEX, Qt.Horizontal, "№");
self.historymodel.setHeaderData(UNIXTIME_COLUMN_INDEX, Qt.Horizontal, "Дата");
if self.lottery_config.IsFonbet:
self.historymodel.setHeaderData(FONBETID_COLUMN_INDEX, Qt.Horizontal, "Id");
def __initialized_config_model(self):
self.configmodel.setTable("config")
self.configmodel.select()
def update_history_view(self):
""" перечитаем историю , может быть после обновления
"""
self.__get_limit_values()
self.historymodel.select()
def open(self, path):
self.path = path
filename, file_extension = os.path.splitext(path)
if file_extension.lower()=='.ntr':
""" открываем как ntr. Нужно создать виртуальные history и config """
self.db.setDatabaseName(":memory:")
if not self.db.open():
raise Exception('Ошибка создания базы в памяти')
NtrDb(self.db, path);
self.IsNtr=True;
else:
""" открываем как sqlite """
self.db.setDatabaseName(path)
if not self.db.open():
raise Exception('Ошибка создания открытия базы')
self.IsNtr=False;
self.__load_config()
self.__get_limit_values()
self.__initialized_config_model()
self.__initialized_history_model()
self.select_balls_sql = '' # внутри self.__prepare_history_insert_sql()
self.insert_history_sql = '' # внутри self.__prepare_history_insert_sql()
self.__prepare_history_insert_sql() # подготовим некоторые строки запроса на будущее в зависимости от числа шаров
self.insert_prizes_sql = '' # внутри self.__prepare_prizes_insert_sql()
self.__prepare_prizes_insert_sql() # подготовим строку запроса на будущее
# эмитируем сигнал открытия
self.databaseOpened.emit(self.path)
self.isClosed = False
def close(self):
self.isClosed = True
self.db.close()
self.configmodel.clear()
self.historymodel.clear()
self.lottery_config=self.LotteryConfig() #aka clear()
# Emit the signal.
self.databaseClosed.emit(self.path)
def __prepare_history_insert_sql(self):
""" Подготовим строку для вставки нового тиража
"""
try:
str_list_insert = ['INSERT INTO [History] ([DrawNumber],[Timestamp]'] # запрос на вставку
str_select_balls=[] # часть запроса номеров P1,P2....S2
for i in range(self.lottery_config.NumberOfBalls1):
str_list_insert.append(',[P'+str(i+1)+']')
str_select_balls.append('P'+str(i+1))
for i in range(self.lottery_config.NumberOfBalls2):
str_list_insert.append(',[S'+str(i+1)+']')
str_select_balls.append('S'+str(i+1))
if self.lottery_config.IsFonbet:
str_list_insert.append(',[FonbetId]')
str_list_insert.append(') VALUES (:DrawNumber,:Timestamp')
for i in range(self.lottery_config.NumberOfBalls1):
str_list_insert.append(',:P'+str(i+1)+'')
for i in range(self.lottery_config.NumberOfBalls2):
str_list_insert.append(',:S'+str(i+1)+'')
if self.lottery_config.IsFonbet:
str_list_insert.append(',:FonbetId')
str_list_insert.append(')')
self.select_balls_sql = ','.join(str_select_balls)
self.insert_history_sql = ''.join(str_list_insert)
except Exception as e:
print('__prepare_history_insert_sql error: ', e)
dbg_except()
pass #end __prepare_history_insert_sql
def __prepare_prizes_insert_sql(self):
""" Подготовим строку для вставки призов нового тиража
"""
try:
str_list_insert = ['INSERT INTO [Prizes] ([DrawId]']
for i in range(len(self.lottery_config.WinCategoriesPrizesArray)):
str_list_insert.append(f',[{self.lottery_config.WinCategoriesPrizesArray[i]}]')
str_list_insert.append(') VALUES (:DrawId')
for i in range(len(self.lottery_config.WinCategoriesPrizesArray)):
str_list_insert.append(f',:{self.lottery_config.WinCategoriesPrizesArray[i]}')
str_list_insert.append(')')
self.insert_prizes_sql = ''.join(str_list_insert)
except Exception as e:
print('__prepare_prizes_insert_sql error: ', e)
dbg_except()
pass #end __prepare_prizes_insert_sql
def __get_limit_values(self):
"""определяем первые и конечные тиражи"""
try:
query = QSqlQuery("SELECT * FROM history ORDER BY DrawNumber DESC LIMIT 1")
while query.next():
self.lottery_config.LastDrawNumber=query.value(DRAWNUMBER_COLUMN_INDEX)
self.lottery_config.LastDrawDate=datetime.datetime.fromtimestamp(query.value(UNIXTIME_COLUMN_INDEX))
if self.lottery_config.IsFonbet:
self.lottery_config.LastFonbetId=query.value(FONBETID_COLUMN_INDEX)
query = QSqlQuery("SELECT * FROM history ORDER BY DrawNumber ASC LIMIT 1")
while query.next():
self.lottery_config.FirstDrawNumber=query.value(DRAWNUMBER_COLUMN_INDEX)
self.lottery_config.FirstDrawDate=datetime.datetime.fromtimestamp(query.value(UNIXTIME_COLUMN_INDEX))
except Exception as e:
print('Db:getLimitValues error: ', e)
dbg_except()
pass #end getLimitValues
def __load_config(self):
"""Загружаем настройки лотереи из таблицы config"""
query = QSqlQuery("SELECT * FROM config")
fieldKey = query.record().indexOf("key")
fieldValue = query.record().indexOf("value")
while query.next():
key = query.value(fieldKey)
value = query.value(fieldValue)
self.lottery_config._parse_config_record(key,value)
def get_draws_iter(self,fromDraw,toDraw):
"""Выбираем тиражи между fromDraw и toDraw включительно итерабельно"""
query = QSqlQuery(f"SELECT * FROM history WHERE DrawNumber>={fromDraw} AND DrawNumber<={toDraw}")
rec = query.record()
fields = [rec.fieldName(i) for i in range(rec.count())]
rowtype = collections.namedtuple('DrawResult', fields)
while query.next():
rec = query.record()
yield rowtype(*[rec.value(i) for i in range(rec.count())])
def get_draws_balls_iter(self,fromDraw,toDraw):
"""Выбираем только шары в тиражах между fromDraw и toDraw включительно итерабельно"""
query = QSqlQuery(f"SELECT * FROM history WHERE DrawNumber>={fromDraw} AND DrawNumber<={toDraw}")
rec = query.record()
fields = [rec.fieldName(i) for i in range(UNIXTIME_COLUMN_INDEX+1, rec.count())]
rowtype = collections.namedtuple('DrawResult', fields)
while query.next():
rec = query.record()
yield [rec.value(i) for i in range(UNIXTIME_COLUMN_INDEX+1, rec.count())]
def get_draws_balls_numpy(self,fromDraw,toDraw):
"""Выбираем только шары в тиражах между fromDraw и toDraw включительно и возвращаем в виде двумерного массива Numpy"""
#типа self.select_balls_sql='P 1, P2 .... S2'
query = QSqlQuery(f"SELECT {self.select_balls_sql} FROM history WHERE DrawNumber>={fromDraw} AND DrawNumber<={toDraw}")
rec = query.record()
data = np.empty((0,rec.count()), dtype=int)
while query.next():
rec = query.record()
arr=[rec.value(i) for i in range(rec.count())]
data=np.append(data,[arr], axis=0)
return data
#data = np.empty((0,rec.count()-UNIXTIME_COLUMN_INDEX-1), dtype=int)
#while query.next():
# rec = query.record()
# arr=[rec.value(i) for i in range(UNIXTIME_COLUMN_INDEX+1, rec.count())]
# data=np.append(data,[arr], axis=0)
#return data
pass # end get_draws_balls_numpy
def get_last_fonbet_id(self):
""" Для фонбетовской базы получаем fonbet_id для последнего тиража из базы"""
""" Но также при загрузке базы получаю в __get_limit_values """
try:
query = QSqlQuery(f"SELECT FonbetId FROM history ORDER BY DrawNumber DESC LIMIT 1")
while query.next():
return query.record().value(0)
return None
except Exception as e:
print('database error get_last_fonbet_id: ', e)
dbg_except()
return None
pass # end get_last_fonbet_id
def add_draw(self, draw):
""" Записываем draw:DrawResult в базу
"""
try:
self.last_error=''
#History
query = QSqlQuery()
query.prepare(self.insert_history_sql)
query.bindValue(":DrawNumber", draw.draw_number)
query.bindValue(":Timestamp", draw.draw_date.timestamp())
#а теперь шары P
for i in range(self.lottery_config.NumberOfBalls1):
query.bindValue(":P"+str(i+1), draw.balls1[i])
pass
#а теперь шары S
for i in range(self.lottery_config.NumberOfBalls2):
query.bindValue(":S"+str(i+1), draw.balls2[i])
pass
#а теперь FonbetId
if self.lottery_config.IsFonbet:
query.bindValue(":FonbetId", draw.fonbet_id)
pass
result=query.exec_()
if not result:
return False
#Prizes а теперь выигрыши если есть
if draw.wins:
last_insert_id =query.lastInsertId();
query = QSqlQuery()
query.prepare(self.insert_prizes_sql)
query.bindValue(":DrawId", last_insert_id)
for i in range(len(self.lottery_config.WinCategoriesPrizesArray)):
newkey=self.lottery_config.WinCategoriesPrizesArray[i].replace('WP',"").replace('S','+') #удалим WP и заменим S на +
query.bindValue(f':{self.lottery_config.WinCategoriesPrizesArray[i]}', draw.wins.get(newkey,0.0))
result=query.exec_()
pass
# эмитируем сигнал
self.databaseUpdated.emit(self.path)
return result
except Exception as e:
self.last_error=printf('update_draws error: ', e)
dbg_except()
return False
pass # add_draw
class LotteryConfig(object):
""" класс конфигурации лотереи из таблицы config """
def __init__(self):
""" это хранится в конфиге"""
self.LottoName = ""
self.NumberOfBalls1=0
self.StartOfBalls1=0
self.EndOfBalls1=0
self.NumberOfBalls2=0
self.StartOfBalls2=0
self.EndOfBalls2=0
self.DataImportPlugin='' # имя файла отвечающего за обновление без расширения .py
self.WinCategories=''
self.DefaultWinCost=''
self.GameType = "" # Toto для Фонбета
self.IsTop3=False;
self.IsFonbet=False;
self.MultipleAppearance1=False;
self.MultipleAppearance2=False;
self.WithWins=False;
""" это вспомогательное - вычисляется"""
self.LastDrawNumber=0
self.LastDrawDate=datetime.date.min
self.FirstDrawNumber=0
self.FirstDrawDate=datetime.date.min
self.WinCategoriesPrizesArray=[]
self.LastFonbetId=0 # Fonbet only
pass #end __init__
def _parse_config_record(self, key,value):
""" парсим параметры конфигурации """
if key.lower() == 'LottoName'.lower(): self.LottoName=str(value)
elif key.lower() == 'NumberOfBalls1'.lower(): self.NumberOfBalls1=int(value)
elif key.lower() == 'NumberOfBalls2'.lower(): self.NumberOfBalls2=int(value)
elif key.lower() == 'StartOfBalls1'.lower(): self.StartOfBalls1=int(value)
elif key.lower() == 'StartOfBalls2'.lower(): self.StartOfBalls2=int(value)
elif key.lower() == 'EndOfBalls1'.lower(): self.EndOfBalls1=int(value)
elif key.lower() == 'EndOfBalls2'.lower(): self.EndOfBalls2=int(value)
elif key.lower() == 'DataImportPlugin'.lower(): self.DataImportPlugin=str(value)
elif key.lower() == 'WinCategories'.lower():
self.WinCategories=str(value)
self._prepare_prizes_array()
elif key.lower() == 'DefaultWinCost'.lower(): self.DefaultWinCost=str(value)
elif key.lower() == 'GameType'.lower(): self.GameType=str(value)
elif key.lower() == 'IsFonbet'.lower(): self.IsFonbet=bool(distutils.util.strtobool(value))
elif key.lower() == 'IsTop3'.lower(): self.IsTop3=bool(distutils.util.strtobool(value))
elif key.lower() == 'WithWins'.lower(): self.WithWins=bool(distutils.util.strtobool(value))
elif key.lower() == 'MultipleAppearance1'.lower(): self.MultipleAppearance1=bool(distutils.util.strtobool(value))
elif key.lower() == 'MultipleAppearance2'.lower(): self.MultipleAppearance2=bool(distutils.util.strtobool(value))
def _prepare_prizes_array(self):
"""из 4+1,5,...,8,8+1 сделать список['WP4S1,WP5,...WP8,WP8S1'] """
arr=self.WinCategories.split(',')
self.WinCategoriesPrizesArray.clear()
for line in arr:
line='WP'+line.replace('+','S')
self.WinCategoriesPrizesArray.append(line)
pass
class SqliteDbTableEditer(QWidget):
#Db=sqlite3.connect("test.db")
def __init__(self,dbPath,tblName='',parent=None):
self.app=QApplication(sys.argv)
self.SqliteDbTypes=['integer','real','text','blob']
self.DbPath,self.CurrentTable=dbPath,tblName
#连接数据库
self.Db=sqlite3.connect(self.DbPath)
#构建Gui组件
super(SqliteDbTableEditer,self).__init__(parent)
self.setWindowTitle('Sqlite数据库表修改器')
screen=QDesktopWidget().availableGeometry(0)
self.setGeometry(screen.width()/3/2-1,
screen.height()/5/2-1,
screen.width()*2/3,
screen.height()*4/5
)
#lay
lay=QVBoxLayout()
self.setLayout(lay)
#数据库表设置控件
##layDb
layDb=QHBoxLayout()
lay.addLayout(layDb)
###lblDb
lblDb=QLabel('数据库：')
layDb.addWidget(lblDb)
###self.leDb
self.leDb=QLineEdit()
self.leDb.setText(self.DbPath)
layDb.addWidget(self.leDb)
###btnDb
btnChangeDb=QPushButton('浏览')
btnChangeDb.clicked.connect(self.btnChangeDb_Clicked)
layDb.addWidget(btnChangeDb)
###lblTbl
lblTbl=QLabel('数据表：')
layDb.addWidget(lblTbl)
###self.cbbTbls
self.cbbTbls=QComboBox()
tbls=list(map(lambda x:x[1],
list(filter(lambda x:x[0]=='table',
self.Db.execute(
'Select * From sqlite_master'
).fetchall()
)
)
)
)
self.cbbTbls.addItems(tbls)
if self.CurrentTable!='' :
self.cbbTbls.setCurrentIndex(tbls.index(self.CurrentTable))
else:
self.CurrentTable=tbls[0]
self.makeTableInfo()
self.cbbTbls.setCurrentIndex(0)
layDb.addWidget(self.cbbTbls)
###lblRename
lblRename=QLabel('重命名为：')
layDb.addWidget(lblRename)
###self.leRename
self.leRename=QLineEdit()
self.leRename.setFixedWidth(100)
layDb.addWidget(self.leRename)
###btnRename
btnRenameTable=QPushButton('重命名')
btnRenameTable.clicked.connect(self.btnRenameTable_Clicked)
layDb.addWidget(btnRenameTable)
###btnDeleteTable
btnDeleteTable=QPushButton('删除表')
btnDeleteTable.clicked.connect(self.btnDeleteTable_Clicked)
layDb.addWidget(btnDeleteTable)
###btnShow
self.btnShow=QPushButton('查看表结构')
self.btnShow.clicked.connect(self.btnShow_Clicked)
layDb.addWidget(self.btnShow)
###设置TableView控件self.tv，以呈现表数据
self.tv=QTableView()
lay.addWidget(self.tv)
###self.model基本初始化
self.model=QSqlTableModel(self,QSqlDatabase.addDatabase('QSQLITE'))
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
###self.tv链接到数据源
self.tv.setModel(self.model)
###self.model数据初始化
self.model.database().setDatabaseName(self.DbPath)
self.model.database().open()
self.model.setTable(self.CurrentTable)
self.model.select()
self.cbbTbls.currentIndexChanged.connect(self.changeTable)
##layBtns
layBtns=QHBoxLayout()
lay.addLayout(layBtns)
###btnAddColumn
btnAddColumn=QPushButton('添加列')
btnAddColumn.setToolTip('给当前表添加列')
btnAddColumn.clicked.connect(self.btnAddColumn_Clicked)
layBtns.addWidget(btnAddColumn)
###btnDeleteColumn
btnDeleteColumn=QPushButton('删除列')
btnDeleteColumn.setToolTip('删除当前表的列')
btnDeleteColumn.clicked.connect(self.btnDeleteColumn_Clicked)
layBtns.addWidget(btnDeleteColumn)
###btnRenameColumn
btnRenameColumn=QPushButton('重命名列')
btnRenameColumn.setToolTip('重命名当前表的列')
btnRenameColumn.clicked.connect(self.btnRenameColumn_Clicked)
layBtns.addWidget(btnRenameColumn)
###btnModifyColumnType
btnModifyColumnType=QPushButton('修改列数据类型')
btnModifyColumnType.setToolTip('修改当前表的列的数据类型')
btnModifyColumnType.clicked.connect(self.btnModifyColumnType_Clicked)
layBtns.addWidget(btnModifyColumnType)
###btnModifyColumnConstraint
btnModifyColumnConstraint=QPushButton('修改列约束')
btnModifyColumnConstraint.setToolTip('修改当前表的列的约束')
btnModifyColumnConstraint.clicked.connect(
self.btnModifyColumnConstraint_Clicked)
layBtns.addWidget(btnModifyColumnConstraint)
###btnOrderColumns
btnOrderColumns=QPushButton('调整列顺序')
btnOrderColumns.setToolTip('调整当前表的列的顺序')
btnOrderColumns.clicked.connect(self.btnOrderColumns_Clicked)
layBtns.addWidget(btnOrderColumns)
###btnModifyTableStruct
btnModifyTableStruct=QPushButton('修改表结构')
btnModifyTableStruct.setToolTip('功能：1.增加列；2.删除列；'
+'3.修改列名；4.修改列类型；'
+'5.修改列约束;6.调整列顺序'
)
btnModifyTableStruct.clicked.connect(self.btnModifyTableStruct_Clicked)
layBtns.addWidget(btnModifyTableStruct)
###btnInsertRow
btnInsertRow=QPushButton('插入行')
btnInsertRow.setToolTip('将在数据表最后增加一行新记录')
btnInsertRow.clicked.connect(self.btnInsertRow_Clicked)
layBtns.addWidget(btnInsertRow)
###btnDeleteRows
btnDeleteRows=QPushButton('删除行')
btnDeleteRows.setToolTip('删除所有选中项所在的行')
btnDeleteRows.clicked.connect(self.btnDeleteRows_Clicked)
layBtns.addWidget(btnDeleteRows)
###btnQuery
btnQuery=QPushButton('查询数据')
btnQuery.setToolTip('对当前表或数据库进行查询，查询语句将被直接链接到self.model上')
btnQuery.clicked.connect(self.btnQuery_Clicked)
layBtns.addWidget(btnQuery)
self.show()
self.app.exec_()
def __del__(self):
#销毁多余数据库连接
#self.Db.commit()
self.Db.close()
#----------------------------------------------------------------
def makeTableInfo(self):
#table_info=self.Db.execute('pragma table_info(%s)'%self.CurrentTable).fetchall()
paragmastr="pragma table_info( '" + self.CurrentTable + "' ) "
table_info=self.Db.execute(paragmastr).fetchall()
self.columnsCount=len(table_info)
self.columnsName=list(map(lambda x:x[1],table_info))
self.columnsType=list(map(lambda x:x[2],table_info))
dbinfo=self.Db.execute('select * from sqlite_master').fetchall()
for x in dbinfo:
if x[0]=='table' and x[1]==self.CurrentTable:
self.sqlStr=x[4]
break
def DeleteColumn(self,tableName,columnName,tempName=''):
if tempName=='':
#tempName==''表示直接删除对应的列并提交数据库更改
tempName=tableName+'temp'
sqlite_master_sql="select * from sqlite_master"
sqlite_master=self.Db.execute(sqlite_master_sql).fetchall()
createStr=filter(lambda x:x[0]=='table' and x[1]==tableName,
self.Db.execute('select * from sqlite_master').fetchall())[0][4]
createStr=','.join(filter(lambda x:x.find(columnName)==-1,createStr.split(',')))
newColumns=','.join(map(lambda x:x[1],self.Db.execute('Pragma table_info(%s)'%tableName).fetchall()))
#将旧表重命名为临时表名
self.Db.execute("Alter Table %s Rename To %s"%(tableName,tempName))
#新建删除了指定列的数据表
self.Db.execute(createStr)
#将旧表的数据导入新表
self.Db.execute('Insert Into %s Select %s From %s'%
(tableName,newColumns,tempName))
#删除旧表
self.Db.execute('Drop Table %s'%tempName)
#----------------------------------------------------------------
def btnChangeDb_Clicked(self,event):
pt=QFileDialog.getOpenFileName(
caption='请选择一个sqlite数据库文件：',
filter='sqlite数据库文件 (*.db)',
directory=os.path.dirname(self.DbPath)
)
p=pt[0]
if platform.system()=='Windows':
p=p.replace('/','\\')
if os.path.exists(p):
self.DbPath=p
self.Db=sqlite3.connect(self.DbPath)
tbls=map(lambda x:x[1],
filter(lambda x:x[0]=='table',
self.Db.execute(
'Select * From sqlite_master'
).fetchall()
)
)
self.cbbTbls.currentIndexChanged.disconnect(self.changeTable)
self.cbbTbls.clear()
self.cbbTbls.addItems(tbls)
self.cbbTbls.currentIndexChanged.connect(self.changeTable)
self.CurrentTable=tbls[0]
self.cbbTbls.setCurrentIndex(0)
self.leDb.setText(p)
self.model.database().setDatabaseName(self.DbPath)
self.model.database().open()
self.model.setTable(self.CurrentTable)
self.model.select()
def changeTable(self,event):
if self.CurrentTable!=self.cbbTbls.itemText(event):
self.CurrentTable=self.cbbTbls.itemText(event)
self.model.setTable(self.CurrentTable)
self.model.select()
self.makeTableInfo()
self.btnShow.setText('查看表结构')
def btnDeleteTable_Clicked(self,event):
self.Db.execute('Drop Table %s'%self.CurrentTable)
for i in range(self.cbbTbls.count()-1,-1,-1):
if self.cbbTbls.itemText(i)==self.CurrentTable:
self.cbbTbls.removeItem(i)
break
self.CurrentTable=self.cbbTbls.itemText(0)
self.model.setTable(self.CurrentTable)
self.model.select()
def btnRenameTable_Clicked(self,event):
if self.leRename.text()!='':
if self.leRename.text()!=self.CurrentTable:
try:
self.Db.execute('Alter Table %s Rename To %s'%
(self.CurrentTable,self.leRename.text())
)
except sqlite3.OperationalError as e:
if e.message=='there is already another table or index with this name: %s'%self.leRename.text():
QMessageBox.information(self,'错误',
'抱歉，本数据库中以“'+self.leRename.text()+
'”为名称的表或索引已经存在，无法完'+
'成重命名，请重新输入一个名称',
'知道了')
else:
QMessageBox.information(self,'错误',
'抱歉，可能是因表名包含非法字符，故'+
'无法完成重命名，请重新输入表名',
'知道了')
self.leRename.setText('')
return
self.CurrentTable=self.leRename.text()
self.cbbTbls.setItemText(self.cbbTbls.currentIndex(),
self.CurrentTable
)
self.model.clear()
self.model.setQuery(QSqlQuery(
'Select * From %s'%self.CurrentTable))
self.model.select()
self.leRename.setText('')
else:
QMessageBox.information(self,'注意',
'抱歉，你还没有输入当前表要修改成的表名\n\n'+
'请先在文本框里输入当前表要重命名成的名字，再点击我',
'知道了')
def btnShow_Clicked(self,event):
if self.btnShow.text()=='查看表结构':
self.model.setTable('')
self.model.setQuery(QSqlQuery(
'pragma table_info(%s)'%self.CurrentTable))
self.model.select()
self.btnShow.setText('查看表数据')
else:
self.model.setTable(self.CurrentTable)
self.model.select()
self.btnShow.setText('查看表结构')
#----------------------------------------------------------------
def btnInsertRow_Clicked(self,event):
self.dlg_InsertRow_Values=[]
#self.dlg
self.dlg=QDialog()
self.dlg.setWindowTitle('插入数据行：')
#lay
lay=QVBoxLayout()
self.dlg.setLayout(lay)
#lblprompt
lblprompt=QLabel(
'请参照创建此表的Sql字符串：\n'+self.sqlStr+
'\n设置各个字段的数据：')
lay.addWidget(lblprompt)
#layG
layG=QGridLayout()
lay.addLayout(layG)
for i in range(len(self.columnsName)):
#lbl
lbl=QLabel(self.columnsName[i]+'('+self.columnsType[i]+'):')
lbl.setAlignment(Qt.AlignRight)
layG.addWidget(lbl,i,0)
#le
le=QLineEdit()
layG.addWidget(le,i,1)
if self.columnsType[i].lower() not in self.SqliteDbTypes:
#cbb
cbb=QComboBox()
cbb.addItems(self.SqliteDbTypes)
cbb.setCurrentIndex(2)
cbb.setToolTip(
'此字段的数据类型不是sqlite标准数据'+
'类型，请设置其存储时的使用的sqlite数据类型')
layG.addWidget(cbb,i,2)
self.dlg_InsertRow_Values.append((le,cbb))
else:
self.dlg_InsertRow_Values.append((le,self.columnsType[i]))
layG.setColumnStretch(1,1)
#layH
layH=QHBoxLayout()
lay.addLayout(layH)
#btnOk
btnOk=QPushButton('确定')
btnOk.clicked.connect(self.dlg_InsertRow_btnOk_Clicked)
layH.addWidget(btnOk)
#btnCancel
btnCancel=QPushButton('取消')
btnCancel.clicked.connect(self.dlg.close)
layH.addWidget(btnCancel)
self.dlg.show()
def dlg_InsertRow_btnOk_Clicked(self,event):
sqlStr="Insert Into %s Values("%self.CurrentTable
for item in self.dlg_InsertRow_Values:
if item[0].text()!='':
if type(item[1])==QComboBox:
print (item[0].text(),item[1].currentText())
else:
print (item[0].text(),item[1])
else:
pass
def btnDeleteRows_Clicked(self,event):
rs=list(map(lambda x:x.row(),self.tv.selectedIndexes()))
if len(rs)==0:
QMessageBox.information(self,'提醒','请先选中至少一行，再点击此按钮！')
return
for i in reversed(rs):
self.model.removeRows(i,1)
self.model.submitAll()
def btnQuery_Clicked(self,event):
sqltxt,ok=QInputDialog.getText(self,'查询语句设置',
'参照创建此表的Sql字符串：\n'+self.sqlStr+
'\n请输入要设置到self.model的查询语句：')
if ok:
self.model.setTable('')
self.model.setQuery(QSqlQuery(sqltxt))
self.model.select()
#----------------------------------------------------------------
def btnAddColumn_Clicked(self,event):
self.dlgMake_AddColumn()
def dlgMake_AddColumn(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('添加列：')
else:
##self.grpAddColumn
self.grpAddColumn=QGroupBox('添加列：')
self.grpAddColumn.setCheckable(True)
self.grpAddColumn.setChecked(True)
lay.addWidget(self.grpAddColumn)
###layAddColumn
layAddColumn=QVBoxLayout()
if lay is None:
self.dlg.setLayout(layAddColumn)
else:
self.grpAddColumn.setLayout(layAddColumn)
####self.grpAddColumn_ByCmdArgs
self.grpAddColumn_ByCmdArgs=QGroupBox('使用参数创建列：')
self.grpAddColumn_ByCmdArgs.setCheckable(True)
self.grpAddColumn_ByCmdArgs.setChecked(True)
self.PreviousChecked=0
self.grpAddColumn_ByCmdArgs.toggled.connect(
self.grpAddColumn_ByCmd_toggled)
layAddColumn.addWidget(self.grpAddColumn_ByCmdArgs)
#####layAddColumn_ByCmdArgs
layAddColumn_ByCmdArgs=QHBoxLayout()
self.grpAddColumn_ByCmdArgs.setLayout(layAddColumn_ByCmdArgs)
####lblAddColumn_select
lblAddColumn_name=QLabel('列名：')
layAddColumn_ByCmdArgs.addWidget(lblAddColumn_name)
####self.leAddColumn_name
self.leAddColumn_name=QLineEdit()
self.leAddColumn_name.setFixedWidth(100)
layAddColumn_ByCmdArgs.addWidget(self.leAddColumn_name)
######lblAddColumn_type
lblAddColumn_type=QLabel('类型：')
layAddColumn_ByCmdArgs.addWidget(lblAddColumn_type)
######self.cbbAddColumn_type
self.cbbAddColumn_type=QComboBox()
self.cbbAddColumn_type.addItems(self.SqliteDbTypes)
self.cbbAddColumn_type.setCurrentIndex(0)
self.cbbAddColumn_type.setEditable(True)
layAddColumn_ByCmdArgs.addWidget(self.cbbAddColumn_type)
######lblAddColumn_constraint
lblAddColumn_constraint=QLabel('约束字符串：')
layAddColumn_ByCmdArgs.addWidget(lblAddColumn_constraint)
######self.leAddColumn_constraint
self.leAddColumn_constraint=QLineEdit()
layAddColumn_ByCmdArgs.addWidget(self.leAddColumn_constraint)
####self.grpAddColumn_ByCmdStr
self.grpAddColumn_ByCmdStr=QGroupBox('使用sql字符串创建列：')
self.grpAddColumn_ByCmdStr.setCheckable(True)
self.grpAddColumn_ByCmdStr.setChecked(False)
self.grpAddColumn_ByCmdStr.toggled.connect(
self.grpAddColumn_ByCmd_toggled)
layAddColumn.addWidget(self.grpAddColumn_ByCmdStr)
#####layAddColumn_ByCmdStr
layAddColumn_ByCmdStr=QHBoxLayout()
self.grpAddColumn_ByCmdStr.setLayout(layAddColumn_ByCmdStr)
######lblAddColumn_cmdstr
lblAddColumn_cmdstr=QLabel('用来增加列的部分或完整Sql字符串：')
layAddColumn_ByCmdStr.addWidget(lblAddColumn_cmdstr)
######self.leAddColumn_cmdstr
self.leAddColumn_cmdstr=QLineEdit()
layAddColumn_ByCmdStr.addWidget(self.leAddColumn_cmdstr)
if lay is None:
self.dlg.show()
def grpAddColumn_ByCmd_toggled(self,event):
if self.PreviousChecked==0:
self.grpAddColumn_ByCmdStr.setChecked(True)
self.grpAddColumn_ByCmdArgs.setChecked(False)
self.PreviousChecked=1
else:
self.grpAddColumn_ByCmdArgs.setChecked(True)
self.grpAddColumn_ByCmdStr.setChecked(False)
self.PreviousChecked=0
#----------------------------------------------------------------
def btnDeleteColumn_Clicked(self,event):
self.dlgMake_DeleteColumn()
def dlgMake_DeleteColumn(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('删除列：')
else:
##self.grpDeleteColumn
self.grpDeleteColumn=QGroupBox('删除列：')
self.grpDeleteColumn.setCheckable(True)
self.grpDeleteColumn.setChecked(False)
lay.addWidget(self.grpDeleteColumn)
###layDeleteColumn
layDeleteColumn=QHBoxLayout()
if lay is None:
self.dlg.setLayout(layDeleteColumn)
else:
self.grpDeleteColumn.setLayout(layDeleteColumn)
###layColumnList
layColumnList=QVBoxLayout()
layDeleteColumn.addLayout(layColumnList)
####lblDeleteColumn
lblDeleteColumn=QLabel('原有的所有列：')
layColumnList.addWidget(lblDeleteColumn)
####self.lstColumnList
self.lstColumnList=QListWidget()
self.lstColumnList.addItems(self.columnsName)
self.lstColumnList.setFixedWidth(150)
layColumnList.addWidget(self.lstColumnList)
###layDeleteBtns
layDeleteBtns=QVBoxLayout()
layDeleteColumn.addLayout(layDeleteBtns)
####btnDeleteColumn_Store
btnDeleteColumn_Store=QPushButton('>>')
btnDeleteColumn_Store.setFixedWidth(50)
layDeleteBtns.addWidget(btnDeleteColumn_Store)
####btnDeleteColumn_Unstore
btnDeleteColumn_Unstore=QPushButton('<<')
btnDeleteColumn_Unstore.setFixedWidth(50)
layDeleteBtns.addWidget(btnDeleteColumn_Unstore)
###layColumnsToDelete
layColumnsToDelete=QVBoxLayout()
layDeleteColumn.addLayout(layColumnsToDelete)
####lblColumnsToDelete
lblColumnsToDelete=QLabel('要删除的列：')
layColumnsToDelete.addWidget(lblColumnsToDelete)
####self.lstColumnsToDelete
self.lstColumnsToDelete=QListWidget()
self.lstColumnsToDelete.setFixedWidth(150)
layColumnsToDelete.addWidget(self.lstColumnsToDelete)
if lay is None:
self.dlg.show()
#----------------------------------------------------------------
def btnRenameColumn_Clicked(self,event):
self.dlgMake_RenameColumn()
def dlgMake_RenameColumn(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('重命名列：')
else:
##self.grpRenameColumn
self.grpRenameColumn=QGroupBox('重命名列：')
self.grpRenameColumn.setCheckable(True)
self.grpRenameColumn.setChecked(False)
lay.addWidget(self.grpRenameColumn)
###layRenameColumn
layRenameColumn=QHBoxLayout()
if lay is None:
self.dlg.setLayout(layRenameColumn)
else:
self.grpRenameColumn.setLayout(layRenameColumn)
####lblRenameColumn_select
lblRenameColumn_select=QLabel('选择列：')
layRenameColumn.addWidget(lblRenameColumn_select)
####self.cbbRenameColumn_select
self.cbbRenameColumn_select=QComboBox()
self.cbbRenameColumn_select.addItems(self.columnsName)
layRenameColumn.addWidget(self.cbbRenameColumn_select)
####lblRenameColumn_renameto
lblRenameColumn_renameto=QLabel('重命名为：')
layRenameColumn.addWidget(lblRenameColumn_renameto)
####self.leRenameColumn_renameto
self.leRenameColumn_renameto=QLineEdit()
self.leRenameColumn_renameto.setFixedWidth(80)
layRenameColumn.addWidget(self.leRenameColumn_renameto)
####btnRenameColumn_Store
btnRenameColumn_Store=QPushButton('标记 >>')
layRenameColumn.addWidget(btnRenameColumn_Store)
####self.cbbRenameColumn_Store
self.cbbRenameColumn_Store=QComboBox()
layRenameColumn.addWidget(self.cbbRenameColumn_Store,1)
if lay is None:
self.dlg.show()
#----------------------------------------------------------------
def btnModifyColumnType_Clicked(self,event):
self.dlgMake_ModifyColumnType()
def dlgMake_ModifyColumnType(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('修改列数据类型：')
else:
##self.grpModifyColumnType
self.grpModifyColumnType=QGroupBox('修改列数据类型：')
self.grpModifyColumnType.setCheckable(True)
self.grpModifyColumnType.setChecked(False)
lay.addWidget(self.grpModifyColumnType)
###layModifyColumnType
layModifyColumnType=QHBoxLayout()
if lay is None:
self.dlg.setLayout(layModifyColumnType)
else:
self.grpModifyColumnType.setLayout(layModifyColumnType)
####lblModifyColumnType_select
lblModifyColumnType_select=QLabel('选择列：')
layModifyColumnType.addWidget(lblModifyColumnType_select)
####self.cbbModifyColumnType_select
self.cbbModifyColumnType_select=QComboBox()
self.cbbModifyColumnType_select.addItems(self.columnsName)
layModifyColumnType.addWidget(self.cbbModifyColumnType_select)
####lblModifyColumnType_modifyto
lblModifyColumnType_modifyto=QLabel('改类型为：')
layModifyColumnType.addWidget(lblModifyColumnType_modifyto)
####self.cbbModifyColumnType_modifyto
self.cbbModifyColumnType_modifyto=QComboBox()
self.cbbModifyColumnType_modifyto.setEditable(True)
self.cbbModifyColumnType_modifyto.addItems(self.SqliteDbTypes)
self.cbbModifyColumnType_modifyto.setCurrentIndex(2)
self.cbbModifyColumnType_modifyto.setFixedWidth(80)
layModifyColumnType.addWidget(self.cbbModifyColumnType_modifyto)
####btnModifyColumnType_Store
btnModifyColumnType_Store=QPushButton('标记 >>')
layModifyColumnType.addWidget(btnModifyColumnType_Store)
####self.cbbModifyColumnType_Store
self.cbbModifyColumnType_Store=QComboBox()
layModifyColumnType.addWidget(self.cbbModifyColumnType_Store,1)
if lay is None:
self.dlg.show()
#----------------------------------------------------------------
def btnModifyColumnConstraint_Clicked(self,event):
self.dlgMake_ModifyColumnConstraint()
def dlgMake_ModifyColumnConstraint(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('修改列约束：')
else:
##self.grpModifyColumnConstraint
self.grpModifyColumnConstraint=QGroupBox('修改列约束：')
self.grpModifyColumnConstraint.setCheckable(True)
self.grpModifyColumnConstraint.setChecked(False)
lay.addWidget(self.grpModifyColumnConstraint)
###layModifyColumnConstraint
layModifyColumnConstraint=QHBoxLayout()
if lay is None:
self.dlg.setLayout(layModifyColumnConstraint)
else:
self.grpModifyColumnConstraint.setLayout(layModifyColumnConstraint)
####lblModifyColumnConstraint_select
lblModifyColumnConstraint_select=QLabel('选择列：')
layModifyColumnConstraint.addWidget(lblModifyColumnConstraint_select)
####self.cbbModifyColumnConstraint_select
self.cbbModifyColumnConstraint_select=QComboBox()
self.cbbModifyColumnConstraint_select.addItems(self.columnsName)
layModifyColumnConstraint.addWidget(self.cbbModifyColumnConstraint_select)
####lblModifyColumnConstraint_modifyto
lblModifyColumnConstraint_modifyto=QLabel('约束改为：')
layModifyColumnConstraint.addWidget(lblModifyColumnConstraint_modifyto)
####self.leModifyColumnConstraint_modifyto
self.leModifyColumnConstraint_modifyto=QLineEdit()
self.leModifyColumnConstraint_modifyto.setFixedWidth(80)
layModifyColumnConstraint.addWidget(self.leModifyColumnConstraint_modifyto)
####btnModifyColumnConstraint_Store
btnModifyColumnConstraint_Store=QPushButton('标记 >>')
layModifyColumnConstraint.addWidget(btnModifyColumnConstraint_Store)
####self.cbbModifyColumnConstraint_Store
self.cbbModifyColumnConstraint_Store=QComboBox()
layModifyColumnConstraint.addWidget(self.cbbModifyColumnConstraint_Store,1)
if lay is None:
self.dlg.show()
#----------------------------------------------------------------
def btnOrderColumns_Clicked(self,event):
self.dlgMake_OrderColumns()
def dlgMake_OrderColumns(self,lay=None):
if lay is None:
self.dlg=QDialog(self)
self.dlg.setWindowTitle('调整列顺序：')
else:
##self.grpAdjustColumnOrder
self.grpAdjustColumnOrder=QGroupBox('调整列顺序：')
self.grpAdjustColumnOrder.setCheckable(True)
self.grpAdjustColumnOrder.setChecked(False)
lay.addWidget(self.grpAdjustColumnOrder)
###layAdjustColumnOrder
layAdjustColumnOrder=QVBoxLayout()
if lay is None:
self.dlg.setLayout(layAdjustColumnOrder)
else:
self.grpAdjustColumnOrder.setLayout(layAdjustColumnOrder)
####lblAdjustColumnOrder
lblAdjustColumnOrder=QLabel('请调整列顺序：')
layAdjustColumnOrder.addWidget(lblAdjustColumnOrder)
####self.lstAdjustColumnOrder
self.lstAdjustColumnOrder=QListWidget()
self.lstAdjustColumnOrder.addItems(self.columnsName)
self.lstAdjustColumnOrder.setFixedWidth(150)
layAdjustColumnOrder.addWidget(self.lstAdjustColumnOrder)
if lay is None:
self.dlg.setFixedWidth(175)
self.dlg.show()
#----------------------------------------------------------------
def btnModifyTableStruct_Clicked(self,event):
self.dlg=QDialog(self)
self.dlg.setWindowTitle(self.CurrentTable+'表结构修改：')
self.dlg.setWindowFlags(Qt.Window|
Qt.MSWindowsFixedSizeDialogHint
)
#lay
lay=QVBoxLayout()
self.dlgMake_AddColumn(lay)
self.dlgMake_RenameColumn(lay)
self.dlgMake_ModifyColumnType(lay)
self.dlgMake_ModifyColumnConstraint(lay)
#layLists
layLists=QHBoxLayout()
lay.addLayout(layLists)
self.dlgMake_DeleteColumn(layLists)
self.dlgMake_OrderColumns(layLists)
##layBtns
layBtns=QHBoxLayout()
lay.addLayout(layBtns)
##btnOk
btnOk=QPushButton('提交修改')
btnOk.clicked.connect(self.btnOk_Clicked)
layBtns.addWidget(btnOk)
##btnCancel
btnCancel=QPushButton('放弃修改')
btnCancel.clicked.connect(self.btnCancel_Clicked)
layBtns.addWidget(btnCancel)
self.dlg.setLayout(lay)
self.dlg.open()
def btnOk_Clicked(self,event):
#do something here
self.dlg.close()
def btnCancel_Clicked(self,event):
self.dlg.close()
class MainWindow(QMainWindow, Ui_MainWindow):
"""Program Main Window."""
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setupUi(self)
if not QSqlDatabase.isDriverAvailable("QSQLITE"):
QMessageBox.critical(
self, "Driver Error",
"SQLite3 driver not available on this system")
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("data.db")
self.model = QSqlTableModel(self, self.db)
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.table.setModel(self.model)
self.table.setSelectionBehavior(QAbstractItemView.SelectRows)
self.actionImport.triggered.connect(self.import_csv)
self.actionExport.triggered.connect(self.exoprt_csv)
self.filterEdit.textEdited.connect(self.filter_rows)
self.actionAbout.triggered.connect(self.about_dialog)
self.addBtn.clicked.connect(self.insert_record)
self.saveBtn.clicked.connect(self.commit_changes)
self.undoBtn.clicked.connect(self.model.revertAll)
self.delBtn.clicked.connect(self.delete_selected_rows)
self.incBtn.clicked.connect(self.increment_selected)
self.decBtn.clicked.connect(self.decrement_selected)
self.init_db()
def init_db(self):
"""Initialize the database connection and model view."""
if not self.db.open():
QMessageBox.critical(self, "Database connection error",
self.db.lastError().text())
return
self.create_db()
self.model.setTable("diseases")
self.model.select()
# Qt lazily loads data, so force it to load all at once
while self.model.canFetchMore():
self.model.fetchMore()
if self.model.lastError().isValid():
QMessageBox.critical(self, "Select Error",
self.model.lastError().text())
# hide primary key
self.table.hideColumn(0)
# set prettier column labels
for i, header in enumerate(HEADERS):
self.model.setHeaderData(i, Qt.Horizontal, header)
# fit the row width to the contents size
self.table.resizeColumnsToContents()
def create_db(self):
"""Create the necessary table if needed."""
query = QSqlQuery(self.db)
query.exec_("""
CREATE TABLE IF NOT EXISTS
"diseases" ( `id` INTEGER PRIMARY KEY AUTOINCREMENT,
`code` TEXT NOT NULL UNIQUE,
`name` TEXT NOT NULL UNIQUE,
`pcount` INTEGER NOT NULL DEFAULT 0 ) """)
if query.lastError().isValid():
QMessageBox.critical(self, "Query Error", query.lastError().text())
def import_csv(self):
"""Import a CSV list of diseases.
Column 0 should be the alphanumeric disease code, and Column 1 should
be a name or description of the disease."""
name = QFileDialog.getOpenFileName(
self, "Import CSV", "", "Comma Separated Values (*.csv);;"
"All Files (*.*)")
if not name[0]:
return # user canceled
filename = name[0]
with open(filename, "r") as csvfile:
reader = csv.reader(csvfile)
# collect full row count for progress bar, then reset the file
row_count = sum(1 for row in reader)
csvfile.seek(0)
progress = QProgressDialog("Importing data ...", "Abort", 0,
row_count, self)
progress.setWindowTitle("Data Import Progress")
progress.setWindowModality(Qt.WindowModal)
for i, row in enumerate(reader):
progress.setValue(i)
if progress.wasCanceled():
break
query = QSqlQuery(self.db)
query.prepare("INSERT INTO `diseases` (code, name) VALUES "
"(:code, :name)")
query.bindValue(":code", row[0])
query.bindValue(":name", row[1])
query.exec_()
if query.lastError().isValid():
QMessageBox.critical(self, "Query Error",
query.lastError().text())
break
progress.setValue(row_count)
self.model.select()
self.table.resizeColumnsToContents()
def exoprt_csv(self):
"""Export database to CSV format."""
name = QFileDialog.getSaveFileName(
self, "Export CSV", "", "Comma Separated Values (*.csv);;"
"All Files (*.*)")
if not name[0]:
return
filename = name[0]
with open(filename, "w", newline="") as csvfile:
writer = csv.writer(csvfile, dialect="excel")
row_count = self.model.rowCount()
progress = QProgressDialog("Exporting data ...", "Abort", 0,
row_count, self)
progress.setWindowTitle("Data Emport Progress")
progress.setWindowModality(Qt.WindowModal)
for row in range(row_count):
if progress.wasCanceled():
break
record = self.model.record(row)
code = record.value(1)
name = record.value(2)
pcount = record.value(3)
print(code, name, pcount)
writer.writerow([code, name, pcount])
progress.setValue(row)
progress.setValue(row_count)
self.statusBar().showMessage("Export complete", 1500)
def filter_rows(self, text):
"""Loop through all rows, and see if "text" matches the row name."""
self.model.setFilter(f"name LIKE '%{text}%' OR code LIKE '%{text}%'")
def insert_record(self):
"""Append a record to the end of the model."""
if (not self.model.insertRecord(-1, self.model.record())
and self.model.lastError().isValid()):
QMessageBox.critical(self, "Insert Failed",
self.model.lastError().text())
else:
self.table.scrollToBottom()
def prompt_save_changes(self):
"""Ask the user if they'd like to save changes."""
if self.model.isDirty():
answer = QMessageBox.question(
self, "Commit Changes?",
"There are pending changes that have not "
"been commited to the database. Would you like to commit "
"now?", QMessageBox.Yes | QMessageBox.No | QMessageBox.Cancel)
if answer == QMessageBox.Yes:
self.model.submitAll()
return QMessageBox.Yes
elif answer == QMessageBox.No:
self.model.revertAll()
return QMessageBox.No
elif answer == QMessageBox.Cancel:
return QMessageBox.Cancel
def delete_selected_rows(self):
"""Delete the selected rows from the model."""
delete_rows = []
for index in self.table.selectedIndexes():
if index.row() not in delete_rows:
delete_rows.append(index.row())
nrecords = len(delete_rows)
plural = 'record' if len(delete_rows) is 1 else 'records'
QMessageBox.question(
self, "Confirm Delete",
f"You are about to delete <b>{nrecords}</b> "
f"{plural}. Continue?<br>")
for row in delete_rows:
if not self.model.removeRow(row):
# this will be annoying!
QMessageBox.critical(self, "Delete Error",
self.model.lastError().text())
def commit_changes(self):
if not self.model.isDirty():
self.statusBar().showMessage("No changes to save.", 1500)
return
self.model.submitAll()
self.statusBar().showMessage("Changes written to database.", 1500)
self.model.select()
def closeEvent(self, event):
"""Override the app close event in case there are unsaved changes."""
if self.prompt_save_changes() == QMessageBox.Cancel:
event.ignore()
return
self.model.clear()
self.db.close()
def selected_rows(self) -> set:
"""Get a set of selected rows."""
selected_rows = set()
for index in self.table.selectedIndexes():
selected_rows.add(index.row())
return selected_rows
def increment_selected(self):
"""Increment the 'pcount' column of the selected row(s)."""
for row in self.selected_rows():
rec = self.model.record(row) # type: QSqlRecord
try:
rec.setValue("pcount", rec.value("pcount") + 1)
except TypeError:
QMessageBox.critical(
self, "Invalid Data", "Invaild <b>Patient"
" Count</b> value for <b>"
f"{rec.value('code')}</b>.<br>"
"Must be an integer.")
break
self.model.setRecord(row, rec)
self.commit_changes()
def decrement_selected(self):
"""Decrement the 'pcount' column of the selected row(s)."""
for row in self.selected_rows():
rec = self.model.record(row) # type: QSqlRecord
try:
rec.setValue("pcount", rec.value("pcount") - 1)
except TypeError:
QMessageBox.critical(
self, "Invalid Data", "Invaild <b>Patient"
" Count</b> value for <b>"
f"{rec.value('code')}</b>.<br>"
"Must be an integer.")
break
self.model.setRecord(row, rec)
self.commit_changes()
def about_dialog(self):
"""Trigger the About dialog."""
QMessageBox.about(
self, "About", "<a href='https://github.com/miniCruz"
"er/disease-tracking'>Disease Tracking</a>, v{} <br>"
"Copyright © 2018 Samuel Hoffman<br>"
"Distributed under the GNU GPLv3 License<br><br> "
'Written with <font color="red">❤</font>'
" for Hadi Faour".format(__version__))
