def test_update_future_lambda(self): User, users = self.classes.User, self.tables.users sess = Session(future=True) john, jack, jill, jane = ( sess.execute(select(User).order_by(User.id)).scalars().all() ) sess.execute( lambda_stmt( lambda: update(User) .where(User.age > 29) .values({"age": User.age - 10}) .execution_options(synchronize_session="evaluate") ), ) eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27]) eq_( sess.execute(select(User.age).order_by(User.id)).all(), list(zip([25, 37, 29, 27])), ) sess.execute( lambda_stmt( lambda: update(User) .where(User.age > 29) .values({User.age: User.age - 10}) .execution_options(synchronize_session="evaluate") ) ) eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 29, 27]) eq_( sess.query(User.age).order_by(User.id).all(), list(zip([25, 27, 29, 27])), ) sess.query(User).filter(User.age > 27).update( {users.c.age_int: User.age - 10}, synchronize_session="evaluate" ) eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 19, 27]) eq_( sess.query(User.age).order_by(User.id).all(), list(zip([25, 27, 19, 27])), ) sess.query(User).filter(User.age == 25).update( {User.age: User.age - 10}, synchronize_session="fetch" ) eq_([john.age, jack.age, jill.age, jane.age], [15, 27, 19, 27]) eq_( sess.query(User.age).order_by(User.id).all(), list(zip([15, 27, 19, 27])), )
def find_shared_data_id(attr_hash: int, shared_data: str) -> StatementLambdaElement: """Find a data_id by hash and shared_data.""" return lambda_stmt( lambda: select(EventData.data_id) .filter(EventData.hash == attr_hash) .filter(EventData.shared_data == shared_data) )
def find_states_to_purge(purge_before: datetime) -> StatementLambdaElement: """Find states to purge.""" return lambda_stmt( lambda: select(States.state_id, States.attributes_id) .filter(States.last_updated < purge_before) .limit(MAX_ROWS_TO_PURGE) )
def delete_event_data_rows(data_ids: Iterable[int]) -> StatementLambdaElement: """Delete event_data rows.""" return lambda_stmt( lambda: delete(EventData) .where(EventData.data_id.in_(data_ids)) .execution_options(synchronize_session=False) )
def delete_states_rows(state_ids: Iterable[int]) -> StatementLambdaElement: """Delete states rows.""" return lambda_stmt( lambda: delete(States) .where(States.state_id.in_(state_ids)) .execution_options(synchronize_session=False) )
def all_stmt( start_day: dt, end_day: dt, event_types: tuple[str, ...], states_entity_filter: ClauseList | None = None, events_entity_filter: ClauseList | None = None, context_id: str | None = None, ) -> StatementLambdaElement: """Generate a logbook query for all entities.""" stmt = lambda_stmt( lambda: select_events_without_states(start_day, end_day, event_types)) if context_id is not None: # Once all the old `state_changed` events # are gone from the database remove the # _legacy_select_events_context_id() stmt += lambda s: s.where(Events.context_id == context_id).union_all( _states_query_for_context_id(start_day, end_day, context_id), legacy_select_events_context_id(start_day, end_day, context_id), ) else: if events_entity_filter is not None: stmt += lambda s: s.where(events_entity_filter) if states_entity_filter is not None: stmt += lambda s: s.union_all( _states_query_for_all(start_day, end_day).where( states_entity_filter)) else: stmt += lambda s: s.union_all( _states_query_for_all(start_day, end_day)) stmt += lambda s: s.order_by(Events.time_fired) return stmt
def find_legacy_event_state_and_attributes_and_data_ids_to_purge( purge_before: datetime, ) -> StatementLambdaElement: """Find the latest row in the legacy format to purge.""" return lambda_stmt(lambda: select( Events.event_id, Events.data_id, States.state_id, States.attributes_id ).join(States, Events.event_id == States.event_id).filter( Events.time_fired < purge_before).limit(MAX_ROWS_TO_PURGE))
def delete_recorder_runs_rows(purge_before: datetime, current_run_id: int) -> StatementLambdaElement: """Delete recorder_runs rows.""" return lambda_stmt( lambda: delete(RecorderRuns).filter(RecorderRuns.start < purge_before). filter(RecorderRuns.run_id != current_run_id).execution_options( synchronize_session=False))
def query(names): stmt = lambda_stmt( lambda: select(User.name, Address.email_address).where( User.name.in_(names)).join(User.addresses)) + ( lambda s: s.order_by(User.id, Address.id)) return s.execute(stmt)
def query(names): u1 = aliased(User) stmt = lambda_stmt(lambda: select(u1).where( u1.name.in_(names)).options(selectinload(u1.addresses)) ) + (lambda s: s.order_by(u1.id)) return s.execute(stmt)
def entities_devices_stmt( start_day: dt, end_day: dt, event_types: tuple[str, ...], entity_ids: list[str], json_quoted_entity_ids: list[str], json_quoted_device_ids: list[str], ) -> StatementLambdaElement: """Generate a logbook query for multiple entities.""" stmt = lambda_stmt( lambda: _apply_entities_devices_context_union( select_events_without_states(start_day, end_day, event_types).where( _apply_event_entity_id_device_id_matchers( json_quoted_entity_ids, json_quoted_device_ids ) ), start_day, end_day, event_types, entity_ids, json_quoted_entity_ids, json_quoted_device_ids, ).order_by(Events.time_fired) ) return stmt
def fetch_files_by_name(filenames='all'): """ 根据输入的物理量名,从files table获取 file object(s) Parameters ---------- filenames : str or list[str] Returns ------- list[File] """ stmt = lambda_stmt(lambda: select(File)) if (filenames == 'all') or ('all' in list(filenames)): pass else: if isinstance(filenames, str): filenames = [filenames] stmt += lambda s: s.where(File.name.in_(filenames)) try: with Session() as session: files = session.execute(stmt).scalars().all() finally: if not files: raise Exception(f"{filenames} doesn't exists") return files
def data_ids_exist_in_events_sqlite( data_ids: Iterable[int], ) -> StatementLambdaElement: """Find data ids that exist in the events table.""" return lambda_stmt( lambda: select(distinct(Events.data_id)).filter(Events.data_id.in_(data_ids)) )
def find_events_to_purge(purge_before: datetime) -> StatementLambdaElement: """Find events to purge.""" return lambda_stmt( lambda: select(Events.event_id, Events.data_id) .filter(Events.time_fired < purge_before) .limit(MAX_ROWS_TO_PURGE) )
def disconnect_states_rows(state_ids: Iterable[int]) -> StatementLambdaElement: """Disconnect states rows.""" return lambda_stmt( lambda: update(States) .where(States.old_state_id.in_(state_ids)) .values(old_state_id=None) .execution_options(synchronize_session=False) )
def query(names): stmt = lambda_stmt( lambda: select(User) .where(User.name.in_(names)) .options(subqueryload(User.addresses)) ) + (lambda s: s.order_by(User.id)) return s.execute(stmt)
def find_statistics_runs_to_purge( purge_before: datetime, ) -> StatementLambdaElement: """Find statistics_runs to purge.""" return lambda_stmt( lambda: select(StatisticsRuns.run_id) .filter(StatisticsRuns.start < purge_before) .limit(MAX_ROWS_TO_PURGE) )
def delete_event_rows( event_ids: Iterable[int], ) -> StatementLambdaElement: """Delete statistics_short_term rows.""" return lambda_stmt( lambda: delete(Events) .where(Events.event_id.in_(event_ids)) .execution_options(synchronize_session=False) )
def delete_statistics_short_term_rows( short_term_statistics: Iterable[int], ) -> StatementLambdaElement: """Delete statistics_short_term rows.""" return lambda_stmt( lambda: delete(StatisticsShortTerm) .where(StatisticsShortTerm.id.in_(short_term_statistics)) .execution_options(synchronize_session=False) )
def delete_statistics_runs_rows( statistics_runs: Iterable[int], ) -> StatementLambdaElement: """Delete statistics_runs rows.""" return lambda_stmt( lambda: delete(StatisticsRuns) .where(StatisticsRuns.run_id.in_(statistics_runs)) .execution_options(synchronize_session=False) )
def find_short_term_statistics_to_purge( purge_before: datetime, ) -> StatementLambdaElement: """Find short term statistics to purge.""" return lambda_stmt( lambda: select(StatisticsShortTerm.id) .filter(StatisticsShortTerm.start < purge_before) .limit(MAX_ROWS_TO_PURGE) )
def attributes_ids_exist_in_states_sqlite( attributes_ids: Iterable[int], ) -> StatementLambdaElement: """Find attributes ids that exist in the states table.""" return lambda_stmt( lambda: select(distinct(States.attributes_id)).filter( States.attributes_id.in_(attributes_ids) ) )
def find_shared_attributes_id( data_hash: int, shared_attrs: str ) -> StatementLambdaElement: """Find an attributes_id by hash and shared_attrs.""" return lambda_stmt( lambda: select(StateAttributes.attributes_id) .filter(StateAttributes.hash == data_hash) .filter(StateAttributes.shared_attrs == shared_attrs) )
def go(ids, values): stmt = lambda_stmt(lambda: update(User).where(User.id.in_(ids))) s.execute( stmt, values, # note this currently just unrolls the lambda on the statement. # so lambda caching for updates is not actually that useful # unless synchronize_session is turned off. # evaluate is similar just doesn't work for IN yet. execution_options={"synchronize_session": "fetch"}, )
def get_data(self): c = ','.join(self.cols) if self.cols else '*' query = text(f' {c} from {self.table}') stmt = lambda_stmt(lambda: select(query)) if self.iterate_from and self.iterate_to: col = column(self.iterable_column) it_from = self.iterate_from it_to = self.iterate_to stmt += lambda s: s.where((col >= it_from) & (col <= it_to)) if self.iterable_column: col = column(self.iterable_column) stmt += lambda s: s.order_by(col) result = self.connection.execute(stmt) return result, [col for col in result.keys()]
def query(names): class Foo(object): def __init__(self): self.u1 = aliased(User) f1 = Foo() stmt = lambda_stmt( lambda: select(f1.u1).where(f1.u1.name.in_(names)). options(selectinload(f1.u1.addresses)), track_on=[f1.u1], ).add_criteria(lambda s: s.order_by(f1.u1.id), track_on=[f1.u1]) return s.execute(stmt)
def test_update_fetch_returning_lambda(self): User = self.classes.User sess = Session(testing.db, future=True) john, jack, jill, jane = (sess.execute(select(User).order_by( User.id)).scalars().all()) with self.sql_execution_asserter() as asserter: stmt = lambda_stmt(lambda: update(User).where(User.age > 29). values({"age": User.age - 10})) sess.execute(stmt, execution_options={"synchronize_session": "fetch"}) # these are simple values, these are now evaluated even with # the "fetch" strategy, new in 1.4, so there is no expiry eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27]) if testing.db.dialect.full_returning: asserter.assert_( CompiledSQL( "UPDATE users SET age_int=(users.age_int - %(age_int_1)s) " "WHERE users.age_int > %(age_int_2)s RETURNING users.id", [{ "age_int_1": 10, "age_int_2": 29 }], dialect="postgresql", ), ) else: asserter.assert_( CompiledSQL( "SELECT users.id FROM users " "WHERE users.age_int > :age_int_1", [{ "age_int_1": 29 }], ), CompiledSQL( "UPDATE users SET age_int=(users.age_int - :age_int_1) " "WHERE users.age_int > :age_int_2", [{ "age_int_1": 10, "age_int_2": 29 }], ), )
def test_delete_fetch_returning_lambda(self): User = self.classes.User sess = Session(testing.db, future=True) john, jack, jill, jane = (sess.execute(select(User).order_by( User.id)).scalars().all()) in_(john, sess) in_(jack, sess) with self.sql_execution_asserter() as asserter: stmt = lambda_stmt(lambda: delete(User).where(User.age > 29)) sess.execute(stmt, execution_options={"synchronize_session": "fetch"}) if testing.db.dialect.full_returning: asserter.assert_( CompiledSQL( "DELETE FROM users WHERE users.age_int > %(age_int_1)s " "RETURNING users.id", [{ "age_int_1": 29 }], dialect="postgresql", ), ) else: asserter.assert_( CompiledSQL( "SELECT users.id FROM users " "WHERE users.age_int > :age_int_1", [{ "age_int_1": 29 }], ), CompiledSQL( "DELETE FROM users WHERE users.age_int > :age_int_1", [{ "age_int_1": 29 }], ), ) in_(john, sess) not_in_(jack, sess) in_(jill, sess) not_in_(jane, sess)
def lambda_stmt_and_join_attributes( schema_version: int, no_attributes: bool, include_last_changed: bool = True ) -> tuple[StatementLambdaElement, bool]: """Return the lambda_stmt and if StateAttributes should be joined. Because these are lambda_stmt the values inside the lambdas need to be explicitly written out to avoid caching the wrong values. """ # If no_attributes was requested we do the query # without the attributes fields and do not join the # state_attributes table if no_attributes: if include_last_changed: return lambda_stmt(lambda: select(*QUERY_STATE_NO_ATTR)), False return ( lambda_stmt(lambda: select(*QUERY_STATE_NO_ATTR_NO_LAST_CHANGED)), False, ) # If we in the process of migrating schema we do # not want to join the state_attributes table as we # do not know if it will be there yet if schema_version < 25: if include_last_changed: return ( lambda_stmt(lambda: select(*QUERY_STATES_PRE_SCHEMA_25)), False, ) return ( lambda_stmt( lambda: select(*QUERY_STATES_PRE_SCHEMA_25_NO_LAST_CHANGED)), False, ) # Finally if no migration is in progress and no_attributes # was not requested, we query both attributes columns and # join state_attributes if include_last_changed: return lambda_stmt(lambda: select(*QUERY_STATES)), True return lambda_stmt(lambda: select(*QUERY_STATES_NO_LAST_CHANGED)), True
def find_legacy_row() -> StatementLambdaElement: """Check if there are still states in the table with an event_id.""" return lambda_stmt(lambda: select(func.max(States.event_id)))