Awesome thanks again really appreciate it!
On Wednesday, March 20, 2019 at 5:58:44 PM UTC-4, Mike Bayer wrote:
>
> On Wed, Mar 20, 2019 at 5:52 PM mkwyche <mkw...@gmail.com <javascript:>>
> wrote:
> >
> > 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?
>
> I think people usually use one declarative_base shared for all the
> classes in their application because they usually consider all those
> classes against one big object graph with relationships between them.
> But there are certainly useful cases to use multiple declarative bases
> also, which doesn't prevent you from having relationships between them
> either.
>
> For example I didn't show you this but you *can* associate a schema
> with a declarative_base via the metadata:
>
> A = declarative_base(metadata=MetaData(schema="test_schema"))
> B = declarative_base(metadata=MetaData(schema="test_schema_2"))
>
>
> class A1(A):
> __tablename__ = 'a'
>
> id = Column(Integer, primary_key=True)
> data = Column(String)
> bs = relationship("B1")
>
>
> class B1(B):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> a_id = Column(ForeignKey(A1.id))
> data = Column(String)
>
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> A.metadata.create_all(e)
> B.metadata.create_all(e)
>
> gives you:
>
> CREATE TABLE test_schema.a (
> id SERIAL NOT NULL,
> data VARCHAR,
> PRIMARY KEY (id)
> )
>
>
> CREATE TABLE test_schema_2.b (
> id SERIAL NOT NULL,
> a_id INTEGER,
> data VARCHAR,
> PRIMARY KEY (id),
> FOREIGN KEY(a_id) REFERENCES test_schema.a (id)
> )
>
>
> not like I'd do it that way, but that's an option.
>
>
>
>
>
>
>
> >
> > 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> 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.
> >> > 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.
