Thanks for the help. Is it best practice to use one declarative_base for
all models and set the schema on any Models that reference separate
databases?
On Wednesday, March 20, 2019 at 5:14:30 PM UTC-4, Mike Bayer wrote:
>
> 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 <mkw...@gmail.com <javascript:>>
> 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
