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
