the biggest issue is that you are being confused by the ".bind"
argument to MetaData, in that it suggests the MetaData would know
about the default schema name of the engine which is not the case.
The .bind argument will be going away in a future release for reasons
like this.
On Wed, Mar 20, 2019 at 2:23 PM mkwyche <mkwy...@gmail.com> wrote:
>
> 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.
--
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.
