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.

Reply via email to