def toDB(self): q = QtSql.QSqlQuery() try: confdate = self.confdate.date().toPyDate() query = """UPDATE LIABILITY SET CONFIRM_DATE='%s' WHERE SUB_CODE='%s'""" % ( confdate, self.subcode) q.exec_(query) #print query QtSql.QSqlDatabase().commit() q = QtSql.QSqlQuery( """SELECT AMOUNT FROM LIABILITY WHERE SUB_CODE='%s'""" % self.subcode) while q.next(): amount = q.value(0).toDouble()[0] ct = CashTrade(book=0, trader=self.user.id, tradeDateTime=datetime.datetime.fromordinal( confdate.toordinal()), amount=amount, comment=u'申购%s' % self.subcode) ct.toDB() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def getTypeData(code): q = QtSql.QSqlQuery( """SELECT SEC_NAME, SEC_TYPE, EXCHANGE FROM SECINFO WHERE SEC_CODE='%s'""" % code) while q.next(): name = q.value(0).toString() insttype = q.value(1).toString() exchange = q.value(2).toString() return name, insttype, exchange infolist = ['sec_name', 'sec_type', 'exch_city'] result = w.wss( unicode(code), infolist, 'tradeDate={0}'.format(format(datetime.datetime.today(), '%Y%m%d'))) if result: if result.ErrorCode == 0: name = result.Data[0][0] insttype = result.Data[1][0] exchange = result.Data[2][0] q = QtSql.QSqlQuery() try: q.exec_( """INSERT INTO SECINFO VALUES ('%s','%s','%s','%s')""" % (code, name, insttype, exchange)) QtSql.QSqlDatabase().commit() return name, insttype, exchange except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def bookToDB(self): q = QtSql.QSqlQuery() try: q.exec_( """INSERT INTO DEALS VALUES (%s,%s,%s,%s)""" % (self.dealID, self.book.id, self.events[0].timestamp, None)) QtSql.QSqlDatabase().commit() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def expsettle(self, trader): q = QtSql.QSqlQuery() try: query = """UPDATE TRADES SET EXP_SETTLED_BY='%s' WHERE TRADE_ID='%s'""" % ( self.settledBy, self.tradeID) q.exec_(query) #print query QtSql.QSqlDatabase().commit() self.settledBy = trader except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def newHol(self): nh = NewHolPanel() if nh.exec_(): date = nh.date.date() name = nh.nameList.currentText() status = nh.statusList.currentText() q = QtSql.QSqlQuery() try: q.exec_("""INSERT INTO HOLIDAYS VALUES ('%s','%s','%s')""" % (date.toPyDate(), name, status)) QtSql.QSqlDatabase().commit() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def deleteTrade(self): rowIndex = self.currentIndex().row() tradeID = self.model().index(rowIndex, 14).data().toString() q = QtSql.QSqlQuery() try: query = """DELETE FROM TRADES WHERE TRADE_ID='%s'""" % tradeID q.exec_(query) #print query QtSql.QSqlDatabase().commit() self.refresh() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def bookToDB(self): try: q = QtSql.QSqlQuery() q.exec_( 'INSERT INTO EVENTS VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' % (self.eventID, self.dealID, self.instID, self.timestamp, self.typeID(), self.by, self.at, self.cancelled, self.comment, self.refDate, self.refAmount, self.refPrice, self.refYield)) QtSql.QSqlDatabase().commit() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def toDB(self): q = QtSql.QSqlQuery() try: query = """INSERT INTO TRADES VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',NULL,NULL)""" % ( self.tradeID, self.book, self.trader, self.tradeDateTime, self.settleDate, self.instCode, self.amount, self.price, int(self.collateralized), self.refTrade, self.refYield, self.settledBy, self.comment, self.maturityDate) q.exec_(query) #print query QtSql.QSqlDatabase().commit() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def __init__(self, ticketTableName, queueTypeId, db=QtSql.QSqlDatabase(), parent=None): super(CEQTicketModel, self).__init__(parent, db) self._queueTypeId = queueTypeId self._currentDate = None # для смены фильтра запросов к базе при смене дня (если класс живет на протяж неск. дней) self._queueId = None self._queueName = u'' self._queueOffice = u'' self._ticketPrefix = u'' self._baseOrgStructureId = None self.setTable(ticketTableName) self.setSort([(u'status', QtCore.Qt.DescendingOrder), (u'summonDatetime', QtCore.Qt.AscendingOrder), (u'idx', QtCore.Qt.AscendingOrder)]) self.setEditStrategy(self.OnManualSubmit) self._statusColumn = self.fieldIndex(u'status') self._summonColumn = self.fieldIndex(u'summonDatetime') self._orgStructureColumn = self.fieldIndex(u'orgStructure_id') self.updateForCurrentDate()
def resetPwd(self, newpwd): hex = hashlib.sha1(newpwd).hexdigest() q = QtSql.QSqlQuery() query = """UPDATE USERS SET PWD='%s', PWD_TEMP=0 WHERE ID=%s""" % (hex, self.id) print query q.exec_(query) QtSql.QSqlDatabase().commit() self.pwd = hex
def cancel(self, user, comment=''): self.by = user.name self.at = datetime.datetime.now() self.comment = comment self.cancelled = True q = QtSql.QSqlQuery() query = """UPDATE EVENTS SET CANCEL='%s' WHERE ID=%s""" % ( 1, self.eventID) q.exec_(query) QtSql.QSqlDatabase().commit()
def sign(self, user, comment=''): self.by = user.name self.at = datetime.datetime.now() if comment: self.comment = comment + ' | ' + self.comment q = QtSql.QSqlQuery() query = """UPDATE EVENTS SET SIGNER='%s', SIGNED_AT='%s', COMMENT='%s' WHERE ID='%s'""" % ( user.name, datetime.datetime.now(), self.comment, self.eventID) q.exec_(query) QtSql.QSqlDatabase().commit()
def removeRow(self): rowIndex = self.currentIndex().row() subcode = self.model().index(rowIndex, 6).data().toString() confdate = self.model().index(rowIndex, 12).data().toString() if confdate.isEmpty(): reply = QtGui.QMessageBox.question( self, u'删除本条记录', u'申请书编号:%s' % subcode, QtGui.QMessageBox.Yes | QtGui.QMessageBox.No, QtGui.QMessageBox.No) if reply == QtGui.QMessageBox.Yes: q = QtSql.QSqlQuery() try: q.exec_("""DELETE FROM LIABILITY WHERE SUB_CODE='%s'""" % subcode) QtSql.QSqlDatabase().commit() self.model().removeRow(rowIndex) self.refresh() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def initPwd(self): import random import string import utils initpwd = ''.join(random.choice(string.ascii_letters) for i in range(6)) hex = hashlib.sha1(initpwd).hexdigest() utils.sendmail('*****@*****.**', [self.email], u'重设CAMP密码', initpwd) q = QtSql.QSqlQuery() query = """UPDATE USERS SET PWD='%s', PWD_TEMP=1 WHERE ID=%s""" % (hex, self.id) q.exec_(query) QtSql.QSqlDatabase().commit() self.pwd = hex
def settle(self, trader): q = QtSql.QSqlQuery() try: query = """UPDATE TRADES SET SETTLED_BY='%s' WHERE TRADE_ID='%s'""" % ( trader, self.tradeID) q.exec_(query) #print query QtSql.QSqlDatabase().commit() self.settledBy = trader ct = CashTrade( self.book, self.trader, datetime.datetime.fromordinal(self.settleDate.toordinal()), -self.amount * self.price, self.instCode[-2:] == 'IB' and 'CASH_IB' or 'CASH_EX', refTrade=self.tradeID, comment=u'交割现金流') ct.toDB() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def toDB(self): code = self.subcode.text() client_type = self.clientType.currentIndex() client_name = self.clientname.currentText() sale_type = self.rbDirect.isChecked() and 'A' or 'B' amount, _ = self.amount.text().toDouble() rtn, _ = self.rtn.text().toDouble() subdate = self.subdate.date().toPyDate() settledate = self.settledate.date().toPyDate() expdate = self.expdate.date().toPyDate() expops = self.expops.currentIndex() + 1 comment = self.comment.text() q = QtSql.QSqlQuery() try: query = """INSERT INTO LIABILITY VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s', '%s')""" % ( code, client_type, client_name, sale_type, amount, rtn, subdate, settledate, expdate, expops, comment, '') q.exec_(query) #print query QtSql.QSqlDatabase().commit() except Exception, e: print e.message QtSql.QSqlDatabase().rollback()
def initDB(self, host='caitcfid.mysql.rds.aliyuncs.com', port=3306, dbname='secs'): self.db = QtSql.QSqlDatabase.addDatabase('QMYSQL') self.db.setHostName(host) self.db.setPort(port) self.db.setDatabaseName(dbname) self.db.setUserName('hewei') self.db.setPassword('wehea1984') if not self.db.open(): raise Exception(u'无法连接数据库') QtSql.QSqlDatabase().setConnectOptions('MYSQL_OPT_RECONNECT=1') self.oss = OSS()
def updateInfo(self): c = self.code.text() if c in self.secinfo: n, t, e = self.secinfo[c] self.secname.setText(n) self.prdtype.setText(t) self.exchange.setText(e) else: gotinfo = False infolist = ['sec_name', 'sec_type', 'exch_city'] result = w.wss( unicode(c), infolist, 'tradeDate={0}'.format(format(self.sysdate, '%Y%m%d'))) if result: if result.ErrorCode == 0: self.secname.setText(result.Data[0][0]) self.prdtype.setText(result.Data[1][0]) self.exchange.setText(result.Data[2][0]) self.secinfo[c] = (self.secname.text(), self.prdtype.text(), self.exchange.text()) q = QtSql.QSqlQuery() try: q.exec_( """INSERT INTO SECINFO VALUES ('%s','%s','%s','%s')""" % (c, self.secname.text(), self.prdtype.text(), self.exchange.text())) QtSql.QSqlDatabase().commit() gotinfo = True except Exception, e: print e.message QtSql.QSqlDatabase().rollback() if not gotinfo: self.secname.setText(u'未知') self.prdtype.setText(u'未知') self.exchange.setText(u'未知')
def uploadOrder(self): fn = QtGui.QFileDialog.getOpenFileName(self, u'选取指令文件', '.') if not fn.isEmpty(): rowIndex = self.currentIndex().row() tradeID = self.model().index(rowIndex, 14).data().toString() s = self.oss.upload_trade_order(str(tradeID), unicode(fn)) if s: q = QtSql.QSqlQuery() query = """UPDATE TRADES SET ORDER_SAVED='%s' WHERE TRADE_ID='%s'""" % ( datetime.datetime.now(), tradeID) q.exec_(query) #print query QtSql.QSqlDatabase().commit() QtGui.QMessageBox.about(self, u'交易指令上传', u'上传成功') else: QtGui.QMessageBox.warning(self, u'交易指令上传', u'上传失败,请联系开发人员解决问题')
def __init__(self, queueTypeId, db=QtSql.QSqlDatabase(), parent=None): super(CEQTicketControlModel, self).__init__(u'EQueueTicket', queueTypeId, db, parent) # self.setEditStrategy(self.OnFieldChange) self._currentIdList = [] self._nextId = QtCore.QVariant() self._prevIdStack = [] # self._summonTimerId = None # self._summonTimeout = 5.0 self._ticketIdToValue = { } #FIXME: atronah: возможно, лучше не хранить дубликаты данных и получать их через rowById self._noUpdateMutex = QtCore.QMutex()
def __init__(self): # Вызов конструктора базового класса QtGui.QDialog.__init__(self) # Подключение описания интерфейса self.__ui = Ui_Dialog() self.__ui.setupUi(self) # Подключение драйвера MySQL self.__db = QtSql.QSqlDatabase("QMYSQL") # Создание пяти моделей представления результатов выборки self.__model = { "host_list": CMyModel(), "file_list": CMyModel(), "sea_res": CMyModel(), "down_list": CMyModel(), "down_finish": CMyModel() } # Создание пяти таблиц графического интерфейса, # каждая из которых связывается со своей # моделью представления результатов выборки self.__ui.host_list.setModel(self.__model["host_list"]) self.__ui.file_list.setModel(self.__model["file_list"]) self.__ui.sea_res.setModel(self.__model["sea_res"]) self.__ui.down_list.setModel(self.__model["down_list"]) self.__ui.down_finish.setModel(self.__model["down_finish"]) self.__server = {} self.__filelist = None # Считывание параметров подключения из конфигурационного файла self.first_config_host = config.host self.first_config_mysql_port = str(config.mysql_port) self.first_config_route_port = str(config.route_port) self.first_config_upload_port = str(config.upload_port) self.first_config_db = config.db self.first_config_user = config.user # Обновление интерфейса self.conf_reset_click()
def __init__(self, queueTypeId, orgStructureId=None, db=QtSql.QSqlDatabase(), parent=None): self._orgStructureId = orgStructureId self._isInProgressDisplay = True self._isWaitersDisplay = True super(CEQTicketViewModel, self).__init__(u'vEQueueTicket', queueTypeId, db, parent) self._valueColumn = self.fieldIndex('value') self._rowLimit = 0 self._baseFont = QtGui.QFont() self._baseFont.setPointSize(64) self._inProgressBrush = QtGui.QBrush( QtGui.QColor( QtCore.Qt.green).lighter()) #, QtCore.Qt.Dense5Pattern) self._summonRowList = [] self._lastInProgressRow = -1 self._isBlink = False self._blinkTimerId = None self._blinkTimeout = 0.5 # seconds self._maxElapsedSummon = QtCore.QTime( 0, # hours 0, # minutes 5, # seconds 0) # mseconds self._alreadySommoningList = []
def __init__(self, user, db, host="localhost", port=None): self.__QByteArray_type = type(QtCore.QByteArray()) self.__db = [] # Создаются два подключения: # один для главного потока программы, # другой для возможно существующего дочернего потока программы for x in range(0, 2): # Загрузка драйвера для целевой СУБД next_db = QtSql.QSqlDatabase("QMYSQL") # Установка параметров подключения к целевому серверу БД next_db.setHostName(host) if port != None: next_db.setPort(port) next_db.setUserName(user) next_db.setDatabaseName(db) # Подключение к целевому серверу БД if not next_db.open(): raise Exception("Ошибка при открытии базы данных") self.__db.append(next_db) # Инициализация объектов класса QSqlQuery, # с помощью которых будут выполнятся различные запросы к БД self.__query = [ QtSql.QSqlQuery(db=self.__db[0]), QtSql.QSqlQuery(db=self.__db[1]) ]
def __init__(self, parent=None, db=QtSql.QSqlDatabase()): super(CSqlTableModel, self).__init__(parent, db) self._sortRuleList = []
def refresh(self): #self.dataModel.query().exec_() if not QtSql.QSqlDatabase().isOpen(): print 'Lost Connection!!!!' self.dataModel.setQuery(self.query)
# # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # import datetime from PyQt4 import QtSql db = QtSql.QSqlDatabase("QSQLITE") db.setDatabaseName("/home/user/.rtcom-eventlogger/el-v1.db") def insert_sms_in_history(sms): global db # Pre num = sms['num'] if not sms['message']: return msg = sms['message'].encode('utf-8').decode('utf-8') group_uid = str(num[-7:]) now = int(datetime.datetime.now().strftime("%s")) db.open()
def closeOnDB(self): q = QtSql.QSqlQuery() query = """UPDATE DEALS SET CLOSE='%s' WHERE ID='%s'""" % ( self.events[-1].timestamp) q.exec_(query) QtSql.QSqlDatabase().commit()
def __init__(self): QMainWindow.__init__(self) self.db = QtSql.QSqlDatabase() #kalau ini perlu, karena mungkin akan sering dipakai oleh method lain di kelas ini self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE") self.model = None #sebenernya ini ga perlu dijadikan variabel object. But its okay self.filename = None