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.

218646 次浏览
小开

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

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

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

提交答案