python - SQLAlchemy ON DUPLICATE KEY UPDATE -

python - SQLAlchemy ON DUPLICATE KEY UPDATE -

is there elegant way insert ... on duplicate key update in sqlalchemy? mean syntax similar inserter.insert().execute(list_of_dictionaries) ?

on duplicate key update in sql statement

if want generated sql include on duplicate key update, simplest way involves using @compiles decorator.

the code (linked thread on subject on reddit) illustration can found on github:

class="lang-py prettyprint-override">from sqlalchemy.ext.compiler import compiles sqlalchemy.sql.expression import insert @compiles(insert) def append_string(insert, compiler, **kw): s = compiler.visit_insert(insert, **kw) if 'append_string' in insert.kwargs: homecoming s + " " + insert.kwargs['append_string'] homecoming s my_connection.execute(my_table.insert(append_string = 'on duplicate key update foo=foo'), my_values)

but note in approach, have manually create append_string. alter append_string function automatically changes insert string insert 'on duplicate key update' string, i'm not going here due laziness.

on duplicate key update functionality within orm

sqlalchemy not provide interface on duplicate key update or merge or other similar functionality in orm layer. nevertheless, has session.merge() function can replicate functionality only if key in question primary key.

session.merge(modelobject) first checks if row same primary key value exists sending select query (or looking locally). if does, sets flag somewhere indicating modelobject in database already, , sqlalchemy should utilize update query. note merge quite bit more complicated this, replicates functionality primary keys.

but if want on duplicate key update functionality non-primary key (for example, unique key)? unfortunately, sqlalchemy doesn't have such function. instead, have create resembles django's get_or_create(). another stackoverflow reply covers it, , i'll paste modified, working version of here convenience.

class="lang-py prettyprint-override">def get_or_create(session, model, defaults=none, **kwargs): instance = session.query(model).filter_by(**kwargs).first() if instance: homecoming instance else: params = dict((k, v) k, v in kwargs.iteritems() if not isinstance(v, clauseelement)) if defaults: params.update(defaults) instance = model(**params) homecoming instance

python mysql sqlalchemy