我有一个 Web 应用程序,它允许用户运行一些 ETL 并通过 UI 中的 SQLAlchemy 模型公开最终表。这些模型使用 SQLAlchemy 的 auto_load 特性:
Base = declarative_base(name='Model')
class Product(Base):
__table__ = Table('product', Base.metadata,
Column('product_number', Text, primary_key=True),
autoload=True, autoload_with=engine, schema='data')
有时 ETL 会导致不同的表结构(新的/删除的/重命名的列,表名仍然相同),直到现在我必须重新启动整个 Web 应用程序以更新自动加载的 SQLAlchemy 模型。有没有办法在我的模型上重新运行自动加载过程?
请您参考如下方法:
我现在构建了一个可以像模块一样使用的访问器类:
from sqlalchemy.ext.declarative import declarative_base
# in my case this saves the last timestamp when the db was
# updated into a memcache
from app.elsewhere import get_last_database_change_time
class Models():
"""A module-like accessor which allows to reset the models.
"""
_models = {}
def __init__(self):
self.Base = declarative_base(name='Base')
self._last_reset = get_last_database_change_time()
def reset(self):
"""Resets all models so that they are autoloaded again the next time they are accessed via the properties"""
self._models.clear()
# without resetting Base, we get errors that the same table is declared twice...
# extend_existing = True was not enough :-(
self.Base = declarative_base(name='Base')
def __reset_if_needed(self):
"""Resets all models after the database changed"""
# this is backed by a memcached value which gets reset when the ETL finishes
# needed as we do not have direct access to this object when we run the ETL
last_reset = get_last_database_change_time()
if last_reset != self._last_reset:
self.reset()
self._last_reset = last_reset
@property
def Model1(self):
self.__reset_if_needed()
if 'Model1' not in self._models:
class Model1(self.Base):
__tablename__ = 'table_name'
__table_args__ = dict(schema='schema_name', autoload=True, autoload_with=some_engine,
extend_existing=True)
id = Column(Text, primary_key=True)
self._models['Model1'] = Model1
return self._models['Model1']
def __declare_main_models(self):
"""Helper to declare the main models in one go due to dependency issues"""
class Model2(self.Base):
# model with dependency on/relationship with Model3
class Model3(self.Base):
# model with dependency on/relationship with Model2
self._models['Model2'] = Model2
self._models['Model3'] = Model3
@property
def Model2(self):
self.__reset_if_needed()
if 'Model2' not in self._models:
self.__declare_main_models()
return self._models['Model2']
models = Models()
像这样使用:
from app.database import models as m
p = m.Model1(...)
对于某些模型,我不得不一次性声明它们,因为它们彼此之间存在关系,并且很难解决这个问题。
对于“只读”情况,这在具有多个工作人员(=进程)的 gunicorn 中工作得很好。在多线程应用程序中使用它可能不安全...
