def get_new_offer_number(user_id: int) -> int: text = f"SELECT public.get_new_offer_number({user_id})" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record().value(0)
def get_merchandise_record(merchandise_id: int) -> QSqlRecord: text = f"select * from merchandise_view('{date.today()}') where merchandise_id = '{merchandise_id}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record()
def get_var(key: str) -> str: text = f"SELECT value FROM public.vars WHERE key = '{key}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") record = query.record() return record.value("value")
def main(): db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("foo.sqlite") if not db.open(): sys.exit(-1) lib = CDLL(os.path.join(CURRENT_DIR, "libqsqlite.so")) lib.enable_extension(c_void_p(shiboken2.getCppPointer(db.driver())[0])) load_spatialite() query = QSqlQuery() query.exec_("CREATE TABLE my_line(id INTEGER PRIMARY KEY)") query.exec_( """SELECT AddGeometryColumn("my_line","geom" , 4326, "LINESTRING", 2)""" ) polygon_wkt = "POLYGON ((11 50,11 51,12 51,12 50,11 50))" XA = 11 YA = 52 XB = 12 YB = 49 line_wkt = "LINESTRING({0} {1}, {2} {3})".format(XA, YA, XB, YB) query.prepare("""INSERT INTO my_line VALUES (?,GeomFromText(?, 4326))""") query.addBindValue(1) query.addBindValue(line_wkt) query.exec_() query.prepare( """SELECT astext(st_intersection(geom, GeomFromText(?, 4326))) from my_line WHERE st_intersects(geom, GeomFromText(?, 4326))""" ) query.addBindValue(polygon_wkt) query.addBindValue(polygon_wkt) query.exec_() while query.next(): for i in range(query.record().count()): print(query.value(i))
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()
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: print(p) else: print("Error in select") query.finish() window = QtWidgets.QTableView() window.setWindowTitle("QSqlQueryModel") # Create model sqm = QSqlQueryModel(parent=window) sqm.setQuery(good_select) # Задаем заголовки для столбцов модели sqm.setHeaderData(1, QtCore.Qt.Horizontal, "Название") sqm.setHeaderData(2, QtCore.Qt.Horizontal, "Количество")