On Mon, Mar 28, 2022, at 1:31 PM, mkmo...@gmail.com wrote: > 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.
as I already agreed, it is unintuitive for now, but it's not clear it can be made fully automatic. it would be potentially a very large job for something that can already be achieved right now with a little more API use. > > I think it should be as easy as .returning(User) and it should return the > full ORM model with column_properties preloaded. this remains a non-trivial improvement that is not on the timeline right now, so you will have to work with what we have. > > 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? not really sure, this is part of the problem. we reserve the right to make backwards incompatible changes in a the middle point since we are not on semver. as returning(User) is not that intuitive when the documented approach isn't used, we will assume people are not using that form very much should we decide to implement this feature. > > 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. that can likely be improved for 2.0. > > 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: sure, there's a very complex process that's used to SELECT all columns. your use case should work right now if you do something like this: select(User).from_statement(insert(User).returning(User, User.my_column_property)) > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/a2daf61e-d84f-4678-a6e4-fa96547c07f8n%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/f1222311-ca61-4ad4-9ff7-63ea945448b6%40www.fastmail.com.
