def insert(table, values, overwrite=False, select="", columns=[]):
if len(values) == 0:
return
if type(select) is tuple:
select = ', '.join(select)
if select:
select = f'SELECT {select} FROM '
overwrite = ' OVERWRITE' if overwrite else ''
columns = f' ({", ".join(columns)})' if columns else ''
sql = (f"INSERT{overwrite}\n"
f" INTO {table}{columns}\n"
f" {select}VALUES {sql_value_placeholders(len(values))}\n"
f";")
jsony = (dict, list, tuple, Exception, datetime)
params_with_json = [[
v.isoformat() if isinstance(
v, datetime) else utils.json_dumps(v) if isinstance(v, jsony) else
utils.format_exception(v) if isinstance(v, Exception) else v
for v in vp
] for vp in values]
return execute(sql, params=params_with_json, fix_errors=False)
def record_status(results, alert_id):
try:
db.execute(
f"UPDATE results.alerts "
f"SET handled=PARSE_JSON(%s) "
f"WHERE alert:ALERT_ID='{alert_id}'",
params=[json_dumps(results)])
except Exception as e:
log.error(e,
f"Failed to update alert {alert_id} with status {results}")
def do_insert(table,
values,
overwrite=False,
select="",
columns=[],
dryrun=False):
if len(values) == 0:
return {'number of rows inserted': 0}
if type(values[0]) is dict:
select, columns = determine_cols(values)
values = [tuple(v.get(c) for c in columns) for v in values]
if type(select) in (tuple, list):
select = ', '.join(select)
if select:
select = f'SELECT {select} FROM '
overwrite = ' OVERWRITE' if overwrite else ''
columns = f' ({", ".join(columns)})' if columns else ''
sql = (f"INSERT{overwrite}\n"
f" INTO {table}{columns}\n"
f" {select}VALUES {sql_value_placeholders(len(values))}\n"
f";")
params_with_json = [[
v.isoformat() if isinstance(
v, datetime) else utils.json_dumps(v) if isinstance(v, JSONY) else
utils.format_exception(v) if isinstance(v, Exception) else v
for v in vp
] for vp in values]
if dryrun:
print('db.insert', table, columns, utils.json_dumps(values))
return {'number of rows inserted': len(values)}
return next(fetch(sql, params=params_with_json, fix_errors=False))
def insert(table, values, overwrite=False, select=""):
if len(values) == 0:
return
if select:
select = f'SELECT {select} FROM '
overwrite = ' OVERWRITE' if overwrite else ''
sql = (f"INSERT{overwrite}\n"
f" INTO {table}\n"
f" {select}VALUES {sql_value_placeholders(len(values))}\n"
f";")
jsony = (dict, list, tuple)
params_with_json = [[
utils.json_dumps(v) if isinstance(v, jsony) else v for v in vp
] for vp in values]
return execute(sql, params=params_with_json, fix_errors=False)
def default(self, o):
return json_dumps(o)