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.
