Re: [sqlalchemy] Batch patch ORM entities

[Pierre Massé]

Thanks a lot, sorry to have been a bother for such a simple question..

Le mer. 11 oct. 2023 à 17:16, Mike Bayer <
mike_not_on_goo...@zzzcomputing.com> a écrit :

>
>
> On Wed, Oct 11, 2023, at 11:10 AM, Pierre Massé wrote:
>
> Thanks a lot for the insight Mike,
>
> My question might then be quite naive: let's say I have a list of ORM
> entities on one side, and an accordingly sorted numpy array of computed
> features, how would I merge back attributes on entities?
>
> Let's say I have a list like :
> user_list = [User(id=1, dist=None), User(id=2, dist=None)]
>
> and a pandas DataFrame (or numpy array) like:
>
> dist_df =
> id dist
> 1 123
> 2 90
>
> How would I correlate those 2 into:
> [User(id=1, dist=123), User(id=2, dist=90)]
>
> Would the way to go be a simple for loop? Like:
> for user in user_list:
> user.dist = dist_df.loc[user.id, 'dist']
>
> Or is there something included in SQLAlchemy for this kind of task?
>
>
> let's assume the two lists are ordered, which I would strongly recommend.
> merge them with zip:
>
> for user, dist in zip(user_list, dist_df):
> user.dist = dist
>
>
>
>
> Regards,
>
> Pierre
>
> Le mer. 11 oct. 2023 à 15:07, Mike Bayer <
> mike_not_on_goo...@zzzcomputing.com> a écrit :
>
>
>
>
> On Wed, Oct 11, 2023, at 4:22 AM, Pierre Massé wrote:
>
> Dear all,
>
> I have a requirement that makes me think that I need to "mass patch" some
> ORM objects. However, I am open to any suggestions regarding the way to
> answer my requirements.
>
> I have defined an ORM object which represents a user, holding longitude
> and latitude (among other attributes). At some point, I want to query many
> of those users, and send them back holding the geographical distance from a
> certain point (defined by longitude and latitude) along with their other
> data.
>
> Computing the distance is computationally heavy, and I noticed that I
> could greatly improve performance by mass computing those distances, using
> numpy for example.
>
> My question is: would it be possible to split my flow in 2 :
> - a flow that queries the data that is simply available in the database,
> as ORM entities
> - a flow that queries lon/lat as a numpy array, perform the distance
> computation
> and afterward merge those 2 in the queried ORM entities?
>
>
> This is a straightforward programming task.   Query for the set of objects
> you want, assemble the appropriate values into a numpy array, do wahtever
> numpy thing you need, then merge back.you'd likely want to ensure you
> can correlate numpy rows back to original objects most likely by keeping a
> sort order between your result set and your numpy array.
>
> not stated here is if these numpy-calculated values as assembled onto ORM
> objects are also database-column mapped, it sounds like they are not
> (otherwise those values would be in the database), so the matrix values can
> be applied to plain attributes on the objects directly.
>
> now where this may be more challenging, not sure if this is what you're
> asking, is if you want this to happen implicitly for all queries or
> something like that.   there's ways to do this depending on the programming
> patterns you are looking to achieve however I'd certainly start simple with
> a function like "apply_lat_long_to_list_of_objects(obj)".
>
>
>
> It is important to me that I finally get back a list of ORM entities fully
> populated, because my whole downstream process is built around this
> assumption.
>
> Thanks a lot for your insights on the matter!
>
> Regards,
>
> Pierre
>
> PS: giving me a "SQLAlchemy fast distance computation" won't do the trick,
> because I have other kinds of computations that may not be optimizable this
> way.
>
>
> --
> 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/CAH4TWVuJWP9WsSYNScPH%2BK9JJ3PqbOwxkm%3D_PXbPtYXzpBdvcg%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVuJWP9WsSYNScPH%2BK9JJ3PqbOwxkm%3D_PXbPtYXzpBdvcg%40mail.gmail.com?utm_medium=email_source=footer>
> .
>
>
>
> --
> SQLAl

Re: [sqlalchemy] Batch patch ORM entities

[Pierre Massé]

Thanks a lot for the insight Mike,

My question might then be quite naive: let's say I have a list of ORM
entities on one side, and an accordingly sorted numpy array of computed
features, how would I merge back attributes on entities?

Let's say I have a list like :
user_list = [User(id=1, dist=None), User(id=2, dist=None)]

and a pandas DataFrame (or numpy array) like:

dist_df =
id dist
1 123
2 90

How would I correlate those 2 into:
[User(id=1, dist=123), User(id=2, dist=90)]

Would the way to go be a simple for loop? Like:
for user in user_list:
user.dist = dist_df.loc[user.id, 'dist']

Or is there something included in SQLAlchemy for this kind of task?

Regards,

Pierre

Le mer. 11 oct. 2023 à 15:07, Mike Bayer <
mike_not_on_goo...@zzzcomputing.com> a écrit :

>
>
> On Wed, Oct 11, 2023, at 4:22 AM, Pierre Massé wrote:
>
> Dear all,
>
> I have a requirement that makes me think that I need to "mass patch" some
> ORM objects. However, I am open to any suggestions regarding the way to
> answer my requirements.
>
> I have defined an ORM object which represents a user, holding longitude
> and latitude (among other attributes). At some point, I want to query many
> of those users, and send them back holding the geographical distance from a
> certain point (defined by longitude and latitude) along with their other
> data.
>
> Computing the distance is computationally heavy, and I noticed that I
> could greatly improve performance by mass computing those distances, using
> numpy for example.
>
> My question is: would it be possible to split my flow in 2 :
> - a flow that queries the data that is simply available in the database,
> as ORM entities
> - a flow that queries lon/lat as a numpy array, perform the distance
> computation
> and afterward merge those 2 in the queried ORM entities?
>
>
> This is a straightforward programming task.   Query for the set of objects
> you want, assemble the appropriate values into a numpy array, do wahtever
> numpy thing you need, then merge back.you'd likely want to ensure you
> can correlate numpy rows back to original objects most likely by keeping a
> sort order between your result set and your numpy array.
>
> not stated here is if these numpy-calculated values as assembled onto ORM
> objects are also database-column mapped, it sounds like they are not
> (otherwise those values would be in the database), so the matrix values can
> be applied to plain attributes on the objects directly.
>
> now where this may be more challenging, not sure if this is what you're
> asking, is if you want this to happen implicitly for all queries or
> something like that.   there's ways to do this depending on the programming
> patterns you are looking to achieve however I'd certainly start simple with
> a function like "apply_lat_long_to_list_of_objects(obj)".
>
>
>
> It is important to me that I finally get back a list of ORM entities fully
> populated, because my whole downstream process is built around this
> assumption.
>
> Thanks a lot for your insights on the matter!
>
> Regards,
>
> Pierre
>
> PS: giving me a "SQLAlchemy fast distance computation" won't do the trick,
> because I have other kinds of computations that may not be optimizable this
> way.
>
>
> --
> 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/CAH4TWVuJWP9WsSYNScPH%2BK9JJ3PqbOwxkm%3D_PXbPtYXzpBdvcg%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAH4TWVuJWP9WsSYNScPH%2BK9JJ3PqbOwxkm%3D_PXbPtYXzpBdvcg%40mail.gmail.com?utm_medium=email_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/msg

[sqlalchemy] Batch patch ORM entities

[Pierre Massé]

Dear all,

I have a requirement that makes me think that I need to "mass patch" some
ORM objects. However, I am open to any suggestions regarding the way to
answer my requirements.

I have defined an ORM object which represents a user, holding longitude and
latitude (among other attributes). At some point, I want to query many of
those users, and send them back holding the geographical distance from a
certain point (defined by longitude and latitude) along with their other
data.

Computing the distance is computationally heavy, and I noticed that I could
greatly improve performance by mass computing those distances, using numpy
for example.

My question is: would it be possible to split my flow in 2 :
- a flow that queries the data that is simply available in the database, as
ORM entities
- a flow that queries lon/lat as a numpy array, perform the distance
computation
and afterward merge those 2 in the queried ORM entities?

It is important to me that I finally get back a list of ORM entities fully
populated, because my whole downstream process is built around this
assumption.

Thanks a lot for your insights on the matter!

Regards,

Pierre

PS: giving me a "SQLAlchemy fast distance computation" won't do the trick,
because I have other kinds of computations that may not be optimizable this
way.

-- 
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/CAH4TWVuJWP9WsSYNScPH%2BK9JJ3PqbOwxkm%3D_PXbPtYXzpBdvcg%40mail.gmail.com.

Re: [sqlalchemy] Usage of sqlalchemy Uuid type in declarative mapping

[Pierre Massé]

You are fing awesome Mike.

Thanks for the answer and the links, I will dive into it tomorrow first
thing in the morning.

Le jeu. 6 juil. 2023 à 19:27, Mike Bayer <
mike_not_on_goo...@zzzcomputing.com> a écrit :

>
>
> On Thu, Jul 6, 2023, at 1:21 PM, Pierre Massé wrote:
>
> Dear all,
>
> I am currently working on implementing UUID as primary keys in my database.
>
> I have come across an issue that I cannot find an explanation for: using
> the `sqlalchemy.Uuid` type in declarative mapping yields different results
> at initialization. I sometimes get the following error:
>
> *"The type provided inside the 'id' attribute Mapped annotation is the
> SQLAlchemy type . Expected a Python
> type instead"*
>
>
> this message is referring to the difference between *Python* types and
> *SQLAlchemy* types.  A SQLAlchemy type is like "sqlalchemy.Uuid" as you are
> using.  The Python type is the Python UUID type:
> https://docs.python.org/3/library/uuid.html#uuid.UUID
>
> the default lookup is documented here:
> https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#mapped-column-derives-the-datatype-and-nullability-from-the-mapped-annotation
>
> so you would want
>
> import uuid
>
> class XYZ(...):
> # ...
>
>id: Mapped[uuid.UUID] = mapped_column(...)
>
>
>
>
>
>
>
>
> I have a Caregiver table, for which id is a PostgreSQL UUID, and a Match
> Event table holding 2 fields which use the Caregiver id as foreign key.
> Those 2 fields are PostgreSQL UUID as well.
>
> If my classes definitions are as follow, the initialization runs smoothly,
> as well as inserts in the Match Event table:
>
> from sqlalchemy import (
> ForeignKey,
> Uuid,
> )
> from sqlalchemy.orm import (
> Mapped,
> mapped_column,
> relationship,
> )
>
> class MatchEventOrm(Base):
> __tablename__ = "match_event"
>
> id: Mapped[int] = mapped_column(primary_key=True)
> subject_caregiver_id: Mapped[Uuid] = mapped_column(
> ForeignKey("caregiver.id")
> )
> object_caregiver_id: Mapped[Uuid] = mapped_column(
> ForeignKey("caregiver.id")
> )
> subject_caregiver: Mapped["CaregiverOrm"] = relationship(
> back_populates="match_event_subject_of",
> foreign_keys=[subject_caregiver_id],
> )
> object_caregiver: Mapped["CaregiverOrm"] = relationship(
> back_populates="match_event_object_of",
> foreign_keys=[object_caregiver_id],
> )
>
> class CaregiverOrm(Base):
> __tablename__ = "caregiver"
>
> id: Mapped[*str*] = mapped_column(primary_key=True)
> uid: Mapped[str]
> user_email: Mapped[str]
>
> ...
>
>
>
> However, if I change to the following:
> from sqlalchemy import (
> ForeignKey,
> Uuid,
> )
> from sqlalchemy.orm import (
> Mapped,
> mapped_column,
> relationship,
> )
>
> class MatchEventOrm(Base):
> __tablename__ = "match_event"
>
> id: Mapped[int] = mapped_column(primary_key=True)
> subject_caregiver_id: Mapped[Uuid] = mapped_column(
> ForeignKey("caregiver.id")
> )
> object_caregiver_id: Mapped[Uuid] = mapped_column(
> ForeignKey("caregiver.id")
> )
> subject_caregiver: Mapped["CaregiverOrm"] = relationship(
> back_populates="match_event_subject_of",
> foreign_keys=[subject_caregiver_id],
> )
> object_caregiver: Mapped["CaregiverOrm"] = relationship(
> back_populates="match_event_object_of",
> foreign_keys=[object_caregiver_id],
> )
>
> class CaregiverOrm(Base):
> __tablename__ = "caregiver"
>
> id: Mapped[*Uuid*] = mapped_column(primary_key=True)
> uid: Mapped[str]
> user_email: Mapped[str]
>
> ...
>
>
> I get the aforementioned error message.
>
> I may be using type annotations wrong, any insight on what I am doing
> wrong would be welcome.
>
> Thanks a lot for your support.
>
> Regards,
>
> Pierre
>
>
>
> --
> 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/CAH4TWVuadpW3d5%3Dt3pgs8DU55Wpx%3DVGBajBKQiskzc87f-aiMw%40mail.gmail.com
> <https://groups.google.co

[sqlalchemy] Usage of sqlalchemy Uuid type in declarative mapping

[Pierre Massé]

Dear all,

I am currently working on implementing UUID as primary keys in my database.

I have come across an issue that I cannot find an explanation for: using
the `sqlalchemy.Uuid` type in declarative mapping yields different results
at initialization. I sometimes get the following error:

*"The type provided inside the 'id' attribute Mapped annotation is the
SQLAlchemy type . Expected a Python
type instead"*

I have a Caregiver table, for which id is a PostgreSQL UUID, and a Match
Event table holding 2 fields which use the Caregiver id as foreign key.
Those 2 fields are PostgreSQL UUID as well.

If my classes definitions are as follow, the initialization runs smoothly,
as well as inserts in the Match Event table:

from sqlalchemy import (
ForeignKey,
Uuid,
)
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship,
)

class MatchEventOrm(Base):
__tablename__ = "match_event"

id: Mapped[int] = mapped_column(primary_key=True)
subject_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
object_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
subject_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_subject_of",
foreign_keys=[subject_caregiver_id],
)
object_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_object_of",
foreign_keys=[object_caregiver_id],
)

class CaregiverOrm(Base):
__tablename__ = "caregiver"

id: Mapped[*str*] = mapped_column(primary_key=True)
uid: Mapped[str]
user_email: Mapped[str]
...



However, if I change to the following:
from sqlalchemy import (
ForeignKey,
Uuid,
)
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship,
)

class MatchEventOrm(Base):
__tablename__ = "match_event"

id: Mapped[int] = mapped_column(primary_key=True)
subject_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
object_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
subject_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_subject_of",
foreign_keys=[subject_caregiver_id],
)
object_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_object_of",
foreign_keys=[object_caregiver_id],
)

class CaregiverOrm(Base):
__tablename__ = "caregiver"

id: Mapped[*Uuid*] = mapped_column(primary_key=True)
uid: Mapped[str]
user_email: Mapped[str]
...


I get the aforementioned error message.

I may be using type annotations wrong, any insight on what I am doing wrong
would be welcome.

Thanks a lot for your support.

Regards,

Pierre

-- 
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/CAH4TWVuadpW3d5%3Dt3pgs8DU55Wpx%3DVGBajBKQiskzc87f-aiMw%40mail.gmail.com.

Re: [sqlalchemy] Should I use a surrogate primary key on an Association Object pattern?

[Pierre Massé]

Thanks a lot Micheal.

It was the way I was leaning forward to - but having confirmation from a
long time user made me take the decision.

Have a nice day !

Pierre

Le lun. 3 juil. 2023 à 22:54, 'Michael Mulqueen' via sqlalchemy <
sqlalchemy@googlegroups.com> a écrit :

> Hi Pierre,
>
> This isn't an official answer, I'm just a long time user of SQLAlchemy.
>
> Either way should work fine. The association object is driven by the
> columns on the association table being FKs, whether or not they're part of
> a PK isn't relevant.
>
> I've used both ways. In my experience, an artificial PK is easier to
> maintain in the long run. Each way has its minor advantages and
> disadvantages, but generally a single artificial PK would be my preference.
>
> Mike
>
> On Mon, 3 Jul 2023, 16:43 Pierre Massé, 
> wrote:
>
>> Dear all,
>>
>> I am currently reworking a bit of my model and stumbled into this
>> question, which I think mainly has opinionated answers - but I would like
>> to have some insight regarding SQLAlchemy usage or preferences.
>>
>> I have a situation where I am in the exact same case like the one
>> described in the Association Object
>> <https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#association-object>
>>  in
>> the SQLAlchemy ORM docs.
>>
>> I want to modelize :
>> - Caregiver - a person taking care of one or more Helpee
>> - Helpee - a person who is being taken care of, by one or more Caregiver
>> - their Relationship, which links a Caregiver to a Helpee, but with
>> additional data like their family ties (spouse, parent, friend, ...)
>>
>> This is typically the Association object use case, a many to many
>> relationship, holding additional data.
>>
>> So far, I have been using a "natural" primary key on the Relationship
>> table, by using the Caregiver Id, and the Helpee Id to form a composite
>> primary key.
>>
>> From a handful of blog posts (this StackOverflow answer
>> <https://dba.stackexchange.com/a/6110> being quite in depth), it looks
>> like adding an "artificial" or surrogate primary key on the Relationship
>> table should be the way to go. Of course, I would keep a unique constraint
>> on (Caregiver Id x Helpee Id) on this table along the new primary key.
>>
>> My questions are :
>> - is the addition of a surrogate primary key a good idea - without taking
>> into account the fact that I am using SQLAlchemy?
>> - would the "magic" of the association object still operate even though
>> the mapped ORM relationships would not be part of the primary key anymore?
>>
>> The docs example would become:
>>
>>
>> from typing import Optional
>>
>> from sqlalchemy import ForeignKey
>> from sqlalchemy import Integer
>> from sqlalchemy.orm import Mapped
>> from sqlalchemy.orm import mapped_column
>> from sqlalchemy.orm import DeclarativeBase
>> from sqlalchemy.orm import relationship
>>
>>
>> class Base(DeclarativeBase):
>> pass
>>
>>
>> class Association(Base):
>> __tablename__ = "association_table"
>> *id: Mapped[int] = mapped_column(primary_key=True)*
>> left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*,
>> primary_key=True*)
>> right_id: Mapped[int] = mapped_column(
>> ForeignKey("right_table.id")*, primary_key=True*
>> )
>> extra_data: Mapped[Optional[str]]
>> child: Mapped["Child"] = relationship(back_populates="parents")
>> parent: Mapped["Parent"] = relationship(back_populates="children")
>> *__table_args__ = (UniqueConstraint('left_id', 'right_id',
>> name='_relationship_uc'),)*
>>
>>
>> class Parent(Base):
>> __tablename__ = "left_table"
>> id: Mapped[int] = mapped_column(primary_key=True)
>> children: Mapped[List["Association"]] =
>> relationship(back_populates="parent")
>>
>>
>> class Child(Base):
>> __tablename__ = "right_table"
>> id: Mapped[int] = mapped_column(primary_key=True)
>> parents: Mapped[List["Association"]] =
>> relationship(back_populates="child")
>>
>>
>> --
>> 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
>&

[sqlalchemy] Should I use a surrogate primary key on an Association Object pattern?

[Pierre Massé]

Dear all,

I am currently reworking a bit of my model and stumbled into this question,
which I think mainly has opinionated answers - but I would like to have
some insight regarding SQLAlchemy usage or preferences.

I have a situation where I am in the exact same case like the one described
in the Association Object

in
the SQLAlchemy ORM docs.

I want to modelize :
- Caregiver - a person taking care of one or more Helpee
- Helpee - a person who is being taken care of, by one or more Caregiver
- their Relationship, which links a Caregiver to a Helpee, but with
additional data like their family ties (spouse, parent, friend, ...)

This is typically the Association object use case, a many to many
relationship, holding additional data.

So far, I have been using a "natural" primary key on the Relationship
table, by using the Caregiver Id, and the Helpee Id to form a composite
primary key.

>From a handful of blog posts (this StackOverflow answer
 being quite in depth), it looks like
adding an "artificial" or surrogate primary key on the Relationship table
should be the way to go. Of course, I would keep a unique constraint on
(Caregiver Id x Helpee Id) on this table along the new primary key.

My questions are :
- is the addition of a surrogate primary key a good idea - without taking
into account the fact that I am using SQLAlchemy?
- would the "magic" of the association object still operate even though the
mapped ORM relationships would not be part of the primary key anymore?

The docs example would become:


from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
pass


class Association(Base):
__tablename__ = "association_table"
*id: Mapped[int] = mapped_column(primary_key=True)*
left_id: Mapped[int] = mapped_column(ForeignKey("left_table.id")*,
primary_key=True*)
right_id: Mapped[int] = mapped_column(
ForeignKey("right_table.id")*, primary_key=True*
)
extra_data: Mapped[Optional[str]]
child: Mapped["Child"] = relationship(back_populates="parents")
parent: Mapped["Parent"] = relationship(back_populates="children")
*__table_args__ = (UniqueConstraint('left_id', 'right_id',
name='_relationship_uc'),)*


class Parent(Base):
__tablename__ = "left_table"
id: Mapped[int] = mapped_column(primary_key=True)
children: Mapped[List["Association"]] =
relationship(back_populates="parent")


class Child(Base):
__tablename__ = "right_table"
id: Mapped[int] = mapped_column(primary_key=True)
parents: Mapped[List["Association"]] =
relationship(back_populates="child")

-- 
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/CAH4TWVvPVKtjtyVHJO9WtX2ZCjmdX3aGWDBGCQrvU_7c-CswEg%40mail.gmail.com.

Re: [sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Pierre Massé]

Thanks for this quick answer Mike!

Obivously, I wasted way to much energy in trying to make everything work 
with table reflection... :(  Let's say I gained experience from it.

Regarding the origin of the impression I might have gotten, I'd say I tried 
to read a big chunk of the docs (unified tutorial and ORM docs) prior to 
moving to ORM, and got some nudges towards this understanding:
- I do not think that I read something like "__tablename__ and attribute 
names must be the same like in the underlying database" anywhere (but was 
not thorough in my reading of the docs). I will try to see what exception I 
will get if I try to map a non-existing field :)
- In the unified tutorial 
<https://docs.sqlalchemy.org/en/20/tutorial/metadata.html#table-reflection>, 
I misunderstood this part: "*Table reflection refers to the process of 
generating Table 
<https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table> 
and 
related objects by reading the current state of a database. Whereas in the 
previous sections we’ve been declaring Table 
<https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Table>**
 objects 
in Python and then emitting DDL to the database, the reflection process 
does it in reverse.*" like if it was one of 2 choices
- And the overall quality of the other docs entries that described so well 
how to "post declare" attributes, how to defer reflection, etc... that 
enabled a beginner to pull of such a complex application

Not sure if this misconception is widespread, but maybe some tweaking of 
the docs may have prevented this error. I'd be happy to contribute, but 
have never pull requested to an open source repo.

Anyway, thanks for the answer, wish you a pleasant day!

Regards,

Pierre

Le mardi 21 mars 2023 à 13:54:59 UTC+1, Mike Bayer a écrit :

hi -

I think things would be easier if you defined your ORM mappings/ tables 
without relying upon reflection.   There is no such requirement that 
reflection is used for an existing database, you just want to have ORM 
table metadata that matches the schema.   the ORM/table metadata does not 
have to match the schema exactly either, it can omit columns and tables you 
aren't using.

reflection is more of a utility function that can be used for some special 
cases but for a first class app /database combination it would not be 
prevalent. It's a bit curious where you might have gotten the 
impression that "reflection is mandatory when working with an existing 
database".

On Tue, Mar 21, 2023, at 7:58 AM, Pierre Massé wrote:

Dear all,

I am quite new to ORMs and SQLAlchemy, and I have a maybe somewhat naive 
question set regarding how to build an application around a database.

Just a few words of context (may not yield importance, but who knows): I am 
building a mobile app, for which the server side will be an AWS Lambda 
function serving GraphQL queries. Data persistence is achieved through a 
hosted PostgreSQL instance. Server side code is python and "database 
access" through SQLAlchemy. The first version of the database schema has 
been "manually" built and populated with some test data (via simple SQL 
queries in pgAdmin4).

Regarding SQLAlchemy usage, first version was using Core only, but I 
decided to move to ORM, and I got it quite hard - maybe because of poor 
choices on my end.

What I do, now that I have a working example:
- when the Lambda is fired, I import a module defining "bare" ORM classes, 
with no attribute apart the table name - inheriting from *`Reflected`* and 
a declarative base (using deferred reflection 
<https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#using-deferredreflection>
)
- engine is set up and connection established to the db
- *`Reflected`* class is prepared through *Reflected.prepare(engine=engine)*
- New attributes, relationships, etc... are added to the "bare" ORM classes 
as *`column_property`*, after reflection (this way 
<https://docs.sqlalchemy.org/en/20/orm/mapped_sql_expr.html#adding-column-property-to-an-existing-declarative-mapped-class>
)
- Mapper is then ready, data is queried and mutated using sessions

My questions are:
- is reflection mandatory when working with an existing database? (I think 
this would be like an overwhelmingly prevalent case for Production 
applications?)
- is it possible to have a mixed approach regarding the mapping 
definitions: some attributes being defined in the ORM classes prior to the 
reflection, and reflection then completes those classes with other fields 
from the database schema?
- when using reflection, is the only way to define new attributes, 
relationships, etc... to add those attributes after this reflection via 
adding column_properties after class definition, like described above?
- I feel like I am losing much of the "Declarative Mapping" by working the 
way I do, what do you think

[sqlalchemy] Working with an existing database, reflection and usage of ORM Declarative Mapping

[Pierre Massé]

Dear all,

I am quite new to ORMs and SQLAlchemy, and I have a maybe somewhat naive 
question set regarding how to build an application around a database.

Just a few words of context (may not yield importance, but who knows): I am 
building a mobile app, for which the server side will be an AWS Lambda 
function serving GraphQL queries. Data persistence is achieved through a 
hosted PostgreSQL instance. Server side code is python and "database 
access" through SQLAlchemy. The first version of the database schema has 
been "manually" built and populated with some test data (via simple SQL 
queries in pgAdmin4).

Regarding SQLAlchemy usage, first version was using Core only, but I 
decided to move to ORM, and I got it quite hard - maybe because of poor 
choices on my end.

What I do, now that I have a working example:
- when the Lambda is fired, I import a module defining "bare" ORM classes, 
with no attribute apart the table name - inheriting from *`Reflected`* and 
a declarative base (using deferred reflection 

)
- engine is set up and connection established to the db
- *`Reflected`* class is prepared through *Reflected.prepare(engine=engine)*
- New attributes, relationships, etc... are added to the "bare" ORM classes 
as *`column_property`*, after reflection (this way 

)
- Mapper is then ready, data is queried and mutated using sessions

My questions are:
- is reflection mandatory when working with an existing database? (I think 
this would be like an overwhelmingly prevalent case for Production 
applications?)
- is it possible to have a mixed approach regarding the mapping 
definitions: some attributes being defined in the ORM classes prior to the 
reflection, and reflection then completes those classes with other fields 
from the database schema?
- when using reflection, is the only way to define new attributes, 
relationships, etc... to add those attributes after this reflection via 
adding column_properties after class definition, like described above?
- I feel like I am losing much of the "Declarative Mapping" by working the 
way I do, what do you think about it?
- overall, what could be simplified regarding the ways of working I set up?

Some code snippets below:

*Bare ORM class definitions:*
class MessageOrm(Reflected, Base):
__tablename__ = "single_recipient_message"


class HeaderOrm(Reflected, Base):
__tablename__ = "single_recipient_message_header"

*Post reflection addition of relationships and attributes*
HeaderOrm.messages = relationship(
MessageOrm,
foreign_keys=[MessageOrm.header_id],
back_populates="header",
)
MessageOrm.sent_by_who = column_property(
case(
(MessageOrm.sender_id == current_id, "me"),
else_="other",
)
)
MessageOrm.header = relationship(
"HeaderOrm",
foreign_keys=[MessageOrm.header_id],
back_populates="messages",
)

Thanks a lot!

Regards,

Pierre





-- 
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/433f9917-7ac0-4d1b-b41e-16d8ae255d15n%40googlegroups.com.