def async_setup(hass: HomeAssistant) -> None: """Set up the history hooks.""" hass.data[STATISTICS_BAKERY] = baked.bakery() hass.data[STATISTICS_META_BAKERY] = baked.bakery() hass.data[STATISTICS_SHORT_TERM_BAKERY] = baked.bakery() def entity_id_changed(event: Event) -> None: """Handle entity_id changed.""" old_entity_id = event.data["old_entity_id"] entity_id = event.data["entity_id"] with session_scope(hass=hass) as session: session.query(StatisticsMeta).filter( (StatisticsMeta.statistic_id == old_entity_id) & (StatisticsMeta.source == DOMAIN)).update( {StatisticsMeta.statistic_id: entity_id}) @callback def entity_registry_changed_filter(event: Event) -> bool: """Handle entity_id changed filter.""" if event.data[ "action"] != "update" or "old_entity_id" not in event.data: return False return True if hass.is_running: hass.bus.async_listen( entity_registry.EVENT_ENTITY_REGISTRY_UPDATED, entity_id_changed, event_filter=entity_registry_changed_filter, )
def __init__(self, database, logLevel='INFO'): self.db = database self.logger = Logger(__name__, level=logLevel) super(BaseListener, self).__init__() self.session = database.session self.bakery = baked.bakery() self.bake_common_queries()
def test_subqueryload_post_context(self): User = self.classes.User Address = self.classes.Address assert_result = [ User( id=7, addresses=[Address(id=1, email_address="*****@*****.**")] ) ] self.bakery = baked.bakery(size=3) bq = self.bakery(lambda s: s.query(User)) bq += lambda q: q.options(subqueryload(User.addresses)) bq += lambda q: q.order_by(User.id) bq += lambda q: q.filter(User.name == bindparam("name")) sess = Session() def set_params(q): return q.params(name="jack") # test that the changes we make using with_post_criteria() # are also applied to the subqueryload query. def go(): result = bq(sess).with_post_criteria(set_params).all() eq_(assert_result, result) self.assert_sql_count(testing.db, go, 2)
def get_tender_by_id(self, tender_id: int): with self.session_scope() as session: bakery = baked.bakery() bq = bakery(lambda s: session.query(Tender)) bq += lambda q: q.filter(Tender.id == tender_id) result = bq(session).params(tender_id=tender_id).all() return result
def get_tenders(self): with self.session_scope() as session: bakery = baked.bakery() bq = bakery(lambda s: session.query(Tender)) bq += lambda q: q.limit(10) result = bq(session).params().all() return result
def cats(data, session): if data: try: attr, order, offset, limit = validate_cats_params(data) if order and not attr: raise ValueError except ValueError: return ( 400, {"status": "Bad request.", "exception": ""}, {"Content-type": "application/json"}, ) bakery = baked.bakery() cat = bakery(lambda session: session.query(Cats)) if attr: sort_func = desc if order == "desc" else asc cat += lambda c: c.order_by(sort_func(getattr(Cats, attr))) if offset: cat += lambda c: c.offset(offset) if limit: cat += lambda c: c.limit(limit) cats = cat(session).all() else: cats = session.query(Cats) cats_list = [] for i in cats: cats_list.extend([i.to_dict()]) return (200, cats_list, {"Content-type": "application/json"})
def test_advanced_examples(): # Specify exactly what to return by accessing the underlying query. print_query( "session(User)['jack'].addresses._query.add_columns(User.id, Address.id)", [(1, 1), (1, 2)], globals()) # If `QueryMakerSession` isn't used, the session can be provided at the end of the query. However, this means the ``.q`` property won't be useful (since it has no assigned session). print_query("User['jack'].to_query(session)", [jack], globals()) # If the `QueryMakerDeclarativeMeta` metaclass wasn't used, this performs the equivalent of ``User['jack']`` manually. print_query("QueryMaker(User)['jack'].to_query(session)", [jack], globals()) # Add to an existing query: first, find the User named jack. q = session.query().select_from(User).filter(User.name == 'jack') # Then ask for the Address for [email protected]. print_query("q.query_maker().addresses['*****@*****.**']", [jack.addresses[0]], globals(), locals()) # Do the same manually (without relying on the `QueryMakerQuery` ``query_maker`` method). print_query("QueryMaker(query=q).addresses['*****@*****.**']", [jack.addresses[0]], globals(), locals()) # `Baked queries <http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html>`_ are supported. bakery = baked.bakery() baked_query = bakery(lambda session: session(User)) baked_query += lambda query: query[User.name == bindparam('username')] # The last item in the query must end with a ``.q``. Note that this doesn't print nicely. Using ``.to_query()`` instead fixes this. baked_query += lambda query: query.q.order_by(User.id).q print_query( "baked_query(session).params(username='******', email='*****@*****.**')", [jack], globals(), locals())
def test_subqueryload_post_context_w_cancelling_event( self, before_compile_nobake_fixture ): User = self.classes.User Address = self.classes.Address assert_result = [ User( id=7, addresses=[Address(id=1, email_address="*****@*****.**")] ) ] self.bakery = baked.bakery(size=3) bq = self.bakery(lambda s: s.query(User)) bq += lambda q: q.options(subqueryload(User.addresses)) bq += lambda q: q.order_by(User.id) bq += lambda q: q.filter(User.name == bindparam("name")) sess = fixture_session() def set_params(q): return q.params(name="jack") # test that the changes we make using with_post_criteria() # are also applied to the subqueryload query. def go(): result = bq(sess).with_post_criteria(set_params).all() eq_(assert_result, result) self.assert_sql_count(testing.db, go, 2)
def test_baked_query(n): """test a baked query of the full entity.""" bakery = baked.bakery() s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery(lambda s: s.query(Customer)) q += lambda q: q.filter(Customer.id == bindparam('id')) q(s).params(id=id_).one()
def get_last_modified_date(self): with self.session_scope() as session: bakery = baked.bakery() max_date_modified = func.max(Tender.date_modified).label('tesr') bq = bakery(lambda s: session.query(max_date_modified)) print(bq) result = bq(session).params().one() return result
def test_baked_query(n): """test a baked query of the full entity.""" bakery = baked.bakery() s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery(lambda s: s.query(Customer)) q += lambda q: q.filter(Customer.id == bindparam("id")) q(s).params(id=id_).one()
def test_baked_query_cols_only(n): """test a baked query of only the entity columns.""" bakery = baked.bakery() s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery(lambda s: s.query(Customer.id, Customer.name, Customer. description)) q += lambda q: q.filter(Customer.id == bindparam("id")) q(s).params(id=id_).one()
def test_baked_query_cols_only(n): """test a baked query of only the entity columns.""" bakery = baked.bakery() s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery( lambda s: s.query( Customer.id, Customer.name, Customer.description)) q += lambda q: q.filter(Customer.id == bindparam('id')) q(s).params(id=id_).one()
def __init__(self): self.session = create_session() self.clear_tables() self.os_names_db_objs = list() self.add_default_values() self.add_triggers() self.add_views() # inspector = reflection.Inspector.from_engine(get_engine()) # print("Tables:", inspector.get_table_names()) # print("Views:", inspector.get_view_names()) self.baked_queries_map = self.bake_baked_queries() self.bakery = baked.bakery()
def __init__(self, db_endpoint: str, fs_root: str, max_cache_mem: int, ttl: int, engine_kwargs: dict = None): """ Initialize a new instance of SQLAlchemyModelRepository. :param db_endpoint: SQLAlchemy connection string. :param fs_root: Root directory where to store the models. :param max_cache_mem: Maximum memory size to use for model cache (in bytes). :param ttl: Time-to-live for each model in the cache (in seconds). :param engine_kwargs: Passed directly to SQLAlchemy's `create_engine()`. """ self.fs_root = fs_root # A version of db_endpoint that never contain password is needed for logging db_endpoint_components = urlparse(db_endpoint) if db_endpoint_components.password is not None: password, netloc = db_endpoint_components.password, db_endpoint_components.netloc password_index = netloc.rindex(password) safe_netloc = "%s%s%s" % ( db_endpoint_components.netloc[:password_index], "<PASSWORD>", db_endpoint_components.netloc[password_index + len(password):]) safe_db_endpoint_components = list(db_endpoint_components) safe_db_endpoint_components[1] = safe_netloc self._safe_db_endpoint = urlunparse(safe_db_endpoint_components) else: self._safe_db_endpoint = db_endpoint must_initialize = not database_exists(db_endpoint) if must_initialize: self._log.debug("%s does not exist, creating", self._safe_db_endpoint) create_database(db_endpoint) self._log.warning("created a new database at %s", self._safe_db_endpoint) self._engine = create_engine( db_endpoint, **(engine_kwargs if engine_kwargs is not None else {})) must_initialize |= not self._engine.has_table(Model.__tablename__) if must_initialize: Model.metadata.create_all(self._engine) self._sessionmaker = ContextSessionMaker( sessionmaker(bind=self._engine)) bakery = baked.bakery() self._get_query = bakery(lambda session: session.query(Model)) self._get_query += lambda query: query.filter( and_(Model.analyzer == bindparam("analyzer"), Model.repository == bindparam("repository"))) self._cache = cachetools.TTLCache(maxsize=max_cache_mem, ttl=ttl, getsizeof=asizeof) self._cache_lock = threading.Lock()
def __init__(self): self.session = create_session() self.read_func_by_format = {"info": self.read_from_svn_info, "text": self.read_from_text, "props": self.read_props, "file-sizes": self.read_file_sizes } self.write_func_by_format = {"text": self.write_as_text,} self.files_read_list = list() self.files_written_list = list() self.comments = list() self.baked_queries_map = self.bake_baked_queries() self.bakery = baked.bakery()
async def async_setup(hass, config): """Set up the history hooks.""" conf = config.get(DOMAIN, {}) filters = sqlalchemy_filter_from_include_exclude_conf(conf) hass.data[HISTORY_BAKERY] = baked.bakery() use_include_order = conf.get(CONF_ORDER) hass.http.register_view(HistoryPeriodView(filters, use_include_order)) hass.components.frontend.async_register_built_in_panel( "history", "history", "hass:poll-box") return True
def _default_recorder(hass): """Return a recorder with reasonable defaults.""" return Recorder( hass, auto_purge=True, auto_repack=True, keep_days=7, commit_interval=1, uri="sqlite://", db_max_retries=10, db_retry_wait=3, entity_filter=CONFIG_SCHEMA({DOMAIN: {}}), exclude_t=[], exclude_attributes_by_domain={}, bakery=baked.bakery(), )
def test_subquery_eagerloading(self): User = self.classes.User Address = self.classes.Address Order = self.classes.Order # Override the default bakery for one with a smaller size. This used to # trigger a bug when unbaking subqueries. self.bakery = baked.bakery(size=3) base_bq = self.bakery(lambda s: s.query(User)) base_bq += lambda q: q.options(subqueryload(User.addresses), subqueryload(User.orders)) base_bq += lambda q: q.order_by(User.id) assert_result = [ User(id=7, addresses=[Address(id=1, email_address='*****@*****.**')], orders=[Order(id=1), Order(id=3), Order(id=5)]), User(id=8, addresses=[ Address(id=2, email_address='*****@*****.**'), Address(id=3, email_address='*****@*****.**'), Address(id=4, email_address='*****@*****.**'), ]), User(id=9, addresses=[Address(id=5)], orders=[Order(id=2), Order(id=4)]), User(id=10, addresses=[]) ] for i in range(4): for cond1, cond2 in itertools.product(*[(False, True) for j in range(2)]): bq = base_bq._clone() sess = Session() if cond1: bq += lambda q: q.filter(User.name == 'jack') else: bq += lambda q: q.filter(User.name.like('%ed%')) if cond2: ct = func.count(Address.id).label('count') subq = sess.query( ct, Address.user_id).group_by(Address.user_id).\ having(ct > 2).subquery() bq += lambda q: q.join(subq) if cond2: if cond1: def go(): result = bq(sess).all() eq_([], result) self.assert_sql_count(testing.db, go, 1) else: def go(): result = bq(sess).all() eq_(assert_result[1:2], result) self.assert_sql_count(testing.db, go, 3) else: if cond1: def go(): result = bq(sess).all() eq_(assert_result[0:1], result) self.assert_sql_count(testing.db, go, 3) else: def go(): result = bq(sess).all() eq_(assert_result[1:3], result) self.assert_sql_count(testing.db, go, 3) sess.close()
def getbakery(cls): if cls._bakery is None: cls._bakery = staticmethod(bakery()) return cls._bakery
from sqlalchemy import func, bindparam, select from sqlalchemy.orm import make_transient from glycresoft_sqlalchemy.data_model import ( Decon2LSPeak, Decon2LSPeakGroup, Decon2LSPeakToPeakGroupMap, PipelineModule, MSScan, ScanBase) from glycresoft_sqlalchemy.utils.common_math import ppm_error from .common import ( expanding_window, expected_a_peak_regression, centroid_scan_error_regression) TDecon2LSPeakGroup = Decon2LSPeakGroup.__table__ query_oven = bakery() get_group_id_by_mass_window = query_oven(lambda session: session.query(Decon2LSPeakGroup.id)) get_group_id_by_mass_window += lambda q: q.filter(Decon2LSPeakGroup.weighted_monoisotopic_mass.between( bindparam("lower"), bindparam("upper"))) get_group_id_by_mass_window += lambda q: q.filter(Decon2LSPeakGroup.sample_run_id == bindparam("sample_run_id")) class Decon2LSPeakGrouper(PipelineModule): ''' Pipeline Step to post-process Decon2LSPeaks, clustering them by mass and calculating trends across groups. ''' def __init__(self, database_path, sample_run_id=1, grouping_error_tolerance=8e-5, minimum_scan_count=1, max_charge_state=8, minimum_abundance_ratio=0.01, minimum_mass=1200., maximum_mass=15000., minimum_signal_to_noise=1.,
def async_setup(opp): """Set up the history hooks.""" opp.data[STATISTICS_BAKERY] = baked.bakery()
from neutron_lib.callbacks import registry from neutron_lib.callbacks import resources from neutron_lib.plugins import directory from oslo_log import log as logging import sqlalchemy as sa from sqlalchemy.ext import baked from gbpservice.neutron.plugins.ml2plus.drivers.apic_aim import constants from gbpservice.neutron.services.grouppolicy.common import exceptions as exc from gbpservice.neutron.services.sfc.aim import constants as sfc_cts from gbpservice.neutron.services.sfc.aim import exceptions as sfc_exc LOG = logging.getLogger(__name__) flowclassifier.SUPPORTED_L7_PARAMETERS.update(sfc_cts.AIM_FLC_L7_PARAMS) BAKERY = baked.bakery(_size_alert=lambda c: LOG.warning( "sqlalchemy baked query cache size exceeded in %s" % __name__)) class FlowclassifierAIMDriverBase(base.FlowClassifierDriverBase): def create_flow_classifier_precommit(self, context): pass def create_flow_classifier(self, context): pass def update_flow_classifier(self, context): pass def delete_flow_classifier(self, context): pass
def test_subquery_eagerloading(self): User = self.classes.User Address = self.classes.Address Order = self.classes.Order self.bakery = baked.bakery() base_bq = self.bakery(lambda s: s.query(User)) base_bq += lambda q: q.options( subqueryload(User.addresses), subqueryload(User.orders) ) base_bq += lambda q: q.order_by(User.id) assert_result = [ User( id=7, addresses=[Address(id=1, email_address="*****@*****.**")], orders=[Order(id=1), Order(id=3), Order(id=5)], ), User( id=8, addresses=[ Address(id=2, email_address="*****@*****.**"), Address(id=3, email_address="*****@*****.**"), Address(id=4, email_address="*****@*****.**"), ], ), User( id=9, addresses=[Address(id=5)], orders=[Order(id=2), Order(id=4)], ), User(id=10, addresses=[]), ] for i in range(4): for cond1, cond2 in itertools.product( *[(False, True) for j in range(2)] ): bq = base_bq._clone() sess = fixture_session() if cond1: bq += lambda q: q.filter(User.name == "jack") else: bq += lambda q: q.filter(User.name.like("%ed%")) if cond2: ct = func.count(Address.id).label("count") subq = ( sess.query(ct, Address.user_id) .group_by(Address.user_id) .having(ct > 2) .subquery() ) bq += lambda q: q.join(subq) if cond2: if cond1: def go(): result = bq(sess).all() eq_([], result) self.assert_sql_count(testing.db, go, 1) else: def go(): result = bq(sess).all() eq_(assert_result[1:2], result) self.assert_sql_count(testing.db, go, 3) else: if cond1: def go(): result = bq(sess).all() eq_(assert_result[0:1], result) self.assert_sql_count(testing.db, go, 3) else: def go(): result = bq(sess).all() eq_(assert_result[1:3], result) self.assert_sql_count(testing.db, go, 3) sess.close()
# coding: utf-8 from enum import IntEnum, auto from dataclasses import dataclass, astuple import sqlalchemy as sa from sqlalchemy.ext import baked from bookworm import config from bookworm.logger import logger from bookworm.database.models import Book from .annotation_models import Bookmark, Note, Quote log = logger.getChild(__name__) # The bakery caches query objects to avoid recompiling them into strings in every call BAKERY = baked.bakery() @dataclass class AnnotationFilterCriteria: book_id: int = 0 tag: str = "" section_title: str = "" content_snip: str = "" def any(self): return any(astuple(self)) def filter_query(self, model, query): if not self.any(): return query clauses = []
from sqlalchemy import (PickleType, Numeric, Unicode, Table, bindparam, Column, Integer, ForeignKey, UnicodeText, Boolean) import numpy as np from .base import Base from .connection import DatabaseManager from .generic import MutableDict, ParameterStore from ..structure.composition import Composition from ..utils.common_math import DPeak from ..utils.collectiontools import groupby PROTON = Composition("H+").mass observed_ions_bakery = bakery() class HasPeakChromatogramData(object): peak_data = Column(MutableDict.as_mutable(PickleType)) def get_chromatogram(self): peak_data = self.peak_data scans = peak_data['scan_times'] intensity = peak_data['intensities'] scan_groups = groupby( zip(scans, intensity), key_fn=operator.itemgetter(0), transform_fn=operator.itemgetter(1)) scans = []
def iterate_db_families(session, tax_db, families_query): """Returns an iterator over families in the Dfam MySQL database.""" class_db = load_classification(session) # A "bakery" caches queries. The performance gains are worth it here, where # the queries are done many times with only the id changing. Another # approach that could be used is to make each of these queries once instead # of in a loop, but that would require a more significant restructuring. bakery = baked.bakery() clade_query = bakery(lambda s: s.query(dfam.t_family_clade.c.dfam_taxdb_tax_id)) clade_query += lambda q: q.filter(dfam.t_family_clade.c.family_id == bindparam("id")) search_stage_query = bakery(lambda s: s.query(dfam.t_family_has_search_stage.c.repeatmasker_stage_id)) search_stage_query += lambda q: q.filter(dfam.t_family_has_search_stage.c.family_id == bindparam("id")) buffer_stage_query = bakery(lambda s: s.query( dfam.FamilyHasBufferStage.repeatmasker_stage_id, dfam.FamilyHasBufferStage.start_pos, dfam.FamilyHasBufferStage.end_pos, )) buffer_stage_query += lambda q: q.filter(dfam.FamilyHasBufferStage.family_id == bindparam("id")) assembly_data_query = bakery(lambda s: s.query( dfam.Assembly.dfam_taxdb_tax_id, dfam.FamilyAssemblyDatum.hmm_hit_GA, dfam.FamilyAssemblyDatum.hmm_hit_TC, dfam.FamilyAssemblyDatum.hmm_hit_NC, dfam.FamilyAssemblyDatum.hmm_fdr, )) assembly_data_query += lambda q: q.filter(dfam.FamilyAssemblyDatum.family_id == bindparam("id")) assembly_data_query += lambda q: q.filter(dfam.Assembly.id == dfam.FamilyAssemblyDatum.assembly_id) feature_query = bakery(lambda s: s.query(dfam.FamilyFeature)) feature_query += lambda q: q.filter(dfam.FamilyFeature.family_id == bindparam("id")) feature_attr_query = bakery(lambda s: s.query(dfam.FeatureAttribute)) feature_attr_query += lambda q: q.filter(dfam.FeatureAttribute.family_feature_id == bindparam("id")) cds_query = bakery(lambda s: s.query(dfam.CodingSequence)) cds_query += lambda q: q.filter(dfam.CodingSequence.family_id == bindparam("id")) alias_query = bakery(lambda s: s.query(dfam.FamilyDatabaseAlia)) alias_query += lambda q: q.filter(dfam.FamilyDatabaseAlia.family_id == bindparam("id")) citation_query = bakery(lambda s: s.query( dfam.Citation.title, dfam.Citation.authors, dfam.Citation.journal, dfam.FamilyHasCitation.order_added, )) citation_query += lambda q: q.filter(dfam.Citation.pmid == dfam.FamilyHasCitation.citation_pmid) citation_query += lambda q: q.filter(dfam.FamilyHasCitation.family_id == bindparam("id")) hmm_query = bakery(lambda s: s.query(dfam.HmmModelDatum.hmm)) hmm_query += lambda q: q.filter(dfam.HmmModelDatum.family_id == bindparam("id")) for record in families_query: family = famdb.Family() # REQUIRED FIELDS family.name = record.name family.accession = record.accession family.title = record.title family.version = record.version family.consensus = record.consensus family.length = record.length # RECOMMENDED FIELDS family.description = record.description family.author = record.author family.date_created = record.date_created family.date_modified = record.date_modified family.refineable = record.refineable family.target_site_cons = record.target_site_cons family.general_cutoff = record.hmm_general_threshold if record.classification_id in class_db: cls = class_db[record.classification_id] family.classification = cls.full_name() family.repeat_type = cls.type_name family.repeat_subtype = cls.subtype_name # clades and taxonomy links family.clades = [] for (clade_id,) in clade_query(session).params(id=record.id).all(): family.clades += [clade_id] # "SearchStages: A,B,C,..." ss_values = [] for (stage_id,) in search_stage_query(session).params(id=record.id).all(): ss_values += [str(stage_id)] if ss_values: family.search_stages = ",".join(ss_values) # "BufferStages:A,B,C[D-E],..." bs_values = [] for (stage_id, start_pos, end_pos) in buffer_stage_query(session).params(id=record.id).all(): if start_pos == 0 and end_pos == 0: bs_values += [str(stage_id)] else: bs_values += ["{}[{}-{}]".format(stage_id, start_pos, end_pos)] if bs_values: family.buffer_stages = ",".join(bs_values) # Taxa-specific thresholds. "ID, GA, TC, NC, fdr" th_values = [] for (tax_id, spec_ga, spec_tc, spec_nc, spec_fdr) in assembly_data_query(session).params(id=record.id).all(): if None in (spec_ga, spec_tc, spec_nc, spec_fdr): raise Exception("Found value of None for a threshold value for " + record.accession + " in tax_id" + str(tax_id)) th_values += ["{}, {}, {}, {}, {}".format(tax_id, spec_ga, spec_tc, spec_nc, spec_fdr)] tax_db[tax_id].mark_ancestry_used() if th_values: family.taxa_thresholds = "\n".join(th_values) feature_values = [] for feature in feature_query(session).params(id=record.id).all(): obj = { "type": feature.feature_type, "description": feature.description, "model_start_pos": feature.model_start_pos, "model_end_pos": feature.model_end_pos, "label": feature.label, "attributes": [], } for attribute in feature_attr_query(session).params(id=feature.id).all(): obj["attributes"] += [{"attribute": attribute.attribute, "value": attribute.value}] feature_values += [obj] if feature_values: family.features = json.dumps(feature_values) cds_values = [] for cds in cds_query(session).params(id=record.id).all(): obj = { "product": cds.product, "translation": cds.translation, "cds_start": cds.cds_start, "cds_end": cds.cds_end, "exon_count": cds.exon_count, "exon_starts": str(cds.exon_starts), "exon_ends": str(cds.exon_ends), "external_reference": cds.external_reference, "reverse": (cds.reverse == 1), "stop_codons": cds.stop_codons, "frameshifts": cds.frameshifts, "gaps": cds.gaps, "percent_identity": cds.percent_identity, "left_unaligned": cds.left_unaligned, "right_unaligned": cds.right_unaligned, "description": cds.description, "protein_type": cds.protein_type, } cds_values += [obj] if cds_values: family.coding_sequences = json.dumps(cds_values) # External aliases alias_values = [] for alias in alias_query(session).params(id=record.id).all(): alias_values += ["%s: %s" % (alias.db_id, alias.db_link)] if alias_values: family.aliases = "\n".join(alias_values) citation_values = [] for citation in citation_query(session).params(id=record.id).all(): obj = { "title": citation.title, "authors": citation.authors, "journal": citation.journal, "order_added": citation.order_added, } citation_values += [obj] if citation_values: family.citations = json.dumps(citation_values) # MODEL DATA + METADATA hmm = hmm_query(session).params(id=record.id).one_or_none() if hmm: family.model = gzip.decompress(hmm[0]).decode() if record.hmm_maxl: family.max_length = record.hmm_maxl family.is_model_masked = record.model_mask seed_count = session.execute("SELECT COUNT(*) from seed_region where family_id=:id", {"id": record.id}).fetchone()[0] family.seed_count = seed_count yield family
class Transcript(TranscriptBase): # Query baking to minimize overhead bakery = baked.bakery() blast_baked = bakery(lambda session: session.query(Hit)) blast_baked += lambda q: q.filter( and_(Hit.query == bindparam("query"), Hit.evalue <= bindparam("evalue") ), ) blast_baked += lambda q: q.order_by(asc(Hit.evalue)) # blast_baked += lambda q: q.limit(bindparam("max_target_seqs")) def __init__(self, *args, configuration=None, **kwargs): super().__init__(*args, **kwargs) self.__configuration = None self.configuration = configuration @property def configuration(self): """ Configuration dictionary. It can be None. :return: """ if self.__configuration is None: self.__configuration = default_config.copy() return self.__configuration @configuration.setter def configuration(self, configuration): """ Setter for the configuration dictionary. :param configuration: None or a dictionary :type configuration: (None | MikadoConfiguration | DaijinConfiguration) :return: """ if configuration is None: configuration = default_config.copy() assert isinstance(configuration, (MikadoConfiguration, DaijinConfiguration)) self.__configuration = configuration def __getstate__(self): state = super().__getstate__() if hasattr(self, "configuration") and self.configuration is not None: state["configuration"] = self.configuration.copy() assert isinstance(state["configuration"], (MikadoConfiguration, DaijinConfiguration)), type(self.configuration) if isinstance(state["configuration"].reference.genome, pysam.FastaFile): state["configuration"]["reference"]["genome"] = state[ "configuration"].reference.genome.filename return state def __setstate__(self, state): self.configuration = state.pop("configuration", None) self.__dict__.update(state) self._calculate_cds_tree() self._calculate_segment_tree() self.logger = None def split_by_cds(self) -> List: """This method is used for transcripts that have multiple ORFs. It will split them according to the CDS information into multiple transcripts. UTR information will be retained only if no ORF is down/upstream. """ for new_transcript in splitting.split_by_cds(self): yield new_transcript return def load_information_from_db(self, configuration, introns=None, data_dict=None): """This method will load information regarding the transcript from the provided database. :param configuration: Necessary configuration file :type configuration: (MikadoConfiguration|DaijinConfiguration) :param introns: the verified introns in the Locus :type introns: None,set :param data_dict: a dictionary containing the information directly :type data_dict: dict Verified introns can be provided from outside using the keyword. Otherwise, they will be extracted from the database directly. """ retrieval.load_information_from_db(self, configuration, introns=introns, data_dict=data_dict) def load_orfs(self, candidate_orfs): """ Thin layer over the load_orfs method from the retrieval module. :param candidate_orfs: list of candidate ORFs in BED12 format. :return: """ retrieval.load_orfs(self, candidate_orfs) def find_overlapping_cds(self, candidate_orfs): """ Thin wrapper for the homonym function in retrieval :param candidate_orfs: List of candidate ORFs :return: """ return retrieval.find_overlapping_cds(self, candidate_orfs) # We need to overload this because otherwise we won't get the metrics from the base class. @classmethod @functools.lru_cache(maxsize=None, typed=True) def get_available_metrics(cls) -> list: """This function retrieves all metrics available for the class.""" metrics = TranscriptBase.get_available_metrics() for member in inspect.getmembers(cls): if not member[0].startswith( "__") and member[0] in cls.__dict__ and isinstance( cls.__dict__[member[0]], Metric): metrics.append(member[0]) _metrics = sorted(set([metric for metric in metrics])) final_metrics = ["tid", "alias", "parent", "original_source", "score" ] + _metrics return final_metrics # We need to overload this because otherwise we won't get the metrics from the base class. @classmethod @functools.lru_cache(maxsize=None, typed=True) def get_modifiable_metrics(cls) -> set: metrics = TranscriptBase.get_modifiable_metrics() for member in inspect.getmembers(cls): not_private = (not member[0].startswith("_" + cls.__name__ + "__") and not member[0].startswith("__")) in_dict = (member[0] in cls.__dict__) if in_dict: is_metric = isinstance(cls.__dict__[member[0]], Metric) has_fset = (getattr(cls.__dict__[member[0]], "fset", None) is not None) else: is_metric = None has_fset = None if all([not_private, in_dict, is_metric, has_fset]): metrics.append(member[0]) return set(metrics)
registry[STORAGE] = RDBStorage(registry[DBSESSION]) global _DBSESSION _DBSESSION = registry[DBSESSION] if registry.settings.get('blob_bucket'): registry[BLOBS] = S3BlobStorage( registry.settings['blob_bucket'], read_profile_name=registry.settings.get('blob_read_profile_name'), store_profile_name=registry.settings.get('blob_store_profile_name') ) else: registry[BLOBS] = RDBBlobStorage(registry[DBSESSION]) Base = declarative_base() bakery = baked.bakery() baked_query_resource = bakery(lambda session: session.query(Resource)) baked_query_unique_key = bakery( lambda session: session.query(Key).options( orm.joinedload_all( Key.resource, Resource.data, CurrentPropertySheet.propsheet, innerjoin=True, ), ).filter(Key.name == bindparam('name'), Key.value == bindparam('value')) ) class RDBStorage(object): batchsize = 1000
import logging import pprint import tornado.web import tornado.httpserver from tornado.options import define, options, parse_command_line from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext import baked from sqlalchemy.orm import Session # models BAKERY = baked.bakery() Base = declarative_base() ENGINE = create_engine('sqlite:///:memory:', echo=False) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String)
class ModelViewWithBakedQueryMixin: bakery = baked.bakery() def _apply_path_joins(self, query, joins, path, inner_join=True): """ Apply join path to the query. :param query: Query to add joins to :param joins: List of current joins. Used to avoid joining on same relationship more than once :param path: Path to be joined :param fn: Join function """ last = None if path: for item in path: key = (inner_join, item) alias = joins.get(key) if key not in joins: if not isinstance(item, Table): alias = aliased(item.property.mapper.class_) fn = query.join if inner_join else query.outerjoin if last is None: query = fn(item) if alias is None else fn(alias, item) else: prop = getattr(last, item.key) query = fn(prop) if alias is None else fn(alias, prop) joins[key] = alias last = alias return query, joins, last def _order_by(self, query, joins, sort_joins, sort_field, sort_desc): """ Apply order_by to the query :param query: Query :pram joins: Current joins :param sort_joins: Sort joins (properties or tables) :param sort_field: Sort field :param sort_desc: Ascending or descending """ if sort_field is not None: # Handle joins query, joins, alias = self._apply_path_joins( query, joins, sort_joins, inner_join=False) column = sort_field if alias is None else getattr( alias, sort_field.key) # if sort_desc: # query += lambda q: q.order_by(desc(column)) # else: # query += lambda q: q.order_by(column) if sort_desc: query += lambda q: q.order_by(desc(bindparam('order_by'))) else: query += lambda q: q.order_by(bindparam('order_by')) self.bakery_query_params['order_by'] = column.name return query, joins def _apply_filters(self, query, count_query, joins, count_joins, filters): for idx, flt_name, value in filters: flt = self._filters[idx] alias = None count_alias = None # Figure out joins if isinstance(flt, sqla_filters.BaseSQLAFilter): # If no key_name is specified, use filter column as filter key filter_key = flt.key_name or flt.column path = self._filter_joins.get(filter_key, []) query, joins, alias = self._apply_path_joins( query, joins, path, inner_join=False) if count_query is not None: count_query, count_joins, count_alias = self._apply_path_joins( count_query, count_joins, path, inner_join=False) # Clean value .clean() and apply the filter clean_value = flt.clean(value) try: query = flt.apply(query, clean_value, alias) except TypeError: spec = inspect.getargspec(flt.apply) if len(spec.args) == 3: warnings.warn('Please update your custom filter %s to ' 'include additional `alias` parameter.' % repr(flt)) else: raise query = flt.apply(query, clean_value) if count_query is not None: try: count_query = flt.apply( count_query, clean_value, count_alias) except TypeError: count_query = flt.apply(count_query, clean_value) return query, count_query, joins, count_joins def _apply_search(self, query, count_query, joins, count_joins, search): """ Apply search to a query. """ terms = search.split(' ') for term in terms: if not term: continue stmt = tools.parse_like_term(term) filter_stmt = [] count_filter_stmt = [] for field, path in self._search_fields: query, joins, alias = self._apply_path_joins( query, joins, path, inner_join=False) count_alias = None if count_query is not None: count_query, count_joins, count_alias = self._apply_path_joins( count_query, count_joins, path, inner_join=False) column = field if alias is None else getattr(alias, field.key) filter_stmt.append( cast(column, Unicode).ilike(stmt)) # 使用ilike进行检索 if count_filter_stmt is not None: column = field if count_alias is None else getattr( count_alias, field.key) count_filter_stmt.append(cast(column, Unicode).ilike(stmt)) query += lambda q: q.filter(or_(*filter_stmt)) # “或”查询 if count_query is not None: count_query += lambda q: q.filter(or_(*count_filter_stmt)) return query, count_query, joins, count_joins def _apply_pagination(self, query, page, page_size): if page_size is None: page_size = self.page_size if page_size: query += lambda q: q.limit(bindparam('page_size')) self.bakery_query_params['page_size'] = page_size if page and page_size: # query += lambda q: q.offset(page * page_size) query += lambda q: q.offset(bindparam('offset')) self.bakery_query_params['offset'] = page * page_size return query def get_list(self, page, sort_column, sort_desc, search, filters, execute=True, page_size=None): """ Return records from the database. :param page: Page number :param sort_column: Sort column name :param sort_desc: Descending or ascending sort :param search: Search query :param execute: Execute query immediately? Default is `True` :param filters: List of filter tuples :param page_size: Number of results. Defaults to ModelView's page_size. Can be overriden to change the page_size limit. Removing the page_size limit requires setting page_size to 0 or False. """ # Will contain join paths with optional aliased object joins = {} count_joins = {} query = self.get_query() count_query = self.get_count_query() if not self.simple_list_pager else None # Ignore eager-loaded relations (prevent unnecessary joins) # TODO: Separate join detection for query and count query? # eager-loading: https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#eager-loading if hasattr(query, '_join_entities'): for entity in query._join_entities: for table in entity.tables: joins[table] = None # Apply search criteria if self._search_supported and search: query, count_query, joins, count_joins = self._apply_search( query, count_query, joins, count_joins, search) # Apply filters if filters and self._filters: query, count_query, joins, count_joins = self._apply_filters( query, count_query, joins, count_joins, filters) # Calculate number of rows if necessary count = count_query(self.session()).scalar() if count_query else None # Auto join for j in self._auto_joins: # joinedload: https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#sqlalchemy.orm.joinedload query += lambda q: q.options(joinedload(j)) # Sorting query, joins = self._apply_sorting( query, joins, sort_column, sort_desc) # Pagination query = self._apply_pagination(query, page, page_size) # Execute if needed if execute: query = query(self.session()).params( **self.bakery_query_params ).all() return count, query def get_one(self, id): """override""" return super().get_one(id) # TODO: # q = self.bakery(lambda s: s.query(self.model)) # q += lambda q: q.filter(self.model.id == bindparam("id")) # return q(self.session()).params(id=id).one() def get_query(self): """override""" # return super().get_query() q = self.bakery(lambda s: s.query(self.model)) self.bakery_query_params = {} return q def get_count_query(self): """override""" # return super().get_count_query() q = self.bakery(lambda s: s.query( func.count('*')).select_from(self.model)) return q
def async_setup(hass): """Set up the history hooks.""" hass.data[HISTORY_BAKERY] = baked.bakery()
def async_setup(opp): """Set up the history hooks.""" opp.data[HISTORY_BAKERY] = baked.bakery()
def setup(self): self.bakery = baked.bakery()
def async_setup(hass): """Set up the history hooks.""" hass.data[STATISTICS_BAKERY] = baked.bakery()
def setup_test(self): self.bakery = baked.bakery()
from .base import Base, Namespace from .generic import ( MutableDict, HasTaxonomy, HasReferenceAccessionNumber, HasClassBakedQueries) from ..utils.database_utils import get_or_create from ..utils.memoize import memoclone FrozenGlycanComposition = glycan_composition.FrozenGlycanComposition crossring_pattern = re.compile(r"\d,\d") glycoct_parser = memoclone(100)(glycoct.loads) glycan_bakery = bakery() class MassShift(Base): __tablename__ = "MassShift" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(Unicode(128), index=True) mass = Column(Numeric(10, 6, asdecimal=False)) def __hash__(self): return hash(self.name) def __eq__(self, other): return (self.name == other.name) and (self.mass == other.mass)
from sqlalchemy.orm import relationship, backref from sqlalchemy.ext.hybrid import hybrid_property from sqlalchemy import Numeric, Unicode, Column, Integer, ForeignKey, Table, UnicodeText, bindparam from ..base import Base from sqlalchemy.ext.baked import bakery from glycresoft_sqlalchemy.structure.sequence_composition import SequenceComposition, Composition sequencing_bakery = bakery() class SequenceBuildingBlock(Base): __tablename__ = "SequenceBuildingBlock" id = Column(Integer, primary_key=True) name = Column(Unicode(120), index=True) mass = Column(Numeric(10, 6), index=True) hypothesis_id = Column(Integer, ForeignKey("Hypothesis.id"), index=True) class SequenceSegment(Base): __tablename__ = "SequenceSegment" id = Column(Integer, primary_key=True) sequence = Column(Unicode(128)) mass = Column(Numeric(12, 6), index=True) count_n_glycosylation = Column(Integer) class AminoAcidComposition(Base):
from sqlalchemy.orm import relationship, backref, make_transient, Query, validates from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property from sqlalchemy import (PickleType, Numeric, Unicode, Table, bindparam, Column, Integer, ForeignKey, UnicodeText, Boolean, event) from sqlalchemy.orm.exc import DetachedInstanceError from ..generic import MutableDict, MutableList, HasClassBakedQueries from ..base import Base from ..hypothesis import Hypothesis from ..glycomics import ( with_glycan_composition, TheoreticalGlycanCombination, has_glycan_composition_listener) from ...structure import sequence, residue peptide_bakery = bakery() class Protein(Base): __tablename__ = "Protein" id = Column(Integer, primary_key=True, autoincrement=True) protein_sequence = Column(UnicodeText, default=u"") name = Column(Unicode(128), default=u"", index=True) other = Column(MutableDict.as_mutable(PickleType)) hypothesis_id = Column(Integer, ForeignKey("Hypothesis.id", ondelete="CASCADE")) _n_glycan_sequon_sites = None @property def n_glycan_sequon_sites(self):
def test_subquery_eagerloading(self): User = self.classes.User Address = self.classes.Address Order = self.classes.Order # Override the default bakery for one with a smaller size. This used to # trigger a bug when unbaking subqueries. self.bakery = baked.bakery(size=3) base_bq = self.bakery(lambda s: s.query(User)) base_bq += lambda q: q.options(subqueryload(User.addresses), subqueryload(User.orders)) base_bq += lambda q: q.order_by(User.id) assert_result = [ User(id=7, addresses=[Address(id=1, email_address='*****@*****.**')], orders=[Order(id=1), Order(id=3), Order(id=5)]), User(id=8, addresses=[ Address(id=2, email_address='*****@*****.**'), Address(id=3, email_address='*****@*****.**'), Address(id=4, email_address='*****@*****.**'), ]), User(id=9, addresses=[Address(id=5)], orders=[Order(id=2), Order(id=4)]), User(id=10, addresses=[]) ] for i in range(4): for cond1, cond2 in itertools.product( *[(False, True) for j in range(2)]): bq = base_bq._clone() sess = Session() if cond1: bq += lambda q: q.filter(User.name == 'jack') else: bq += lambda q: q.filter(User.name.like('%ed%')) if cond2: ct = func.count(Address.id).label('count') subq = sess.query( ct, Address.user_id).group_by(Address.user_id).\ having(ct > 2).subquery() bq += lambda q: q.join(subq) if cond2: if cond1: def go(): result = bq(sess).all() eq_([], result) self.assert_sql_count(testing.db, go, 1) else: def go(): result = bq(sess).all() eq_(assert_result[1:2], result) self.assert_sql_count(testing.db, go, 3) else: if cond1: def go(): result = bq(sess).all() eq_(assert_result[0:1], result) self.assert_sql_count(testing.db, go, 3) else: def go(): result = bq(sess).all() eq_(assert_result[1:3], result) self.assert_sql_count(testing.db, go, 3) sess.close()
'ModelAPIMixin'] class BaseCls(object): created_time = Column(DateTime, default=datetime.datetime.now) modified_time = Column(DateTime, onupdate=datetime.datetime.now, default=datetime.datetime.now) # 各种要多次使用的客户端,基类 Base = declarative_base(cls=BaseCls) # 继承了2个时间字段的Base NativeBase = declarative_base() # 原生Base metadata = Base.metadata Session = sessionmaker() sql_bakery = bakery() redis_cli = redis.StrictRedis( host=CommonConfig.REDIS_HOST, port=CommonConfig.REDIS_PORT, password=CommonConfig.REDIS_PASSWORD, decode_responses=True ) @contextlib.contextmanager def session_context(uri=CommonConfig.SQLALCHEMY_URI): engine = create_engine(uri) session = Session(bind=engine) yield session try: session.commit()
from neutron_lib.api.definitions import portbindings from opflexagent import rpc as o_rpc from oslo_log import log from gbpservice.neutron.db.grouppolicy.extensions import ( apic_auto_ptg_db as auto_ptg_db) from gbpservice.neutron.db.grouppolicy.extensions import ( apic_segmentation_label_db as seg_label_db) from gbpservice.neutron.db.grouppolicy import group_policy_mapping_db as gpmdb from gbpservice.neutron.plugins.ml2plus.drivers.apic_aim import ( constants as md_const) LOG = log.getLogger(__name__) BAKERY = baked.bakery(_size_alert=lambda c: LOG.warning( "sqlalchemy baked query cache size exceeded in %s" % __name__)) EndpointPtInfo = namedtuple( 'EndpointPtInfo', ['pt_id', 'ptg_id', 'apg_id', 'inject_default_route', 'l3p_project_id', 'is_auto_ptg']) class AIMMappingRPCMixin(object): """RPC mixin for AIM mapping. Collection of all the RPC methods consumed by the AIM mapping.
def async_setup(hass: HomeAssistant) -> None: """Set up the history hooks.""" hass.data[HISTORY_BAKERY] = baked.bakery()
) from sqlalchemy.ext import baked from sqlalchemy.ext.compiler import compiles from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.session import Session from sqlalchemy.pool import NullPool, Pool, QueuePool from sqlalchemy.sql import func, select from sqlalchemy.sql.expression import Insert from ichnaea.config import ( DB_DDL_URI, DB_RW_URI, DB_RO_URI, ) BAKERY = baked.bakery(size=500) DB_TYPE = { 'ddl': DB_DDL_URI, 'ro': DB_RO_URI, 'rw': DB_RW_URI, } @compiles(Insert, 'mysql') def on_duplicate(insert, compiler, **kw): """Custom MySQL insert on_duplicate support.""" stmt = compiler.visit_insert(insert, **kw) my_var = insert.dialect_kwargs.get('mysql_on_duplicate', None) if my_var: stmt += ' ON DUPLICATE KEY UPDATE %s' % my_var