I think I see my biggest issue now. I thought that I needed multiple
`declarative_base` for each database but I can use the same one for all
models and specify schema.
On Wednesday, March 20, 2019 at 1:02:47 PM UTC-4, mkwyche wrote:
>
> Here's the code I'm using to create `declarative_base`:
>
> ```
>
> PHIL_DATA = "phil_data"
> PHIL_DATA3 = "phil_data3"
> MESA = "mesa"
> SQL_DEBUG = bool(os.getenv("SQL_DEBUG", False))
> _ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA, echo=SQL_DEBUG)
> _ENGINE_PHIL_DATA3 = get_mysql_engine(database=PHIL_DATA3, echo=SQL_DEBUG)
> _ENGINE_MESA = get_mysql_engine(database=MESA, echo=SQL_DEBUG)
>
> # Set up base contstants
> PHIL_DATA3_BASE = declarative_base(bind=_ENGINE_PHIL_DATA3.connect())
> PHIL_DATA_BASE = declarative_base(bind=_ENGINE_PHIL_DATA.connect())
> MESA_BASE = declarative_base(bind=_ENGINE_MESA.connect())
> ```
>
> That base is then used to create their models:
>
> ```
>
> class PlayerPro(MESA_BASE):
> ''' player_pro model '''
> __tablename__ = "player_pro"
>
> # Primary Keys
> ebis_id = Column(MEDIUMINT(9), primary_key=True)
> bam_id = Column(MEDIUMINT(9), index=True)
> phil_id = Column(MEDIUMINT(9), index=True)
>
>
>
> class EvalProHit(PHIL_DATA_BASE):
> ''' Eval Pro Hit model '''
> __tablename__ = "eval_pro_hit"
>
> def list_id_default(self): # pylint: disable=no-self-use
> return uuid.uuid4().hex
>
> # Keys
> eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default)
> phil_id = Column(MEDIUMINT(9), nullable=False)
>
> ForeignKeyConstraint([phil_id],
> [PlayerPro.phil_id],
> onupdate="CASCADE")
>
> ``` Prior to adding Schema as a table_args to PlayerPro model the creation
> of the foreign key didn't add `mesa.player_pro.phil_id` and instead was
> using `player_pro.phil_id? Adding schema makes things
>
> work. But I'm wondering why that info couldn't have been derived from the
> base associated with the actual model.
>
>
> On Wednesday, March 20, 2019 at 9:55:44 AM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Mar 20, 2019 at 7:43 AM mkwyche <mkw...@gmail.com> wrote:
>> >
>> > For anyone else that runs into a similar issue. This
>> https://stackoverflow.com/questions/43584453/how-to-handle-cross-schema-foreign-key-in-sqlalchemy
>>
>> was helpful.
>> >
>> > I needed to add
>> > ```
>> > __table_args__ = {'schema': 'mesa'}
>> > ```
>> >
>> > To `player_pro` model. Then it worked. Why isn't the schema derived
>> from the `declaritive_base` associated with the model?
>>
>> How is the schema being associated with the declarative base ? I
>> don't see any mention of "schema" until your example here.
>>
>>
>> >
>> > On Wednesday, March 20, 2019 at 7:16:06 AM UTC-4, mkwyche wrote:
>> >>
>> >> Also I tried to put in their mesa.player_pro.phil_Id instead of the
>> actual column and I get this error:
>> >>
>> >> ```
>> >> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
>> column 'eval_pro_hit.phil_id' could not find table 'mesa.player_pro' with
>> which to generate a foreign key to target column 'phil_id'
>> >> ```
>> >>
>> >> Is there a way to reference a table from another DeclaritiveBase?
>> >>
>> >> On Wednesday, March 20, 2019 at 7:11:06 AM UTC-4, mkwyche wrote:
>> >>>
>> >>> Hi,
>> >>>
>> >>> I have two classes:
>> >>>
>> >>> ```python
>> >>>
>> >>> class PlayerPro(MESA_BASE):
>> >>> ''' player_pro model '''
>> >>> __tablename__ = "player_pro"
>> >>>
>> >>> # Primary Keys
>> >>> ebis_id = Column(MEDIUMINT(9), primary_key=True)
>> >>> bam_id = Column(MEDIUMINT(9), index=True)
>> >>> phil_id = Column(MEDIUMINT(9), index=True)
>> >>>
>> >>>
>> >>>
>> >>> class EvalProHit(PHIL_DATA_BASE):
>> >>> ''' Eval Pro Hit model '''
>> >>> __tablename__ = "eval_pro_hit"
>> >>>
>> >>> def list_id_default(self): # pylint: disable=no-self-use
>> >>> return uuid.uuid4().hex
>> >>>
>> >>> # Keys
>> >>> eval_id = Column(VARCHAR(255), primary_key=True,
>> default=list_id_default)
>> >>> phil_id = Column(MEDIUMINT(9), nullable=False)
>> >>>
>> >>> ForeignKeyConstraint([phil_id],
>> >>> [PlayerPro.phil_id],
>> >>> onupdate="CASCADE")
>> >>>
>> >>> ```
>> >>>
>> >>> There in two different databases. When I go to create the tables the
>> create statement is incorrect:
>> >>>
>> >>> ```sql
>> >>> INFO:sqlalchemy.engine.base.Engine:
>> >>> CREATE TABLE eval_pro_hit (
>> >>> eval_id VARCHAR(255) NOT NULL,
>> >>> phil_id MEDIUMINT(9) NOT NULL,
>> >>>
>> >>> FOREIGN KEY(phil_id) REFERENCES player_pro (phil_id) ON UPDATE
>> CASCADE
>> >>> )
>> >>> ```
>> >>>
>> >>> Should be mesa.player_pro instead of player_pro. This is occuring
>> from PHIL_DATA_BASE.metadata.create_all() . How can I accomplish this with
>> models?
>> >
>> > --
>> > SQLAlchemy -
>> > The Python SQL Toolkit and Object Relational Mapper
>> >
>> > http://www.sqlalchemy.org/
>> >
>> > To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> > ---
>> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> > To unsubscribe from this group and stop receiving emails from it, send
>> an email to sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
