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 <[email protected]>wrote:
>
> On Sep 10, 2013, at 11:48 PM, Joe Martin <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
