def __init__(self,
parent: QWidget,
tableName: str,
tableView: QtWidgets.QTableView,
parentPkFieldName: str = '',
db=QtSql.QSqlDatabase()):
'''用于使用tableView显示一个表中的数据，也可以作为主子表情况下子表数据模型。\n
可以设置统计列数据（StatisticsValueChange信号会传递相关结果）;
也可能加一些计算列（addViewColumn方法增加列时，省略fieldIndex参数会被认为只计算列）
'''
super().__init__(parent=parent, db=db)
self._tableView = tableView
# 存放tableView中要显示的列的信息
self._columnInfomation: list = []
# 存放用户增加的统计列的信息
self._StatisticsColumn: list = []
self._parentPkFieldName = parentPkFieldName
self.setTable(tableName)
self.setEditStrategy(QSqlTableModel.OnRowChange)
self.beforeInsert.connect(self._beforeInsert)
self._setPrimaryKeyInfomation()
self._readOnlyDelegate = _ReadOnlyDelegate()
# 是否已经设置过只读列
self._readOnlyDelegateSeted = False
self._EditDataMode = JPEditDataMode.readOnlyMode
def connect(self):
""" Connect to the database and set some parameters.
"""
if Database.database is None:
Database.database = QtSql.QSqlDatabase("QPSQL")
Database.database.setHostName(
os.environ.get("DATABASE_HOST", settings.DB_HOST))
Database.database.setPort(
int(os.environ.get(
"DATABASE_PORT",
settings.DB_PORT,
)))
Database.database.setUserName(
os.environ.get("DATABASE_USER", settings.USERNAME))
Database.database.setPassword(
os.environ.get("DATABASE_PASSWORD", settings.PASSWORD))
Database.database.setDatabaseName(
os.environ.get("DATABASE_NAME", settings.DBNAME))
if not Database.database.open():
if rapi.utils.check_x11():
# We need this to create an app before opening a window.
import gui.utils
self.tmp = QtWidgets.QApplication(sys.argv)
gui.utils.error("Error", "Can't join database")
print("Can't join database")
sys.exit(1)
def __init__(self,
parent: QWidget,
tableName: str,
filter: str = None,
db=QtSql.QSqlDatabase()):
'''用于窗体模式进行数据编辑时的主窗体数据模型。\n
会自动增加数据映射器，但是外键字段要使用addComboBoxData方法增加列表文字。
最后要调用tofirst()方法定位编辑的记录。\n
注：数据映射器不能增删记录，只能编辑
'''
super().__init__(parent=parent, db=db)
self.parent = parent
self.setTable(tableName)
if filter:
self.setFilter(filter)
self.select()
rec = self.record()
self.mapper = QDataWidgetMapper(parent)
self.mapper.setModel(self)
self.mapper.setItemDelegate(_JPRelationalDelegate(parent, self.mapper))
self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit)
for i in range(rec.count()):
widget = parent.findChild(self._tp, rec.fieldName(i))
if widget:
if not isinstance(widget, QComboBox):
self.mapper.addMapping(widget, i)
def __init__(self,
PaperSize=QPrinter.A4,
Orientation=QPrinter.Orientation(0),
db=QtSql.QSqlDatabase()):
self.TotalPagesCalculated = False
self.PaperSize = PaperSize
self.Orientation = Orientation
self.BoolString = ['是', '否']
self.Margins: QMargins = QMargins(0, 0, 0, 0)
self.ReportHeader = _jpSectionReportHeader(self)
self.ReportFooter = _jpSectionReportFooter(self)
self.PageHeader = _jpSectionPageHeader(self)
self.PageFooter = _jpSectionPageFooter(self)
self.Detail = _jpSectionDetail(self)
self._Copys = 1
self._CurrentPage = 0
self._CurrentCopys = 0
self.__PageCount = 0
self.__DataSource = []
self.__Errors = []
self.__Printer: typing.Any = None
self.__PageHeight = 0
self.__Calculated = False
self.__Reseted = False
self.__SectionPrintBeginY = 0
self.__ExecNewPageTimes = 0
self.__db: QtSql.QSqlDatabase = db
self.__FieldTypes = {}
self._GroupLinkList = _GroupLinkList(self)
self.DataSourceRecord: QSql.QSqlRecord = None
def __init__(self):
# initialising radio buttons initial status
self.host_id_model = None
self.site_id_model = None
self.selected_host_id = None
self.selected_site_id = None
self.user_roles_dict = {
'adminRadioButton': 'A',
'userRadioButton': 'U'
}
self.user_group_dict = {
'groupFalseRadioButton': 'F',
'groupTrueRadioButton': 'T'
}
# set up window layout
app = QtWidgets.QApplication(sys.argv)
win = QtWidgets.QMainWindow()
self.new_user_win = LocalViewNewUser_Ui.Ui_MainWindow()
self.new_user_win.setupUi(win)
# set up user interaction signals
self.setup_navigation_model()
# initialise database - in this case Postgres
self.db = QtSql.QSqlDatabase('QPSQL')
if not self.setup_db():
db_error_msg = self.db.lastError().text()
no_text_alert = QtWidgets.QMessageBox(
QtWidgets.QMessageBox.Information, "DB Error", db_error_msg,
QtWidgets.QMessageBox.Ok)
no_text_alert.exec_()
else:
# initialise list view
self.populate_host_id()
win.show()
sys.exit(app.exec_())
def __get_qt_connection(self):
sql_params = self.__read_db_config()
connection = QtSql.QSqlDatabase().addDatabase('QMYSQL')
connection.setDatabaseName(self.db_name)
connection.setHostName(sql_params['host'])
connection.setUserName(sql_params['user'])
connection.setPassword(sql_params['password'])
return connection
def EstablecerDB(conexion):
datos = StringToList(conexion)
db = QtSql.QSqlDatabase('QPSQL')
db.setDatabaseName(datos[0])
db.setHostName(datos[1])
db.setPort(int(datos[2]))
db.setUserName(datos[3])
db.setPassword(datos[4])
return db
def analysingData(self):
nowatime = datetime.datetime.now().strftime('%Y_%m_%d_%H%M') # 当前时间
self.exe_crawl()
# 先连接至分析数据库获取上次所用分析表的时间
db = QtSql.QSqlDatabase().addDatabase("QMYSQL")
db.setDatabaseName("macao_handicap_analysis")
db.setHostName("127.0.0.1") # set address
db.setUserName("root") # set user name
db.setPassword("1994") # set user pwd
if not db.open():
# 打开失败
print('打开数据库失败！')
return db.lastError()
print("连接至 macao_handicap_analysis success!")
# 创建QsqlQuery对象，用于执行sql语句
query = QtSql.QSqlQuery()
query.exec('show tables')
query.first()
# 查询出刚才更新的表
table_name = query.value(0)
query.exec('SELECT * FROM '+table_name)
query.next()
match_info_list = []
for i in range(query.size()):
start_time = time.strptime(query.value(4), "%Y-%m-%d %H:%M")
mk_start_time = time.mktime(start_time)
match_id = query.value(0)
# 定时任务
# if (time.time() - mk_start_time) < 0:
# f = open('timer_id.txt', 'r')
# timer_text = f.read()
# timer_list = timer_text.split(',')
# f.close()
# if not match_id in timer_list:
# scheduler = BackgroundScheduler()
# scheduler.add_job(self.updateData, 'date', run_date=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(mk_start_time))) # 在指定的时间，只执行一次
# print('设定定时任务,match_id:',match_id,'开始时间',start_time)
# # 将定时执行的比赛id写入,防止重复
# f = open('timer_id.txt', 'a')
# f.write(match_id+',')
# f.close()
# scheduler.start() # 这里的调度任务是独立的一个线程
match_info = {}
match_info['start_time'] = query.value(4)
match_info['host'] = query.value(1)
match_info['handicap'] = query.value(8)
match_info['guest'] = query.value(2)
match_info['league_name'] = query.value(3)
match_info['support'] = query.value(9)
match_info_list.append(match_info)
query.next()
if len(match_info_list) > 0:
self.print_form_info(match_info_list)
db.close()
print('断开数据库')
def openDb(self):
self.db = QtSql.QSqlDatabase().database()
if self.db.isValid():
return self.db
self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("127.0.0.1")
self.db.setDatabaseName("employees")
self.db.setUserName("root")
self.db.setPassword("rock")
return self.db
def query_sql_model(q):
db = QtSql.QSqlDatabase('QSQLITE')
db.setDatabaseName('db/chinook.db')
db.open()
model = QtSql.QSqlQueryModel()
model.setQuery(q, db)
# model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
# model.setHeaderData(1, QtCore.Qt.Horizontal, "First name")
# model.setHeaderData(2, QtCore.Qt.Horizontal, "Last name")
return model
def dbtotree(
dbname
): #since everything should already be formatted in here, can just select on a join
conn = QtSql.QSqlDatabase('QSQLITE')
conn.addDatabase('QSQLITE')
conn.setDatabaseName(dbname)
if not (conn.isValid()):
print('connection failed :(')
return ConnectionError
else:
return conn
def db_queryServer(self, delete=0, currentIndex=0):
query = QtSql.QSqlDatabase(self.db)
if delete == 1:
query.exec_(
"DELETE FROM `servers` WHERE `id` = " +
str(self.serverModel.record(currentIndex.row()).value(0)))
self.message("Сервер удалён.")
else:
query.exec_("INSERT INTO `servers`(`host`, `port`) VALUES('" +
self.edit_server.text() + "', '" +
self.edit_server_port.text() + "')")
self.message("Сервер добавлен.")
self.serverModel.select()
def exportSQL(self,
viewColumns,
SQL: str,
db=QtSql.QSqlDatabase(),
sheetName='newsheet'):
'''导出一个查询语句'''
if not isinstance(SQL, str):
raise Exception('SQL参数类型错！')
query = QtSql(SQL, db)
if not query.record():
raise Exception('SQL执行结果有误，请检查SQL！')
self.__exportOneQuery(viewColumns, query, sheetName)
self.__saveFile()
def __init__(self, controller, parent=None):
super(BookmarkManagerDialog, self).__init__(parent=parent)
self.ui = bookmark_manager_dialog_ui.Ui_Bookmark_Dialog()
self.ui.setupUi(self)
self.controller = controller
path = get_dir_name(
controller.model.settings_manager.settings.fileName())
self.db = QtSql.QSqlDatabase().addDatabase("QSQLITE")
self.db.setDatabaseName(path + '/bookmark.db')
if self.db.open():
self.model = QtSql.QSqlTableModel(self, self.db)
self.model.setTable('Bookmark')
self.model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
self.model.select()
self.model.setHeaderData(2, QtCore.Qt.Horizontal, "Name")
self.model.setHeaderData(3, QtCore.Qt.Horizontal, "Page")
self.ui.bookmark_table.setModel(self.model)
self.ui.bookmark_table.hideColumn(0)
self.ui.bookmark_table.hideColumn(1)
self.ui.bookmark_table.hideColumn(4)
self.ui.bookmark_table.horizontalHeader().setSectionResizeMode(
2, QtWidgets.QHeaderView.Stretch)
self.ui.bookmark_table.horizontalHeader().setSectionResizeMode(
3, QtWidgets.QHeaderView.ResizeToContents)
self.ui.button_remove.clicked.connect(self._remove_table_item)
self.ui.button_load.clicked.connect(self._get_comic_to_open)
selection = self.ui.bookmark_table.selectionModel()
selection.selectionChanged.connect(self.selection_changed)
self.no_cover_label = self.ui.page_image_label.pixmap(
).scaledToWidth(self.width() * self.SCALE_RATIO,
QtCore.Qt.SmoothTransformation)
self.ui.page_image_label.setPixmap(self.no_cover_label)
if self.model.rowCount():
self.ui.button_load.setEnabled(True)
self.ui.button_remove.setEnabled(True)
logger.info('Database load!')
else:
logger.error('Unable to create db file!')
def get_sql_model():
db = QtSql.QSqlDatabase('QSQLITE')
db.setDatabaseName('db/chinook.db')
db.open()
model = QtSql.QSqlRelationalTableModel(db=db)
model.setTable('tracks')
model.setRelation(2, QtSql.QSqlRelation('albums', 'AlbumId', 'Title'))
model.setRelation(3,
QtSql.QSqlRelation('media_types', 'MediaTypeId', 'Name'))
model.setRelation(4, QtSql.QSqlRelation('genres', 'GenreId', 'Name'))
model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
model.select()
# model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
# model.setHeaderData(1, QtCore.Qt.Horizontal, "First name")
# model.setHeaderData(2, QtCore.Qt.Horizontal, "Last name")
return model
def SaveReport(self):
print("脏数据", self.ModelReport.mapper.model().isDirty())
row = self.ModelReport.mapper.currentIndex()
result = self.ModelReport.mapper.submit()
self.ModelReport.submitAll()
print(str(QtSql.QSqlDatabase().lastError().text()))
# self.ModelReport.submitAll()
# self.Mapper.setCurrentIndex(row)
# if self.addMode:
# if result:
# self.initReportSelecter1()
# sql = 'SELECT LAST_INSERT_ID()'
# query = QtSql.QSqlQuery()
# query.exec(sql)
# if query.next():
# for i in range(self.ui.ReportSelect1.count()):
# if self.ui.ReportSelect1.itemData(i) == query.value(0):
# self.ui.ReportSelect1.setCurrentIndex(i)
# self.addMode = False
print(result)
def UpdateGlobalVariables(qtCreatorFile, MyApp, conn):
#------------------------------------create the Connection to database
dbProject = QtSql.QSqlDatabase()
dbProject = QtSql.QSqlDatabase.addDatabase('QSQLITE')
dbProject.setDatabaseName(cfg.gvDic['DatabaseFile'])
if dbProject.isValid(): print("SQL db instance OK")
else:
print("SQL db instance error = "+ dbProject.lastError().text())
if not dbProject.open(): print(dbProject.lastError().text())
else: print("Database connected")
gpClass = uic.loadUiType(qtCreatorFile)[0] # Load the UI
pwFieldList = ['Transition Type','Description','Possible','Cycle End','Restart Age','Plantation','Production','Min Age to Cut','Max Age to Cut']
#---------------------------------------------------subClass of a TableModel
class pwTableModel(QtSql.QSqlTableModel):
def __init__(self, parent=None, *args):
super(pwTableModel, self).__init__()
self.setTable('TransitionType')
Erro = self.lastError()
print("erro =",str(Erro.text()))
print("erro =",str(Erro.databaseText()))
self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.setSort(0, 0)
if self.select(): print("select OK")
else: print("select error")
for i in range(0,len(pwFieldList)):
self.setHeaderData(i, QtCore.Qt.Horizontal, pwFieldList[i])
def flags(self, index):
itemFlags = super(pwTableModel, self).flags(index)
itemFlags = itemFlags | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEditable
return itemFlags
def data(self, item, role):
val = QtSql.QSqlTableModel.data(self, item, role)
row = item.row()
column = item.column()
if role == QtCore.Qt.CheckStateRole and column == 2:
if str(item.data()) == 'True':
return QtCore.QVariant(QtCore.Qt.Checked)
else:
return QtCore.QVariant(QtCore.Qt.UnChecked)
if role == Qt.DisplayRole:
if column in (7,8):
try:
return '{:{}{}{}}'.format(val, '>', '-', 10)
except ValueError:
pass
if role == Qt.EditRole:
if column in (7,8):
try:
return val
except ValueError:
pass
if role == QtCore.Qt.TextAlignmentRole:
if column in (7,8):
return QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter
return val
#end class pwTableModel(QtSql.QSqlTableModel)
class CheckBoxDelegate(QtWidgets.QStyledItemDelegate):
"""
A delegate that places a fully functioning QCheckBox in every
cell of the column to which it's applied
"""
def __init__(self, parent):
QtWidgets.QItemDelegate.__init__(self, parent)
def createEditor(self, parent, option, index):
#Important, otherwise an editor is created if the user clicks in this cell.
#** Need to hook up a signal to the model
return None
def paint(self, painter, option, index):
#Paint a checkbox without the label.
if index.data() in ('OK', 'Ok', 'ok', 'True' , 'true' , 'Yes', 'yes','1'):
checked = bool(1)
elif index.data() in ('False', 'false', 'No' , 'no' ,'0'):
checked = bool(0)
else: checked = bool(0)
check_box_style_option = QtWidgets.QStyleOptionButton()
check_box_style_option.state |= QtWidgets.QStyle.State_Enabled
check_box_style_option.rect = self.getCheckBoxRect(option)
if checked: check_box_style_option.state |= QtWidgets.QStyle.State_On
else: check_box_style_option.state |= QtWidgets.QStyle.State_Off
QtWidgets.QApplication.style().drawControl(QtWidgets.QStyle.CE_CheckBox, check_box_style_option, painter)
def editorEvent(self, event, model, option, index):
if index.data() in ('OK', 'Ok', 'ok', 'True' , 'true' , 'Yes', 'yes','1'):
checked = bool(1)
elif index.data() in ('False', 'false', 'No' , 'no' ,'0'):
checked = bool(0)
else: checked = bool(0)
if event.type() in (QtCore.QEvent.MouseButtonPress, QtCore.QEvent.MouseButtonDblClick) :
if checked: x = 'False'
else: x = 'True'
model.setData(index, x)
return True
return False
def getCheckBoxRect(self, option):
check_box_style_option = QtWidgets.QStyleOptionButton()
check_box_rect = QtWidgets.QApplication.style().subElementRect(QtWidgets.QStyle.SE_CheckBoxIndicator, check_box_style_option, None)
check_box_point = QtCore.QPoint (option.rect.x() +
option.rect.width() / 2 -
check_box_rect.width() / 2,
option.rect.y() +
option.rect.height() / 2 -
check_box_rect.height() / 2)
return QtCore.QRect(check_box_point, check_box_rect.size())
#end class CheckBoxDelegate(QtWidgets.QStyledItemDelegate)
class MyTableModel(QtSql.QSqlRelationalTableModel):
def __init__(self, parent=None, *args):
super(MyTableModel, self).__init__()
self.TableName = ''
self.FieldList = []
self.IntegerColumns = []
self.FloatColumns = []
def ConfigTable(self,TableName, FieldList, IntegerColumns, FloatColumns):
self.TableName = TableName
self.FieldList = FieldList
self.IntegerColumns = IntegerColumns
self.FloatColumns = FloatColumns
self.setTable(self.TableName)
Erro = self.lastError()
if len(Erro.text()) > 1:
print("erro =",str(Erro.text()))
print("erro =",str(Erro.databaseText()))
self.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.setSort(0, 0)
if self.select(): print("SQL Select from a table OK")
else: print("SQL Select from a table with error")
for i in range(0,len(FieldList)):
self.setHeaderData(i, QtCore.Qt.Horizontal, FieldList[i])
def flags(self, index):
itemFlags = super(MyTableModel, self).flags(index)
itemFlags = itemFlags | QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable | QtCore.Qt.ItemIsEditable
return itemFlags
def data(self, item, role):
val = QtSql.QSqlTableModel.data(self, item, role)
column = item.column()
self.NumberColumns = self.IntegerColumns + self.FloatColumns
if role == Qt.DisplayRole:
if column in self.IntegerColumns:
try: return '{:{}{}{}}'.format(val, '>', '-', 10)
except ValueError: pass
if column in self.FloatColumns:
try: return '{:,.3f}'.format(val)
except ValueError: pass
if role == Qt.EditRole:
if column in self.NumberColumns:
try: return val
except ValueError: pass
if role == QtCore.Qt.TextAlignmentRole:
if column in self.NumberColumns:
return QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter
return val
class gParamClass(QMainWindow, gpClass):
def __init__(self, parent=None):
QMainWindow.__init__(self, parent)
gpClass.__init__(self)
# General Parameters
self.setupUi(self)
self.Next0Button.clicked.connect(self.Next0Button_clicked)
self.Next1Button.clicked.connect(self.Next1Button_clicked)
self.Next2Button.clicked.connect(self.Next2Button_clicked)
self.RunButton.clicked.connect(self.close)
self.ParametersTab.currentChanged.connect(self._tab_changed)
self.ParamPage = 0
self.dbFileButton.clicked.connect(self.dbFileButton_clicked)
self.wDirButton.clicked.connect(self.wDirButton_clicked)
self.dbFileEdit.setText(cfg.gvDic['DatabaseFile'])
self.wDirEdit.setText(cfg.gvDic['WorkingDirectory'])
self.simEdit.setText(cfg.gvDic['SimulationTitle'])
self.simDescEdit.setText(cfg.gvDic['SimulationDesc'])
self.solverEdit.setText(cfg.gvDic['MySolver'])
self.LastIgorLabel.text = str(cfg.ParamIgor)
if cfg.gvDic['RunType'] == 'PayOffMatrix':
self.poMatrixRButton.setChecked(True)
elif cfg.gvDic['RunType'] == 'ParetoFrontier':
self.pFrontierRButton.setChecked(True)
elif cfg.gvDic['RunType'] == 'MonteCarlo':
self.mCarloRButton.setChecked(True)
elif cfg.gvDic['RunType'] == 'InitialArea':
self.IniAreaRButton.setChecked(True)
# Optimization Parameters
#---------------------------------------------------Fill Criteria list
NotInSelectionList = [item for item in cfg.ObjectiveDic.keys() if item not in cfg.ObjSelectionL ]
for item1 in NotInSelectionList:
self.CriteriaList.addItem(item1)
for item2 in cfg.ObjSelectionL:
self.OptCriteriaList.addItem(item2)
#-------------------------------------------------Set Values and rates
self.ProdPcPrevMinSpin.setValue(cfg.OptParam.ProdPcPrevMin)
self.ProdPcPrevMaxSpin.setValue(cfg.OptParam.ProdPcPrevMax)
self.GenMatMinSpin.setValue(cfg.OptParam.GenMatMin)
self.GenMatMaxSpin.setValue(cfg.OptParam.GenMatMax)
self.discRateSpin.setValue(cfg.OptParam.discRate)
self.IgorValueSpin.setValue(cfg.OptParam.IgorValue)
self.goalRateSpin.setValue(cfg.OptParam.goalRate)
self.mcIterationsSpin.setValue(int(cfg.OptParam.mcIterations))
self.Period2MaxSpin.setValue(cfg.OptParam.Period2Max)
self.Period2FlowSpin.setValue(cfg.OptParam.Period2Flow)
self.rgControlPeriodSpin.setValue(cfg.OptParam.rgControlPeriod)
self.rgControlPcPrevSpin.setValue(cfg.OptParam.rgControlPcPrev)
self.rgLastAgeClassSpin.setValue(cfg.OptParam.rgLastAgeClass)
#----------------------------------------------Set Optional contraints
if cfg.OptParam.conNDY == 'yes': self.conNDYBox.setChecked(True)
else: self.conNDYBox.setChecked(False)
if cfg.OptParam.conMktProd == 'yes': self.conMktProdBox.setChecked(True)
else: self.conMktProdBox.setChecked(False)
if cfg.OptParam.conMillPrd == 'yes': self.conMillPrdBox.setChecked(True)
else: self.conMillPrdBox.setChecked(False)
if cfg.OptParam.conRangeProd == 'yes': self.conRangeProdBox.setChecked(True)
else: self.conRangeProdBox.setChecked(False)
if cfg.OptParam.conGenMatSecurity == 'yes': self.conGenMatSecurityBox.setChecked(True)
else: self.conGenMatSecurityBox.setChecked(False)
if cfg.OptParam.conIgor == 'yes': self.conIgorBox.setChecked(True)
else: self.conIgorBox.setChecked(False)
#----------------------------------------------Set Regulation Variable
if cfg.OptParam.Regulation == 'Area': self.AreaRButton.setChecked(True)
elif cfg.OptParam.Regulation == 'TotProd': self.TotProdRButton.setChecked(True)
elif cfg.OptParam.Regulation == 'Prod1': self.Prod1RButton.setChecked(True)
#-----------------------------------------------------------------------------
# prescription Writer Parameters
#-----------------------------------------------------------------------------
self.tTypeModel = pwTableModel()
self.TTypeTableView.setModel(self.tTypeModel)
self.TTypeTableView.setWindowTitle('Transition Types')
self.TTypeTableView.setSortingEnabled(True)
for i in [2,3,4,5,6]:
self.TTypeTableView.setItemDelegateForColumn(i,CheckBoxDelegate(self))
self.TTypeTableView.show()
#-----------------------------------------------------------------------------
# General Funcion to link a TableModel to a TableView and show them
#-----------------------------------------------------------------------------
def ModelTableViewLink (MyTableView, MyTableName):
MyModel = MyTableModel()
MyModel.ConfigTable(MyTableName,cfgDB.DicTable[MyTableName][0],
cfgDB.DicTable[MyTableName][1],
cfgDB.DicTable[MyTableName][2])
for row in cfgDB.DicJoin[MyTableName]:
(NumColumn,(SqlTable, SqlKey, SqlDesc)) = row
MyModel.setRelation(NumColumn,QtSql.QSqlRelation(SqlTable, SqlKey, SqlDesc))
MyTableView.setModel(MyModel)
MyTableView.setSortingEnabled(True)
if len(cfgDB.DicJoin[MyTableName])>0:
MyTableView.setItemDelegate(QtSql.QSqlRelationalDelegate(MyTableView))
MyTableView.show()
ModelTableViewLink(self.VariableTableView, 'Variable')
ModelTableViewLink(self.RegionTableView, 'Region')
ModelTableViewLink(self.MarketTableView, 'Market')
ModelTableViewLink(self.SpeciesTableView, 'Species')
ModelTableViewLink(self.ProductTableView, 'Product')
ModelTableViewLink(self.IniAreaTableView, 'InitialArea')
ModelTableViewLink(self.ProductivityTableView, 'Production')
ModelTableViewLink(self.MktDemandTableView, 'MarketDemand')
ModelTableViewLink(self.TimberPriceTableView, 'Price')
ModelTableViewLink(self.CarbonPriceTableView, 'Period')
ModelTableViewLink(self.HarvestCstTableView, 'HarvCosts')
ModelTableViewLink(self.SilvCstTableView, 'SilvCosts')
def dbFileButton_clicked(self):
dbDialog = QFileDialog()
dbDialog.setFileMode(QFileDialog.ExistingFile)
options = dbDialog.Options()
dbCurrentFile = str(self.dbFileEdit.text())
fileName, _ = QFileDialog.getOpenFileName(self,"Choose Database File",
dbCurrentFile,
"db files (*.db)",
options=options)
if fileName:
self.dbFileEdit.setText(fileName)
def wDirButton_clicked(self):
pfDialog = QFileDialog()
pfDialog.setFileMode(QFileDialog.DirectoryOnly)
wCurrentDir = str(self.wDirEdit.text())
wDirName = QFileDialog.getExistingDirectory(self, "Working Directory",
wCurrentDir ,
QFileDialog.ShowDirsOnly)
if wDirName != "":
self.wDirEdit.setText(wDirName)
def _tab_changed(self, _index):
if self.ParamPage == 0:
#-----------------------------------------------------save new global variables
#-------------------------------------------------into the same inital xml file
# general parameters
cfg.gvDic['DatabaseFile'] = str(self.dbFileEdit.text())
cfg.gvDic['WorkingDirectory'] = str(self.wDirEdit.text())
cfg.gvDic['SimulationTitle'] = str(self.simEdit.text())
cfg.gvDic['SimulationDesc'] = str(self.simDescEdit.text())
cfg.gvDic['MySolver'] = str(self.solverEdit.text())
if self.poMatrixRButton.isChecked():
cfg.gvDic['RunType'] = 'PayOffMatrix'
if self.pFrontierRButton.isChecked():
cfg.gvDic['RunType'] = 'ParetoFrontier'
if self.mCarloRButton.isChecked():
cfg.gvDic['RunType'] = 'MonteCarlo'
if self.IniAreaRButton.isChecked():
cfg.gvDic['RunType'] = 'InitialArea'
SaveGlobalVariables('Plt2_GlobalVar.xml')
self.ParamPage = _index
elif self.ParamPage == 1:
#-------------------------------------------save updated optmization parameters
#-------------------------------------------------into the same inital xml file
#----------------------------------------------------Save Criteria Selection
cfg.ObjSelectionL = []
for row in range(self.OptCriteriaList.count()):
cfg.ObjSelectionL.append(self.OptCriteriaList.item(row).text())
#------------------------------------------------------Save Values and rates
cfg.OptParam.ProdPcPrevMin = self.ProdPcPrevMinSpin.value()
cfg.OptParam.ProdPcPrevMax = self.ProdPcPrevMaxSpin.value()
cfg.OptParam.GenMatMin = self.GenMatMinSpin.value()
cfg.OptParam.GenMatMax = self.GenMatMaxSpin.value()
cfg.OptParam.discRate = self.discRateSpin.value()
cfg.OptParam.IgorValue = self.IgorValueSpin.value()
cfg.OptParam.goalRate = self.goalRateSpin.value()
cfg.OptParam.mcIterations = int(self.mcIterationsSpin.value())
cfg.OptParam.Period2Max = self.Period2MaxSpin.value()
cfg.OptParam.Period2Flow = self.Period2FlowSpin.value()
cfg.OptParam.rgControlPeriod = self.rgControlPeriodSpin.value()
cfg.OptParam.rgControlPcPrev = self.rgControlPcPrevSpin.value()
cfg.OptParam.rgLastAgeClass = self.rgLastAgeClassSpin.value()
#--------------------------------------------------Save Optional Constraints
if self.conNDYBox.isChecked(): cfg.OptParam.conNDY = 'yes'
else: cfg.OptParam.conNDY = 'no'
if self.conMktProdBox.isChecked(): cfg.OptParam.conMktProd = 'yes'
else: cfg.OptParam.conMktProd = 'no'
if self.conMillPrdBox.isChecked(): cfg.OptParam.conMillPrd = 'yes'
else: cfg.OptParam.conMillPrd = 'no'
if self.conRangeProdBox.isChecked(): cfg.OptParam.conRangeProd = 'yes'
else: cfg.OptParam.conRangeProd = 'no'
if self.conGenMatSecurityBox.isChecked(): cfg.OptParam.conGenMatSecurity = 'yes'
else: cfg.OptParam.conGenMatSecurity = 'no'
if self.conIgorBox.isChecked(): cfg.OptParam.conIgor = 'yes'
else: cfg.OptParam.conIgor = 'no'
#---------------------------------------------------Save Regulation Variable
if self.AreaRButton.isChecked() == True:
cfg.OptParam.Regulation = 'Area'
if self.TotProdRButton.isChecked() == True:
cfg.OptParam.Regulation = 'TotProd'
if self.Prod1RButton.isChecked() == True:
cfg.OptParam.Regulation = 'Prod1'
NumCriteria = self.OptCriteriaList.count()
CanLeave = False
RunTypeString = str(cfg.gvDic['RunType'])
if RunTypeString in ('PayOffMatrix','GoalProgramming'):
if (NumCriteria >= 1): CanLeave = True
else: InfoText = 'To run PayOff or GoalProgramming we need at least one criterion'
else:
if RunTypeString == 'ParetoFrontier':
if (NumCriteria >= 2): CanLeave = True
else: InfoText = 'To run a Pareto Frontier Procedure we need at least 2 criteria'
else:
if RunTypeString == 'MonteCarlo':
if (NumCriteria >= 1): CanLeave = True
else: InfoText = 'To run a Monte Carlo Procudure we need at least one criterion'
if CanLeave:
SaveOptVariables('Plt2_OptVar.xml')
InitCrtSelectionL()
self.ParamPage = _index
else:
msg = QMessageBox()
MsgText = "Error in Criteria selection"
msg.setStandardButtons(QMessageBox.Ok)
buttonReply = QMessageBox.critical(self, MsgText, InfoText, QMessageBox.Ok, QMessageBox.Ok)
self.ParametersTab.setCurrentIndex(1)
pass
elif self.ParamPage == 2:
NumRows = self.tTypeModel.rowCount()
tTypeKey = ''
tTypeRow = ()
for i in range(NumRows):
tTypeKey = self.tTypeModel.record(i).value("ttype")
tTypeRow = (self.tTypeModel.record(i).value("trsDescription"),
StrToBoolean(self.tTypeModel.record(i).value("Possible")),
StrToBoolean(self.tTypeModel.record(i).value("CycleEnd")),
StrToBoolean(self.tTypeModel.record(i).value("ReStartAge")),
StrToBoolean(self.tTypeModel.record(i).value("Plantation")),
StrToBoolean(self.tTypeModel.record(i).value("Production")),
self.tTypeModel.record(i).value("ccAMin"),
self.tTypeModel.record(i).value("ccAMax"))
cfg.pwDic[tTypeKey] = tTypeRow
self.ParamPage = _index
# end of ifs and elses of tabs
# end of "def __init__(self, parent=None)"
def Next0Button_clicked(self):
self.ParametersTab.setCurrentIndex(1)
def Next1Button_clicked(self):
self.ParametersTab.setCurrentIndex(2)
def Next2Button_clicked(self):
self.ParametersTab.setCurrentIndex(3)
def keyPressEvent(self, e):
if e.key() == QtCore.Qt.Key_Escape:
self.close()
def closeEvent(self, event):
reply = QMessageBox()
reply = QMessageBox.question(
self, "Message",
"Are you sure you want to finish Parameter definitions and run the model ? ",
QMessageBox.Yes | QMessageBox.No | QMessageBox.Cancel,
QMessageBox.Yes)
if reply == QMessageBox.Yes:
dbProject.commit()
dbProject.close()
cfg.Calculate = True
event.accept()
elif reply == QMessageBox.Cancel:
dbProject.commit()
dbProject.close()
cfg.Calculate = False
event.accept()
else:
pass
def StrToBoolean (xStr):
if xStr in ('OK', 'Ok', 'ok',
'True' , 'true' , 'Yes', 'yes','1'): stb = True
elif xStr in ('False', 'false', 'No' , 'no' ,'0'): stb = False
else: stb = None
return stb
# open the window
gpWindow = gParamClass(None)
gpWindow.show()
MyApp.exec_()
#Create New Simulation Record
with conn:
str_sql = "SELECT seq FROM SQLITE_SEQUENCE where name = 'Simulation'"
IdSimCursor = conn.cursor()
IdSimCursor.execute(str_sql)
IdSimList = IdSimCursor.fetchone()
idSimulation = IdSimList[0] + 1
cfg.gvDic['idSimulation'] = idSimulation
IdSimCursor.close()
cfg.gvDic['SimulationTitle'] = str(idSimulation)+"-"+str(cfg.gvDic['SimulationTitle'])
cfg.gvDic['idSimulation'] = idSimulation
str_sql = "insert into Simulation (SimName, SimDescription, SimDate, SimIterations) "\
+"values ("\
+ "'"+str(cfg.gvDic['SimulationTitle'])+"' , "\
+ "'"+str(cfg.gvDic['SimulationDesc']) +"' , "\
+ " datetime('now') , "\
+ str(cfg.OptParam.mcIterations) + " )"
cursorSim = conn.cursor()
cursorSim.execute(str_sql)
cursorSim.close()
conn.commit()
precio = rec.indexOf("precio")
#datos de la consulta
while consulta.next():
linea = consulta.value(codigo)+"\t"+formatear(consulta.value(cantidad))+"\t"+\
str(consulta.value(ud))+"\t"+consulta.value(resumen)+"\t"+formatear(consulta.value(precio))+"\t"+\
formatear(consulta.value(cantidad)*consulta.value(precio))
tabp = P(
stylename=Instancia.Estilos("Tabuladores Hoja Listados Normal"))
teletype.addTextToElement(tabp, linea)
documento.text.addElement(tabp)
return documento
if __name__ == "__main__":
datos = [
"sdmed", "localhost", "5432", "sdmed", "sdmed", "CENZANO",
"/home/david/.sdmed/python/plugins/C1/", ""
]
db = QtSql.QSqlDatabase('QPSQL')
db.setDatabaseName(datos[0])
db.setHostName(datos[1])
db.setPort(int(datos[2]))
db.setUserName(datos[3])
db.setPassword(datos[4])
obra = datos[5]
location = datos[6]
nombreficheroguardar = datos[7]
if db.open():
imprimir(db, obra) #*datos
CONFIG["REDIS_HOST"] = redis_ip
CONFIG["REDIS_PASSWORD"] = redis_password
except:
print("Your redis credentials are wrong, please check them and try again")
sys.exit()
should_config_db = input(
"Should I configure the db too (is unsure, say n) [y/N] > ")
if should_config_db.lower() == "y":
db_host = input("Database host > ")
db_user = input("Database username > ")
db_password = getpass.getpass("Database password > ")
db_name = input("Database name > ")
database = QtSql.QSqlDatabase("QPSQL")
database.setHostName(db_host)
database.setUserName(db_user)
database.setPassword(db_password)
database.setDatabaseName(db_name)
if not database.open():
print(
"Your postgres credentials are wrong, please check them and try again"
)
sys.exit()
rs.set("DB_HOST", db_host)
rs.set("USERNAME", db_user)
rs.set("PASSWORD", db_password)
rs.set("DBNAME", db_name)
def __init__(self, parent=None, db=QtSql.QSqlDatabase()):
QtSql.QSqlTableModel.__init__(self, parent, db)
def __init__(self, db=QtSql.QSqlDatabase()):
'''保存所有tableview中的列信息'''
self._list = []
self.db = db
def onActivated(self, text): ##用来实现combobox关联的函数
if text == 'select league':
self.league_name = ''
return
self.league_name = text
# 先连接至分析数据库获取上次所用分析表的时间
db = QtSql.QSqlDatabase().addDatabase("QMYSQL")
db.setDatabaseName("match_" + text + "_analysis")
db.setHostName("127.0.0.1") # set address
db.setUserName("root")
# set user name
db.setPassword("")
# set user pwd
if not db.open():
# 打开失败
return db.lastError()
print("连接至 match_", text, "_analysis success!")
# 创建QsqlQuery对象，用于执行sql语句
query = QtSql.QSqlQuery()
build_table = (
"CREATE TABLE IF NOT EXISTS "
' %s '
""
"(event_id VARCHAR(20) NOT NULL PRIMARY KEY,"
"host_name VARCHAR(20) NOT NULL,"
"guest_name VARCHAR(20) NOT NULL,"
"handicap_name VARCHAR(20) NOT NULL,"
"start_time VARCHAR(20) NOT NULL,"
"host_price_net_support INT(4) NOT NULL DEFAULT 0,"
"host_volume_net_support INT(4) NOT NULL DEFAULT 0,"
"host_volume_price_net_support INT(4) NOT NULL DEFAULT 0,"
"is_end INT(4) DEFAULT 0,"
"handicap_result FLOAT(4)," # 9 表示未知
"last_updatetime VARCHAR(20))")
query.exec(build_table % 'analysis_result')
# 查询出当前数据库中的所有表名
query.exec('SELECT * FROM analysis_result')
query.next()
# 保存最后分析用的表上的时间，以便之后分析跳过用过的表
if query.size() > 0:
last_load_data_time = time.mktime(
time.strptime(query.value(10), '%Y-%m-%d %H:%M:%S'))
else:
last_load_data_time = 0
db.close()
# 连接数据库
db = QtSql.QSqlDatabase().addDatabase("QMYSQL")
db.setDatabaseName("match_" + text)
db.setHostName("127.0.0.1") # set address
db.setUserName("root") # set user name
db.setPassword("") # set user pwd
# 打开数据库
if not db.open():
# 打开失败
return db.lastError()
print("连接至 match_", text, "success!")
# 保存比赛分析结果的字典，用event_id 映射单场比赛
match_analysis_result = {}
# 创建QsqlQuery对象，用于执行sql语句
query = QtSql.QSqlQuery()
# 查询出当前数据库中的所有表名
query.exec('SHOW TABLES FROM match_' + text)
query.next()
# 保存上次满足条件查询到的表单数据(列表)
prev_table_info = []
# 遍历整个数据库的表名
for i in range(query.size()):
# query.value(0) 为当前查询的表名
# 保存当前表的记录时间(北京时间)
current_table_record_time_list = query.value(0).split('_')
current_table_record_time = datetime.datetime.strptime(
current_table_record_time_list[2] +
current_table_record_time_list[3], '%Y%m%d%H%M')
# 如果当前表格时间小于last_load_data_time，则跳过:
# pdb.set_trace()
print('当前表的记录时间：', current_table_record_time, '当前last_load_time:',
last_load_data_time)
if time.mktime(current_table_record_time.timetuple()
) < last_load_data_time:
query.next()
continue
# 是否已经找到满足条件的表格
satisfy_condition = True
# 根据表名上的时间判断是否读取该表数据
# 目前限制在五天内的数据表
now = datetime.datetime.now()
time_offset = now - current_table_record_time
# 如果当前时间比开赛时间过去了 5 天，就不再拉取该表数据
if int(time_offset.days) > 5:
satisfy_condition = False
# 开始查询满足时间段的表格
if satisfy_condition:
table_query = QtSql.QSqlQuery()
print('开始分析数据:', query.value(0))
# 读取 比赛ID，主队名称，客队名称，成交量，是否正在进行
# 主队让球盘口，主队卖盘价格1，主队卖盘1注单
# 客队卖盘价格1，客队卖盘1注单
table_query.exec(
'select event_id,host_name,guest_name,volume,if_running,handicap_name,'
'handicap_host_win_back_price1,handicap_host_win_back_price1_amount,'
'handicap_guest_win_back_price1,handicap_guest_win_back_price1_amount,start_time'
' from ' + query.value(0))
table_query.next()
# 遍历单场比赛
for k in range(table_query.size()):
# if_running = 1 表示比赛正在进行，这里跳过分析
if table_query.value(4) == 1:
continue
# 单场比赛的字典
prev_table_info_match = {}
# 给当前比赛在结果字典中分配一个记录
if not table_query.value(
0) in match_analysis_result.keys():
match_analysis_result[table_query.value(0)] = {}
match_analysis_result[table_query.value(
0)]['event_id'] = table_query.value(0)
match_analysis_result[table_query.value(
0)]['start_time'] = table_query.value(10)
match_analysis_result[table_query.value(
0)]['handicap_name'] = table_query.value(5)
match_analysis_result[table_query.value(
0)]['host_name'] = table_query.value(1)
match_analysis_result[table_query.value(
0)]['host_price_support'] = 0
match_analysis_result[table_query.value(
0)]['host_volume_support'] = 0
match_analysis_result[table_query.value(
0)]['host_volume_price_support'] = 0
match_analysis_result[table_query.value(
0)]['guest_name'] = table_query.value(2)
match_analysis_result[table_query.value(
0)]['guest_price_support'] = 0
match_analysis_result[table_query.value(
0)]['guest_volume_support'] = 0
match_analysis_result[table_query.value(
0)]['guest_volume_price_support'] = 0
# 如果prev_table_info list存在数据
# 存储prev list中是否已经保存有当前比赛数据
prev_exit_event_id = False
if len(prev_table_info) > 0:
# 遍历保存的上一个表信息
for prev_match_info in prev_table_info:
# 可能price会变成0 ，那就跳过这场比赛
if table_query.value(6) == 0 or table_query.value(
8) == 0:
continue
# 如果比赛ID相同说明是同一场比赛
if prev_match_info[
'event_id'] == table_query.value(0):
# 说明prev list中已经存在当前比赛数据，之后只有更新其中的value即可
prev_exit_event_id = True
# 记录时间差 以second为单位
table_record_time_offset = (
current_table_record_time -
prev_match_info['table_record_time']
).seconds
# 投注量变化
volume_change = table_query.value(
3) - prev_match_info['volume']
# 分别处理两表handicap_name , 并记录两者盘口是否相等
if prev_match_info[
'handicap_name'] == table_query.value(
5):
identical_handicap = True
# 盘口相等可以进行水位比较
handicap_host_price_change = table_query.value(
6) - prev_match_info[
'handicap_host_win_back_price1']
handicap_guest_price_change = table_query.value(
8) - prev_match_info[
'handicap_guest_win_back_price1']
# price有可能会出现0 等等异常值，要做一下特殊判断
if abs(handicap_host_price_change) > 1:
handicap_host_price_change = 0
elif abs(handicap_guest_price_change) > 1:
handicap_guest_price_change = 0
else:
identical_handicap = False
# 根据字符串盘口返回float型，方便比较
prev_handicap_value = handicap2float(
prev_match_info['handicap_name'])
current_handicap_value = handicap2float(
table_query.value(5))
# 记录最新盘口变化
handicap_change = current_handicap_value - prev_handicap_value
# core 判断算法
# 成交量增加量占之前比例，用来判断这段时间是否有大额投注
if prev_match_info['volume'] != 0:
volume_add_ratio = volume_change / prev_match_info[
'volume']
else:
volume_add_ratio = 0
# 暂定成》10% 为大额投注
volume_heavy_chip_rate = 0.10
# 重点水位判定，要根据赔率值所在区间有不同变化
host_heavy_price_change = 0.05
guest_heavy_price_change = 0.05
if table_query.value(6) >= 2:
host_heavy_price_change = 0.08
if table_query.value(8) >= 2:
guest_heavy_price_change = 0.08
# 限定时间差在150s内, 且满足大额投注条件
limit_time = 150
if table_record_time_offset <= limit_time:
# 用盘口变化、价格变化、投注比三种方法来判断倾向
# 当盘口相同
if identical_handicap:
# 水位变化超过限定水位
if abs(handicap_host_price_change
) >= host_heavy_price_change:
# 根据变化方向判断倾向
if handicap_host_price_change < 0:
match_analysis_result[
table_query.value(
0
)]['host_price_support'] += 1
print(
'由于主队水位大幅下降因此 host_price_support + 1 ',
table_query.value(1))
else:
match_analysis_result[
table_query.value(
0
)]['host_price_support'] -= 1
print(
'由于主队水位大幅上升因此 host_price_support - 1 ',
table_query.value(2))
if abs(handicap_guest_price_change
) >= guest_heavy_price_change:
# 根据变化方向判断倾向
if handicap_guest_price_change < 0:
match_analysis_result[
table_query.value(
0
)]['guest_price_support'] += 1
print(
'由于客队水位大幅下降因此 guest_price_support + 1 ',
table_query.value(1))
else:
match_analysis_result[
table_query.value(
0
)]['guest_price_support'] -= 1
print(
'由于客队水位大幅上升因此 guest_price_support - 1 ',
table_query.value(2))
# 水位变化判断结束
# 必须满足成交比和之前成交额达到2000
if prev_match_info[
'volume'] > 2000 and volume_add_ratio >= volume_heavy_chip_rate:
if handicap_host_price_change < 0:
if abs(
handicap_host_price_change
) < host_heavy_price_change:
match_analysis_result[
table_query.value(
0
)]['host_volume_support'] -= 1
print(
'由于大量投注压向主队 host_volume_support - 1 ',
table_query.value(1))
else:
match_analysis_result[
table_query.value(
0
)]['host_volume_price_support'] += 1
print(
'由于大额压水投注压向主队 host_volume_price_support + 1 ',
table_query.value(1))
if handicap_guest_price_change < 0:
if abs(
handicap_guest_price_change
) < guest_heavy_price_change:
match_analysis_result[
table_query.value(
0
)]['guest_volume_support'] -= 1
print(
'由于大量投注压向客队 guest_volume_support - 1 ',
table_query.value(2))
else:
match_analysis_result[
table_query.value(
0
)]['guest_volume_price_support'] += 1
print(
'由于大额压水投注压向主队 guest_volume_price_support + 1 ',
table_query.value(1))
# else:
# # 暂时不根据盘口变化判断倾向
# print('盘口变化为：', handicap_change)
# if handicap_change > 0:
# match_analysis_result[table_query.value(0)]['host_support'] += 1
# # print('由于主队升盘因此 host_suppoer + 1')
# else:
# match_analysis_result[table_query.value(0)]['guest_support'] += 1
# # print('由于客队升盘因此 guest_suppoer + 1')
# 将当前数据替换prev_list 中prev table的数据
prev_match_info[
'table_record_time'] = current_table_record_time
prev_match_info['volume'] = table_query.value(
3)
prev_match_info[
'handicap_host_win_back_price1'] = table_query.value(
6)
prev_match_info[
'handicap_host_win_back_price1_amount'] = table_query.value(
7)
prev_match_info[
'handicap_guest_win_back_price1'] = table_query.value(
8)
prev_match_info[
'handicap_guest_win_back_price1_amount'] = table_query.value(
9)
break
if not prev_exit_event_id:
# 将当前查询到的数据保存到prev_table_info 方便下次分析
prev_table_info_match['event_id'] = table_query.value(
0)
prev_table_info_match[
'table_record_time'] = current_table_record_time
prev_table_info_match['host_name'] = table_query.value(
1)
prev_table_info_match[
'guest_name'] = table_query.value(2)
prev_table_info_match['volume'] = table_query.value(3)
prev_table_info_match[
'if_running'] = table_query.value(4)
prev_table_info_match[
'handicap_name'] = table_query.value(5)
prev_table_info_match[
'handicap_host_win_back_price1'] = table_query.value(
6)
prev_table_info_match[
'handicap_host_win_back_price1_amount'] = table_query.value(
7)
prev_table_info_match[
'handicap_guest_win_back_price1'] = table_query.value(
8)
prev_table_info_match[
'handicap_guest_win_back_price1_amount'] = table_query.value(
9)
prev_table_info.append(prev_table_info_match)
table_query.next()
# 记录下最后拉取表的时间
# 先转化为时间戳再比较
# pdb.set_trace()
ans_time = time.mktime(current_table_record_time.timetuple())
print('当前数据表的记录时间2：', current_table_record_time,
'当前last_load_time:', last_load_data_time)
if last_load_data_time == 0 or last_load_data_time < ans_time:
last_load_data_time = ans_time
query.next()
# 查询结束
db.close()
print('断开查询数据库')
# 连接记录分析数据库
reord_db = QtSql.QSqlDatabase().addDatabase("QMYSQL")
reord_db.setDatabaseName("match_" + text + "_analysis")
reord_db.setHostName("127.0.0.1") # set address
reord_db.setUserName("root")
# set user name
reord_db.setPassword("")
# set user pwd
# 打开数据库
if not reord_db.open():
# 打开失败
return reord_db.lastError()
print("连接至 match_", text, "_analysis success!")
for key in match_analysis_result:
# 获取之前保存的数据信息
saved_info_query = QtSql.QSqlQuery()
# 查询出相应的比赛
saved_info_query.exec(
"SELECT * FROM analysis_result WHERE event_id=" +
match_analysis_result[key]['event_id'])
# 如果有当前比赛就直接更新数据
if saved_info_query.size() > 0:
saved_info_query.next()
recent_saved_info_query = QtSql.QSqlQuery()
host_price_net_support = saved_info_query.value(
5) + match_analysis_result[key][
"host_price_support"] - match_analysis_result[key][
"guest_price_support"]
host_volume_net_support = saved_info_query.value(
6) + match_analysis_result[key][
"host_volume_support"] - match_analysis_result[key][
"guest_volume_support"]
host_volume_price_net_support = saved_info_query.value(
7) + match_analysis_result[key][
"host_volume_price_support"] - match_analysis_result[
key]["guest_volume_price_support"]
last_updatetime = time.strftime(
"%Y-%m-%d %H:%M:%S", time.localtime(last_load_data_time))
recent_saved_info_query.exec(
"UPDATE analysis_result SET host_price_net_support = " +
str(host_price_net_support) +
", host_volume_net_support = " +
str(host_volume_net_support) +
", host_volume_price_support = " +
str(host_volume_price_net_support) +
", last_updatetime = '%s' WHERE event_id='%s'" %
(last_updatetime, match_analysis_result[key]['event_id']))
# recent_saved_info_query.exec("UPDATE analysis_result SET host_price_net_support = "+str(host_price_net_support)+", host_volume_net_support = "+str(host_volume_net_support)+", last_updatetime = "+last_updatetime+" WHERE event_id="+match_analysis_result[key]['event_id'])
print('update 数据库成功')
# 如果没有当前比赛就插入数据
else:
insert_table = (
"INSERT INTO analysis_result VALUES"
"('%s','%s','%s','%s','%s',%d,%d,%d,%d,%d,'%s')")
recent_saved_info_query = QtSql.QSqlQuery()
recent_saved_info_query.exec(
insert_table %
(match_analysis_result[key]['event_id'],
match_analysis_result[key]['host_name'],
match_analysis_result[key]['guest_name'],
match_analysis_result[key]['handicap_name'],
match_analysis_result[key]['start_time'],
match_analysis_result[key]['host_price_support'] -
match_analysis_result[key]['guest_price_support'],
match_analysis_result[key]['host_volume_support'] -
match_analysis_result[key]['guest_volume_support'],
match_analysis_result[key]['host_volume_price_support'] -
match_analysis_result[key]['guest_volume_price_support'],
0, 9,
time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(last_load_data_time))))
print('insert 数据库成功')
if not reord_db.commit():
reord_db.rollback()
# 获取保存的数据信息
get_saved_info_query = QtSql.QSqlQuery()
get_saved_info_query.exec("SELECT * FROM analysis_result")
get_saved_info_query.next()
print('开始打印分析结果：')
# 先清空所有表项
self.tableWidget.clearContents()
# 设置行数
self.tableWidget.setRowCount(get_saved_info_query.size())
row_count = 0
for i in range(get_saved_info_query.size()):
# 循环填入数据
col_count = 0
for j in range(self.tableWidget.columnCount()):
if col_count == 0:
cnt = '%s' % (utc2local(
datetime.datetime.strptime(
get_saved_info_query.value(4),
'%Y-%m-%dT%H:%M:%SZ')).strftime('%Y-%m-%d %H:%M'))
elif col_count == 1:
cnt = '%s' % (get_saved_info_query.value(1))
elif col_count == 2:
cnt = '%s' % (get_saved_info_query.value(3))
elif col_count == 3:
cnt = '%s' % (get_saved_info_query.value(2))
elif col_count == 4:
cnt = '%d' % (get_saved_info_query.value(5))
elif col_count == 5:
cnt = '%d' % (get_saved_info_query.value(6))
elif col_count == 6:
cnt = '%d' % (get_saved_info_query.value(7))
elif col_count == 7:
cnt = '%s' % (get_saved_info_query.value(10))
newItem = QtWidgets.QTableWidgetItem(cnt)
self.tableWidget.setItem(row_count, col_count, newItem)
col_count += 1
get_saved_info_query.next()
row_count += 1
self.tableWidget.resizeColumnsToContents()
self.tableWidget.setSortingEnabled(True)
reord_db.close()
print('断开记录分析数据库')
def __init__(self):
super(self.__class__, self).__init__()
self.DIALMAX = 400
self.DIALMIN = 0
self.DIALINITVALUE = 500
self.LCDINITVALUE = 1.0
self.LCDDIGITSCOUNT = 5
self.DIALLCDREL = 100
self.SETTINGSFILE = "LocalLaplaceOpenCVQt.ini"
self.DBNAME = "LocalLaplaceOpenCVQt2.db3"
self.kSigmaR = 0.8
self.kAlpha = 0.2
self.kBeta = 0.5
self.inputFileName = ""
self.ostatnioOtwieraneMax = 4
self.ostatnioOtwieraneActions = []
self.pixmapObrazWejsciowy = QtGui.QPixmap()
self.pixmapObrazWyjsciowy = QtGui.QPixmap()
self.sliderScale = QtWidgets.QSlider(QtCore.Qt.Horizontal)
self.skalaObrazuWejsciowego = 100
self.LPLevels = 0
self.timer = QtCore.QElapsedTimer()
self.timertotal = QtCore.QElapsedTimer()
self.db = QtSql.QSqlDatabase()
self.dbOpenOk = False
self.setWindowIcon(QtGui.QIcon('LocalLaplaceOpenCVQtPy.ico'))
self.setupUi(self)
self.groupScale.setLayout(self.create_slider_skalowanie())
self.sliderScale.valueChanged.connect(self.slider_scale_value_changed)
self.actionOtworz.triggered.connect(self.otworz_plik_wejsciowy)
self.actionZapiszSesje.triggered.connect(
self.action_zapisz_sesje_triggered)
self.wowe = None
self.actionWyjscie.triggered.connect(self.show_wyjscie)
self.wwy = None
self.quality_results = {
'BRISQUE': None,
'MSE': None,
'PSNR': None,
'SSIM': None,
'GMSD': None
}
# self.quality_maps = {'MSE': None, 'PSNR': None, 'SSIM': None, 'GMSD': None}
self.dialAlpha.setNotchesVisible(True)
self.dialAlpha.setMaximum(self.DIALMAX)
self.dialAlpha.setMinimum(self.DIALMIN)
self.dialAlpha.setValue(self.DIALINITVALUE)
self.lcdNumberAlpha.display(self.LCDINITVALUE)
self.lcdNumberAlpha.setNumDigits(self.LCDDIGITSCOUNT)
self.dialAlpha.valueChanged.connect(self.wyswietl_lcd_alpha)
self.dialBeta.setNotchesVisible(True)
self.dialBeta.setMaximum(self.DIALMAX)
self.dialBeta.setMinimum(self.DIALMIN)
self.dialBeta.setValue(self.DIALINITVALUE)
self.lcdNumberBeta.display(self.LCDINITVALUE)
self.lcdNumberBeta.setNumDigits(self.LCDDIGITSCOUNT)
self.dialBeta.valueChanged.connect(self.wyswietl_lcd_beta)
self.dialSigmaR.setNotchesVisible(True)
self.dialSigmaR.setMaximum(self.DIALMAX)
self.dialSigmaR.setMinimum(self.DIALMIN)
self.dialSigmaR.setValue(self.DIALINITVALUE)
self.lcdNumberSigmaR.display(self.LCDINITVALUE)
self.lcdNumberSigmaR.setNumDigits(self.LCDDIGITSCOUNT)
self.dialSigmaR.valueChanged.connect(self.wyswietl_lcd_sigmar)
self.pushButtonDefault.clicked.connect(
self.push_button_default_clicked)
self.pushButtonApply.clicked.connect(self.push_button_apply_clicked)
self.InitOstatnioOtwierane()
self.InitDB()
def populate(self, d):
#Can be done using table w delegate checkboxes?
#different modls if connected to db or not?
#handling models:
self.attinds = dict()
if type(d) == type(QtSql.QSqlDatabase()): #if fed connection
conn = d
conn.open()
#categories - do as tree through qproxymodel?http://www.qtcentre.org/threads/12064-QSql%2AModel-QTreeView-make-a-tree
self.catq = QtSql.QSqlQuery(conn)
self.catq.exec("SELECT name FROM categories")
self.catmo = chkmdl(QtSql.QSqlQueryModel)
self.catmo.setQuery(self.catq)
#self.catmo.setHeaderData('Categories')
#table
self.qry = QtSql.QSqlQuery(conn)
self.qtxt = "SELECT products.Price,products.Name,products.Barcode,categories.name FROM products INNER JOIN categories WHERE products.CategoryIndex=categories.id"
self.attinds = dict([[i, dbdict[i]['index']] for i in dbdict])
self.qry.exec(self.qtxt)
try:
del self.modl
except:
pass
self.modl = QtSql.QSqlQueryModel()
self.modl.setQuery(self.qry)
if self.modl.rowCount(
) != 0: #if all this worked to add data to model, save filename
self.cash.sv(dbpath=d.databaseName())
else: #if this db file didn't have the right formatting
pass
conn.close()
elif type(d) == type(pandas.DataFrame()): #fed csv tree
try:
del self.modl
except:
pass
self.modl = self.CSVTableModel(data=d, parent=self)
atts = {"price", "description", "category", "barcode"}
self.catmo = chkmdl(QtCore.QStringListModel)
self.catmo.setStringList(
list(d[csvdict['category']['label']].fillna('None').unique()))
for i in atts:
self.attinds[i] = list(self.modl.tree).index(
csvdict[i]['label'])
if self.modl.rowCount(
) != 0: #if all this worked to add data to model, save filename
print(self.modl.path)
self.cash.sv(csvpath=self.modl.path)
else:
ValueError
#handling views:
# self.catview=QListView()
# self.catview.setModel(self.catmo)
# self.catbx.setView(self.catview)
self.catbx.setModel(self.catmo)
self.proxy = QtCore.QSortFilterProxyModel()
self.proxy.setSourceModel(self.modl)
#self.view.setHorizontalHeader(['barcode','description','price'])
self.view.setModel(self.proxy)
self.view.setAlternatingRowColors(True)
self.view.SelectionMode(2)
self.vieworder = {
'barcode': 0,
'price': 1,
'description': 2,
'category': None
}
self.view.setSelectionModel(self.sel)
self.view.selectionModel().selectionChanged.connect(self.selectfn)
self.sel.setModel(self.proxy)
try: #sets inds if model is loaded
for i in self.attinds:
self.view.showColumn(self.attinds[i])
#self.proxy.setHeaderData(self.attinds[i],i)
self.proxy.setFilterKeyColumn(self.attinds['category'])
self.view.hideColumn(self.attinds['category'])
except:
pass
# self.view.clicked.connect(lambda:print())
self.selectlist = dict()
x = self.modl.columnCount()
y = self.modl.rowCount()
#print(x,y)
for ii in range(y):
for jj in range(x):
#QStyleOptionView
self.selectlist[ii] = QCheckBox()
def __init__(self):
self.connection = QtSql.QSqlDatabase()
