# -*- coding: utf-8 -*- from sqlalchemy.orm import declarative_base from zvt.contract.register import register_schema from zvt.contract.schema import ActorEntity ActorMetaBase = declarative_base() # 参与者 class ActorMeta(ActorMetaBase, ActorEntity): __tablename__ = 'actor_meta' register_schema(providers=['em'], db_name='actor_meta', schema_base=ActorMetaBase) # the __all__ is generated __all__ = ['ActorMeta']
def _fixture(self): Base = declarative_base() class Person(Base): __tablename__ = "person" id = Column(Integer, primary_key=True) _name = Column(String) @hybrid.hybrid_property def name(self): return self._name @name.setter def name(self, value): self._name = value.title() class OverrideSetter(Person): __tablename__ = "override_setter" id = Column(Integer, ForeignKey("person.id"), primary_key=True) other = Column(String) @Person.name.setter def name(self, value): self._name = value.upper() class OverrideGetter(Person): __tablename__ = "override_getter" id = Column(Integer, ForeignKey("person.id"), primary_key=True) other = Column(String) @Person.name.getter def name(self): return "Hello " + self._name class OverrideExpr(Person): __tablename__ = "override_expr" id = Column(Integer, ForeignKey("person.id"), primary_key=True) other = Column(String) @Person.name.overrides.expression def name(self): return func.concat("Hello", self._name) class FooComparator(hybrid.Comparator): def __clause_element__(self): return func.concat("Hello", self.expression._name) class OverrideComparator(Person): __tablename__ = "override_comp" id = Column(Integer, ForeignKey("person.id"), primary_key=True) other = Column(String) @Person.name.overrides.comparator def name(self): return FooComparator(self) return ( Person, OverrideSetter, OverrideGetter, OverrideExpr, OverrideComparator, )
import os import pika from fastapi.middleware.cors import CORSMiddleware from fastapi import FastAPI from sqlalchemy import Column, Integer, String from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, declarative_base SQLALCHEMY_DATABASE_URL = USER = os.getenv( 'DB_URI', "postgresql://*****:*****@localhost:5432/dev") engine = create_engine(SQLALCHEMY_DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = declarative_base() app = FastAPI() origins = [ "*", ] app.add_middleware( CORSMiddleware, allow_origins=origins, allow_credentials=True, allow_methods=["*"], allow_headers=["*"], )
def legacy_database_fixer(database_path, database, database_name, database_exists): database_directory = os.path.dirname(database_path) old_database_path = database_path old_filename = os.path.basename(old_database_path) new_filename = f"Pre_Alembic_{old_filename}" pre_alembic_path = os.path.join(database_directory, new_filename) pre_alembic_database_exists = False if os.path.exists(pre_alembic_path): database_path = pre_alembic_path pre_alembic_database_exists = True datas = [] if database_exists: Session, engine = db_helper.create_database_session(database_path) database_session = Session() result = inspect(engine).has_table('alembic_version') if not result: if not pre_alembic_database_exists: os.rename(old_database_path, pre_alembic_path) pre_alembic_database_exists = True if pre_alembic_database_exists: Session, engine = db_helper.create_database_session(pre_alembic_path) database_session = Session() api_table = database.api_table() media_table = database.media_table() Base = declarative_base() # DON'T FORGET TO REMOVE # database_name = "posts" # DON'T FORGET TO REMOVE legacy_api_table = api_table.legacy(Base, database_name) legacy_media_table = media_table.legacy(Base) result = database_session.query(legacy_api_table) post_db = result.all() for post in post_db: post_id = post.id created_at = post.created_at new_item = {} new_item["post_id"] = post_id new_item["text"] = post.text new_item["price"] = post.price new_item["paid"] = post.paid new_item["postedAt"] = created_at new_item["medias"] = [] result2 = database_session.query(legacy_media_table) media_db = result2.filter_by(post_id=post_id).all() for media in media_db: new_item2 = {} new_item2["media_id"] = media.id new_item2["post_id"] = media.post_id new_item2["links"] = [media.link] new_item2["directory"] = media.directory new_item2["filename"] = media.filename new_item2["size"] = media.size new_item2["media_type"] = media.media_type new_item2["downloaded"] = media.downloaded new_item2["created_at"] = created_at new_item["medias"].append(new_item2) datas.append(new_item) print database_session.close() x = export_sqlite(old_database_path, datas, database_name, legacy_fixer=True) print
# -*- coding: utf-8 -*- from sqlalchemy.orm import declarative_base from zvt.contract import TradableEntity from zvt.contract.register import register_schema, register_entity StockusMetaBase = declarative_base() # 美股 @register_entity(entity_type="stockus") class Stockus(StockusMetaBase, TradableEntity): __tablename__ = "stockus" register_schema(providers=["em"], db_name="stockus_meta", schema_base=StockusMetaBase) # the __all__ is generated __all__ = ["Stockus"]
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, DateTime, Boolean, Float, Integer, ForeignKey from sqlalchemy.orm import declarative_base from sqlalchemy.orm import relationship from zvt.contract import Mixin from zvt.contract.register import register_schema from zvt.utils import to_string TraderBase = declarative_base() # trader信息 class TraderInfo(TraderBase, Mixin): __tablename__ = 'trader_info' # 机器人名字 trader_name = Column(String(length=128)) entity_type = Column(String(length=128)) start_timestamp = Column(DateTime) end_timestamp = Column(DateTime) provider = Column(String(length=32)) level = Column(String(length=32)) real_time = Column(Boolean) kdata_use_begin_time = Column(Boolean) kdata_adjust_type = Column(String(length=32)) # account stats of every day @to_string class AccountStats(TraderBase, Mixin):
def _register_class(target_class): if target_class.__name__ not in ('Factor', 'FilterFactor', 'ScoreFactor', 'StateFactor'): factor_cls_registry[target_class.__name__] = target_class class FactorMeta(type): def __new__(meta, name, bases, class_dict): cls = type.__new__(meta, name, bases, class_dict) _register_class(cls) return cls FactorBase = declarative_base() # 用于保存factor的状态 class FactorState(FactorBase, Mixin): __tablename__ = 'factor_state' # 因子名字 factor_name = Column(String(length=128)) # json string state = Column(Text()) register_schema(providers=['zvt'], db_name='factor_info', schema_base=FactorBase)
@sqla.event.listens_for(sqla.engine.Engine, "connect") def set_sqlite_pragma(dbapi_connection, connection_record): cursor = dbapi_connection.cursor() cursor.execute("PRAGMA foreign_keys=ON") cursor.close() # Contextual/thread-local sessions. session_factory = sqla_orm.sessionmaker( autocommit=False, autoflush=False, bind=engine, ) Session = sqla_orm.scoped_session(session_factory) # Declarative base. Base = sqla_orm.declarative_base(metadata=metadata) Base.query = Session.query_property() # Connection. def get_connection(): return engine.connect() # Session. def get_session(): return sqla_orm.Session(engine) # Table. def get_table(name): return sqla.Table( name, metadata,
""" aim: execute select/insert/update with table-class Reference: - https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html - [Update class](https://docs.sqlalchemy.org/en/14/core/dml.html#sqlalchemy.sql.expression.Update) """ from sqlalchemy import create_engine from sqlalchemy import MetaData, Table, Column, Integer, String from sqlalchemy.orm import declarative_base, Session, aliased from sqlalchemy import insert, select, update Base = declarative_base() # sqlalchemy.orm.decl_api.DeclarativeMeta class User(Base): __tablename__ = 'user_account' id = Column(Integer, primary_key=True) name = Column(String(30)) fullname = Column(String) def __repr__(self): return f"""User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})""" driver2db = create_engine(url="sqlite+pysqlite:///:memory:", echo=False, future=True) # new tables Base.metadata.create_all(driver2db)
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Float from sqlalchemy.orm import declarative_base from zvt.contract.schema import TradableEntity from zvt.contract.register import register_schema, register_entity CountryMetaBase = declarative_base() @register_entity(entity_type="country") class Country(CountryMetaBase, TradableEntity): __tablename__ = "country" #: 区域 #: region region = Column(String(length=128)) #: 首都 #: capital city capital_city = Column(String(length=128)) #: 收入水平 #: income level income_level = Column(String(length=64)) #: 贷款类型 #: lending type lending_type = Column(String(length=64)) #: 经度 #: longitude longitude = Column(Float) #: 纬度
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, DateTime, Float, Boolean, Integer from sqlalchemy.orm import declarative_base from zvt.contract.register import register_schema from zvt.contract.schema import TradableMeetActor StockActorBase = declarative_base() class StockTopTenFreeHolder(StockActorBase, TradableMeetActor): __tablename__ = 'stock_top_ten_free_holder' report_period = Column(String(length=32)) report_date = Column(DateTime) # 持股数 holding_numbers = Column(Float) # 持股比例 holding_ratio = Column(Float) # 持股市值 holding_values = Column(Float) class StockTopTenHolder(StockActorBase, TradableMeetActor): __tablename__ = 'stock_top_ten_holder' report_period = Column(String(length=32)) report_date = Column(DateTime) # 持股数
# -*- coding: utf-8 -*- from sqlalchemy import Column, String from sqlalchemy.orm import declarative_base from zvt.contract import Mixin from zvt.contract.register import register_schema StockTagsBase = declarative_base() class StockTags(StockTagsBase, Mixin): """ Schema for storing stock tags """ __tablename__ = "stock_tags" #: :class:`~.zvt.tag.tags.actor_tag.ActorTag` values actor_tag = Column(String(length=64)) #: :class:`~.zvt.tag.tags.style_tag.StyleTag` values style_tag = Column(String(length=64)) #: :class:`~.zvt.tag.tags.cycle_tag.CycleTag` values cycle_tag = Column(String(length=64)) #: :class:`~.zvt.tag.tags.market_value_tag.MarketValueTag` values market_value_tag = Column(String(length=64)) register_schema(providers=["zvt"], db_name="stock_tags", schema_base=StockTagsBase) # the __all__ is generated
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Float from sqlalchemy.orm import declarative_base from zvt.contract import Portfolio, PortfolioStockHistory from zvt.contract.register import register_schema, register_entity IndexMetaBase = declarative_base() # 指数 @register_entity(entity_type='index') class Index(IndexMetaBase, Portfolio): __tablename__ = 'index' # 发布商 publisher = Column(String(length=64)) # 类别 # see IndexCategory category = Column(String(length=64)) # 基准点数 base_point = Column(Float) class IndexStock(IndexMetaBase, PortfolioStockHistory): __tablename__ = 'index_stock' register_schema(providers=['exchange'], db_name='index_meta', schema_base=IndexMetaBase) # the __all__ is generated
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Float from sqlalchemy.orm import declarative_base from zvt.contract import Mixin from zvt.contract.register import register_schema MonetaryBase = declarative_base() class TreasuryYield(MonetaryBase, Mixin): __tablename__ = "treasury_yield" code = Column(String(length=32)) # 2年期 yield_2 = Column(Float) # 5年期 yield_5 = Column(Float) # 10年期 yield_10 = Column(Float) # 30年期 yield_30 = Column(Float) register_schema(providers=["em"], db_name="monetary", schema_base=MonetaryBase) # the __all__ is generated __all__ = ["TreasuryYield"]
from kombu.utils.compat import register_after_fork from sqlalchemy import create_engine from sqlalchemy.exc import DatabaseError from sqlalchemy.orm import sessionmaker from sqlalchemy.pool import NullPool from celery.utils.time import get_exponential_backoff_interval try: from sqlalchemy.orm import declarative_base except ImportError: # TODO: Remove this once we drop support for SQLAlchemy < 1.4. from sqlalchemy.ext.declarative import declarative_base ResultModelBase = declarative_base() __all__ = ('SessionManager',) PREPARE_MODELS_MAX_RETRIES = 10 def _after_fork_cleanup_session(session): session._after_fork() class SessionManager: """Manage SQLAlchemy sessions.""" def __init__(self): self._engines = {}
from sqlalchemy import Column, ForeignKey, Integer from sqlalchemy.orm import declarative_base, relationship BaseDBModel = declarative_base() class Address(BaseDBModel): __tablename__ = 'address' id = Column(Integer, primary_key=True) class Person(BaseDBModel): __tablename__ = 'person' id = Column(Integer, primary_key=True) address_id = Column(Integer, ForeignKey('address.id')) addresses = relationship(Address)
# -*- coding: utf-8 -*- from sqlalchemy.orm import declarative_base from zvt.contract.register import register_schema, register_entity from zvt.contract.schema import TradableEntity CurrencyMetaBase = declarative_base() @register_entity(entity_type="currency") class Currency(CurrencyMetaBase, TradableEntity): __tablename__ = "currency" register_schema(providers=["em"], db_name="currency_meta", schema_base=CurrencyMetaBase) # the __all__ is generated __all__ = ["Currency"]
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Float from sqlalchemy.orm import declarative_base from zvt.contract import Mixin from zvt.contract.register import register_schema TradingBase = declarative_base() class ManagerTrading(TradingBase, Mixin): __tablename__ = "manager_trading" provider = Column(String(length=32)) code = Column(String(length=32)) # 日期 变动人 变动数量(股) 交易均价(元) 结存股票(股) 交易方式 董监高管 高管职位 与高管关系 # 2017-08-11 韦春 200 9.16 -- 竞价交易 刘韬 高管 兄弟姐妹 # 变动人 trading_person = Column(String(length=32)) # 变动数量 volume = Column(Float) # 交易均价 price = Column(Float) # 结存股票 holding = Column(Float) # 交易方式 trading_way = Column(String(length=32)) # 董监高管 manager = Column(String(length=32)) # 高管职位
from sqlalchemy import (Boolean, Column, DateTime, Float, ForeignKey, Integer, String, create_engine, desc, func, inspect) from sqlalchemy.exc import NoSuchModuleError from sqlalchemy.orm import Query, declarative_base, relationship, scoped_session, sessionmaker from sqlalchemy.pool import StaticPool from sqlalchemy.sql.schema import UniqueConstraint from freqtrade.constants import DATETIME_PRINT_FORMAT, NON_OPEN_EXCHANGE_STATES from freqtrade.enums import SellType from freqtrade.exceptions import DependencyException, OperationalException from freqtrade.misc import safe_value_fallback from freqtrade.persistence.migrations import check_migrate logger = logging.getLogger(__name__) _DECL_BASE: Any = declarative_base() _SQL_DOCS_URL = 'http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls' def init_db(db_url: str, clean_open_orders: bool = False) -> None: """ Initializes this module with the given config, registers all known command handlers and starts polling for message updates :param db_url: Database to use :param clean_open_orders: Remove open orders from the database. Useful for dry-run or if all orders have been reset on the exchange. :return: None """ kwargs = {}
### api_table.py ### from datetime import datetime from typing import cast import sqlalchemy from sqlalchemy.orm import declarative_base # type: ignore LegacyBase = declarative_base() class api_table: __tablename__ = "" id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) post_id = sqlalchemy.Column(sqlalchemy.Integer, unique=True, nullable=False) text = sqlalchemy.Column(sqlalchemy.String) price = cast(int, sqlalchemy.Column(sqlalchemy.Integer)) paid = sqlalchemy.Column(sqlalchemy.Integer) archived = cast(bool, sqlalchemy.Column(sqlalchemy.Boolean, default=False)) created_at = cast(datetime, sqlalchemy.Column(sqlalchemy.TIMESTAMP)) def legacy(self, table_name): class legacy_api_table(LegacyBase): __tablename__ = table_name id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) text = sqlalchemy.Column(sqlalchemy.String) price = sqlalchemy.Column(sqlalchemy.Integer) paid = sqlalchemy.Column(sqlalchemy.Integer) created_at = sqlalchemy.Column(sqlalchemy.DATETIME) return legacy_api_table
def setup_test(self): global Base Base = declarative_base()
# Copyright (c) 2020 Huawei Technologies Co.,Ltd. # # openGauss is licensed under Mulan PSL v2. # You can use this software according to the terms and conditions of the Mulan PSL v2. # You may obtain a copy of Mulan PSL v2 at: # # http://license.coscl.org.cn/MulanPSL2 # # THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND, # EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT, # MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE. # See the Mulan PSL v2 for more details. from sqlalchemy.orm import declarative_base # To storage at remote database server, mainly saving large scale data business, such as # the result of time-series forecasting and slow query analysis. Base = declarative_base() # To record dynamic config not likes static text-based file. # The dynamic config can be modified by user frequently and fresh immediately. DynamicConfig = declarative_base(name='DynamicConfig')
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Float from sqlalchemy.orm import declarative_base from zvt.contract import Mixin from zvt.contract.register import register_schema ValuationBase = declarative_base() class StockValuation(ValuationBase, Mixin): __tablename__ = "stock_valuation" code = Column(String(length=32)) name = Column(String(length=32)) # 总股本(股) capitalization = Column(Float) # 公司已发行的普通股股份总数(包含A股,B股和H股的总股本) circulating_cap = Column(Float) # 市值 market_cap = Column(Float) # 流通市值 circulating_market_cap = Column(Float) # 换手率 turnover_ratio = Column(Float) # 静态pe pe = Column(Float) # 动态pe pe_ttm = Column(Float) # 市净率 pb = Column(Float)
def _deserialize(value): if value is None: return None # sqlite likes to return ints for strings that look like ints, even though # the column type is string. That's how you do dynamic typing wrong. value = str(value) # Just in case someone's mucking with the DB in a way we can't account for, # ignore json parsing errors try: value = json.loads(value) except ValueError: pass return value BASE = declarative_base() MYSQL_TABLE_ARGS = { 'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_ci' } class NickIDs(BASE): """Nick IDs table SQLAlchemy class.""" __tablename__ = 'nick_ids' nick_id = Column(Integer, primary_key=True) class Nicknames(BASE): """Nicknames table SQLAlchemy class."""
def setup_test_class(cls): from sqlalchemy import literal symbols = ("usd", "gbp", "cad", "eur", "aud") currency_lookup = dict( ((currency_from, currency_to), Decimal(str(rate))) for currency_to, values in zip( symbols, [ (1, 1.59009, 0.988611, 1.37979, 1.02962), (0.628895, 1, 0.621732, 0.867748, 0.647525), (1.01152, 1.6084, 1, 1.39569, 1.04148), (0.724743, 1.1524, 0.716489, 1, 0.746213), (0.971228, 1.54434, 0.960166, 1.34009, 1), ], ) for currency_from, rate in zip(symbols, values) ) class Amount(object): def __init__(self, amount, currency): self.currency = currency self.amount = amount def __add__(self, other): return Amount( self.amount + other.as_currency(self.currency).amount, self.currency, ) def __sub__(self, other): return Amount( self.amount - other.as_currency(self.currency).amount, self.currency, ) def __lt__(self, other): return self.amount < other.as_currency(self.currency).amount def __gt__(self, other): return self.amount > other.as_currency(self.currency).amount def __eq__(self, other): return self.amount == other.as_currency(self.currency).amount def as_currency(self, other_currency): return Amount( currency_lookup[(self.currency, other_currency)] * self.amount, other_currency, ) def __clause_element__(self): # helper method for SQLAlchemy to interpret # the Amount object as a SQL element if isinstance(self.amount, (float, int, Decimal)): return literal(self.amount) else: return self.amount def __str__(self): return "%2.4f %s" % (self.amount, self.currency) def __repr__(self): return "Amount(%r, %r)" % (self.amount, self.currency) Base = declarative_base() class BankAccount(Base): __tablename__ = "bank_account" id = Column(Integer, primary_key=True) _balance = Column("balance", Numeric) @hybrid.hybrid_property def balance(self): """Return an Amount view of the current balance.""" return Amount(self._balance, "usd") @balance.setter def balance(self, value): self._balance = value.as_currency("usd").amount cls.Amount = Amount cls.BankAccount = BankAccount
# -*- coding: utf-8 -*- from sqlalchemy import Column, String, Text from sqlalchemy.orm import declarative_base from zvt.contract import Mixin from zvt.contract.register import register_schema ZvtInfoBase = declarative_base() # 用于保存recorder的状态 class RecorderState(ZvtInfoBase, Mixin): __tablename__ = 'recoder_state' # recorder名字 recoder_name = Column(String(length=128)) # json string state = Column(Text()) # 用于保存tagger的状态 class TaggerState(ZvtInfoBase, Mixin): __tablename__ = 'tagger_state' # tagger名字 tagger_name = Column(String(length=128)) # json string state = Column(Text()) register_schema(providers=['zvt'], db_name='zvt_info', schema_base=ZvtInfoBase)
# -*- coding: utf-8 -*- import sqlalchemy as sa from sqlalchemy import orm import sqlalchemy_mate as sam from learn_sqlalchemy.db import engine_psql as engine # use PostgresSQL engine Base = orm.declarative_base() class User(Base, sam.ExtendedBase): __tablename__ = "user" id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) with orm.Session(engine) as ses: ses.add_all([ User(id=1, name="Alice1"), User(id=2, name="Bob1"), User(id=3, name="Cathy1"), User(id=4, name="David1"), ]) ses.commit() # try with a transaction with orm.Session(engine) as ses:
"""Models used by Sql Alchemy""" # pylint:disable=too-few-public-methods from sqlalchemy import Column # type: ignore from sqlalchemy.ext.declarative import DeclarativeMeta # type: ignore from sqlalchemy.orm import declarative_base # type: ignore from sqlalchemy.types import Date, DateTime, Integer, Numeric, String, Time # type: ignore Base: DeclarativeMeta = declarative_base() class CirculatorRidershipXLS(Base): """Table holding the ridership by vehicle, route and date""" __tablename__ = 'ccc_aggregate_ridership_manual' RidershipDate = Column(Date, primary_key=True) Route = Column(String(length=10), primary_key=True) BlockID = Column(Integer, primary_key=True) Riders = Column(Integer) class CirculatorArrival(Base): """Table holding circulator arrival times and on time status""" __tablename__ = 'ccc_arrival_times' date = Column(Date, primary_key=True) route = Column(String(length=50), primary_key=True) stop = Column(String) block_id = Column(String(length=100), primary_key=True) scheduled_arrival_time = Column(Time, primary_key=True) actual_arrival_time = Column(Time) scheduled_departure_time = Column(Time)
def test_sqlalchemy(self): # using postgres with log_statement=all so that we can see the incorrect queries # (use a transient docker instance) dbname = ''.join(choice(ascii_letters) for _ in range(16)).lower() # https://stackoverflow.com/questions/6506578/how-to-create-a-new-database-using-sqlalchemy engine = create_engine('postgresql://postgres@localhost:5432/postgres') conn = engine.connect() conn.execute('commit') conn.execute(f'create database {dbname}') conn.close() engine = create_engine( f'postgresql://postgres@localhost:5432/{dbname}') Base = declarative_base() Session = sessionmaker(engine) class SourceType(IntEnum): SOURCE = 0 ACTIVITY = 2 ACTIVITY_TOPIC = 10 class StatisticJournalType(IntEnum): STATISTIC = 0 TIMESTAMP = 4 class FileHash(Base): __tablename__ = 'file_hash' id = Column(Integer, primary_key=True) class Source(Base): __tablename__ = 'source' id = Column(Integer, primary_key=True) type = Column(Integer, nullable=False, index=True) __mapper_args__ = { 'polymorphic_identity': SourceType.SOURCE, 'polymorphic_on': type } class GroupedSource(Source): __abstract__ = True class ActivityJournal(GroupedSource): __tablename__ = 'activity_journal' id = Column(Integer, ForeignKey('source.id', ondelete='cascade'), primary_key=True) file_hash_id = Column(Integer, ForeignKey('file_hash.id'), nullable=False, index=True, unique=True) file_hash = relationship('FileHash', backref=backref('activity_journal', uselist=False)) __mapper_args__ = {'polymorphic_identity': SourceType.ACTIVITY} class ActivityTopicJournal(GroupedSource): __tablename__ = 'activity_topic_journal' id = Column(Integer, ForeignKey('source.id', ondelete='cascade'), primary_key=True) file_hash_id = Column(Integer, ForeignKey('file_hash.id'), nullable=False, index=True, unique=True) file_hash = relationship('FileHash', backref=backref('activity_topic_journal', uselist=False)) __mapper_args__ = { 'polymorphic_identity': SourceType.ACTIVITY_TOPIC } class StatisticName(Base): __tablename__ = 'statistic_name' id = Column(Integer, primary_key=True) name = Column(Text, nullable=False) class StatisticJournal(Base): __tablename__ = 'statistic_journal' id = Column(Integer, primary_key=True) type = Column(Integer, nullable=False, index=True) statistic_name_id = Column(Integer, ForeignKey('statistic_name.id', ondelete='cascade'), nullable=False) statistic_name = relationship('StatisticName') source_id = Column(Integer, ForeignKey('source.id', ondelete='cascade'), nullable=False) source = relationship('Source') __mapper_args__ = { 'polymorphic_identity': StatisticJournalType.STATISTIC, 'polymorphic_on': 'type' } class StatisticJournalTimestamp(StatisticJournal): __tablename__ = 'statistic_journal_timestamp' id = Column(Integer, ForeignKey('statistic_journal.id', ondelete='cascade'), primary_key=True) value = Column(DateTime, nullable=False) __mapper_args__ = { 'polymorphic_identity': StatisticJournalType.TIMESTAMP } Base.metadata.create_all(engine) def build_source_query(s, value): q = s.query(Source.id). \ join(StatisticJournalTimestamp). \ join(StatisticName). \ filter(StatisticName.name.like('start')). \ filter(StatisticJournalTimestamp.value > value) q_direct = s.query(ActivityJournal.id). \ filter(ActivityJournal.id.in_(q.subquery().select())) q_via_topic = s.query(ActivityJournal.id). \ join(FileHash). \ join(ActivityTopicJournal). \ filter(ActivityTopicJournal.id.in_(q.subquery().select())) constraints = union(q_direct, q_via_topic).subquery().select() return s.query(Source).filter(Source.id.in_(constraints)) with Session() as s: build_source_query(s, dt.datetime(2020, 1, 1, 3, 0, tzinfo=pytz.UTC)).all() build_source_query(s, dt.datetime(2021, 1, 1, 3, 0, tzinfo=pytz.UTC)).all()
import sqlalchemy as sa from sqlalchemy.orm import declarative_base from sqlalchemy.orm import sessionmaker import typing_extensions as tx from handofcats import as_command metadata = sa.MetaData() Base = declarative_base(metadata=metadata) Session = sessionmaker(autoflush=False, autocommit=False) class Note(Base): __tablename__ = "notes" id = sa.Column(sa.Integer, primary_key=True) text = sa.Column(sa.String, nullable=False) completed = sa.Column(sa.Boolean, nullable=False, default=False) @as_command() def run(*, format: tx.Literal["json", "tabular"] = "tabular") -> None: db_url = "sqlite:///:memory:" engine = sa.create_engine( db_url, connect_args={"check_same_thread": False}, echo=True # , future=True ) metadata.create_all(bind=engine) session = Session(bind=engine) with session.begin():