Thanks! I'll try it out
On Sat, Apr 14, 2018 at 11:01 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> Here is a recipe that will create a Table in a separate metadata so
> that the ForeignKey can refer to something. with this you entire
> example works with autogenerate:
>
> import sqlalchemy as sa
>
>
> def remote_fk(table_def):
> table, colname = table_def.split(".", 1)
> if "." in table:
> schema, table = table.split(".", 1)
> else:
> schema = None
>
> return sa.Table(
> table, sa.MetaData(),
> sa.Column(colname, sa.Integer), schema=schema).c[colname]
>
>
> metadata_one = sa.MetaData(schema="one")
>
> table_one = sa.Table(
> "table_one", metadata_one,
> sa.Column("id", sa.Text, primary_key=True),
> schema="table_one",
> )
>
> metadata_two = sa.MetaData(schema="two")
>
> table_two = sa.Table(
> "table_two", metadata_two,
> sa.Column("id", sa.Text, primary_key=True),
> sa.Column("one_id", sa.Text,
> sa.ForeignKey(remote_fk("one.table_one.id")), nullable=False),
> schema="two",
> )
>
> target_metadata = metadata_two
>
>
>
>
>
> On Fri, Apr 13, 2018 at 7:43 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> > but note that you can have the FOREIGN KEY in your database and not
> > have the ForeignKey object in Python. In your alembic migration you
> > can use ForeignKey within op.create_table() and that part will
> > actually work, because it works around there being no other table
> > around. But on the autogenerate side, nope.
> >
> > On Fri, Apr 13, 2018 at 7:41 PM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
> >> On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel <soxfan...@gmail.com> wrote:
> >>> Err why would I do #3? This is a just a simple reproduction of my
> actual
> >>> problem, in which the tables are obviously more fleshed out, and I
> would
> >>> actually want to enforce that the value referenced in the foreign key
> column
> >>> actually existed in the foreign table. Am I missing something?
> >>
> >> no function of the ForeignKey python object will work if it can't find
> >> a Table object that it refers towards and it will do nothing towards
> >> being aware of this constraint or making any use of it (most
> >> operations will fail with a similar error as what you are getting).
> >> So you need to decide how you'd like it to do that (metadata shared,
> >> or pass it the Column object).
> >>
> >>
> >>
> >>>
> >>> I'm fine with doing #1 or #2 but just wanted to make sure that was the
> only
> >>> way.
> >>>
> >>> On Friday, April 13, 2018 at 2:51:14 PM UTC-7, Mike Bayer wrote:
> >>>>
> >>>> On Fri, Apr 13, 2018 at 5:11 PM, Ashu Goel <soxf...@gmail.com> wrote:
> >>>> > Hey there,
> >>>> >
> >>>> > I am trying to setup a multi-schema database, where each schema has
> it's
> >>>> > own
> >>>> > metadata and tables, but ForeignKey constraints are allowed to cross
> >>>> > schemas. This usually works fine until I start introducing something
> >>>> > like
> >>>> > alembic revision autogeneration, which uses the sorted_tables
> feature of
> >>>> > the
> >>>> > metadata, in which case I get errors because sqlalchemy cannot find
> the
> >>>> > table referenced by the foreign key. I've attached a simple script
> that
> >>>> > showcases my findings, along
> >>>> > with the stacktrace below. I understand that a simple fix would be
> to
> >>>> > use
> >>>> > actual model object instead of a string to reference the foreign
> column,
> >>>> > but
> >>>> > ideally would like to continue using strings in case we ever decide
> to
> >>>> > break
> >>>> > up the code into different projects pointing to the same database.
> Any
> >>>> > thoughts?
> >>>>
> >>>>
> >>>> here are your choices:
> >>>>
> >>>> 1. put the two tables on the same MetaData object
> >>>>
> >>>> 2. use the column object in the ForeignKey, not the string
> >>>>
> >>>> 3. don't use ForeignKey.
> >>>>
> >>>> Looks like you want to use #3 because the ForeignKey construct here is
> >>>> completely useless without it being able to find the other Table, it's
> >>>> not buying you anything.
> >>>>
> >>>>
> >>>>
> >>>> >
> >>>> > Traceback (most recent call last):
> >>>> > File "simple.py", line 21, in <module>
> >>>> > print(metadata_two.sorted_tables)
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/sql/schema.py",
> >>>> > line 3842, in sorted_tables
> >>>> > return ddl.sort_tables(sorted(self.tables.values(), key=lambda
> t:
> >>>> > t.key))
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/sql/ddl.py",
> >>>> > line 1028, in sort_tables
> >>>> > tables, filter_fn=_skip_fn, extra_dependencies=extra_
> dependencies)
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/sql/ddl.py",
> >>>> > line 1095, in sort_tables_and_constraints
> >>>> > dependent_on = fkc.referred_table
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/sql/schema.py",
> >>>> > line 3002, in referred_table
> >>>> > return self.elements[0].column.table
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/util/langhelpers.py",
> >>>> > line 767, in __get__
> >>>> > obj.__dict__[self.__name__] = result = self.fget(obj)
> >>>> > File
> >>>> >
> >>>> > "/Users/ashu/Documents/server/env/lib/python3.5/site-
> packages/sqlalchemy/sql/schema.py",
> >>>> > line 1891, in column
> >>>> > tablekey)
> >>>> > sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
> >>>> > column
> >>>> > 'table_two.one_id' could not find table 'one.table_one' with which
> to
> >>>> > generate a foreign key to target column 'id'
> >>>> >
> >>>> >
> >>>> > Thanks!
> >>>> > Ashu
> >>>> >
> >>>> > --
> >>>> > 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.
>
--
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.
