Sqlalchemy using inheritance in postgres

Question

trying to learn sqlalchemy (and python), I am trying to duplicate an existing project, but it is difficult for me to understand sqlalchemy and inheritance with postgres.

here is an example of what our postgres database does (obviously, this is simplified):

CREATE TABLE system (system_id SERIAL PRIMARY KEY, system_name VARCHAR(24) NOT NULL); CREATE TABLE file_entry(file_entry_id SERIAL, file_entry_msg VARCHAR(256) NOT NULL, file_entry_system_name VARCHAR(24) REFERENCES system(system_name) NOT NULL); CREATE TABLE ops_file_entry(CONSTRAINT ops_file_entry_id_pkey PRIMARY KEY (file_entry_id), CONSTRAINT ops_system_name_check CHECK ((file_entry_system_name = 'ops'::bpchar))) INHERITS (file_entry); CREATE TABLE eng_file_entry(CONSTRAINT eng_file_entry_id_pkey PRIMARY KEY (file_entry_id), CONSTRAINT eng_system_name_check CHECK ((file_entry_system_name = 'eng'::bpchar)) INHERITS (file_entry); CREATE INDEX ops_file_entry_index ON ops_file_entry USING btree (file_entry_system_id); CREATE INDEX eng_file_entry_index ON eng_file_entry USING btree (file_entry_system_id); 

And then the inserts will be performed using the trigger so that they are correctly inserted into the child databases. Sort of:

 CREATE FUNCTION file_entry_insert_trigger() RETURNS "trigger" AS $$ DECLARE BEGIN IF NEW.file_entry_system_name = 'eng' THEN INSERT INTO eng_file_entry(file_entry_id, file_entry_msg, file_entry_type, file_entry_system_name) VALUES (NEW.file_entry_id, NEW.file_entry_msg, NEW.file_entry_type, NEW.file_entry_system_name); ELSEIF NEW.file_entry_system_name = 'ops' THEN INSERT INTO ops_file_entry(file_entry_id, file_entry_msg, file_entry_type, file_entry_system_name) VALUES (NEW.file_entry_id, NEW.file_entry_msg, NEW.file_entry_type, NEW.file_entry_system_name); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 

In general, I have a parent table with a foreign key in another table. then I have two child tables that exist, and inserts are done based on the given value. in my example above, if file_entry_system_name is "ops", then the row goes to the ops_file_entry table; 'eng' goes to eng_file_entry_table. we have hundreds of tables for children in our production environment, and given the amount of data, it really speeds up the work, so I would like to keep the same structure. I can ask for the parent, and as long as I give him the correct "system_name", he immediately knows which child table to look for.

my desire is to emulate this with sqlalchemy, but I cannot find any examples that go into this part. I am looking at sql generated by sqlalchemy with examples, and I can say that it does nothing of the kind on the database side.

the best i can think of is something like:

 class System(_Base): __tablename__ = 'system' system_id = Column(Integer, Sequence('system_id_seq'), primary_key = True) system_name = Column(String(24), nullable=False) def __init(self, name) self.system_name = name class FileEntry(_Base): __tablename__ = 'file_entry' file_entry_id = Column(Integer, Sequence('file_entry_id_seq'), primary_key=True) file_entry_msg = Column(String(256), nullable=False) file_entry_system_name = Column(String(24), nullable=False, ForeignKey('system.system_name')) __mapper_args__ = {'polymorphic_on': file_entry_system_name} def __init__(self, msg, name) self.file_entry_msg = msg self.file_entry_system_name = name class ops_file_entry(FileEntry): __tablename__ = 'ops_file_entry' ops_file_entry_id = Column(None, ForeignKey('file_entry.file_entry_id'), primary_key=True) __mapper_args__ = {'polymorphic_identity': 'ops_file_entry'} 

in the end what am I missing? how can I tell sqlalchemy to bind something that is inserted into FileEntry with the system name "ops" to go to the table "ops_file_entry"? is this my understanding

some understanding of what i should do would be amazing.

Answer 1

You simply create a new instance of ops_file_entry (should it not be OpsFileEntry ?), Add it to the session, and after the flush one row will be inserted into the file_entry table, as well as the ops_file_entry table.

You do not need to set the file_entry_system_name attribute or trigger.

Answer 2

I really don't know python or sqlalchemy, but I decided that I would give it a chance in antiquity .;)

Have you tried basically setting up your trigger at the application level? Maybe something like this:

 from sqlalchemy import event, orm def my_after_insert_listener(mapper, connection, target): # set up your constraints to store the data as you want if target.file_entry_system_name = 'eng' # do your child table insert  elseif target.file_entry_system_name = 'ops' # do your child table insert #… mapped_file_entry_class = orm.mapper(FileEntry, 'file_entry') # associate the listener function with FileEntry, # to execute during the "after_insert" hook event.listen(mapped_file_entry_class, 'after_insert', my_after_insert_listener) 

I'm not sure, but I think that target (or maybe mapper ) should contain the inserted data.

Events (esp. After_create) and mapper are likely to be useful.