Hi Mike, When using `column_property`, this `select(User).from_statement(ins.returing(User))` construct will not load in the column property. Instead the ORM will issue a second query when the column property is accessed.
I am able to get it working using the following: `select(User).from_statement(ins.returing(*select(User).selected_columns))` I get your point that there may not be much of a demand for this, but I would argue that it is a bit unexpected for `returning(User)` to return a Core row, and that the solution is bit unintuitive. I think it should be as easy as .returning(User) and it should return the full ORM model with column_properties preloaded. This proposed change is backwards incompatible right? E.g. if people are depending on `returning(User)` returning a core Row in 2.0, is it OK to change this to return a Model instance in 2.1? By the way, I think I found a bug with insert().values() when the ORM uses different field names than the Database column names. update().values() works fine, but not insert().values(). Please check my issue here when you have a moment. https://github.com/sqlalchemy/sqlalchemy/issues/7864 ------ Here is how to reproduce the case where column_property results in an extra query, if you are interested: class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) first_name = Column(String(30)) last_name = Column(String(30)) full_name = column_property(first_name + " " + last_name) # returning(User) triggers extra query on column_property access res = session.execute(select(User).from_statement(insert(User).values(first_name='foo', last_name='bar').returning(User))) user = res.scalars().one() # This triggers a select print(user.full_name) session.expunge(user) # normal query does not trigger a select as expected res = session.execute(select(User).where(User.id == user.id)) user = res.scalars().one() print(user.full_name) session.expunge(user) # use *selected(User).selected_columns to avoid triggering an extra select res = session.execute(select(User).from_statement(update(User).values(first_name='foo2').where(User.id == user.id).returning(*select(User).selected_columns))) user = res.scalars().one() # no extra query print(user.full_name) Thanks and best regards, Matthew On Sunday, March 27, 2022 at 7:28:55 PM UTC-7 Mike Bayer wrote: > > > On Sun, Mar 27, 2022, at 2:56 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > I'm writing a library that uses SQLAlchemy. The user will pass the library > an update, and the library will add a RETURNING clause for postgresql > users, and then return the model back to the user. The idea here is to > update and select the row in a single database call, instead of the normal > approach where two calls are made. > > However, `upd.returning(User)` will actually return a Core row, not the > ORM model instance: > > upd = update(User).values(name='foo').where(User.id == > 1).returning(User) > result = session.execute(upd) > row = result.one() > assert isinstance(row, Row) > > The key question I have is how to convert a Core row into an ORM model > instance. > > > use the construct select(User).from_statement(update(User)...returning()) > . See the example at > https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#using-insert-update-and-on-conflict-i-e-upsert-to-return-orm-objects > > > > > `model(**row._mapping)` fails in at least these two cases: different > field name in ORM vs database, and column_property. > > I also wonder, should SQLAlchemy return the Model instead of the core row > in this case? > > > unknown at this time. The above link illustrates a very new technique by > which this can work now. if this were to become more implicit without > the extra step, that would at best be a 2.1 thing not expected for at least > 18 months, it would be based on general demand for this kind of thing > (which does seem to be increasing). > > > > > > > > ------- > > I've gotten this far: > > model = model_from_dml(upd) > upd = upd.returning(*select(model).selected_columns) # this will > apply the column_property to the RETURNING > row = session.execute(upd).one() > > Now I just need to take this row and convert it to an ORM object. > > Is there a public API I can use to take a Core `row` and convert it to an > ORM model? > > Thanks and best regards, > > Matthew > On Sunday, March 27, 2022 at 11:11:30 AM UTC-7 Mike Bayer wrote: > > > > On Sun, Mar 27, 2022, at 2:08 PM, mkmo...@gmail.com wrote: > > Hi Mike, > > Thanks. Should I use column_descriptions[0]['type'] ? > > > yup, that should be pretty consistent in this case. > > I've implemented most of an actual feature for this but isn't committed > yet at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3742 > > > > In my case, `type`, `expr` and `entity` all return the model class that I > am interested in. > > Thanks and best regards, > > Matthew > On Saturday, March 26, 2022 at 12:02:54 PM UTC-7 Mike Bayer wrote: > > > the Project model is actually in there, but not in a public API place > (this is not the solution, but look inside of table._annotations to see it). > > The closest public API we have for this very new API right now is the > Query equivalent of column_descriptions, which is available on the select() > construct and works when the thing being selected is ORM-enabled, and, > alarmingly, it seems there is no documentation whatsoever for the Select > version of it, that is wrong, but anyway see the 1.x docs for now: > https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.column_descriptions > > This accessor would ideally be on insert, update and delete also, which it > currently is not. However, here's a quick way to get it right now: > > class A(Base): > __tablename__ = 'a' > > id = Column(Integer, primary_key=True) > data = Column(String) > > > upd = update(A) > > print(select(upd.table).column_descriptions) > > i might take a crack at cleaning this up now but the above will get you > what you need. > > On Sat, Mar 26, 2022, at 1:34 PM, mkmo...@gmail.com wrote: > > Hello, > > How can I infer the ORM model class from an update (or insert, or delete) > function result? > > upd = update(Project).values(name='foo').where( > Project.id == 1 > ) > > def my_library_function(session, upd): > result = session.execute(upd) > # how to get the Project ORM model here, using only session and upd ? > > I saw that the update() object has a `table` attribute, but this returns > the Core table (not the ORM model). In addition I don't have access to the > base/registry from this function (unless it can be derived from session?). > Moreover it seems like searching the registry is O(n) and will not work in > all cases, such as when two ORM models map to the same Core table. > > Thanks and best regards, > > Matthew > > > > -- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/9fc63126-a36d-4e36-b4df-50701bfcae47n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > -- > 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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/abc01658-17a5-451b-aca7-0864998c5af7n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/abc01658-17a5-451b-aca7-0864998c5af7n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > > -- > 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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/1ab4ec6a-aff6-4b7d-8835-6553a48a68b6n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/1ab4ec6a-aff6-4b7d-8835-6553a48a68b6n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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/a2daf61e-d84f-4678-a6e4-fa96547c07f8n%40googlegroups.com.