FWIW, I often use the events to ensure an object or column is "read only".
Sometimes I also will use two different attributes, where `Object.foo` is a getter for `Object._foo` in the database. The database will raise an error (hopefully) if I try to write to a 'protected column', and SQLAlchemy code is somewhat easy to audit to ensure I am only reading and never accessing the protected column. I generally like to integrate that approach because I know something special is going on with the columns that have a leading _ and will immediately check the table's schema to remind myself. On Friday, April 14, 2023 at 4:18:05 PM UTC-4 Mike Bayer wrote: > > > On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote: > > "Mike Bayer" <mike_not_...@zzzcomputing.com> writes: > > > >> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote: > >>> 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. > >> > >> how does "SELECT s.counter" work if the table does not have an actual > "counter" column? > > > > As explained here [1], "the field notation and functional notation are > > equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM > > t AS a" means the same, when "foo" is a function accepting the "implicit > > composite type corresponding to a row in the table t". > > oh geez it's that silly PostgreSQL syntax. not a fan. you can use that but > you'd be on your own.... > > > > >> > >> Using Computed in this way is equivalent to using FetchedValue. Why is > that a problem? > > > > No, it's not a problem, but the following > > > > stmt = something.update().values(counter=42).where(something.c.id=1) > > connection.execute(stmt) > > > > will raise an error at execution time, as will, assuming > > "instance_of_something" is an instance of the ORM class mapped to the > > table "something" > > So what then is a "read only column" if not one that raises an error if > someone tries to write to it explicitly? > > > instance_of_something.counter = 42 > > session.flush() > > > > I was just imagining something that could raise an error earlier. > > well you have the ORM validates and the before_cursor_execute approaches, > but I would think if this is simple developer level programming guards, the > PG exception is perfect > > > > > > Unfortunately in this case PG does not help: the closest thing is a > > "GENERATED" column [2], but (up to version 15 at least) it "is a special > > column that is always computed from other columns", it cannot be an > > arbitrary subquery. > > trigger, then. im sure this is a thing PG can do > > > > >> Without using server-side constructs, in a practical sense, simply > >> omitting the column from the Table or using mapper.exclude_properties > >> is in my experience sufficient. > > > > Uhm, how could I then select that value, to be returned by an API that > > queries the "something" table? > > OK so the "readonly" you are looking for includes one where your > application actually needs to load it, then fine, dont exclude it from your > mappings. > > -- 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/4cf77276-32bf-4ba7-8510-83197384feden%40googlegroups.com.
