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".
signature.asc
Description: Message signed with OpenPGP using GPGMail
