StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLAlchemy - Mapping a Class against Multiple Tables, one of which is read only
text
Body
copied!<p>I have a foobar concept which is represented by the 'foobar' and 'foobar_data_cache' tables is the database and with the 'Foobar' class is python. The 'foobar' table represents some data about the concept and the 'foobar_data_cache' table represents some other data about the concept, data that derived from many other information in the database and that are computed by a database trigger. To keep consistency, INSERT, UPDATE and DELETE privileges are revoked from 'foobar_data_cache' table.</p> <p>With SQLAlchemy I want to map the 'Foobar' class to the two tables 'foobar' and 'foobar_data_cache' with a join. There is no reason to use another class to represent the data from 'foobar_data_cache' table and to build a relationship between those two classes because the data from both tables are strongly related. Indeed, from the database point of view, there is a one-to-one relationship between the two tables, guaranteed by:<br> - the primary key of 'foobar_data_cache' beeing also a foreign key referencing the primary key of 'foobar'<br> - and a trigger that checks that each row in 'foobar' has a corresponding row in 'foobar_data_cache'</p> <p>My problem is that when I try to persist a new Foobar object with SQLAlchemy ORM, it tries to insert some row for 'foobar_data_cache' table, which I would like to prevent.</p> <p>So, is it this possible to configure SQLAlchemy in a way that would consider 'foobar_data_cache' table as read-only? And if yes, how?</p> <p>Here is the code to explain my problem: </p> <pre><code>from sqlalchemy import ( Table, Column, Integer, String, ForeignKeyConstraint, join, create_engine, ) from sqlalchemy.orm import ( column_property, sessionmaker, ) from sqlalchemy.schema import ( FetchedValue, ) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() _foobar_table = Table('foobar', Base.metadata, Column('id', Integer, primary_key=True), Column('some_data', String), ) _foobar_data_cache_table = Table('foobar_data_cache', Base.metadata, Column('foobar_id', Integer, primary_key=True), Column('computed_data', String, server_default=FetchedValue()), ForeignKeyConstraint(['foobar_id'], ['foobar.id']), ) class Foobar(Base): __table__ = _foobar_table.join(_foobar_data_cache_table) _id = column_property(_foobar_table.c.id, _foobar_data_cache_table.c.foobar_id) def __init__(self, some_data): self.some_data = some_data if __name__ == '__main__': engine = create_engine('postgresql://tester@localhost:5432/mytestdb') Session = sessionmaker(bind=engine) session = Session() my_foobar = Foobar('Dummy data') session.add(my_foobar) session.commit() </code></pre> <p>Here is the SQL commands to create the two tables: </p> <pre><code>CREATE TABLE foobar ( id int NOT NULL DEFAULT -2147483648, some_data varchar NOT NULL, CONSTRAINT pk_foobar PRIMARY KEY (id) ); CREATE TABLE foobar_data_cache ( foobar_id int NOT NULL, computed_data varchar NOT NULL, CONSTRAINT pk_foobar_data_cache PRIMARY KEY (foobar_id), CONSTRAINT fk_foobar_data_cache_foobar_1 FOREIGN KEY (foobar_id) REFERENCES foobar (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE ); </code></pre> <p><strong>Note:</strong><br> Some people might wonder why I split my data in two different tables considering the fact that it is a one-to-one relationship between them. The problem would be easily solve by using a single table and the FetchedValue construct (see <a href="https://stackoverflow.com/questions/13977154/how-to-persist-only-a-subset-of-the-mapped-columns-on-sqlalchemy">How to persist only a subset of the mapped columns on SQLAlchemy?</a>) on computed columns. Well, it is a bit complex but i will try to explain. First, the other things not explained above are:<br> - I use PostgreSQL 8.4 which, among other things, cannot have deferrable UNIQUE constraints<br> - none of my columns accept a NULL value<br> - some columns in 'foobar_data_cache' have a unique constraint (not deferrable)<br> - the trigger that computes the data in 'foobar_data_cache' is deferred until the end of the transaction. Indeed, it is because it takes information from others tables that can only be inserted after the insert on 'foobar', due to foreign key constraints. </p> <p>That said, in the case of using a single table, that means I would have to use a temporary dummy value for my computed columns because of the NOT-NULL constraints. My trigger would finally override it at the end of the transaction. And the problem is about concurency. Indeed, new transactions Tx trying to insert new 'foobar' while another transaction T1 is performing, would fail because the default dummy values for the unique columns already exists in the row corresponding to the performing transaction T1. I could generate random dummy values for columns having unique constraints but I don't like that style of doing things.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload