There's a complete example of mapping to inherits "partitions" at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PostgreSQLInheritance. The key strategy here is that Postgresql handles the presence of the "inherits" tables implicitly, e.g. your "b" here. The ORM never knows about them. So it would only INSERT into "a".
Now in this case, your example is quite different. You are trying to add new columns to "b" that aren't in "a", and the way the above works, that would not be possible. When I wrote the "parititioning" document above, I vaguely recall reading that Postgresqls' inheritance feature was intended for the above use case alone. However these days, PG is pushing hard on the so-called "object oriented database" concept for some reason (IMO PG would benefit tremendously if they focused on out of the box HA instead of adding more and more SQL complexity, but here we are), so now their docs seem to encourage deep creativity with the inherits feature. Since your "b" table has every column you need, you would need to map it as "concrete=True". Then the "B" mapper will only deal with the "b" table and never refer to the "a" table. However, I'm not sure how well this works in terms of selecting from "A", if the polymorphic loading still works in that case or not, because the mapper might be expecting a separate "polymorphic selectable". But you should try adding the "concrete=True" example to the B mapping options and see if it works. If not, it might not be possible but I can try running an MCVE here to see what might be needed. As a separate issue, I'd also advise dropping the "public" schema from the mapping and instead ensuring your connection's search_path is set to "public", especially when doing reflection across schemas, see http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path for that. On Tue, May 22, 2018 at 1:17 PM, Demitri Muna <demitri.m...@gmail.com> wrote: > > Hello, > > I have two tables in PostgreSQL where one is INHERITed from the other: > > CREATE TABLE a > ( > pk serial, > type_pk integer, > value numeric, > CONSTRAINT a_pkey PRIMARY KEY (pk), > CONSTRAINT a_type_fk FOREIGN KEY (a_type_pk) > REFERENCES a_type (pk) MATCH SIMPLE > ON UPDATE CASCADE > ON DELETE RESTRICT > ); > > > CREATE TABLE type > ( > pk serial, > label text > ); > > > CREATE TABLE b > ( > pk , > type_pk , > value , > b_value numeric > ) > INHERITS (a); > > I have my SQLAlchemy classes defined like this: > > class Type(Base): > __tablename__ = 'a_type' > __table_args__ = {'autoload':True, 'schema':'public'} > > > class A(Base): > __tablename__ = 'a' > __table_args__ = {'autoload':True, 'schema':'public'} > > > type_pk = Column("type_pk", Integer, ForeignKey(Type.pk)) > > > type = relationship(Type, > primaryjoin=Type.pk==type_pk, > backref="as") > > > __mapper_args__ = { > 'polymorphic_identity':"a", > 'polymorphic_on':case([ > (type_pk == 1, "b") > ], else_="a") > } > > class B(A): > __tablename__ = 'b' > __table_args__ = {'autoload':True, 'schema':'public'} > > > pk = Column(name='pk', type_=Integer, primary_key=True, > autoincrement=True) > __mapper_args__ = { > 'polymorphic_identity': "b", > 'inherit_condition':pk==A.pk, > 'inherit_foreign_keys':['type_pk'] > } > > > > This very nearly works and I get the polymorphism I am looking for. When I > create a new B() object in code and look at the SQL generated, it triggers > two inserts - one for the 'a' table, and one for the 'b' table. How can I > suppress the INSERT into 'a'? With PostgreSQL's INHERIT, I only need the 'b' > INSERT. This works very well otherwise. > > I get a few of these warnings. Is there a configuration I need to specify to > tell SQLAlchemy not to worry about it? > >> >> /usr/local/anaconda/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py:1766: >> SAWarning: Implicitly combining column a.pk with column b.pk under attribute >> 'pk'. Please configure one or more attributes for these same-named columns >> explicitly. > > > Finally, is there a way I can automatically set a default value of 1 for the > 'type_pk' propoerty when a new B() is created? I don't know if I can do this > in PostgreSQL since the field is inherited. > > Thanks! > > Demitri > > -- > 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.
