Re: [sqlalchemy] Re: Dealing with readonly column
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" 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.
Re: [sqlalchemy] Re: Dealing with readonly column
On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote: > "Mike Bayer" 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/eb9fc1e0-8494-4ec8-aad5-4cab3ae10bf5%40app.fastmail.com.
[sqlalchemy] Re: Dealing with readonly column
"Mike Bayer" 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". Consider: foo=# \d artists Table "public.artists" Column | Type | Collation | Nullable | Default +-+---+--+- id | integer | | not null | name | text| | | Indexes: "artists_pkey" PRIMARY KEY, btree (id) foo=# select a.name, name(a) from artists as a; name | name ---+--- peter gabriel | peter gabriel (1 row) [1] https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE > In general, whenever a column has a server side default of this > nature, you want to use FetchedValue or some subclass of this in the > column definition, so the ORM as well as Core knows that something in > the server will be generating a value for this column: > https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns > / > https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults > >> >> 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. > > 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" instance_of_something.counter = 42 session.flush() I was just imagining something that could raise an error earlier. > true "readonly" at the Core level, where any attempt to use > connection.execute() in such a way that data is being sent to this > column, you'd need to use event hooks such as before_cursor_execute() > https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute > and then do heuristics to figure out what the statement is, and if any > of the parameters point to this column. I would not go this route > since it's overkill and not that reliable for said overkill. Right, not worth the effort indeed. > If you want to absolutely disallow client side changes to this column, > since you are setting up PG defaults anyway, using a trigger or a true > COMPUTED column would be the easiest route. 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. [2] https://www.postgresql.org/docs/15/ddl-generated-columns.html > 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? Anyway, as said, it was just to be sure I didn't miss some SA magic (it wouldn't be the first time you've managed to surprise me, and I bet you'll keep doing that :-). My coworkers will surely be able to cope with an error coming back from the database at flush time! Thanks, lele. -- 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