def create_update_query(self, columns, row, updates_location, mail_types):
table_to_update = pypika.Table(self.name)
update_query = pypika.Query() \
.update(table_to_update) \
.where(table_to_update.id == row["id"])
for key in columns:
if key in row:
update_query = update_query.set(
key, Table.format_exit_string(row[key]))
result_query = Table.get_sql(update_query)
file_name = mail_types[row[
"id"]] if self.name == "mail_content" else self.name + '_' + str(
row["id"])
update_file = open(updates_location + file_name + ".sql", 'w')
update_file.write(result_query)
update_file.close()
print("File " + file_name + ".sql" + " successfully generated here " +
updates_location)
return result_query
def update_dt_exclusions_ma(units, rng_dates=None, dates=None):
# set MA=0 (False) for units in given date range range
t = pk.Table('DowntimeExclusions')
cond = [t.Unit.isin(units)]
if not rng_dates is None:
cond.append(t.Date.between(*rng_dates))
if not dates is None:
cond.append(t.Date.isin(dates))
q = pk.Query().update(t).set(t.MA, 0).where(pk.Criterion.all(cond))
sql = q.get_sql()
cursor = db.cursor
rows = cursor.execute(sql).rowcount
cursor.commit()
print(f'Rows updated: {rows}')
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
return Table.get_sql(insert_query)
def create_update_query(self, columns, row, updates_location, mail_types):
table_to_update = pypika.Table(self.name)
update_query = pypika.Query() \
.update(table_to_update) \
.where(table_to_update.id == row["id"])
for key in columns:
if key in row:
update_query = update_query.set(
key, Table.format_exit_string(row[key]))
# WORKAROUND: H2 database does not support quoting for table names or column names.
# Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now).
result_query = update_query.get_sql(quote_char="") + ";"
file_name = mail_types[row[
"id"]] if self.name == "mail_content" else self.name + '_' + str(
row["id"])
update_file = open(updates_location + file_name + ".sql", 'w')
update_file.write(result_query)
update_file.close()
print("File " + file_name + ".sql" + " successfully generated here " +
updates_location)
return result_query
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
# WORKAROUND: H2 database does not support quoting for table names or column names.
# Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now).
return insert_query.get_sql(quote_char="") + ";"