def celery_commit_updates(update_key, update_statements, table, is_mysql, commit_frequency = 1000):
"""
Executes bulk updates for a given table. This is typically much faster than going through
the SQLAlchemy ORM. In order to be flexible, the update statements must be set up in a specific
way. You can only update one column at a time. The dictionaries in the list `update_statements`
must have two keys: `pk`, which is the primary_key for the record to be updated, and `update`
which is the new value for the column you want to change. The column you want to change
is specified as a string by the argument `update_key`.
If is_mysql is True, then the update will be performed by inserting the record updates
into the table temporary_update and then executing an UPDATE/JOIN. If is_mysql is False,
then SQLite is assumed, and traditional updates are used (lib.alchemy.match.commit_updates)
A session is generated using the scoped_session factory through SQLAlchemy, and then
the actual task is dispatched.
Args:
update_key -- the name of the column we want to update
update_statements -- list of dictionaries of updates. See above description
table -- SQLAlchemy table object. If you have a table reference, you can use TableName.__table
commit_frequency -- tune this for speed. Runs "session.commit" every `commit_frequency` items
"""
session = session_generator()
if not is_mysql:
commit_updates(session, update_key, update_statements, table, commit_frequency)
return
commit_inserts(session, update_statements, temporary_update, is_mysql, 10000)
# now update using the join
primary_key = table.primary_key.columns.values()[0]
update_key = table.columns[update_key]
session.execute("UPDATE {0} join temporary_update ON temporary_update.pk = {1} SET {2} = temporary_update.update;".format(table.name, primary_key.name, update_key.name ))
session.commit()
session.execute("truncate temporary_update;")
session.commit()
def bulk_commit_inserts(insert_statements,
table,
is_mysql,
commit_frequency=1000,
dbtype='grant'):
"""
Executes bulk inserts for a given table. This is typically much faster than going through
the SQLAlchemy ORM. The insert_statement list of dictionaries may fall victim to SQLAlchemy
complaining that certain columns are null, if you did not specify a value for every single
column for a table.
A session is generated using the scoped_session factory through SQLAlchemy, and then
the actual lib.alchemy.match.commit_inserts task is dispatched.
Args:
insert_statements -- list of dictionaries where each dictionary contains key-value pairs of the object
table -- SQLAlchemy table object. If you have a table reference, you can use TableName.__table__
is_mysql -- adjusts syntax based on if we are committing to MySQL or SQLite. You can use alchemy.is_mysql() to get this
commit_frequency -- tune this for speed. Runs "session.commit" every `commit_frequency` items
dbtype -- which base schema to use. Either 'grant' or 'application'
"""
session = session_generator(dbtype=dbtype)
commit_inserts(session, insert_statements, table, is_mysql,
commit_frequency)
session.commit()
def bulk_commit_updates(update_key,
update_statements,
table,
is_mysql,
commit_frequency=1000,
dbtype='grant'):
"""
Executes bulk updates for a given table. This is typically much faster than going through
the SQLAlchemy ORM. In order to be flexible, the update statements must be set up in a specific
way. You can only update one column at a time. The dictionaries in the list `update_statements`
must have two keys: `pk`, which is the primary_key for the record to be updated, and `update`
which is the new value for the column you want to change. The column you want to change
is specified as a string by the argument `update_key`.
If is_mysql is True, then the update will be performed by inserting the record updates
into the table temporary_update and then executing an UPDATE/JOIN. If is_mysql is False,
then SQLite is assumed, and traditional updates are used (lib.alchemy.match.commit_updates)
A session is generated using the scoped_session factory through SQLAlchemy, and then
the actual task is dispatched.
Args:
update_key -- the name of the column we want to update
update_statements -- list of dictionaries of updates. See above description
table -- SQLAlchemy table object. If you have a table reference, you can use TableName.__table
is_mysql -- adjusts syntax based on if we are committing to MySQL or SQLite. You can use alchemy.is_mysql() to get this
commit_frequency -- tune this for speed. Runs "session.commit" every `commit_frequency` items
dbtype -- which base schema to use. Either 'grant' or 'application'
"""
session = session_generator(dbtype=dbtype)
if not is_mysql:
commit_updates(session, update_key, update_statements, table,
commit_frequency)
return
session.rollback()
if is_mysql:
session.execute('truncate temporary_update;')
else:
session.execute('delete from temporary_update;')
if dbtype == 'grant':
commit_inserts(session, update_statements, temporary_update, is_mysql,
10000)
else:
commit_inserts(session, update_statements, app_temporary_update,
is_mysql, 10000)
# now update using the join
primary_key = table.primary_key.columns.values()[0]
update_key = table.columns[update_key]
session.execute(
"UPDATE {0} join temporary_update ON temporary_update.pk = {1} SET {2} = temporary_update.update;"
.format(table.name, primary_key.name, update_key.name))
session.commit()
if is_mysql:
session.execute('truncate temporary_update;')
else:
session.execute('delete from temporary_update;')
session.commit()
def celery_commit_inserts(insert_statements, table, is_mysql, commit_frequency = 1000):
"""
Executes bulk inserts for a given table. This is typically much faster than going through
the SQLAlchemy ORM. The insert_statement list of dictionaries may fall victim to SQLAlchemy
complaining that certain columns are null, if you did not specify a value for every single
column for a table.
A session is generated using the scoped_session factory through SQLAlchemy, and then
the actual lib.alchemy.match.commit_inserts task is dispatched.
Args:
insert_statements -- list of dictionaries where each dictionary contains key-value pairs of the object
table -- SQLAlchemy table object. If you have a table reference, you can use TableName.__table__
is_mysql -- adjusts syntax based on if we are committing to MySQL or SQLite. You can use alchemy.is_mysql() to get this
commit_frequency -- tune this for speed. Runs "session.commit" every `commit_frequency` items
"""
session = session_generator()
commit_inserts(session, insert_statements, table, is_mysql, commit_frequency)
