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.
