SQL Agent Module

Similar functionality to SQL Server Agent. Flush with functionality to automate SQL tasks.

alyeska.sqlagent.execute_sql(cnxn: psycopg2.extensions.connection, cmd: str) → None

Open cnxn and pass the cmd argument.

? Should we handle EOF errors?

Parameters:
  • cnxn (psycopg2.extensions.connection) – Connection used to execute command.
  • cmd (str) – SQL command to be executed.
Returns:

None
alyeska.sqlagent.execute_tasks(cnxn: psycopg2.extensions.connection, *tasks) → None

Execute the SQL in each task argument in order

Parameters:cnxn (psycopg2.extensions.connection) – [description]
alyeska.sqlagent.find_sql_files(sql_dir: pathlib.Path, include_subdirs: bool = True) → Coroutine[pathlib.Path, None, pathlib.Path]

Find SQL files in the given directory.

Parameters:
  • sql_dir (pathlib.Path) – The directory to look for SQL files
  • include_subdirs (bool) – Whether to include subdirectories in the search
Returns:

[description]
Return type:

Coroutine[pathlib.Path, None, pathlib.Path]
alyeska.sqlagent.gather_subtasks(d: Dict[KT, VT]) → collections.OrderedDict

Declare subtasks and log messages in order

Parameters:d (Dict) – a dict-like object mapping subtasks to log messages
Returns:map tasks to log messages in order of execution
Return type:OrderedDict
alyeska.sqlagent.plan_tasks(sql_dir: pathlib.Path) → List[pathlib.Path]

Generate an ordered sequence of SQL files.

Parameters:sql_dir (pathlib.Path) – Where to look for SQL files.
Returns:An ordered sequence of filepaths.
Return type:List[pathlib.Path]

Notes

plan_tasks doesn’t return a generator here because the sorting step creates a list. Returning this sorted list as a generator would just create computational overhead.

alyeska.sqlagent.process_batch(cnxn: psycopg2.extensions.connection, sql_dir: pathlib.Path) → None

Find SQL files in sql_dir and execute as batch process

Parameters:
  • cnxn (psycopg2.extensions.connection) – [description]
  • sql_dir (str) – [description]
Returns:

[description]
Return type:

None
alyeska.sqlagent.run_sql(cnxn: psycopg2.extensions.connection, fp: pathlib.Path, msg: str) → None

Run SQL: Read from file and execute with connection.

Parameters:
  • cnxn (psycopg2.extensions.connection) – Connection used to execute the SQL
  • fp (pathlib.Path) – Filepath where target SQL is stored (relative or absolute)
  • msg (str) – Message for logger when running subtask
Returns:

None
alyeska.sqlagent.run_subtasks(cnxn: psycopg2.extensions.connection, subtasks: Dict[pathlib.Path, str]) → None

Fetch SQL files and run them in order.

Parameters:
  • cnxn (psycopg2.extensions.connection) – Database connection used to run subtasks.
  • subtasks (OrderedDict[pathlib.Path, str]) – OrderedDict containing paths to sql files mapped to the text read by logger.
Returns:

None