since it will be very hard to change this after 2.0 is released I will try to further attempt to get ORM objects to be returned, though this will not at first support any special features:
https://github.com/sqlalchemy/sqlalchemy/issues/7865 this will allow retunring(User) to send back an instance but there's a lot of cases to be worked out. On Mon, Mar 28, 2022, at 2:05 PM, Mike Bayer wrote: > > > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/f1222311-ca61-4ad4-9ff7-63ea945448b6%40www.fastmail.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/c968c278-c0a7-4612-b6df-98b61061a66b%40www.fastmail.com.
