.. image:: https://github.com/capitalone/locopy/workflows/Python%20package/badge.svg :target: https://github.com/capitalone/locopy/actions .. image:: https://img.shields.io/badge/code%20style-black-000000.svg :target: https://github.com/ambv/black
locopy: Data Load and Copy using Python ========================================
A Python library to assist with ETL processing for:
- Amazon Redshift (`
COPY,UNLOAD) - Snowflake (
COPY INTO,COPY INTO) In addition:DB-API 2.0- The library supports Python 3.10 to 3.13
- DB Driver (Adapter) agnostic. Use your favourite driver that complies with
_- It provides functionality to download and upload data to S3 buckets, and internal stages (Snowflake)
Quick Installation ==================
.. code-block:: bash
pip install locopy
or install from conda-forge
.. code-block:: bash
conda config --add channels conda-forge conda install locopy
Installation instructions -------------------------
A virtual or conda environment is highly recommended
.. code-block:: bash
$ virtualenv locopy $ source locopy/bin/activate $ pip install --upgrade setuptools pip $ pip install locopy
Python Database API Specification 2.0 -------------------------------------
Rather than using a specific Python DB Driver / Adapter for Postgres (which should supports Amazon Redshift or Snowflake),
locopyprefers to be agnostic. As an end user you can use any Python Database API Specification 2.0 package.The following packages have been tested:
psycopg2pg8000snowflake-connector-pythonYou can use which ever one you prefer by importing the package and passing it into the constructor inputdbapi.Usage -----
You need to store your connection parameters in a YAML file (or pass them in directly). The YAML would consist of the following items:
.. code-block:: yaml
# required to connect to redshift host: my.redshift.cluster.com port: 5439 database: db user: userid password: password ## optional extras for the dbapi connector sslmode: require another_option: 123
If you aren't loading data, you don't need to have AWS tokens set up. The Redshift connection (
Redshift) can be used like this:.. code-block:: python
import pg8000 import locopy
with locopy.Redshift(dbapi=pg8000, config_yaml="config.yml") as redshift: redshift.execute("SELECT * FROM schema.table") df = redshift.to_dataframe() print(df)
If you want to load data to Redshift via S3, the
Redshiftclass inherits fromS3:.. code-block:: python
import pg8000 import locopy
with locopy.Redshift(dbapi=pg8000, config_yaml="config.yml") as redshift: redshift.execute("SET query_group TO quick") redshift.execute("CREATE TABLE schema.table (variable VARCHAR(20)) DISTKEY(variable)") redshift.load_and_copy( local_file="example/example_data.csv", s3_bucket="my_s3_bucket", table_name="schema.table", delim=",") redshift.execute("SELECT * FROM schema.table") res = redshift.cursor.fetchall()
print(res)
If you want to download data from Redshift to a CSV, or read it into Python
.. code-block:: python
my_profile = "some_profile_with_valid_tokens" with locopy.Redshift(dbapi=pg8000, config_yaml="config.yml", profile=my_profile) as redshift: ##Optionally provide export if you ALSO want the exported data copied to a flat file redshift.unload_and_copy( query="SELECT * FROM schema.table", s3_bucket="my_s3_bucket", export_path="my_output_destination.csv")
Note on tokens ^^^^^^^^^^^^^^
To load data to S3, you will need to be able to generate AWS tokens, or assume the IAM role on a EC2 instance. There are a few options for doing this, depending on where you're running your script and how you want to handle tokens. Once you have your tokens, they need to be accessible to the AWS command line interface. See http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html#config-settings-and-precedence for more information, but you can:
- Populate environment variables
AWS_ACCESS_KEY_ID,AWS_SECRET_ACCESS_KEY, etc.- Leverage the AWS credentials file. If you have multiple profiles configured
locopy.Redshift(profile="my-profile"), or set up an environment variableAWS_DEFAULT_PROFILE.docs- If you are on a EC2 instance you can assume the credentials associated with the IAM role attached.
Advanced Usage --------------
See the
_ for more detailed usage instructions and examples including Snowflake.Contributor License Agreement (CLA)Contributors ------------
We welcome and appreciate your contributions! Before we can accept any contributions, we ask that you please be sure to sign the
_.Open Source Code of ConductThis project adheres to the
_. By participating, you are expected to honor this code.hereRoadmap -------
Roadmap details can be found
Related Stories
Xiami Mimo 2.6 Live Post-Training Dashboard
Hacker News 1 hour agoHow good are frontier models at physics?
Hacker News 2 hours agoAccurate Models of AMD Matrix Cores
Hacker News 2 hours agoFed hikes rates as inflation worries push up bond yields
Hacker News 2 hours agoTraining a 4B model to produce 81% faster query plans than Postgres
Hacker News 2 hours agoChat with meRelated Stories