Thank you for your reply. Then I thought the following would work: company_schema = 'c' + str(company_id) db.session.execute(CreateSchema(company_schema)) db.session.commit() meta = db.MetaData(bind=db.engine) for table in db.metadata.tables.items(): if table[1].name == 'customer': table[1].tometadata(meta, company_schema) elif table[1].name == 'company': table[1].tometadata(meta, 'app') # or table[1].tometadata(meta)? print meta.tables.items() meta.create_all()
Now I see print meta shows 2 tables, but somehow error is still the same: "NoReferencedTableError: Foreign key associated with column 'customer.company_id' could not find table 'company' with which to generate a foreign key to target column 'id' " However, with my original metadata I was able to create both tables: app.company and public.customer. So, I'm confused with the issue. Thanks for your time. On Wed, Sep 11, 2013 at 11:25 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Sep 10, 2013, at 11:48 PM, Joe Martin <jandos...@gmail.com> wrote: > > I need to create a new schema with some tables in it whenever a new > company record is added. > Below are my entities (defined with Flask-SqlAlchemy framework extension): > > class Company(db.Model): > __tablename__ = 'company' > __table_args__ = {"schema":"app"} > id = db.Column(db.Integer, primary_key=True) > name = db.Column(db.String(100), nullable=False) > > class Customer(db.Model): > __tablename__ = 'customer' > company_id = db.Column(db.Integer, db.ForeignKey('app.company.id', > onupdate='CASCADE', ondelete='CASCADE'), nullable=False) > id = db.Column(db.Integer, primary_key=True) > code = db.Column(db.String(20), nullable=False) > name = db.Column(db.String(100), nullable=False) > > Now when a company is inserted the following code executes: > > company_schema = 'c' + str(company_id) > db.session.execute(CreateSchema(company_schema)) > db.session.commit() > meta = db.MetaData(bind=db.engine) > for table in db.metadata.tables.items(): > if table[1].name not in ('company'): > table[1].tometadata(meta, company_schema) > meta.create_all() > > The above will throw an error: > NoReferencedTableError: Foreign key associated with column > 'customer.company_id' could not find table 'company' > > > Well that's because you are specifically not copying the "company" table > over to that new MetaData object. customer.c.company_id has a ForeignKey > in it, which has a string "app.company.id" in it, how can it find the > Table that points towards ? > > since you're just looking for "create table " here and nothing else (I'm > hoping, also FYI no DBA in the world would let you build an app that > creates tables on the fly like this), copy the whole metadata over to the > new one, but when you copy over "company" itself, don't supply an alternate > schema, just leave it with "app". > > > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.