为什么 schema_translate_map 不改变架构? [英] Why doesn't schema_translate_map change schema?

问题描述

我正在尝试使用 schema_translate_map 来更改架构:

I'm trying to use schema_translate_map to change a schema:

Base = declarative_base()


class DataAccessLayer():

    def __init__(self):
        conn_string = "mysql+mysqlconnector://root:root@localhost/"
        self.engine = create_engine(conn_string)
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        self.session = Session()

    def change_schema(self):
        self.session.connection(execution_options={"schema_translate_map": {"belgarath": "belgarath_test"}})


class Player(Base):
    __tablename__ = "player"
    __table_args__ = {'schema': "belgarath"}

    id_ = Column(Integer, primary_key=True)


dal = DataAccessLayer()
dal.change_schema()
qry = dal.session.query(Player.id_)
print(qry)

但是，SQL 输出为:

However, the SQL comes out as:

SELECT belgarath.player.id_ AS belgarath_player_id_ 
FROM belgarath.player

代替:

SELECT belgarath_test.player.id_ AS belgarath_test_player_id_ 
FROM belgarath_test.player

我哪里出错了?

推荐答案

试试如果你只是将 .all() 附加到你的 qry 会发生什么:

Try what happens if you simply append .all() to your qry:

from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class DataAccessLayer():

    def __init__(self):
        conn_string = "sqlite:///:memory:"
        #conn_string = "mysql+mysqlconnector://root:root@localhost/"
        self.engine = create_engine(conn_string)
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        self.session = Session()

    def change_schema(self):
        self.session.connection(execution_options={"schema_translate_map": {"belgarath": "belgarath_test"}})


class Player(Base):
    __tablename__ = "player"
    __table_args__ = {'schema': "belgarath"}

    id_ = Column(Integer, primary_key=True)


dal = DataAccessLayer()
dal.change_schema()
qry = dal.session.query(Player.id_)
print(qry.all())

输出(无痕):

OperationalError: (sqlite3.OperationalError) no such table: belgarath_test.player
[SQL: SELECT belgarath_test.player.id_ AS belgarath_player_id_ 
FROM belgarath_test.player]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

我不是专家，但我想这可能与以下问题有关:

I'm not an expert, but I guess this might be related to the following issue:

模式转换功能发生在编译器中，这显然是错误的.模式分配应该在生成 SQL 之后进行，这样我们只需要一个缓存键.这与#5002 一致，但是我认为即使是与烘焙等一起使用的现有缓存键机制也需要将架构翻译完全从编译器中拉出 1.4 并将其添加到从 ExecutionContext 发生的翻译中，以及扩展逻辑参数集.模式转换旨在为成百上千的模式提供服务，因此必须更改预缓存.

the schema translate feature takes place within the compiler and this is plainly wrong. the schema assignment should be taking place after the SQL is generated so that we only need one cache key. This is along the lines of #5002 however I think even the existing cache key mechanism used with baked etc. needs to pull the schema translate out of the compiler entirely for 1.4 and add it to the translations which occur from the ExecutionContext, along with the expanding parameter sets of logic. schema translate is intended to service many hundreds / thousands of schemas so having this occur pre-cache has to change.

这篇关于为什么 schema_translate_map 不改变架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！