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+unsubscr...@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/a73e131f-e177-4a82-b8bb-9abf9f9f3e64%40www.fastmail.com.
