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 <[email protected]> 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 <[email protected]> wrote:
>> On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel <[email protected]> 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 <[email protected]> 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 [email protected].
>>>> > To post to this group, send email to [email protected].
>>>> > 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
