Skip to main content
 首页 » 编程设计

python之SQLAlchemy之再次运行自动加载

2025年02月15日9xiaohuochai

我有一个 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 中工作得很好。在多线程应用程序中使用它可能不安全...