Hi, I wonder if there is a way to declare a particular column of a table as "readonly", either for the purpose of documenting the model, or to get early error should someone try to update it.
Some context: I have to maintain an old application, based on PostgreSQL, with several surrounding tools (mainly a PHP (bleach :-!) frontend and set of "services" built with Python+SQLObject). At the same time, I'm slowly developing a "future" replacement, rewriting the whole stack, starting from the DB layer using SQLAlchemy. Implementing a new "feature", where I have to rewrite the logic that keeps up-to-date a "counter" column, I thought of replacing that column with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL SQL function that compute that counter on-the-fly [1]: this allowed me to avoid touching dozens of places where that column is read in the PHP code (what a relief!). To illustrate, where I had a table such as CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER) I now have CREATE TABLE something (id SERIAL, name TEXT) CREATE FUNCTION counter(something) RETURNS INTEGER AS $$ SELECT count(*) FROM something_else se WHERE se.something_id = $1.id $$ STABLE SQL and thus existing queries such as SELECT s.name, s.counter FROM something AS s work as before. For now, I just left the SA Table definition (I'm using classic mappings) untouched, that is something like from sqlalchemy import Column, Integer, MetaData, Table, Text metadata = MetaData() something = Table("something", metadata, Column("id", Integer, primary_key=True), Column("name", Text), Column("counter", Integer)) but as said, I looked around to see if there is a way to inform SA that that "counter" column cannot be updated, just read. I tried to use the Computed() thing like from sqlalchemy import Computed something = Table("something", metadata, Column("id", Integer, primary_key=True), Column("name", Text), Column("counter", Integer, Computed("counter"))) but accordingly to the documentation [2] "SA behavior ... is currently that the value [assigned to the column] will be ignored", so that does not bring me any advantage. On the ORM side, I could "hide" the concrete column and expose it thru a readonly property, but I'd like to have a similar safety-belt also at the Core layer, because many services/APIs will be written at that level. Do you have any suggestion? Thanks in advance, bye, lele. [1] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS [2] https://docs.sqlalchemy.org/en/20/core/defaults.html#computed-ddl -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87y1mullgf.fsf%40metapensiero.it.