I'm trying to do a bulk update but it fails. This is how my table looks like
class Hashes(db.Model):
__tablename__ = 'Hashes'
id = db.Column(db.Integer, primary_key=True)
hash_val = db.Column(db.String(1024), unique=True)
hash_salt = db.Column(db.String(256))
hash_plain = db.Column(db.String(256))
Objects are presented as dictionary:
[
{'hash_val': '40350254ba198f1efcc9f8dc042fd15b', 'hash_plain': '287742velornesjo'},
{'hash_val': 'a75b1ef3e16f0a5cae736e48137d7c8b', 'hash_plain': 'Mister King'},
...
]
And this is how I'm trying to save them in DB:
dbobjects = [
Hashes(hash_val=x['hash_val'], hash_plain=x['hash_plain']) for x in hash_good
]
db.session.bulk_insert_objects(dbobjects, update_changed_only=True)
db.session.commit()
The error I'm getting is IntegrityError and by looking at the full Traceback it seems that I'm not generating any UPDATE statements but only INSERT. How to fix that?
Traceback (most recent call last)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1997, in __call__
return self.wsgi_app(environ, start_response)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1985, in wsgi_app
response = self.handle_exception(e)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1540, in handle_exception
reraise(exc_type, exc_value, tb)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
response = self.full_dispatch_request()
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/user/envs/project/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
rv = self.dispatch_request()
File "/home/user/envs/project/lib/python2.7/site-packages/flask_debugtoolbar/__init__.py", line 125, in dispatch_request
return view_func(**req.view_args)
File "/app/views.py", line 317, in upload
db.session.bulk_save_objects(dbobjects, update_changed_only=True)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2461, in bulk_save_objects
return_defaults, update_changed_only, False)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2625, in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2620, in _bulk_save_mappings
isstates, return_defaults, render_nulls)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 69, in _bulk_insert
bookkeeping=return_defaults)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements
execute(statement, multiparams)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
context)
File "/home/user/envs/project/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 105, in do_executemany
rowcount = cursor.executemany(statement, parameters)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/cursors.py", line 192, in executemany
self._get_db().encoding)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/cursors.py", line 229, in _do_execute_many
rows += self.execute(sql + postfix)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute
result = self._query(query)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
conn.query(q)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result
result.read()
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read
first_packet = self.connection._read_packet()
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/connections.py", line 1018, in _read_packet
packet.check_error()
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error
err.raise_mysql_exception(self._data)
File "/home/user/envs/project/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
raise errorclass(errno, errval)
IntegrityError: (pymysql.err.IntegrityError) (1062, u"Duplicate entry '40350254ba198f1efcc9f8dc042fd15b' for key 'hash_val'") [SQL: u'INSERT INTO `Hashes` (hash_val, hash_plain) VALUES (%(hash_val)s, %(hash_plain)s)'] [parameters: ({'hash_val': '40350254ba198f1efcc9f8dc042fd15b', 'hash_plain': '287742velornesjo'}, {'hash_val': 'a75b1ef3e16f0a5cae736e48137d7c8b', 'hash_plain': 'Mister King'}, {'hash_val': '8ecd03e1fe66c8ee543ff048298af20c', 'hash_plain': 'Farringdon456@'}, {'hash_val': '932d08bfbf87d40ed903f7629a8b3afe', 'hash_plain': '2chilledwater'}, {'hash_val': '8eb47a40e5fdc0e7818d32e0c7fba5b9', 'hash_plain': '1867327El10'}, {'hash_val': '06516de2e5a707621fa6d847e667ce84', 'hash_plain': 'wisky_chocolat$'}, {'hash_val': '7fd1a5943d5b47d98190fc9888131669', 'hash_plain': 'apolloniaime'}, {'hash_val': '93060afe8e7d49dcab6f12c3a9dd146a', 'hash_plain': 'maronmilan89'} ... displaying 10 of 33 total bound parameter sets ... {'hash_val': '14a356c3c4c3775ab183ea24c6a527ce', 'hash_plain': 'dik14424905'}, {'hash_val': 'a2abb9bdff9c730888e64129b89d36aa', 'hash_plain': 'imago2798'})] (Background on this error at: http://sqlalche.me/e/gkpj)