def readSQL(sql_text, params=None, named=False, check_unique=False): if params is None: params = [] query = QSqlQuery(db_connection()) query.setForwardOnly(True) if not query.prepare(sql_text): logging.error( f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' | '{params}'" ) return None for param in params: query.bindValue(param[0], param[1]) if not query.exec_(): logging.error( f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' | '{params}'" ) return None if query.next(): res = readSQLrecord(query, named=named) if check_unique and query.next(): logging.warning( g_tr('DB', "More than 1 record matched SQL query ") + f"'{sql_text}' | '{params}'") return None # More then one record in result when only one expected return res else: return None
def executeSQL(db, sql_text, params = [], forward_only = True): query = QSqlQuery(db) query.setForwardOnly(forward_only) if not query.prepare(sql_text): logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None for param in params: query.bindValue(param[0], param[1]) if not query.exec_(): logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None return query
def getValue(self, key): get_query = QSqlQuery(self.db) get_query.setForwardOnly(True) get_query.prepare("SELECT value FROM settings WHERE name=:key") value = None get_query.bindValue(":key", key) if not get_query.exec_(): logging.fatal(f"Failed to get settings for key='{key}'") return value if get_query.next(): value = get_query.value(0) return value
def readSQL(db, sql_text, params = []): query = QSqlQuery(db) query.setForwardOnly(True) if not query.prepare(sql_text): logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None for param in params: query.bindValue(param[0], param[1]) if not query.exec_(): logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None if query.next(): return readSQLrecord(query) else: return None
class OperationsModel(QAbstractTableModel): PAGE_SIZE = 100 COL_TYPE = 0 COL_SUBTYPE = 1 COL_ID = 2 COL_TIMESTAMP = 3 COL_ACCOUNT_ID = 4 COL_ACCOUNT = 5 COL_NUMBER_PEER = 6 COL_ASSET_ID = 7 COL_ASSET = 8 COL_ASSET_NAME = 9 COL_NOTE = 10 COL_NOTE2 = 11 COL_AMOUNT = 12 COL_QTY = 13 COL_PRICE = 14 COL_FEE_TAX = 15 COL_TOTAL_AMOUNT = 16 COL_TOTAL_QTY = 17 COL_CURRENCY = 18 COL_RECONCILED = 19 _columns = [ " ", g_tr('OperationsModel', "Timestamp"), g_tr('OperationsModel', "Account"), g_tr('OperationsModel', "Notes"), g_tr('OperationsModel', "Amount"), g_tr('OperationsModel', "Balance"), g_tr('OperationsModel', "Currency") ] _tables = { TransactionType.Action: "actions", TransactionType.Dividend: "dividends", TransactionType.Trade: "trades", TransactionType.Transfer: "transfers", TransactionType.CorporateAction: "corp_actions" } OperationSign = { (TransactionType.Action, -1): ('—', CustomColor.DarkRed), (TransactionType.Action, +1): ('+', CustomColor.DarkGreen), (TransactionType.Dividend, DividendSubtype.Dividend): ('Δ', CustomColor.DarkGreen), (TransactionType.Dividend, DividendSubtype.BondInterest): ('%', CustomColor.DarkGreen), (TransactionType.Trade, -1): ('S', CustomColor.DarkRed), (TransactionType.Trade, +1): ('B', CustomColor.DarkGreen), (TransactionType.Transfer, TransferSubtype.Outgoing): ('<', CustomColor.DarkBlue), (TransactionType.Transfer, TransferSubtype.Incoming): ('>', CustomColor.DarkBlue), (TransactionType.Transfer, TransferSubtype.Fee): ('=', CustomColor.DarkRed), (TransactionType.CorporateAction, CorporateAction.Merger): ('âƒ', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.SpinOff): ('⎇', CustomColor.DarkGreen), (TransactionType.CorporateAction, CorporateAction.Split): ('á—•', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.SymbolChange): ('🡘', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.StockDividend): ('Δ\ns', CustomColor.DarkGreen) } CorpActionNames = { CorporateAction.SymbolChange: g_tr('OperationsModel', "Symbol change {old} -> {new}"), CorporateAction.Split: g_tr('OperationsModel', "Split {old} {before} into {after}"), CorporateAction.SpinOff: g_tr('OperationsModel', "Spin-off {after} {new} from {before} {old}"), CorporateAction.Merger: g_tr('OperationsModel', "Merger {before} {old} into {after} {new}"), CorporateAction.StockDividend: g_tr('OperationsModel', "Stock dividend: {after} {new}") } def __init__(self, parent_view): super().__init__(parent_view) self._view = parent_view self._amount_delegate = None self._data = [] self._row_count = 0 self._query = QSqlQuery(db_connection()) self._begin = 0 self._end = 0 self._account = 0 self._text_filter = '' self.prepareData() def rowCount(self, parent=None): return len(self._data) def columnCount(self, parent=None): return len(self._columns) def canFetchMore(self, index): return len(self._data) < self._row_count def fetchMore(self, index): new_size = len(self._data) + self.PAGE_SIZE new_size = new_size if new_size < self._row_count else self._row_count self.beginInsertRows(index, len(self._data), new_size - 1) i = 0 indexes = range(self._query.record().count()) while (i < self.PAGE_SIZE) and self._query.next(): values = list(map(self._query.value, indexes)) self._data.append(values) i += 1 self.endInsertRows() def headerData(self, section, orientation, role=Qt.DisplayRole): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self._columns[section] return None def get_operation(self, row): if (row >= 0) and (row < len(self._data)): return self._data[row][self.COL_TYPE], self._data[row][self.COL_ID] else: return [0, 0] def data(self, index, role=Qt.DisplayRole): if not index.isValid(): return None if role == Qt.DisplayRole: return self.data_text(index.row(), index.column()) if role == Qt.FontRole and index.column() == 0: font = QFont() font.setBold(True) return font if role == Qt.ForegroundRole: return self.data_foreground(index.row(), index.column()) if role == Qt.TextAlignmentRole: if index.column() == 0: return Qt.AlignCenter if index.column() == 4 or index.column() == 5: return Qt.AlignRight return Qt.AlignLeft def data_text(self, row, column): if column == 0: try: return self.OperationSign[self._data[row][self.COL_TYPE], self._data[row][self.COL_SUBTYPE]][0] except KeyError: return '?' elif column == 1: if (self._data[row][self.COL_TYPE] == TransactionType.Trade) or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \ or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction): return f"{datetime.utcfromtimestamp(self._data[row][self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}\n# {self._data[row][self.COL_NUMBER_PEER]}" else: return datetime.utcfromtimestamp(self._data[row][ self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S') elif column == 2: if self._data[row][self.COL_TYPE] == TransactionType.Action: return self._data[row][self.COL_ACCOUNT] elif (self._data[row][self.COL_TYPE] == TransactionType.Trade) \ or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \ or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction): return self._data[row][self.COL_ACCOUNT] + "\n" + self._data[ row][self.COL_ASSET_NAME] elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: if self._data[row][self.COL_SUBTYPE] == TransferSubtype.Fee: return self._data[row][self.COL_ACCOUNT] elif self._data[row][ self.COL_SUBTYPE] == TransferSubtype.Outgoing: return self._data[row][ self.COL_ACCOUNT] + " -> " + self._data[row][ self.COL_NOTE2] elif self._data[row][ self.COL_SUBTYPE] == TransferSubtype.Incoming: return self._data[row][ self.COL_ACCOUNT] + " <- " + self._data[row][ self.COL_NOTE2] elif column == 3: if self._data[row][self.COL_TYPE] == TransactionType.Action: note = self._data[row][self.COL_NUMBER_PEER] if self._data[row][self.COL_ASSET] != '' and self._data[row][ self.COL_FEE_TAX] != 0: note += "\n" + g_tr('OperationsModel', "Rate: ") if self._data[row][self.COL_FEE_TAX] >= 1: note += f"{self._data[row][self.COL_FEE_TAX]:.4f} " \ f"{self._data[row][self.COL_ASSET]}/{self._data[row][self.COL_CURRENCY]}" else: note += f"{1/self._data[row][self.COL_FEE_TAX]:.4f} " \ f"{self._data[row][self.COL_CURRENCY]}/{self._data[row][self.COL_ASSET]}" return note elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: rate = 0 if self._data[row][ self.COL_PRICE] == '' else self._data[row][self.COL_PRICE] if self._data[row][self.COL_CURRENCY] != self._data[row][ self.COL_NUMBER_PEER]: if rate != 0: if rate > 1: return self._data[row][ self. COL_NOTE] + f" [1 {self._data[row][self.COL_CURRENCY]} = {rate:.4f} {self._data[row][self.COL_NUMBER_PEER]}]" elif rate < 1: rate = 1 / rate return self._data[row][ self. COL_NOTE] + f" [{rate:.4f} {self._data[row][self.COL_CURRENCY]} = 1 {self._data[row][self.COL_NUMBER_PEER]}]" else: return self._data[row][self.COL_NOTE] else: return g_tr('OperationsModel', "Error. Zero rate") else: return self._data[row][self.COL_NOTE] elif self._data[row][self.COL_TYPE] == TransactionType.Dividend: return self._data[row][self.COL_NOTE] + "\n" + g_tr( 'OperationsModel', "Tax: ") + self._data[row][self.COL_NOTE2] elif self._data[row][self.COL_TYPE] == TransactionType.Trade: if self._data[row][self.COL_FEE_TAX] != 0: text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n({self._data[row][self.COL_FEE_TAX]:.2f}) " else: text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n" text = text + self._data[row][ self.COL_NOTE] if self._data[row][self.COL_NOTE] else text return text elif self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: basis = 100.0 * self._data[row][self.COL_PRICE] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.StockDividend: qty_after = self._data[row][ self.COL_QTY] - self._data[row][self.COL_AMOUNT] else: qty_after = self._data[row][self.COL_QTY] text = self.CorpActionNames[self._data[row][ self.COL_SUBTYPE]].format( old=self._data[row][self.COL_ASSET], new=self._data[row][self.COL_NOTE], before=self._data[row][self.COL_AMOUNT], after=qty_after) if self._data[row][ self.COL_SUBTYPE] == CorporateAction.SpinOff: text += f"; {basis:.2f}% " + g_tr( 'OperationsModel', " cost basis") + "\n" + self._data[row][self.COL_NOTE2] return text else: assert False elif column == 4: if self._data[row][self.COL_TYPE] == TransactionType.Trade: return [ self._data[row][self.COL_AMOUNT], self._data[row][self.COL_QTY] ] elif self._data[row][self.COL_TYPE] == TransactionType.Dividend: return [ self._data[row][self.COL_AMOUNT], -self._data[row][self.COL_FEE_TAX] ] elif self._data[row][self.COL_TYPE] == TransactionType.Action: if self._data[row][self.COL_ASSET] != '': return [ self._data[row][self.COL_AMOUNT], self._data[row][self.COL_PRICE] ] else: return [self._data[row][self.COL_AMOUNT]] elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: return [self._data[row][self.COL_AMOUNT]] elif self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: if self._data[row][ self. COL_SUBTYPE] == CorporateAction.SpinOff or self._data[ row][self. COL_SUBTYPE] == CorporateAction.StockDividend: return [ None, self._data[row][self.COL_QTY] - self._data[row][self.COL_AMOUNT] ] else: return [ -self._data[row][self.COL_AMOUNT], self._data[row][self.COL_QTY] ] else: assert False elif column == 5: upper_part = f"{self._data[row][self.COL_TOTAL_AMOUNT]:,.2f}" if self._data[ row][self.COL_TOTAL_AMOUNT] != '' else "-.--" lower_part = f"{self._data[row][self.COL_TOTAL_QTY]:,.2f}" if self._data[ row][self.COL_TOTAL_QTY] != '' else '' if self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: qty_before = self._data[row][ self.COL_AMOUNT] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.SpinOff else 0 qty_after = self._data[row][self.COL_TOTAL_QTY] if self._data[row][ self. COL_SUBTYPE] == CorporateAction.StockDividend else self._data[ row][self.COL_QTY] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.StockDividend: text = f"\n{qty_after:,.2f}" if qty_after != '' else "\n-.--" else: text = f"{qty_before:,.2f}\n{qty_after:,.2f}" return text elif self._data[row][ self.COL_TYPE] == TransactionType.Action or self._data[ row][self.COL_TYPE] == TransactionType.Transfer: return upper_part else: return upper_part + "\n" + lower_part elif column == 6: if self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: asset_before = self._data[row][ self.COL_ASSET] if self._data[row][ self. COL_SUBTYPE] != CorporateAction.StockDividend else "" return f" {asset_before}\n {self._data[row][self.COL_NOTE]}" else: if self._data[row][self.COL_ASSET] != '': return f" {self._data[row][self.COL_CURRENCY]}\n {self._data[row][self.COL_ASSET]}" else: return f" {self._data[row][self.COL_CURRENCY]}" else: assert False def data_foreground(self, row, column): if column == 0: try: return QBrush( self.OperationSign[self._data[row][self.COL_TYPE], self._data[row][self.COL_SUBTYPE]][1]) except KeyError: return QBrush(CustomColor.LightRed) if column == 5: if self._data[row][self.COL_RECONCILED] == 1: return QBrush(CustomColor.Blue) def configureView(self): self._view.setColumnWidth(0, 10) self._view.setColumnWidth( 1, self._view.fontMetrics().width("00/00/0000 00:00:00") * 1.1) self._view.setColumnWidth(2, 300) self._view.horizontalHeader().setSectionResizeMode( 3, QHeaderView.Stretch) font = self._view.horizontalHeader().font() font.setBold(True) self._view.horizontalHeader().setFont(font) self._amount_delegate = ColoredAmountsDelegate(self._view) self._view.setItemDelegateForColumn(4, self._amount_delegate) self._view.verticalHeader().setSectionResizeMode( QHeaderView.ResizeToContents) @Slot() def setAccount(self, account_id): if self._account != account_id: self._account = account_id self.prepareData() def getAccount(self): return self._account @Slot() def setDateRange(self, start, end=0): self._begin = start if end: self._end = end else: self._end = QDate.currentDate().endOfDay(Qt.UTC).toSecsSinceEpoch() self.prepareData() @Slot() def filterText(self, filter): if filter: self._text_filter = f" AND (num_peer LIKE '%{filter}%' COLLATE NOCASE "\ f"OR note LIKE '%{filter}%' COLLATE NOCASE "\ f"OR note2 LIKE '%{filter}%' COLLATE NOCASE "\ f"OR asset LIKE '%{filter}%' COLLATE NOCASE "\ f"OR asset_name LIKE '%{filter}%' COLLATE NOCASE)" else: self._text_filter = '' self.prepareData() def update(self): self.prepareData() def get_operation_type(self, row): if (row >= 0) and (row < len(self._data)): return self._data[row][self.COL_TYPE] else: return 0 def reconcile_operation(self, row): if (row >= 0) and (row < len(self._data)): timestamp = self._data[row][self.COL_TIMESTAMP] account_id = self._data[row][self.COL_ACCOUNT_ID] _ = executeSQL( "UPDATE accounts SET reconciled_on=:timestamp WHERE id = :account_id", [(":timestamp", timestamp), (":account_id", account_id)]) self.prepareData() @Slot() def refresh(self): idx = self._view.selectionModel().selection().indexes() self.prepareData() if idx: self._view.setCurrentIndex(idx[0]) def prepareData(self): self._data = [] if self._begin == 0 and self._end == 0: self._row_count = 0 else: count_pfx = "SELECT COUNT(*) " query_pfx = "SELECT * " query_suffix = f"FROM all_operations AS o WHERE o.timestamp>={self._begin} AND o.timestamp<={self._end}" + \ self._text_filter if self._account: query_suffix = query_suffix + f" AND o.account_id = {self._account}" self._row_count = readSQL(count_pfx + query_suffix) self._query.prepare(query_pfx + query_suffix) self._query.setForwardOnly(True) self._query.exec_() self.fetchMore(self.createIndex(0, 0)) self.modelReset.emit() def deleteRows(self, rows): for row in rows: if (row >= 0) and (row < len(self._data)): table_name = self._tables[self._data[row][self.COL_TYPE]] query = f"DELETE FROM {table_name} WHERE id={self._data[row][self.COL_ID]}" _ = executeSQL(query) self.prepareData()
print("error with insert record cartridge", e.lastError().text()) query.finish() query.prepare("insert into good values (null, :name, :count)") list1 = ['Cd', 'Dvd', 'Scanner'] list2 = [9, 4, 7] query.bindValue(':name', list1) query.bindValue(':count', list2) if not query.execBatch(): print("error with batch", e.lastError().text()) query.finish() else: print("Error with create table good", e.lastError().text()) good_select = "select * from good order by goodname" query.prepare(good_select) query.setForwardOnly(True) if query.exec_(): print("Select is done") lst = [] if query.isActive( ): # запрос находится в активном состоянии, т е ранее вызывались методы exec_, execBatch query.first() # Есть еще seek while query.isValid( ): # если внутренний указатель указывает на какую-либо запись lst.append( query.value('goodname') + ": " + str(query.value('goodcount')) + ' шт.') print("Number of record", query.at(), "about record", query.record()) query.next() for p in lst: