upsert (insert or update) operation using SQLAlchemy

To achieve an “upsert” (insert or update) operation using SQLAlchemy, you can use the merge method. This method will check if the record exists and update it if it does, or insert it if it does not. Here’s how you can do it:

Define your SQLAlchemy model: Ensure that you have your model defined. For example, let’s assume you have a model called MyModel.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class MyModel(Base):
    __tablename__ = 'my_model'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(String)

engine = create_engine('sqlite:///mydatabase.db')
Session = sessionmaker(bind=engine)
session = Session()

Use the merge method: The merge method will ensure that if the object with the primary key already exists in the database, it will be updated with the new values. If it does not exist, a new record will be created.
```
new_data = MyModel(id=1, name='example', value='updated_value')
session.merge(new_data)
session.commit()
```
In this example, if a record with id=1 exists, it will be updated with the new values. If it doesn’t exist, a new record will be created.

Related