sscdev.sql#
Classes#
Module Contents#
- class sscdev.sql.SqlClient(DB)#
- 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.
- 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’)
- 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”)
- 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)’)
- list_schemas() list#
Lists all schemas in the connected database.
Returns: - list: A list of schema names in the database.
Example: >>> SqlClient.list_schemas()
- 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’)
- 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’)
- 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’)
- close_connection() None#
Closes the database connection.
Example: >>> SqlClient.close_connection()
- 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’})