Created
February 3, 2012 15:13
-
-
Save kung-foo/1730627 to your computer and use it in GitHub Desktop.
ON DUPLICATE KEY UPDATE hack
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from sqlalchemy.ext.compiler import compiles | |
from 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: | |
return s + " " + insert.kwargs['append_string'] | |
return s | |
my_connection.execute(my_table.insert(append_string = 'ON DUPLICATE KEY UPDATE foo=foo'), my_values) |
my_table = MyModel.__table__
@rclmenezes The second (mysql) append_string
gives me KeyError
:
File "/vagrant/src/.../migration.py", line 41, in <module>
Insert.argument_for("mysql", "append_string", None)
File "/vagrant-dev/opt/dev_virtualenv/local/lib/python2.7/site-packages/sqlalchemy/sql/base.py", line 184, in argument_for
construct_arg_dictionary[cls][argument_name] = default
KeyError: <class 'sqlalchemy.sql.dml.Insert'>
Tried some alternate approaches but none came to anything working... Do you have any more ideas? Does your above mentioned solution work out of the box for you?
The following is for Postgres (ON CONFLICT DO NOTHING) and works out of the box for me for bulk_insert_mapping
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
return compiler.visit_insert(insert, **kw) + " ON CONFLICT DO NOTHING"
However in my case I don't need it to be optional (well, actually I use it for a specific table only, but not dynamically optional)
The problem is that it will modify all the insert statements. Is there a way to avoid this ?
@areshand try this:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
@compiles(Insert, 'postgresql')
def ignore_duplicates(insert, compiler, **kw):
s = compiler.visit_insert(insert, **kw)
ignore = insert.kwargs.get('postgresql_ignore_duplicates', False)
return s if not ignore else s + ' ON CONFLICT DO NOTHING'
Insert.argument_for('postgresql', 'ignore_duplicates', None)
Sample usage:
stmt = MyModel.__table__.insert(postgresql_ignore_duplicates = True)
# -- OR --
from sqlalchemy import insert
stmt = insert(MyModel, postgresql_ignore_duplicates = True)
All the method above can't work.
@compiles(Insert, 'mysql')
def mysql_upsert(insert, compiler, **kw):
def _gen_fv_dict(fv_dict):
pass
s = compiler.visit_insert(insert, **kw)
if 'on_duplicate_key_update' in insert.kwargs:
return s + ' ON DUPLICATE KEY UPDATE ' + _gen_fv_dict(insert.kwargs['on_duplicate_key_update'])
return s
Insert.argument_for("mysql", "mysql_upsert", None)
stmt = User.__table__.insert(on_duplicate_key_update={
'age': 'age + VALUES(age)',
})
session.execute(stmt, data)
And get error
SAWarning: Can't validate argument 'on_duplicate_key_update'; can't locate any SQLAlchemy dialect named 'on'
% (k, dialect_name)
sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry 'bulko11' for key 'name'")
[SQL: INSERT INTO user (name, age) VALUES (%s, %s)]
[parameters: ('bulko11', 11)]
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@alexcohaniuc
By default, the first %s_ of each argument is supposed to be the dialect. So, if this is for mysql, simply:
The Insert.argument_for() at the bottom makes sure the mysql dialect knows that this new argument exists.