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

['Michael Mulqueen' via sqlalchemy]

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
> 
>  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
> 
> .
>

-- 
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/CAHxMHYUTkEbh5MHRZKW3H_qhXkk03HKr8pAv1Tp0

Re: [sqlalchemy] Implementing Role-Based Access Control (RBAC) in SQLAlchemy with oso

['Michael Mulqueen' via sqlalchemy]

Thanks for sharing this. Will take a good look at it, I've been looking for
something like this.

There's a broken link on the text "guide to roles patterns

".

On Thu, 7 Jan 2021, 18:15 Stephie Glaser,  wrote:

> Hi all, we've been working towards building Role-Based Access Control
> (RBAC) features into our libraries at oso. We had released a preview of
> those features in our sqlalchemy-oso package, and since then have polished
> those features up, written some docs, and are excited to share a sample app
> showcasing our new out-of-the box roles features!
>
> Link to Introducing Built-in Roles with oso.
>  It covers how to
> structure Role-Based Access Control (RBAC) and how we ship roles
> out-of-the-box for SQLAlchemy. Plus feature designs, broader thinking on
> roles, and the sample app we use to validate and showcase the
> sqlalchemy-oso library.
>
>
>
>
>
> --
> 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/50f0c039-9fdb-42cb-b40e-a17f8c70c282n%40googlegroups.com
> 
> .
>

-- 
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/CAHxMHYWWekkQ7TWbUPfznYupgi5LczjB-yWNKKTBL4X1M0bJYg%40mail.gmail.com.

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

['Michael Mulqueen' via sqlalchemy]

I've just done something like this the other day, but it was with an
existing sequence. We're using Alembic for schema updates, so I'm not sure
whether SQLAlchemy's built-in create_all would behave the same way. You
should still be able to use a similar approach.

shared_sequence = Sequence('shared_id__seq')


class ModelA(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

class ModelB(Base):
shared_id = Column(Integer,
   primary_key=True,
   default=shared_sequence.next_value(),
   server_default=shared_sequence.next_value())

This seems to be working fine.

Before that I'd tried providing Sequence as an arg to Column like you have
and I'd run into some problems and this seemed like an easier option.


On Wed, 5 Aug 2020 at 13:59, Zsolt Ero  wrote:

> Hi,
>
> I've split a table into two tables, for performance reasons. I'd like to
> insert into both tables using the same sequence. I'm inserting using
> executemany_mode='values'.
>
> My idea is to call nextval() on the sequence before insert and fill in the
> values client side, before inserting.
>
> select nextval('mysql') FROM generate_series(1,...)
>
> Everything looks good, except for the default behaviour of SQLAlchemy to
> turn an integer + pk column into a SERIAL.
>
> As an alternative I'm also looking at using Sequence('myseq') from
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#sequences-serial-identity,
> but this is broken for issuing "CREATE SEQUENCE myseq" before the table
> creation, which is missing the IF NOT EXISTS part.
>
> How can I either:
> - turn off the automatic behaviour of making a pg + int = serial?
> - add a IF NOT EXISTS to the Sequence()?
>
> Or any alternative ideas?
>
> --
> 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/7860f5ab-64a4-481d-8e72-9b40d2ab2527o%40googlegroups.com
> 
> .
>


-- 

Michael Mulqueen

*Method B Ltd*
m...@method-b.uk / +44 (0)330 223 0864
http://www.method-b.uk/

Method B Limited is registered in England and Wales with the company number
9189496. Registered office: 28-29 Richmond Place, Brighton, East Sussex,
England, BN2 9NA

-- 
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/CAHxMHYXTJ8zLobCdW%2BAtTMaUHGFKkub-9L8cEJMJhYBfPUO%3DQA%40mail.gmail.com.

Re: [sqlalchemy] How can I query two fields in my database to confirm it exists and then delete

['Michael Mulqueen' via sqlalchemy]

With the ORM, you'd filter, something like this:

.filter(LessonEntity.lesson_id == lesson_id, LessionEntity.device_type_id
== device_type_id)

If you were just expecting to find one, you might want to chain .first()
onto the end of that.

You can delete as normal: session.delete(obj)

If you want to delete without checking whether they exist first and avoid
shuttling data back and forth from the database to the application, you can
use the expression language to issue a DELETE command limited by a WHERE
clause. https://docs.sqlalchemy.org/en/13/core/tutorial.html

Kind regards,
Mike


On Tue, 4 Aug 2020 at 10:32, Adam Shock  wrote:

> My SQL database has the following:
>
>
>
> to query the lesson_id i would do :
> lesson = LessonEntity.query.get(lesson_id)
>
> How can i query the database to check if lesson_id exists on the same
> entry as device type and then remove..
>
> so for example. if lesson_id matches 107 and the same entry includes
> device_type_id = 7 i want to remove this whole entry
>
> --
> 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/85301330-9208-4f8e-b1d4-e89ea8942239o%40googlegroups.com
> 
> .
>


-- 

Michael Mulqueen

*Method B Ltd*
m...@method-b.uk / +44 (0)330 223 0864
http://www.method-b.uk/

Method B Limited is registered in England and Wales with the company number
9189496. Registered office: 28-29 Richmond Place, Brighton, East Sussex,
England, BN2 9NA

-- 
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/CAHxMHYVRk9bSD31Ut6ppQttPr-A1OQ5E8moW6JbdO_uSN-SV7Q%40mail.gmail.com.

Re: [sqlalchemy] session.add with insert-or-update

['Michael Mulqueen' via sqlalchemy]

Hi Keith,

Small world!

You have at least 3 options depending on your requirements:

1. Handle it in your own application logic (e.g. make a get_or_create
method) - I tend to prefer this, business rules for create vs. update often
creeps in.
2. Use session.merge
https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging
3. Upserts -
https://docs.sqlalchemy.org/en/13/dialects/mysql.html#insert-on-duplicate-key-update-upsert
(this also exists for PostgreSQL).

First option example:

# SQLAlchemy models
class Incident(Base):
incident_id = Column(Integer, primary_key=True)

@classmethod
def get_or_create(cls, session, id_=None):
if id_:
incident = session.query(cls).filter(cls.incident_id == id_).one()
else:
incident = Incident()
session.add(incident)
return incident

def populate(self, data):
for key, value in data.items():
assert hasattr(self, key)
setattr(self, key, value)



# Request handler.
def report_incident():
incident = Incident.get_or_create(session, record.get("incident_id"))
incident.populate(record)
session.commit()

session.add doesn't imply insert by the way (it's adding to the session,
not adding to the database), so calling it on an object that's been
retrieved in the same session won't cause problems.

Hope that helps.

Cheers,
Mike



On Sat, 7 Mar 2020, 18:34 Keith Edmunds,  wrote:

> I'm new to SQLAlchemy. Sorry if it shows.
>
> I'm using a MySQL backend. I've set up a declarative_base, defined a table
> class, set up a session. I defined a record as a dictionary and added it
> successfully to the db with:
>
> incident = Incidents(**record)
> session.add(incident)
> session.commit()
>
> The behaviour I'd like from the add/commit steps is to update any existing
> records that has a matching Primary Key, or insert as a new record if
> there's no match.
>
> I see posts on how to do that with core functionality, but how would I do
> that using the ORM as above?
>
> --
> 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/9dfd3fb5-5516-4bea-8cd1-9abf3e6280fc%40googlegroups.com
> 
> .
>

-- 
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/CAHxMHYUoo3bmtg%3DZjcQE%2B5u7dMcwXvy4jYCMknkawz84Gpcr9g%40mail.gmail.com.