CODEWITHSUNDEEP
pythonsqlalchemy
Andrii Yurchuk
Andrii Yurchuk

Reputation: 3260

SQLAlchemy insert or update example

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.

Upvotes: 104

Views: 307276

Answers (4)

Galuoises
Galuoises

Reputation: 3283

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
    })

Upvotes: 0

yurenchen
yurenchen

Reputation: 2473

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

Upvotes: 14

Ehsan
Ehsan

Reputation: 4281

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)

Upvotes: 17

Evan Siroky
Evan Siroky

Reputation: 9408

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

Upvotes: 164

Related Questions