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'
After some googling, I found that tometadata() changes constraint schema
too. I also found a code,
suggested by Michael Bayer (
http://www.mail-archive.com/[email protected]/msg13008.html):
def copy_table(table, metadata, schema_map):
args = []
for c in table.columns:
args.append(c.copy())
for c in table.constraints:
if isinstance(c, db.ForeignKeyConstraint):
elem = list(c.elements)[0]
schema = schema_map[elem.column.table.schema]
else:
schema=None
args.append(c.copy(schema=schema))
return db.Table(table.name, metadata, schema=schema_map[table.schema],
*args)
Now I replaced tometadata() with the above function:
copy_table(table[1], meta, {None: schema_name, 'app': 'app'})
print meta.tables.items()
But the error is the same: NoReferencedTableError. However, print clearly
shows that
for company_id constraint, schema hasn't changed:
('c28.customer',
Table('customer',
MetaData(bind=Engine(postgresql://postgres:concept@localhost:5432/clients)),
Column('company_id', Integer(), ForeignKey('app.company.id'),
table=<customer>, nullable=False),
Column('id', Integer(), table=<customer>, primary_key=True,
nullable=False),
Column('description', String(length=100), table=<customer>,
nullable=False), schema='c28'))
I also tried to define ForeignKey as follows, but the error is the same:
company_id = db.Column(db.Integer, db.ForeignKey(Company.__table__.c.id,
onupdate='CASCADE', ondelete='CASCADE'), nullable=False)
Can someone kindly help me with this issue?
--
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.
