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, [email protected] 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, [email protected] 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, [email protected] 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 [email protected].
> 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 [email protected].
>
> 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 [email protected].
>
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a2daf61e-d84f-4678-a6e4-fa96547c07f8n%40googlegroups.com.
