Instackup

This Python library is an open source way to standardize and simplify connections with cloud-based tools, databases and commonly used tools in data manipulation and analysis. It can help BI teams by having a unified source code for local development and testing as well as remote production (automated scheduled run) environments.

Index

• Current release
• Prerequisites
• Installation
• Documentation
• Version logs

Current release

Version 0.3.0 (beta)

Prerequisites

1. Have a Python 3.6 version or superior installed;
2. Create a YAML (or JSON) file with credentials information;
3. [Optional but recommended] Configure an Environment Variable that points where the Credentials file is.

1. Have a Python 3.6 version or superior installed

Got to this link e download the most current version that is compatible with this package.

2. Create a YAML (or JSON) file with credentials information

Use the files secret_template.yml or secret_blank.yml as a base or copy and paste the code bellow and modify its values to the ones in your credentials/projects:

#################################################################
#                                                               #
#        ACCOUNTS CREDENTIALS. DO NOT SHARE THIS FILE.          #
#                                                               #
# Specifications:                                               #
# - For the credentials you don't have, leave it blank.         #
# - Keep Google's secret file in the same folder as this file.  #
# - BigQuery project_ids must be strings, i.e., inside quotes.  #
#                                                               #
# Recommendations:                                              #
# - YAML specification: https://yaml.org/spec/1.2/spec.html     #
# - Keep this file in a static path like a folder within the    #
# Desktop. Ex.: C:\Users\USER\Desktop\Credentials\secret.yml    #
#                                                               #
#################################################################

Location: local


Google:
  default:
    project_id: project_id
    project_name: project_name
    project_number: "000000000000"
    secret_filename: api_key.json


AWS:
  default:
    access_key: AWSAWSAWSAWSAWSAWSAWS
    secret_key: some_secret_key_value

RedShift:
  default:
    cluster_credentials:
      dbname: db
      user: masteruser
      host: blablabla.random.us-east-2.redshift.amazonaws.com
      cluster_id: cluster
      port: 5439
    master_password:
      dbname: db
      user: masteruser
      host: blablabla.random.us-east-2.redshift.amazonaws.com
      password: masterpassword
      port: 5439


PostgreSQL:
  default:
    dbname: postgres
    user: postgres
    host: localhost
    password: ""
    port: 5432


MySQL:
  default:
    dbname: mydb
    host: localhost
    user: root
    password: ""
    port: 3306

Save this file with .yml extension in a folder where you know the path won't be modified, like the Desktop folder (Example: C:\Users\USER\Desktop\Credentials\secret.yml).

If you prefer, you can follow this step using a JSON file instead. Follow the same instructions but using .json instead of .yml.

3. [Optional but recommended] Configure an Environment Variable that points where the Credentials file is.

To configure the Environment Variable, follow the instructions bellow, based on your Operating System.

Windows

1. Place the YAML (or JSON) file in a folder you won't change its name or path later;
2. In Windows Search, type Environment Variables and click in the Control Panel result;
3. Click on the button Environment Variables...;
4. In Environment Variables, click on the button New;
5. In Variable name type CREDENTIALS_HOME and in Variable value paste the full path to the recently created YAML (or JSON) file;
6. Click Ok in the 3 open windows.

Linux/MacOS

1. Place the YAML (or JSON) file in a folder you won't change its name or path later;
2. Open the file .bashrc. If it doesn't exists, create one in the HOME directory. If you don't know how to get there, open the Terminal, type cd and then ENTER;
3. Inside the file, in a new line, type the command: export CREDENTIALS_HOME="/path/to/file", replacing the content inside quotes by the full path to the recently created YAML (or JSON) file;
4. Save the file and restart all open Terminal windows.

Note: If you don't follow this last prerequisite, you need to set the environment variable manually inside the code. To do that, inside your python code, after the imports, type the command (replacing the content inside quotes by the full path to the recently created YAML (or JSON) file):

os.environ["CREDENTIALS_HOME"] = "/path/to/file"

Installation

Go to the Terminal and type:

pip install instackup

Documentation

Check the documentation by clicking in each topic.

• bigquery_tools
  • Global Variables
  • BigQueryTool
    • __init__(self, authenticate=True)
    • query(self, sql_query)
    • query_and_save_results(self, sql_query, dest_dataset, dest_table, writing_mode="TRUNCATE", create_table_if_needed=False)
    • list_datasets(self)
    • create_dataset(self, dataset, location="US")
    • list_dataset_permissions(self, dataset)
    • add_dataset_permission(self, dataset, role, email_type, email)
    • remove_dataset_permission(self, dataset, email)
    • list_tables_in_dataset(self, dataset, get=None, return_type="dict")
    • get_table_schema(self, dataset, table)
    • convert_postgresql_table_schema(self, dataframe, parse_json_columns=True)
    • convert_multiple_postgresql_tables_schema(self, dataframe, parse_json_columns=True)
    • convert_dataframe_to_numeric(dataframe, exclude_columns=[], **kwargs)
    • clean_dataframe_column_names(dataframe, allowed_chars="abcdefghijklmnopqrstuvwxyz0123456789", special_treatment={})
    • upload(self, dataframe, dataset, table, **kwargs)
    • create_empty_table(self, dataset, table, schema)
    • upload_from_gcs(self, dataset, table, gs_path, file_format="CSV", header_rows=1, delimiter=",", encoding="UTF-8", ignore_unknown_values=False, max_bad_records=0, writing_mode="APPEND", create_table_if_needed=False, schema=None)
    • upload_from_file(self, dataset, table, file_location, file_format="CSV", header_rows=1, delimiter=",", encoding="UTF-8", ignore_unknown_values=False, max_bad_records=0, writing_mode="APPEND", create_table_if_needed=False, schema=None)
    • start_transfer(self, project_path=None, project_name=None, transfer_name=None)
• gcloudstorage_tools
  • GCloudStorageTool
    • __init__(self, uri=None, bucket=None, subfolder="", filename=None, authenticate=True)
    • bucket(self) @property
    • blob(self) @property
    • uri(self) @property
    • set_bucket(self, bucket)
    • set_subfolder(self, subfolder)
    • select_file(self, filename)
    • list_all_buckets(self)
    • get_bucket_info(self, bucket=None)
    • get_file_info(self, filename=None, info=None)
    • list_contents(self, yield_results=False)
    • rename_file(self, new_filename)
    • rename_subfolder(self, new_subfolder)
    • upload_file(self, filename, remote_path=None)
    • upload_subfolder(self, folder_path)
    • upload_from_dataframe(self, dataframe, file_format='CSV', filename=None, overwrite=False, **kwargs)
    • download_file(self, download_to=None, remote_filename=None, replace=False)
    • download_subfolder(self)
    • download_on_dataframe(self, **kwargs)
    • download_as_string(self, remote_filename=None, encoding="UTF-8")
    • delete_file(self)
    • delete_subfolder(self)
• general_tools
  • fetch_credentials(service_name, **kwargs)
  • code_location()
  • unicode_to_ascii(unicode_string)
  • parse_remote_uri(uri, service)
• gsheets_tools
  • GSheetsTool
    • __init__(self, sheet_url=None, sheet_key=None, sheet_gid=None, auth_mode='secret_key', read_only=False, scopes=['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'])
    • set_spreadsheet_by_url(self, sheet_url)
    • set_spreadsheet_by_key(self, sheet_key)
    • set_worksheet_by_id(self, sheet_gid)
    • download(self)
    • upload(self, dataframe, write_mode="APPEND", force_upload=False)
• heroku_tools
  • HerokuTool
    • __init__(self, heroku_path="heroku", app=None, remote=None)
    • app_flag(self) @property
    • execute(self, cmd)
• redshift_tools
  • RedShiftTool
    • __init__(self, connect_by_cluster=True)
    • connect(self, fail_silently=False)
    • commit(self)
    • rollback(self)
    • close_connection(self)
    • execute_sql(self, command, fail_silently=False)
    • query(self, sql_query, fetch_through_pandas=True, fail_silently=False)
    • describe_table(self, table, schema="public", fetch_through_pandas=True, fail_silently=False)
    • get_all_db_info(self, get_json_info=True, fetch_through_pandas=True, fail_silently=False)
    • unload_to_S3(self, redshift_query, s3_path, filename, unload_options="MANIFEST GZIP ALLOWOVERWRITE REGION 'us-east-2'")
• s3_tools
  • S3Tool
    • __init__(self, uri=None, bucket=None, subfolder="")
    • bucket(self) @property
    • uri(self) @property
    • set_bucket(self, bucket)
    • set_subfolder(self, subfolder)
    • rename_file(self, old_filename, new_filename)
    • rename_subfolder(self, new_subfolder)
    • list_all_buckets(self)
    • list_contents(self, yield_results=False)
    • upload_file(self, filename, remote_path=None)
    • upload_subfolder(self, folder_path)
    • download_file(self, remote_path, filename=None)
    • download_subfolder(self, download_to=None)
    • delete_file(self, filename, fail_silently=False)
    • delete_subfolder(self)
• sql_tools
  • SQLTool
    • __init__(self, sql_type, filename=None, connection='default')
    • connect(self, fail_silently=False)
    • commit(self)
    • rollback(self)
    • close_connection(self)
    • execute_sql(self, command, fail_silently=False)
    • query(self, sql_query, fetch_through_pandas=True, fail_silently=False)
  • SQLiteTool
    • __init__(self, filename=None)
    • describe_table(self, table, fetch_through_pandas=True, fail_silently=False)
  • MySQLTool
    • __init__(self, connection='default')
    • describe_table(self, table, fetch_through_pandas=True, fail_silently=False)
  • PostgreSQLTool
    • __init__(self, connection='default')
    • describe_table(self, table, schema="public", fetch_through_pandas=True, fail_silently=False)
    • get_all_db_info(self, get_json_info=True, fetch_through_pandas=True, fail_silently=False)

Version logs

See what changed in every version.

• Beta releases
  • Version 0.3.0 (current release)
  • Version 0.2.6
  • Version 0.2.5
  • Version 0.2.4
  • Version 0.2.3
  • Version 0.2.2
  • Version 0.2.1
  • Version 0.2.0
  • Version 0.1.2
  • Version 0.1.1
  • Version 0.1.0
• Alpha releases
  • Version 0.0.6
  • Version 0.0.5
  • Version 0.0.4
  • Version 0.0.3
  • Version 0.0.2
  • Version 0.0.1