# -*- 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():
