using_pyairtable

Import Records from Snowflake into Airtable

This code example can be used to update or insert ("upsert") the list of records from a Snowflake Table or View into Airtable. This example assumes that your column names in Snowflake match your Airtable field names exactly. You can schedule this script to run on a recurring schedule to keep your Airtable base "in sync" with Snowflake.

This code is based on the generic pyAirtable upsert example and uses pyAirtable (maintained by our community) to interact with the Airtable REST API and Snowflake's python connector to interact with Snowflake.

---

The software made available from this repository is not supported by Formagrid Inc (Airtable) or part of the Airtable Service. It is made available on an "as is" basis and provided without express or implied warranties of any kind.

---

Local setup

1. Clone/unzip code
2. Copy .env.example to .env and populate values
   • Explanations of each environment variable is available below
3. Install Python dependencies using pip3 install -r requirements.txt
4. Run python3 example.py to run the script

Key files and their contents

• example.py is the main code file which is executed when python3 example.py is run. At a high level, it performs the following:
  • Loads dependencies and configuration variables
  • Connects to Snowflake using the Snowflake Python Connector
  • Queries all records from the specified Snowflake Table or View and sets this list to the variable inputRecords
  • In chunks of 10, upserts records
• .env.example is an example file template to follow for your own .env file. The environment variables supported are:
  • AIRTABLE_API_KEY - your Airtable API key or personal access token
  • AIRTABLE_BASE_ID - the ID of your base; you can find this on the base's API docs from https://airtable.com/api. This will always start with app
  • AIRTABLE_TABLE_ID - the ID of the table you want to create/update records in; you can find this in the URL of your browser when viewing the table. It will start with tbl
  • AIRTABLE_UNIQUE_FIELD_NAME - the field name of the field that is used for determining if an existing records exists that needs to be updated (if no record exists, a new one will be created)
  • SNOWFLAKE_USERNAME - your Snowflake username
  • SNOWFLAKE_PASSWORD - your Snowflake password
  • SNOWFLAKE_ACCOUNT - your Snowflake Account identifier
  • SNOWFLAKE_TABLE_OR_VIEW - the name of the table or view you want to retrieve records from

Notes

• pyairtable handles API rate limiting
• The field used for uniqueness does not have to be the primary field.
• The field name for the unique field is expected to remain consistent. If it changes, update the environment variable
• Each existing and new record is expected to have a value for the field used for uniqueness.