SQLAlchemy insert or update example

Question

In Python, using SQLAlchemy, I want to insert or update a row. I tried this:

existing = db.session.query(Toner)
for row in data:
    new = Toner(row[0], row[1], row[2])

It does not work. How do I INSERT or UPDATE new into Toner table? I suspect it's done with merge, but I cannot understand how to do that.

There is no insert-or-update in (standard) SQL. You will have to fetch and update existing objects manually, then insert those that do not exist yet. Alternatively you will have to sidestep the ORM and issue your backend-dependent SQL manually. — Ferdinand Beyer, Oct 25 '11 at 12:56
@Ferdinand Beyer Wouldn't session.merge() (http://www.sqlalchemy.org/docs/orm/session.html#merging) work in my case? — Andrii Yurchuk, Oct 25 '11 at 12:57
`session.merge()` does something completely different: It adds an object to the session that originates from another session. For instance, if there's an object with ID 42, `session.merge()` will fetch a row for ID 42 from the database, and return a new object that represents the same database row. This has nothing to do with `INSERT` or `UPDATE`. — Ferdinand Beyer, Oct 25 '11 at 12:59
your example code is confusing: it shows that your retrieve `Toner` objects from the database, which means they already exist; then you try create `Toner` objects again with same fields. Please expand your example to a more realistic one. — van, Oct 26 '11 at 10:15
Could you change the accepted answer since it has only downvotes and your comment on it shows that it didn't help you? — Noumenon, Jan 13 '16 at 13:23
INSERT or UPDATE can be done, but an efficient implementation depends on the underlying SQLAlchemy database. It is different for MySQL and PostgreSQL. — Mikko Ohtamaa, Dec 05 '21 at 09:53

score 149 · Answer 1 · edited Nov 12 '20 at 19:58

149

assuming certain column names...

INSERT one

newToner = Toner(toner_id = 1,
                    toner_color = 'blue',
                    toner_hex = '#0F85FF')

dbsession.add(newToner)   
dbsession.commit()

INSERT multiple

newToner1 = Toner(toner_id = 1,
                    toner_color = 'blue',
                    toner_hex = '#0F85FF')

newToner2 = Toner(toner_id = 2,
                    toner_color = 'red',
                    toner_hex = '#F01731')

dbsession.add_all([newToner1, newToner2])   
dbsession.commit()

UPDATE

q = dbsession.query(Toner)
q = q.filter(Toner.toner_id==1)
record = q.one()
record.toner_color = 'Azure Radiance'

dbsession.commit()

or using a fancy one-liner using MERGE

record = dbsession.merge(Toner( **kwargs))

edited Nov 12 '20 at 19:58

applecrusher

5,508
5
39
89

answered Aug 14 '13 at 23:45

Evan Siroky

9,040
6
54
73

4

where does the `dbsession` come from ? O.o is it from `create_engine()` ? – jave.web May 04 '21 at 15:34
1

So in case anyone's wondering where does the `dbsession` come from, it's probably from `Session()` or if you have Session factory with pre-config something like `MySession = sessionmaker(create_engine(...))` then `MySession()` to create this `dbsession` – jave.web May 23 '21 at 21:08
update code will get: sqlalchemy.exc.InvalidRequestError: No transaction is begun. – scruel Aug 21 '21 at 09:23
Does `merge` accept a list of objects or mappings? – Marcelo Gazzola Sep 07 '21 at 10:10
About dbsession it's come from this import: from sqlalchemy.orm import Session as dbsession – Fernando Nogueira May 24 '22 at 19:04
record = dbsession.merge(Toner( **kwargs)) dbsession.commit() – Xiaobing Mi Feb 13 '23 at 04:51

score 16 · Answer 2 · edited Dec 30 '21 at 23:58

I try lots of ways and finally try this:

def db_persist(func):
    def persist(*args, **kwargs):
        func(*args, **kwargs)
        try:
            session.commit()
            logger.info("success calling db func: " + func.__name__)
            return True
        except SQLAlchemyError as e:
            logger.error(e.args)
            session.rollback()
            return False
        finally:
            session.close()

    return persist

and :

@db_persist
def insert_or_update(table_object):
    return session.merge(table_object)

yurenchen · Answer 3 · 2022-08-05T10:16:09.223

INSERT .. ON DUPLICATE KEY UPDATE ..

the sql:

INSERT INTO the_table (id, col1) VALUES (%s, %s) 
   ON DUPLICATE KEY UPDATE col1 = %s

in py code:
// test with sqlalchemy 1.4.x, use mysql

def test_insert_or_update():
    insert_stmt = insert(the_table).values(
        id = 'xxx',
        col1 = 'insert value',
    )
    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
        # col1 = insert_stmt.inserted.data,
        col1 = 'update value',
        col2 = 'mark'
    )
    print(on_duplicate_key_stmt)
    session.execute(on_duplicate_key_stmt)
    session.commit()

NOTE: different sql dialect may have different sql statement (sqlalchemy will handle that):

mysql: INSERT ... ON DUPLICAT
sqlite: INSERT ... ON CONFLICT
postgres9.4 or greenplum6: not support

score 0 · Answer 4 · answered Apr 11 '23 at 16:53

There is no builtin function to do insert or update. However, you could query if a record exists first. Then you add it or update it:

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String
  
engine = create_engine(
    "sqlite:///foo.db"
)

Base = declarative_base()


class Toner(Base):
    __tablename__ = 'toner'
    toner_id = Column(Integer, primary_key=True)
    toner_color = Column(String(255))
    toner_hex = Column(String(255))

Base.metadata.tables['toner'].create(engine)
Session = sessionmaker(bind=engine)
session = Session()

newToner = Toner(toner_id = 1,
                 toner_color = 'blue',
                 toner_hex = '#0F85FF')

qry_object = session.query(Toner).where(Toner.toner_id == newToner.toner_id)

if qry_object.first() is None:
    session.add(newToner)
else:
    qry_object.update(newToner)
session.commit()

and you can check the result with

res = session.query(Toner).all()
for r in res:
    print({
        'toner_id': r.toner_id,
        'toner_color': r.toner_color,
        'toner_hex': r.toner_hex
    })

The update() method requires an iterable, else the line `qry_object.update(newToner)` will give an error — AllSolutions, May 22 '23 at 08:33

SQLAlchemy insert or update example

4 Answers4

INSERT .. ON DUPLICATE KEY UPDATE ..