![]() ![]() If it's ok to post the same answer twice (?) here is my small self-contained code example: import sqlalchemy as db Many many thanks to Federico Caselli of the SQLAlchemy project who helped me develop sample code in a discussion at Update Feb 2023: SQLAlchemy version 2 was recently released and supports on_duplicate_key_update in the MySQL dialect. Model._init_(self, **kwargs, id = self.id) On_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( If key not in column_keys or key in all_ignore_keys: Insert_stmt = insert(self._table_).values(**udpate_data)Īll_ignore_keys = Use upset func based on on_duplicate_key_update class Model():Ĭolumn_keys = self._table_.columns.keys() Params = dict((k, v) for k, v in eritems() if not isinstance(v, ClauseElement)) Instance = session.query(model).filter_by(**kwargs).first() def get_or_create(session, model, defaults=None, **kwargs): Another StackOverflow answer covers it, and I'll just paste a modified, working version of it here for convenience. Instead, you have to create something that resembles Django's get_or_create(). Note that merge is quite a bit more complicated than this, but it replicates the functionality well with primary keys.īut what if you want ON DUPLICATE KEY UPDATE functionality with a non-primary key (for example, another unique key)? Unfortunately, SQLAlchemy doesn't have any such function. If it does, it sets a flag somewhere indicating that ModelObject is in the database already, and that SQLAlchemy should use an UPDATE query. rge(ModelObject) first checks if a row with the same primary key value exists by sending a SELECT query (or by looking it up locally). Nevertheless, it has the rge() function that can replicate the functionality only if the key in question is a primary key. SQLAlchemy does not provide an interface to ON DUPLICATE KEY UPDATE or MERGE or any other similar functionality in its ORM layer. ON DUPLICATE KEY UPDATE functionality within the ORM You could probably change the append_string function so that it automatically changes the insert string into an insert with 'ON DUPLICATE KEY UPDATE' string, but I'm not going to do that here due to laziness. My_connection.execute(my_table.insert(append_string = 'ON DUPLICATE KEY UPDATE foo=foo'), my_values)īut note that in this approach, you have to manually create the append_string. The code (linked from a good thread on the subject on reddit) for an example can be found on github: from import compilesįrom import append_string(insert, compiler, **kw): If you want the generated SQL to actually include ON DUPLICATE KEY UPDATE, the simplest way involves using a decorator. ON DUPLICATE KEY UPDATE in the SQL statement somada141's answer below has the best solution: This functionality is now built into SQLAlchemy for MySQL only. ON DUPLICATE KEY UPDATE post version-1.2 for MySQL
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |