lab.data.extraction.postgres

Lab - Data - Extraction - Postgres

local_sql_session(config)

local_sql_session creates the control flow for a local sql session

config = {
    "sql_hostname": os.getenv("PLATFORM_SQL_URI"),  # 'sql_hostname'
    "sql_username": os.getenv("PLATFORM_SQL_USERNAME"),  # 'sql_username'
    "sql_password": os.getenv("PLATFORM_SQL_PWD"),  # 'sql_password'
    "sql_main_database": os.getenv("PLATFORM_SQL_DB"),  # 'db_name'
    "sql_port": int(os.getenv("PLATFORM_SQL_PORT")),
    "ssh_host": os.getenv("SSH_HOST"),  #'ssh_hostname'
    "ssh_user": os.getenv("SSH_USER"),  #'ssh_username'
    "ssh_port": 22,
    "ssh_key": os.getenv("SSH_KEY"),
}

@local_sql_session(config)
def main(session):
    q = session.execute('SELECT * from app_prod.shipment LIMIT 10;')
    print( q.fetchall() )

    # or use pandas
    # session.bind refers to the engine
    df = pd.read_sql(query, session.bind)
    print(df.head())

Parameters:

Name	Type	Description	Default
config	dict	configuration dictionary	required

Source code in dietbox/lab/data/extraction/postgres.py

def local_sql_session(config):
    """
    local_sql_session creates the control flow for a local sql session

    ```python
    config = {
        "sql_hostname": os.getenv("PLATFORM_SQL_URI"),  # 'sql_hostname'
        "sql_username": os.getenv("PLATFORM_SQL_USERNAME"),  # 'sql_username'
        "sql_password": os.getenv("PLATFORM_SQL_PWD"),  # 'sql_password'
        "sql_main_database": os.getenv("PLATFORM_SQL_DB"),  # 'db_name'
        "sql_port": int(os.getenv("PLATFORM_SQL_PORT")),
        "ssh_host": os.getenv("SSH_HOST"),  #'ssh_hostname'
        "ssh_user": os.getenv("SSH_USER"),  #'ssh_username'
        "ssh_port": 22,
        "ssh_key": os.getenv("SSH_KEY"),
    }

    @local_sql_session(config)
    def main(session):
        q = session.execute('SELECT * from app_prod.shipment LIMIT 10;')
        print( q.fetchall() )

        # or use pandas
        # session.bind refers to the engine
        df = pd.read_sql(query, session.bind)
        print(df.head())
    ```

    :param config: configuration dictionary
    :type config: dict
    """

    def local_sql_session_with_config(function):
        @wraps(function)
        def wrapper(*args, **kwargs):
            return with_local_sql_session(function, config, *args, **kwargs)

        return wrapper

    return local_sql_session_with_config

remote_sql_session(config)

local_sql_session creates the control flow for a remote sql session

config = {
    "sql_hostname": os.getenv("PLATFORM_SQL_URI"),  # 'sql_hostname'
    "sql_username": os.getenv("PLATFORM_SQL_USERNAME"),  # 'sql_username'
    "sql_password": os.getenv("PLATFORM_SQL_PWD"),  # 'sql_password'
    "sql_main_database": os.getenv("PLATFORM_SQL_DB"),  # 'db_name'
    "sql_port": int(os.getenv("PLATFORM_SQL_PORT")),
    "ssh_host": os.getenv("SSH_HOST"),  #'ssh_hostname'
    "ssh_user": os.getenv("SSH_USER"),  #'ssh_username'
    "ssh_port": 22,
    "ssh_key": os.getenv("SSH_KEY"),
}

@remote_sql_session(config)
def main(session):
    q = session.execute('SELECT * from app_prod.shipment LIMIT 10;')
    print( q.fetchall() )

    # or use pandas
    # session.bind refers to the engine
    df = pd.read_sql(query, session.bind)
    print(df.head())

Parameters:

Name	Type	Description	Default
config	dict	configuration dictionary	required

Source code in dietbox/lab/data/extraction/postgres.py

def remote_sql_session(config):
    """
    local_sql_session creates the control flow for a remote sql session

    ```python
    config = {
        "sql_hostname": os.getenv("PLATFORM_SQL_URI"),  # 'sql_hostname'
        "sql_username": os.getenv("PLATFORM_SQL_USERNAME"),  # 'sql_username'
        "sql_password": os.getenv("PLATFORM_SQL_PWD"),  # 'sql_password'
        "sql_main_database": os.getenv("PLATFORM_SQL_DB"),  # 'db_name'
        "sql_port": int(os.getenv("PLATFORM_SQL_PORT")),
        "ssh_host": os.getenv("SSH_HOST"),  #'ssh_hostname'
        "ssh_user": os.getenv("SSH_USER"),  #'ssh_username'
        "ssh_port": 22,
        "ssh_key": os.getenv("SSH_KEY"),
    }

    @remote_sql_session(config)
    def main(session):
        q = session.execute('SELECT * from app_prod.shipment LIMIT 10;')
        print( q.fetchall() )

        # or use pandas
        # session.bind refers to the engine
        df = pd.read_sql(query, session.bind)
        print(df.head())
    ```

    :param config: configuration dictionary
    :type config: dict
    """

    def remote_sql_session_with_config(function):
        @wraps(function)
        def wrapper(*args, **kwargs):
            return with_remote_sql_session(function, config, *args, **kwargs)

        return wrapper

    return remote_sql_session_with_config