def update_rows(
self,
*,
cur: pyodbc.Cursor,
table: domain_table.Table,
rows: domain_rows.Rows,
batch_size: int,
column_names: typing.Optional[typing.Set[str]] = None,
) -> None:
if column_names is None:
column_names = table.column_names
for batch in rows.batches(batch_size):
sql = self._sql_adapter.update_rows(
schema_name=table.schema_name,
table_name=table.table_name,
pk_cols=set(table.primary_key.columns),
column_names=column_names,
parameter_placeholder=parameter_placeholder,
)
pk_cols = sorted(set(table.primary_key.columns))
non_pk_cols = sorted(col for col in column_names
if col not in table.primary_key.columns)
unordered_params = batch.as_dicts()
param_order = non_pk_cols + pk_cols
ordered_params = [
tuple(row[k] for k in param_order) for row in unordered_params
]
cur.executemany(sql, ordered_params)
def delete_rows(
self,
*,
cur: pyodbc.Cursor,
table: domain_table.Table,
rows: domain_rows.Rows,
batch_size: int,
) -> None:
sql = self._sql_adapter.delete_rows(
schema_name=table.schema_name,
table_name=table.table_name,
pk_cols=set(table.primary_key.columns),
parameter_placeholder=parameter_placeholder,
row_cols=rows.column_names,
)
for batch in rows.batches(batch_size):
cur.executemany(sql, batch.as_tuples(sort_columns=False))
def add_rows(
self,
*,
cur: pyodbc.Cursor,
schema_name: typing.Optional[str],
table_name: str,
rows: domain_rows.Rows,
batch_size: int,
) -> None:
for batch in rows.batches(batch_size):
sql = self._sql_adapter.add_rows(
schema_name=schema_name,
table_name=table_name,
parameter_placeholder=parameter_placeholder,
rows=batch,
)
params = batch.as_tuples()
cur.executemany(sql, params)
def fetch_rows(
*,
cur: pyodbc.Cursor,
sql: str,
params: typing.Optional[typing.List[typing.Tuple[typing.Any, ...]]] = None,
) -> domain_rows.Rows:
std_sql = sql_formatter.standardize_sql(sql)
logger.debug(f"FETCH:\n\t{std_sql}\n\tparams={params}")
if params is None:
result = cur.execute(std_sql)
elif len(params) > 1:
result = cur.executemany(std_sql, params)
else:
result = cur.execute(std_sql, params[0])
column_names = [description[0] for description in cur.description]
if rows := result.fetchall():
return domain_rows.Rows(column_names=column_names,
rows=[tuple(row) for row in rows])