def modelChanged(self): # Fetch all selected records while self.model().canFetchMore(): self.model().fetchMore() newCount = self.model().rowCount() # Show updated coins count sql = "SELECT count(*) FROM coins" query = QSqlQuery(sql, self.model().database()) query.first() totalCount = query.record().value(0) labelText = self.tr("%d/%d coins") % (newCount, totalCount) self.listCountLabel.setText(labelText) # Restore selected row if self.selectedRowId is not None: idIndex = self.model().fieldIndex('id') startIndex = self.model().index(0, idIndex) indexes = self.proxyModel.match(startIndex, Qt.DisplayRole, self.selectedRowId, 1, Qt.MatchExactly) if indexes: self.selectRow(indexes[0].row()) else: self.selectedRowId = None
def GenerateWeatherDescription(db, weatherCode): categories = [ "Low Cloud Amount", "Visibility Kilometres", "Low Cloud Height", "Weather", "Remarks Mission", "Remarks Weather" ] query = QSqlQuery(db) pos = 0 help = 0 weatherDescription = "" for c in weatherCode: qryStr = "select description from wetter where category = '{0}' and code = '{1}' limit 1".format( categories[pos - help], c) query.exec_(qryStr) query.first() fn = query.value(0) if pos <= 5: weatherDescription += categories[pos] + ': ' + fn if pos < 5: weatherDescription += '\n' else: weatherDescription += '; ' + fn if pos >= 5: help += 1 pos += 1 return weatherDescription
def setKgNameAndCode(self): query = QSqlQuery(self.dbm.db) qryStr = u"select CASE WHEN katastralgemeinde IS NULL AND katastralgemeindenummer IS NULL THEN '--' ELSE katastralgemeindenummer || ' - ' || katastralgemeinde END AS kg FROM fundort WHERE fundortnummer = '{0}'".format( self.siteNumber) query.exec_(qryStr) query.first() self.uiCadastralCommunityEdit.setText(query.value(0))
def loadRecordByKeyAttribute(self, attribute, value): #self.model.setFilter(attribute + " = '" + value + "'") #self.model.select() # self.mapper.toFirst() query = QSqlQuery(self.dbm.db) #qryStr = "select {0} from film where {0} = '{1}' limit 1".format(attribute, value) #qryStr = "SELECT rowid FROM film WHERE {0} = '{1}' limit 1".format(attribute, value) qryStr = "SELECT" \ " (SELECT COUNT(*)" \ " FROM film AS t2" \ " WHERE t2.rowid < t1.rowid" \ " ) + (" \ " SELECT COUNT(*)" \ " FROM film AS t3" \ " WHERE t3.rowid = t1.rowid AND t3.rowid < t1.rowid" \ " ) AS rowNum" \ " FROM film AS t1" \ " WHERE {0} = '{1}'" \ " ORDER BY t1.rowid ASC".format(attribute, value) query.exec_(qryStr) #QMessageBox.warning(None, "Test", str(query.size()) + ',' + str(query.numRowsAffected())) query.first() fn = query.value(0) if fn is not None: self.loadRecordById(fn) return True else: # Film does not exist QMessageBox.warning(None, "Film Nummer", str("Der Film mit der Nummer {0} existiert nicht!".format(value))) return False
def getMappingStats(self): self.imageStatsDict = {} query = QSqlQuery(self.dbm.db) qryStr = "SELECT COUNT(*) AS anzahl_kartiert, MAX(bildnummer_nn) AS letzte_bildnummer, MIN(bildnummer_nn) AS erste_bildnummer FROM luftbild_{0}_cp WHERE filmnummer = '{1}'".format( self.orientation, self.currentFilmNumber) query.exec_(qryStr) query.first() statFields = [ "anzahl_kartiert", "letzte_bildnummer", "erste_bildnummer" ] if query.value(0) is not None: for key in statFields: idx = query.record().indexOf(key) if query.isNull(idx): self.imageStatsDict[key] = 0 else: self.imageStatsDict[key] = query.value(idx) #QMessageBox.warning(None, u"Film Nummer", u"{0}, {1}, {2}".format(self.imageStatsDict["anzahl_kartiert"],self.imageStatsDict["letzte_bildnummer"],self.imageStatsDict["erste_bildnummer"])) else: self.imageStatsDict["anzahl_kartiert"] = 0 self.imageStatsDict["letzte_bildnummer"] = 0 self.imageStatsDict["erste_bildnummer"] = 0 QMessageBox.warning( None, u"Film Nummer", u"Für den Film mit der Nummer {0} sind noch keine Bilder kartiert!" .format(self.currentFilmNumber))
def isFilm(self, filmNumber): # check if filmNumber is a filmNumber in film Table query = QSqlQuery(self.dbm.db) query.prepare("SELECT weise FROM film WHERE filmnummer = '{0}'".format( filmNumber)) query.exec_() query.first() return query.value(0)
def SiteHasFindspot(db, siteNumber): query = QSqlQuery(db) query.prepare( u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer = '{0}'".format( siteNumber)) query.exec_() query.first() return query.value(0)
def getFolderNameSite(self, siteNumber): query = QSqlQuery(self.dbm.db) #qryStr = u"SELECT trim(katastralgemeinde) || ' ' || trim(katastralgemeindenummer) || '.' || substr('000' || fundortnummer_nn_legacy, -3, 3) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'".format(siteNumber) query.prepare( u"SELECT land || '\\' || CASE WHEN land = 'AUT' THEN replace(replace(replace(replace(lower(trim(katastralgemeinde)), '.',''), '-', ' '), '(', ''), ')', '') || ' ' ELSE '' END || substr('000000' || fundortnummer_nn, -6, 6) AS folderName FROM fundort f WHERE f.fundortnummer='{0}'" .format(siteNumber)) query.exec_() query.first() return query.value(0)
def SitesHaveFindspots(db, siteNumbers): sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers) query = QSqlQuery(db) query.prepare( u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format( sites)) query.exec_() query.first() return query.value(0)
def videoInDb(self, video_id): ''' Checks if db contains video_id ''' query = QSqlQuery() query.prepare("""SELECT count(1) FROM videos WHERE video_id = (?)""") query.addBindValue(video_id) query.exec() query.first() return query.value(0)
def playlistInDb(self, playlist_id): ''' Checks if db contains playlist_id ''' query = QSqlQuery() query.prepare( """SELECT count(1) FROM playlists WHERE playlist_id = (?)""") query.addBindValue(playlist_id) query.exec() query.first() return query.value(0)
def channelInDb(self, channel_id): ''' Checks if db contains channel_id ''' query = QSqlQuery() query.prepare( """SELECT count(1) FROM channels WHERE channel_id = (?)""") query.addBindValue(channel_id) query.exec() query.first() return query.value(0)
def getCountryCode(self): query = QSqlQuery(self.dbm.db) #qryStr = "SELECT code FROM osm_boundaries WHERE within(MakePoint({0}, {1}, 4312), geometry)".format(self.imageCenterPoint.x(), self.imageCenterPoint.y()) qryStr = "SELECT code FROM osm_boundaries WHERE intersects(Transform(MakePoint({0}, {1}, 4312), 4326), geometry) AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'osm_boundaries' AND search_frame = Transform(MakePoint({0}, {1}, 4312), 4326))".format( self.imageCenterPoint.x(), self.imageCenterPoint.y()) query.exec_(qryStr) query.first() if query.value(0) is None: return 'INT' else: return query.value(0)
def getChannelTitle(self, channel_id): if self.channelInDb(channel_id): query = QSqlQuery() query.prepare( """SELECT channel_title FROM channels WHERE channel_id = (?)""" ) query.addBindValue(channel_id) query.exec() query.first() return query.value(0) else: return ""
def statusBarUpdate(self): displayedVids = self.videoTable.model().rowCount() totalVids = 0 query = QSqlQuery("SELECT count(*) FROM videos") query.exec() query.first() totalVids = query.value(0) msg = f"[{displayedVids} of {totalVids} videos" if self.videoTableHasSelection(): msg += f", {len(self.videoTable.selectionModel().selectedRows(0))} selected" msg += "]" self.statusBar.showMessage(msg)
def DbHasTable(db, table): query = QSqlQuery(db) query.prepare( "SELECT name FROM sqlite_master WHERE type='table' AND name='{0}'". format(table)) query.exec_() query.first() #QMessageBox.information(None, "info", "{}".format(query.value(0) == table)) if query.value(0) == table: return True else: return False
def IsFilm(db, filmNumber): """ Checks if filmNumber is a filmNumber in film Table :param db: Database :param filmNumber: 10 digit filmNumber :return: """ query = QSqlQuery(db) query.prepare(u"SELECT COUNT(*) FROM film WHERE filmnummer = '{0}'".format( filmNumber)) query.exec_() query.first() return query.value(0)
def tagInDb(self, tag_name, yt_tags=False): ''' Checks if db contains tag ''' table_name = "yt_tags" if yt_tags else "tags" query = QSqlQuery() query.prepare( """SELECT count(1) FROM {} WHERE tag_name = (?)""".format( table_name)) query.addBindValue(tag_name) query.exec() query.first() return query.value(0)
def get_info(self, image_date): query = QSqlQuery(self.db) query.setForwardOnly(True) query.prepare( 'SELECT copyright_info FROM copyright WHERE image_date = ?;') query.addBindValue(image_date) if not query.exec_(): self.error.emit('Error getting copyright info', query.lastError().text()) return '' query.first() copyright_info = query.value(0).toString() return copyright_info
def replay_info(self, uid): ret = {} ret["game_end"] = time.time() with DbContext(self.db): query = QSqlQuery(self.db) queryStr = (""" SELECT game_featuredMods.gamemod, gameType, filename, gameName, host, login, playerId, AI, team FROM `game_stats` LEFT JOIN game_player_stats ON `game_player_stats`.`gameId` = game_stats.id LEFT JOIN table_map ON `game_stats`.`mapId` = table_map.id LEFT JOIN login ON login.id = `game_player_stats`.`playerId` LEFT JOIN game_featuredMods ON `game_stats`.`gameMod` = game_featuredMods.id WHERE game_stats.id = {} """.format(uid)) query.exec_(queryStr) if query.size() == 0: return None RV = self.ReplayInfoQueryValues ret["num_players"] = query.size() query.first() ret["featured_mod"] = str(_qv(query, RV.GAMEMOD)) ret["game_type"] = int(_qv(query, RV.GAMETYPE) or 0) ret["title"] = str(_qv(query, RV.GAMENAME).encode('utf-8')) ret["featured_mod_versions"] = self._featured_mod_versions( _qv(query, RV.GAMEMOD)) mapname = str(_qv(query, RV.FILENAME)) ret["mapname"] = os.path.splitext(os.path.basename(mapname))[0] ret["complete"] = True query.previous() teams = {} while next(query): team = int(_qv(query, RV.TEAM)) name = str(_qv(query, RV.LOGIN)) isAi = int(_qv(query, RV.AI)) if int(_qv(query, RV.HOST)) == int(_qv(query, RV.PLAYER_ID)): ret["host"] = name if isAi == 0: if team not in teams: teams[team] = [] teams[team].append(name) ret["teams"] = teams return ret
def __init__(self, collection): super(CollectionDescription, self).__init__(collection) self.db = collection.db if 'description' not in self.db.tables(): self.create(collection) query = QSqlQuery("SELECT * FROM description", self.db) query.first() record = query.record() self.title = record.value('title') self.description = record.value('description') self.author = record.value('author')
def __init__(self, collection): super().__init__(collection) self.db = collection.db if 'description' not in self.db.tables(): self.create(collection) query = QSqlQuery("SELECT * FROM description", self.db) query.first() record = query.record() self.title = record.value('title') self.description = record.value('description') self.author = record.value('author')
def get_item_progress(item_id): query = QSqlQuery() query.prepare( "SELECT rename, derivatives, copy, complete FROM items WHERE id=:item_id" ) query.bindValue(":item_id", item_id) query.exec_() query.first() progress = { "rename": query.value(0), "derivatives": query.value(1), "copy": query.value(2), "complete": query.value(3) } return progress
def getFilmInfo(self): filmFields = [ "filmnummer_hh_jjjj_mm", "filmnummer_nn", "filmnummer", "anzahl_bilder", "form1", "form2", "weise", "kammerkonstante", "datum_ersteintrag", "datum_aenderung", "projekt", "copyright" ] self.currentFilmInfoDict = {} query = QSqlQuery(self.dbm.db) qryStr = "SELECT {0} FROM film WHERE filmnummer = '{1}'".format( ", ".join(filmFields), self.currentFilmNumber) query.exec_(qryStr) query.first() if query.value(0) is not None: for key in filmFields: value = query.value(query.record().indexOf(key)) self.currentFilmInfoDict[key] = value if self.currentFilmInfoDict["weise"] == u"schräg": self.orientation = "schraeg" self.isOblique = True else: self.orientation = "senk" self.isOblique = False # QMessageBox.information(None, "kkInfo0", f'type: {type(self.currentFilmInfoDict["kammerkonstante"])}') if isinstance( self.currentFilmInfoDict["kammerkonstante"], str ) and not self.currentFilmInfoDict["kammerkonstante"]: QMessageBox.warning( None, u"Film Nummer", u"Der senkrecht Film mit der Nummer {0} hat keine Kammerkonstante (Wird für die Berechnung des Bildmaßstabs und in Folge des Footprints benötigt.) Geben Sie im Film Dialog die Kammerkonstante an, um korrekte Footprints zu erhalten." .format(self.currentFilmNumber)) return False else: if not self.currentFilmInfoDict["kammerkonstante"] > 0: QMessageBox.warning( None, u"Film Nummer", u"Der senkrecht Film mit der Nummer {0} hat eine Kammerkonstante (={1}), die für die Berechnung des Bildmaßstabs und in Folge des Footprints ungeeignet ist! Geben Sie im Film Dialog die Kammerkonstante an, um korrekte Footprints zu erhalten. Falls Bildmittelpunkte bereits gesetzt sind (mit einem Maßstab = 0) müssen auch diese neu kartiert werden!" .format( self.currentFilmNumber, self.currentFilmInfoDict["kammerkonstante"])) return False return True else: QMessageBox.warning( None, u"Film Nummer", u"Der Film mit der Nummer {0} existiert nicht!".format( self.currentFilmNumber)) return False
def getImage(self, img_id): query = QSqlQuery(self.database()) query.prepare("SELECT image FROM images WHERE id=?") query.addBindValue(img_id) query.exec_() if query.first(): return query.record().value(0)
def getPreviewImage(self, img_id): query = QSqlQuery(self.database()) query.prepare("SELECT image FROM images WHERE id=?") query.addBindValue(img_id) query.exec_() if query.first(): return query.record().value(0)
def getImageTitle(self, img_id): query = QSqlQuery(self.database()) query.prepare("SELECT title FROM photos WHERE id=?") query.addBindValue(img_id) query.exec_() if query.first(): return query.record().value(0)
def isExist(self, record): fields = [ 'title', 'value', 'unit', 'country', 'period', 'year', 'mint', 'mintmark', 'type', 'series', 'subjectshort', 'status', 'material', 'quality', 'paydate', 'payprice', 'saller', 'payplace', 'saledate', 'saleprice', 'buyer', 'saleplace', 'variety', 'obversevar', 'reversevar', 'edgevar' ] filterParts = [field + '=?' for field in fields] sqlFilter = ' AND '.join(filterParts) db = self.database() query = QSqlQuery(db) query.prepare("SELECT count(*) FROM coins WHERE id<>? AND " + sqlFilter) query.addBindValue(record.value('id')) for field in fields: query.addBindValue(record.value(field)) query.exec_() if query.first(): count = query.record().value(0) if count > 0: return True return False
def get_total_records(self): try: q = QSqlQuery("SELECT count(*) FROM connections", self.db) if q.exec_() and q.first(): r = q.value(0) except Exception as e: print("db, get_total_records() error:", e)
def __exportOneQuery(self, viewColumns, query: QtSql.QSqlQuery, sheetName='newsheet'): vcs = viewColumns self.__curRow = 0 # 添加一个sheet页 sheet = self.workbook.add_sheet(sheetName) for col, vc in enumerate(vcs): sheet.write(self.__curRow, col, vc.header, self.style_header) self.__curRow += 1 bz = query.first() aligns = self.__getAligns(vcs) chars = [0] * len(vcs) while bz: for col, vc in enumerate(vcs): rec = query.record() vStr = vc.displayString(rec.value(col)) l = len(vStr) if vStr else 0 if l > chars[col]: chars[col] = l tempAlign = self.style_detail.alignment self.style_detail.alignment = aligns[col] sheet.write(self.__curRow, col, vStr, self.style_detail) self.style_detail.alignment = tempAlign self.__curRow += 1 bz = query.next() for col, vc in enumerate(vcs): sheet.col(col).width = 256 * chars[col]
def _load(self): self.clear() count = 0 query = QSqlQuery(self.db) query.prepare("SELECT COUNT(DISTINCT position) " "FROM treeparam WHERE pageid=?") query.addBindValue(self.pageId) query.exec_() if query.first(): count = query.record().value(0) if count: for _ in range(count): self._params.append([]) query = QSqlQuery(self.db) query.prepare("SELECT * FROM treeparam WHERE pageid=?") query.addBindValue(self.pageId) query.exec_() while query.next(): record = query.record() position = record.value('position') fieldId = record.value('fieldid') self._params[position].append(self.fields.field(fieldId))
def addNewSharding(self, siteNumber): self.initalLoad = True self.siteNumber = siteNumber # get new sharding number query = QSqlQuery(self.dbm.db) qryStr = "SELECT CASE WHEN max(begehung) IS NULL THEN 1 ELSE max(begehung)+1 END begehungNeu FROM begehung WHERE fundortnummer='{0}'".format( self.siteNumber) query.exec_(qryStr) query.first() self.shardingNumber = query.value(0) self.model = QSqlRelationalTableModel(self, self.dbm.db) self.model.setTable("begehung") self.model.setFilter("fundortnummer='{0}'".format(self.siteNumber)) res = self.model.select() #self.model.submitAll() while (self.model.canFetchMore()): self.model.fetchMore() row = self.model.rowCount() #QMessageBox.information(None, "begehung", "{0}".format(row)) self.model.insertRow(row) #QMessageBox.information(None, "begehung", "{0}".format(self.model.rowCount())) self.setupMapper() self.mapper.toLast() self.addMode = True self.startEditMode() # self.mapper.submit() # self.model.insertRow(row) # self.mapper.setCurrentIndex(row) self.uiSiteNumberEdit.setText(self.siteNumber) self.uiShardingNumberEdit.setText(str(self.shardingNumber)) now = QDate.currentDate() self.uiShardingDate.setDate(now) self.setKgNameAndCode() #QMessageBox.warning(None, self.tr(u"Neu"), self.tr(u"{0}, {1}".format(siteNumber,nn))) self.initalLoad = False
def getSort(self): query = QSqlQuery(self.db) query.prepare("SELECT sort FROM sections WHERE name=?") query.addBindValue(self.name) query.exec_() if query.first(): data = query.record().value(0) if data: self.sort = bool(data) else: self.sort = False query.clear() return self.sort
def _load(self): query = QSqlQuery(self.db) query.prepare("SELECT * FROM statistics WHERE pageid=?") query.addBindValue(self.pageId) query.exec_() if query.first(): record = query.record() self.__setitem__('showed', bool(record.value('showed'))) self.__setitem__('chart', record.value('chart')) self.__setitem__('fieldid', record.value('fieldid')) self.__setitem__('subfieldid', record.value('subfieldid')) self.__setitem__('items', record.value('items')) self.__setitem__('period', record.value('period')) self.__setitem__('color', bool(record.value('color'))) self.setAutoSave(True)
def getImage(self, img_id): """Read the image data/fileName from the DB.""" query = QSqlQuery(self.database()) query.prepare("SELECT image FROM photos WHERE id=?") query.addBindValue(img_id) query.exec_() if query.first(): imageOrFile = query.record().value(0) if imageOrFile[:7] == 'file://': try: imageFile = open(imageOrFile[7:], "rb") except IOError: imageFile = open(self.badFileNameImage, "rb") image = QtCore.QByteArray(imageFile.read()) imageFile.close() return image, imageOrFile else: return imageOrFile, None
def __init__(self, id_, model): QtCore.QObject.__init__(self, model) self.model = model self.db = model.database() self.id_ = id_ self.file = None self.url = None self.workingDir = model.workingDir self.collectionName = model.collectionName self.changed = False self.cleared = False self.image = QtGui.QImage() query = QSqlQuery(self.db) query.prepare("SELECT * FROM photos WHERE id=?") query.addBindValue(self.id_) query.exec_() if query.first(): self.file = query.record().value('file') self.url = query.record().value('url')
def isExist(self, record): fields = ['title', 'value', 'unit', 'country', 'period', 'year', 'mint', 'mintmark', 'type', 'series', 'subjectshort', 'status', 'material', 'quality', 'paydate', 'payprice', 'seller', 'payplace', 'saledate', 'saleprice', 'buyer', 'saleplace', 'variety', 'obversevar', 'reversevar', 'edgevar'] filterParts = [field + '=?' for field in fields] sqlFilter = ' AND '.join(filterParts) db = self.database() query = QSqlQuery(db) query.prepare("SELECT count(*) FROM coins WHERE id<>? AND " + sqlFilter) query.addBindValue(record.value('id')) for field in fields: query.addBindValue(record.value(field)) query.exec_() if query.first(): count = query.record().value(0) if count > 0: return True return False
def merge(self, fileName): db = QSqlDatabase.addDatabase('QSQLITE', 'merge') db.setDatabaseName(fileName) if not db.open(): print(db.lastError().text()) QMessageBox.critical(self.parent(), self.tr("Merge collections"), self.tr("Can't open collection")) return settings = CollectionSettings(db) if self.settings['Type'] != version.AppName: QMessageBox.critical(self.parent(), self.tr("Merge collections"), self.tr("Collection %s in wrong format %s") % (fileName, version.AppName)) if int(settings['Version']) != CollectionSettings.Default['Version']: QMessageBox.critical(self.parent(), self.tr("Merge collections"), self.tr("Source collection %s in old format %d.\n(Try to open it before merging.)") % (fileName, int(settings['Version']))) return if settings['Password'] != cryptPassword(): dialog = PasswordDialog(settings, self.parent()) result = dialog.exec_() if result == QDialog.Rejected: return False query = QSqlQuery("SELECT COUNT(id) FROM coins", db) query.first() count = query.record().value(0) progressDlg = Gui.ProgressDialog(self.tr("Inserting records"), self.tr("Cancel"), count, self.parent()) big_query = QSqlQuery("""SELECT coins.title AS title, "value", "unit", "country", "year", "period", "mint", "mintmark", "issuedate", "type", "series", "subjectshort", "status", "material", "fineness", "shape", "diameter", "thickness", "weight", "grade", "edge", "edgelabel", "obvrev", "quality", "mintage", "dateemis", "catalognum1", "catalognum2", "catalognum3", "catalognum4", "rarity", "price1", "price2", "price3", "price4", "variety", "obversevar", "reversevar", "edgevar", "paydate", "payprice", "totalpayprice", "saller", "payplace", "payinfo", "saledate", "saleprice", "totalsaleprice", "buyer", "saleplace", "saleinfo", "note", "obversedesign", "obversedesigner", "reversedesign", "reversedesigner", "subject", "defect", "storage", "features", "createdat", "updatedat", "quantity", "url", "barcode", coins.image AS image, images.image AS images_image, obverseimg, obverseimg.image AS obverseimg_image, obverseimg.title AS obverseimg_title, reverseimg, reverseimg.image AS reverseimg_image, reverseimg.title AS reverseimg_title, edgeimg, edgeimg.image AS edgeimg_image, edgeimg.title AS edgeimg_title, photo1, photo1.image AS photo1_image, photo1.title AS photo1_title, photo2, photo2.image AS photo2_image, photo2.title AS photo2_title, photo3, photo3.image AS photo3_image, photo3.title AS photo3_title, photo4, photo4.image AS photo4_image, photo4.title AS photo4_title FROM coins LEFT OUTER JOIN images ON coins.image=images.id LEFT OUTER JOIN photos AS obverseimg ON coins.obverseimg=obverseimg.id LEFT OUTER JOIN photos AS reverseimg ON coins.reverseimg=reverseimg.id LEFT OUTER JOIN photos AS edgeimg ON coins.edgeimg=edgeimg.id LEFT OUTER JOIN photos AS photo1 ON coins.photo1=photo1.id LEFT OUTER JOIN photos AS photo2 ON coins.photo2=photo2.id LEFT OUTER JOIN photos AS photo3 ON coins.photo3=photo3.id LEFT OUTER JOIN photos AS photo4 ON coins.photo4=photo4.id""", db) _model = QSqlTableModel(db=self.db) _model.setTable('coins') _model.select() while big_query.next(): progressDlg.step() if progressDlg.wasCanceled(): break record = big_query.record() record.setNull('id') # remove ID value from record for field in ['obverseimg', 'reverseimg', 'edgeimg', 'photo1', 'photo2', 'photo3', 'photo4']: value = record.value(field + '_image') if value: query = QSqlQuery(self.db) query.prepare("INSERT INTO photos (title, image) VALUES (?, ?)") query.addBindValue(record.value(field + '_title')) query.addBindValue(value) query.exec_() img_id = query.lastInsertId() else: img_id = None record.setValue(field, img_id) record.remove(record.indexOf(field + '_image')) record.remove(record.indexOf(field + '_title')) value = record.value('images_image') if value: query = QSqlQuery(self.db) query.prepare("INSERT INTO images (image) VALUES (?)") query.addBindValue(value) query.exec_() img_id = query.lastInsertId() else: img_id = None record.setValue('image', img_id) record.remove(record.indexOf('images_image')) _model.insertRecord(-1, record) _model.submitAll() progressDlg.reset() db.close() QMessageBox.warning(self.parent(), self.tr("Merge collections"), self.tr("The application will need to restart now")) self.parent().restart()
def prepareMenu(self): self.listWidget = QListWidget(self) filters = self.filters.copy() appliedValues = [] columnFilters = None revert = False if self.fieldid in filters.keys(): columnFilters = filters.pop(self.fieldid) for filter_ in columnFilters.filters(): if filter_.isRevert(): revert = True appliedValues.append(filter_.value) hasBlanks = False columnType = self.model.columnType(self.fieldid) if self.model.columnName(self.fieldid) == 'year': filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql) query = QSqlQuery(sql, self.db) while query.next(): icon = None if query.record().isNull(0): data = None else: orig_data = query.record().value(0) data = str(orig_data) label = data try: year = int(orig_data) if year < 0: label = "%d BC" % -year except ValueError: pass if not data: hasBlanks = True continue item = CustomSortListWidgetItem() item.setData(Qt.DisplayRole, label) item.setData(Qt.UserRole, data) item.setData(Qt.UserRole + 1, orig_data) if data in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) self.listWidget.sortItems() elif columnType == Type.Text or columnType in Type.ImageTypes: dataFilter = BlankFilter(self.columnName).toSql() blanksFilter = DataFilter(self.columnName).toSql() filtersSql = self.filtersToSql(filters.values()) sql = "SELECT 1 FROM coins WHERE " + filtersSql if filtersSql: sql += ' AND ' # Get blank row count blank_sql = sql + blanksFilter + " LIMIT 1" query = QSqlQuery(blank_sql, self.db) if query.first(): hasBlanks = True # Get not blank row count not_blank_sql = sql + dataFilter + " LIMIT 1" query = QSqlQuery(not_blank_sql, self.db) if query.first(): if columnType in Type.ImageTypes: label = self.tr("(Images)") elif columnType == Type.Text: label = self.tr("(Text)") else: label = self.tr("(Data)") item = QListWidgetItem(label, type=FilterMenuButton.DataType) item.setData(Qt.UserRole, label) item.setCheckState(Qt.Checked) if columnFilters and columnFilters.hasData(): item.setCheckState(Qt.Unchecked) self.listWidget.addItem(item) elif columnType == Type.Status: filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s ORDER BY %s ASC" % ( self.columnName, filtersSql, self.columnName) query = QSqlQuery(sql, self.db) while query.next(): value = query.record().value(0) label = Statuses[value] item = StatusSortListWidgetItem(label) item.setData(Qt.UserRole, value) if self.settings['show_filter_icons']: icon = statusIcon(value) item.setIcon(icon) if value in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) self.listWidget.sortItems() elif columnType == Type.Denomination: filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql) query = QSqlQuery(sql, self.db) while query.next(): icon = None if query.record().isNull(0): data = None else: orig_data = query.record().value(0) data = str(orig_data) label, _ = numberWithFraction(data, self.settings['convert_fraction']) if not data: hasBlanks = True continue item = CustomSortListWidgetItem() item.setData(Qt.DisplayRole, label) item.setData(Qt.UserRole, data) item.setData(Qt.UserRole + 1, orig_data) if data in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) self.listWidget.sortItems() else: filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql) query = QSqlQuery(sql, self.db) while query.next(): icon = None if query.record().isNull(0): data = None else: orig_data = query.record().value(0) data = str(orig_data) if self.settings['show_filter_icons']: icon = self.reference.getIcon(self.columnName, data) if not data: hasBlanks = True continue item = QListWidgetItem() item.setData(Qt.DisplayRole, orig_data) item.setData(Qt.UserRole, data) if icon: item.setIcon(icon) if data in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) self.listWidget.sortItems() item = QListWidgetItem(self.tr("(Select all)"), type=FilterMenuButton.SelectAllType) item.setData(Qt.UserRole, self.tr("(Select all)")) item.setCheckState(Qt.Checked) self.listWidget.insertItem(0, item) if hasBlanks: item = QListWidgetItem(self.tr("(Blanks)"), type=FilterMenuButton.BlanksType) item.setData(Qt.UserRole, self.tr("(Blanks)")) item.setCheckState(Qt.Checked) if revert: if columnFilters and not columnFilters.hasBlank(): item.setCheckState(Qt.Unchecked) else: if columnFilters and columnFilters.hasBlank(): item.setCheckState(Qt.Unchecked) self.listWidget.addItem(item) self.listWidget.itemChanged.connect(self.itemChanged) self.searchBox = QLineEdit(self) self.searchBox.setPlaceholderText(self.tr("Filter")) self.searchBox.textChanged.connect(self.applySearch) self.buttonBox = QDialogButtonBox(Qt.Horizontal) self.buttonBox.addButton(QDialogButtonBox.Ok) self.buttonBox.addButton(QDialogButtonBox.Cancel) self.buttonBox.accepted.connect(self.apply) self.buttonBox.rejected.connect(self.menu().hide) layout = QVBoxLayout() layout.addWidget(self.searchBox) layout.addWidget(self.listWidget) layout.addWidget(self.buttonBox) widget = QWidget(self) widget.setLayout(layout) widgetAction = QWidgetAction(self) widgetAction.setDefaultWidget(widget) self.menu().clear() self.menu().addAction(widgetAction) # Fill items if self.listWidget.count() > 1: self.itemChanged(self.listWidget.item(1))
def prepareMenu(self): self.listWidget = QListWidget(self) item = QListWidgetItem(self.tr("(Select all)"), self.listWidget, FilterMenuButton.SelectAllType) item.setData(Qt.UserRole, self.tr("(Select all)")) item.setCheckState(Qt.PartiallyChecked) self.listWidget.addItem(item) filters = self.filters.copy() appliedValues = [] columnFilters = None revert = False if self.fieldid in filters.keys(): columnFilters = filters.pop(self.fieldid) for filter_ in columnFilters.filters(): if filter_.isRevert(): revert = True appliedValues.append(filter_.value) hasBlanks = False columnType = self.model.columnType(self.fieldid) if columnType == Type.Text or columnType in Type.ImageTypes: dataFilter = BlankFilter(self.columnName).toSql() blanksFilter = DataFilter(self.columnName).toSql() filtersSql = self.filtersToSql(filters.values()) sql = "SELECT count(*) FROM coins WHERE " + filtersSql if filtersSql: sql += ' AND ' # Get blank row count query = QSqlQuery(sql + blanksFilter, self.db) query.first() blanksCount = query.record().value(0) # Get not blank row count query = QSqlQuery(sql + dataFilter, self.db) query.first() dataCount = query.record().value(0) if dataCount > 0: if columnType in Type.ImageTypes: label = self.tr("(Images)") elif columnType == Type.Text: label = self.tr("(Text)") else: label = self.tr("(Data)") item = QListWidgetItem(label, self.listWidget, FilterMenuButton.DataType) item.setData(Qt.UserRole, label) item.setCheckState(Qt.Checked) if columnFilters and columnFilters.hasData(): item.setCheckState(Qt.Unchecked) self.listWidget.addItem(item) if blanksCount > 0: hasBlanks = True elif self.model.columnType(self.fieldid) == Type.Status: filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql) if self.settings['sort_filter']: sql += " ORDER BY %s ASC" % self.columnName query = QSqlQuery(sql, self.db) while query.next(): value = query.record().value(0) label = Statuses[value] item = QListWidgetItem(label, self.listWidget) item.setData(Qt.UserRole, value) if label in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) else: filtersSql = self.filtersToSql(filters.values()) if filtersSql: filtersSql = 'WHERE ' + filtersSql sql = "SELECT DISTINCT %s FROM coins %s" % (self.columnName, filtersSql) if self.settings['sort_filter']: sql += " ORDER BY %s ASC" % self.columnName query = QSqlQuery(sql, self.db) while query.next(): if query.record().isNull(0): label = None else: label = str(query.record().value(0)) if not label: hasBlanks = True continue item = QListWidgetItem(label, self.listWidget) item.setData(Qt.UserRole, label) if label in appliedValues: if revert: item.setCheckState(Qt.Checked) else: item.setCheckState(Qt.Unchecked) else: if revert: item.setCheckState(Qt.Unchecked) else: item.setCheckState(Qt.Checked) self.listWidget.addItem(item) if hasBlanks: item = QListWidgetItem(self.tr("(Blanks)"), self.listWidget, FilterMenuButton.BlanksType) item.setData(Qt.UserRole, self.tr("(Blanks)")) item.setCheckState(Qt.Checked) if revert: if columnFilters and not columnFilters.hasBlank(): item.setCheckState(Qt.Unchecked) else: if columnFilters and columnFilters.hasBlank(): item.setCheckState(Qt.Unchecked) self.listWidget.addItem(item) self.listWidget.itemChanged.connect(self.itemChanged) self.searchBox = QLineEdit(self) self.searchBox.setPlaceholderText(self.tr("Filter")) self.searchBox.textChanged.connect(self.applySearch) self.buttonBox = QDialogButtonBox(Qt.Horizontal) self.buttonBox.addButton(QDialogButtonBox.Ok) self.buttonBox.addButton(QDialogButtonBox.Cancel) self.buttonBox.accepted.connect(self.apply) self.buttonBox.rejected.connect(self.menu().hide) layout = QVBoxLayout(self) layout.addWidget(self.searchBox) layout.addWidget(self.listWidget) layout.addWidget(self.buttonBox) widget = QWidget(self) widget.setLayout(layout) widgetAction = QWidgetAction(self) widgetAction.setDefaultWidget(widget) self.menu().clear() self.menu().addAction(widgetAction) # Fill items self.itemChanged(item)
def getTotalCount(self): sql = "SELECT count(*) FROM coins" query = QSqlQuery(sql, self.db) query.first() return query.record().value(0)