def by_title(cls, keyword, locale=None): """Search a music with its title and localized title. Whitespaces are ignored. """ """# Search a music its title exactly matched to keyword""" music = session.query(cls).filter(cls.title == keyword).first() if music: return music """# Search a music its title includes keyword""" reduced_keyword = keyword.replace(' ', '') reduced_title = func.replace(cls.title, ' ', '') music = session.query(cls).\ filter(or_( cls.title.contains(keyword), reduced_title.contains(reduced_keyword))).\ limit(1).first() if music: return music """# Search a music its localized title includes keyword""" if locale is None: return None reduced_text = func.replace(cls.Localization.text, ' ', '') music_data = session.query(cls.Localization).\ filter(cls.Localization.locale == locale).\ filter(or_( cls.Localization.text.contains(keyword), reduced_text.contains(reduced_keyword))).\ limit(1).first() if music_data is not None: return cls.by(music_id=music_data.music_id)
def get_data(self): logging.debug("These queries will take a few mins to run.") budget_query = self.session.query( self.models.MovieInfo.movie_id, func.max( cast( func.replace( func.replace(self.models.MovieInfo.info, ",", ""), "$", ""), types.Numeric)).label('budget')).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_( [BUDGET_TYPE_ID]), self.models.MovieInfo.info.like('$%'), ).group_by(self.models.MovieInfo.movie_id).subquery() year_query = self.session.query( self.models.MovieInfo.movie_id, func.min(func.substr( self.models.MovieInfo.info, -4)).label('release_year')).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]), self.models.MovieInfo.info.like('USA:%')).group_by( self.models.MovieInfo.movie_id).subquery() budget_alias = aliased(self.models.MovieInfo, budget_query) year_alias = aliased(self.models.MovieInfo, year_query) return self.session.query( budget_query.columns.movie_id, budget_query.columns.budget, year_query.columns.release_year).join( year_alias, year_alias.movie_id == budget_alias.movie_id).distinct( self.models.MovieInfo.movie_id).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), )
def get_data(self): logging.debug("These queries will take a few mins to run.") budget_query = self.session.query( self.models.MovieInfo.movie_id, func.max( cast( func.replace( func.replace(self.models.MovieInfo.info, ",", ""), "$", ""), types.Numeric) ).label('budget') ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([BUDGET_TYPE_ID]), self.models.MovieInfo.info.like('$%'), ).group_by(self.models.MovieInfo.movie_id ).subquery() year_query = self.session.query( self.models.MovieInfo.movie_id, func.min( func.substr(self.models.MovieInfo.info, -4) ).label('release_year') ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), self.models.MovieInfo.info_type_id.in_([RELEASE_DATES_ID]), self.models.MovieInfo.info.like('USA:%') ).group_by(self.models.MovieInfo.movie_id ).subquery() budget_alias = aliased(self.models.MovieInfo, budget_query) year_alias = aliased(self.models.MovieInfo, year_query) return self.session.query( budget_query.columns.movie_id, budget_query.columns.budget, year_query.columns.release_year ).join( year_alias, year_alias.movie_id == budget_alias.movie_id ).distinct( self.models.MovieInfo.movie_id ).filter( self.models.MovieInfo.movie_id.in_(self.movie_ids), )
def run(self): from tg import config from sqlalchemy import create_engine from sqlalchemy.sql import func, update import transaction if self.options.dburl is None: load_config(self.options.config) engine = config['pylons.app_globals'].sa_engine else: engine = create_engine(self.options.dburl) print "updating ", engine if self.command == 'update': oldurl, newurl = self.args if not (oldurl.endswith('/') and newurl.endswith('/')): self.parser.error( "Please end URLs with '/' for proper matching") from bq.data_service.model.tag_model import taggable, values #taggable.bind = engine # pylint: disable=no-value-for-parameter stmt = taggable.update ()\ .values (resource_value = func.replace (taggable.c.resource_value, oldurl, newurl))\ .where(taggable.c.resource_value.like (oldurl + '%')) print stmt engine.execute(stmt) stmt = values.update() \ .values (valstr = func.replace (values.c.valstr, oldurl, newurl))\ .where(values.c.valstr.like (oldurl + '%')) print stmt engine.execute(stmt) # Find all values transaction.commit()
def ajax_html_dirlist(self, args): p = args.get('p', '') p = urllib.unquote_plus(p) # Sanitize path path = re.sub(r"/+", "/", p.rstrip('/')).rstrip('#') # Sanitize physical dir pdir = os.path.realpath(re.sub (r"/+", "/", self.config['rootdir'] + '/' + path)) + '/' # The result has to start with the configured rootdir if pdir[0:len(self.config['rootdir'])] != self.config['rootdir']: pdir = os.path.realpath(self.config['rootdir']) + '/' sql = select([func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1).label('npath')]).\ where(func.substring_index(func.replace(self.db_songtable.c.path, pdir, '' ), '/', 1) != '').distinct().order_by('npath') rows = self.dbc.execute(sql).fetchall() plroute = 'dir_playlist_%s' % self.config['plformat'] if len(rows) == 0: return render_template('playdir.html', path=path, plroute=plroute) else: numpercol = ceil (len(rows) / 2.0) return render_template('dirlist.html', rows=rows, numpercol=numpercol, path=path)
def by_keyword(cls, keyword, locale=None): """Search a music with its title, localized title, search alias. Whitespaces are ignored """ """# Search a music its title or localized text includes keyword""" music = cls.by_title(keyword, locale) if music: return music def music_by_id(music_id): return session.query(cls).filter(music_id=music_id).first() """# Search a music its alias includes keyword""" reduced_keyword = keyword.replace(' ', '') reduced_text = func.replace('text', ' ', '') if locale is not None: data = session.query(cls.Alias).\ filter(or_( cls.Alias.locale == locale, cls.Alias.locale == None)).\ filter(or_( cls.Alias.text.contains(keyword), reduced_text.contains(reduced_keyword))).\ order_by(cls.Alias.priority).\ limit(1).first() if data: return music_by_id(data.music_id) """# Search a music its localized text includes keyword with unmatching locales""" data = session.query(cls.Localization).\ filter(or_( cls.Localization.text.contains(keyword), reduced_text.contains(reduced_keyword))).\ limit(1).first() if data: return music_by_id(data.music_id) """# Search a music its alias includes keyword with unmatching locales """ data = session.query(cls.Alias).\ filter(or_( cls.Alias.text.contains(keyword), reduced_text.contains(reduced_keyword))).\ order_by(cls.Alias.priority).\ limit(1).first() if data: return music_by_id(data.music_id)
async def csv_to_sqlite(conn: Connection) -> None: latest_veekun_commit = "" try: latest_veekun_commit = ( subprocess.run( ["git", "rev-list", "-1", "HEAD", "--", "data/veekun"], capture_output=True, check=True, ) .stdout.decode() .strip() ) db = Database.open("veekun") with db.get_session() as session: db_commit = session.query( # type: ignore # sqlalchemy v.LatestCommit.commit_id ).scalar() if db_commit == latest_veekun_commit: return # database is already up-to-date, skip rebuild except ( subprocess.SubprocessError, # generic subprocess error FileNotFoundError, # git is not available OperationalError, # table does not exist ): pass # always rebuild on error print("Rebuilding veekun database...") open("./veekun.sqlite", "w").close() # truncate database db = Database.open("veekun") v.Base.metadata.create_all(db.engine) tables_classes = { obj.__tablename__: obj for name, obj in inspect.getmembers(v) if inspect.isclass(obj) and obj.__module__ == v.__name__ and hasattr(obj, "__tablename__") } with db.get_session() as session: if latest_veekun_commit: session.add(v.LatestCommit(commit_id=latest_veekun_commit)) for table in v.Base.metadata.sorted_tables: tname = table.key if isfile("./data/veekun/" + tname + ".csv"): with open("./data/veekun/" + tname + ".csv", "r") as f: csv_data = csv.DictReader(f) csv_keys = csv_data.fieldnames if csv_keys is not None: session.bulk_insert_mappings( tables_classes[tname], [dict(i) for i in csv_data] ) if "identifier" in csv_keys: session.query(tables_classes[tname]).update( { tables_classes[tname].identifier: func.replace( tables_classes[tname].identifier, "-", "" ) }, synchronize_session=False, ) print("Done.")