Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
Thanks! I'll try it out
On Sat, Apr 14, 2018 at 11:01 AM, Mike Bayer
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
> 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
> wrote:
> >> On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel 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 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
> > 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",
> >>>
Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
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 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 wrote:
>> On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel 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 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
> 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/si
Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
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 wrote: > On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel 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 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 >>> > 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.
Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
On Fri, Apr 13, 2018 at 5:54 PM, Ashu Goel 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 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 >> > 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...@googlegrou
Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
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? 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 > 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 > > 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.
Re: [sqlalchemy] ForeignKey on a column referencing schema in another metadata object
On Fri, Apr 13, 2018 at 5:11 PM, Ashu Goel 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 > 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+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] ForeignKey on a column referencing schema in another metadata object
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?
Traceback (most recent call last):
File "simple.py", line 21, in
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+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.
import sqlalchemy as sa
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("one.table_one.id"), nullable=False),
schema="two",
)
if __name__ == "__main__":
print(metadata_two.sorted_tables)
