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.
