sscdev.sql
==========

.. py:module:: sscdev.sql


Classes
-------

.. autoapisummary::

   sscdev.sql.SqlClient


Module Contents
---------------

.. py:class:: SqlClient(DB)

   .. py:method:: connect_to_db(conn_string: str, max_retries: int = 10) -> None

      Attempts to connect to the database using the provided connection string.

      Parameters:
      - conn_string (str): The database connection string.
      - max_retries (int): The maximum number of connection attempts.

      Raises:
      - Exception: If unable to connect to the database after the specified attempts.



   .. py:method:: read(query: str) -> pandas.DataFrame

      Executes a read (SELECT) SQL query and returns the results as a pandas DataFrame.

      Parameters:
      - query (str): The SQL query to execute.

      Returns:
      - pd.DataFrame: The result of the SQL query.

      Example:
      >>> SqlClient.read('SELECT * FROM SalesLT.SalesOrderDetail')



   .. py:method:: write(data: pandas.DataFrame, table: str, schema: str = None, if_exists='append') -> None

      Writes data to the specified SQL table.

      Parameters:
      - data (pd.DataFrame): The pandas DataFrame to write to the table.
      - table (str): The name of the SQL table to write to.
      - schema (str): The schema name in the database. Default is None.
      - if_exists (str): Behavior when the table already exists.
      Default is "append". Other options include "replace" and "fail".

      Example:
      >>> SqlClient.write(data, "Historical", "Weather")



   .. py:method:: query(query: str) -> None

      Executes a general SQL query (e.g., INSERT, UPDATE, DELETE).

      Parameters:
      - query (str): The SQL query to execute.

      Example:
      >>> SqlClient.query('INSERT INTO TableName (column1, column2) VALUES (value1, value2)')



   .. py:method:: list_schemas() -> list

      Lists all schemas in the connected database.

      Returns:
      - list: A list of schema names in the database.

      Example:
      >>> SqlClient.list_schemas()



   .. py:method:: list_tables(schema: str = None) -> list

      Lists all tables in the connected database, optionally under a specified schema.

      Parameters:
      - schema (str): The schema name (optional).

      Returns:
      - list: A list of table names in the specified schema or all tables if schema is not specified.

      Example:
      >>> SqlClient.list_tables()
      >>> SqlClient.list_tables(schema='public')



   .. py:method:: table_exists(table_name: str, schema: str = None) -> bool

      Checks if a specific table exists in the database.

      Parameters:
      - table_name (str): The name of the table to check.
      - schema (str, optional): The schema name in the database.

      Returns:
      - bool: True if the table exists, False otherwise.

      Example:
      >>> SqlClient.table_exists('SalesOrderDetail', schema='SalesLT')



   .. py:method:: get_columns(table_name: str, schema: str = None) -> pandas.DataFrame

      Retrieves the columns and their data types from a specific table.

      Parameters:
      - table_name (str): The name of the table.
      - schema (str, optional): The schema name in the database.

      Returns:
      - pd.DataFrame: DataFrame containing column names and data types.

      Example:
      >>> SqlClient.get_columns('SalesOrderDetail', schema='SalesLT')



   .. py:method:: close_connection() -> None

      Closes the database connection.

      Example:
      >>> SqlClient.close_connection()



   .. py:method:: execute_stored_procedure(procedure_name: str, params: dict = None) -> pandas.DataFrame

      Executes a stored procedure and returns the result.

      Parameters:
      - procedure_name (str): The name of the stored procedure to execute.
      - params (dict, optional): A dictionary of parameters to pass to the procedure.

      Returns:
      - pd.DataFrame: The result of the stored procedure.

      Example:
      >>> SqlClient.execute_stored_procedure('sp_GetSalesData', {'param1': 'value1'})