How to Build an ETL App for Webflow Data in Python with CData

The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData API Driver for Python and the petl framework, you can build Webflow-connected applications and pipelines for extracting, transforming, and loading Webflow data. This article shows how to connect to Webflow with the CData Python Connector and use petl and pandas to extract, transform, and load Webflow data.

With built-in, optimized data processing, the CData Python Connector offers unmatched performance for interacting with live Webflow data in Python. When you issue complex SQL queries from Webflow, the driver pushes supported SQL operations, like filters and aggregations, directly to Webflow and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to Webflow Data

Connecting to Webflow data looks just like connecting to any relational data source. Create a connection string using the required connection properties. For this article, you will pass the connection string as a parameter to the create_engine function.

Authentication

Webflow uses OAuth 2.0 authentication to ensure secure access to sites, CMS collections, e-commerce data, and other resources. This authentication method allows you to securely connect to your Webflow workspace and manage resources with proper authorization.

OAuth 2.0 Setup and Configuration

Step 1: Create a Webflow OAuth Application

To set up OAuth authentication:

1. Visit the Webflow Developer Portal
2. Navigate to "Apps & Integrations" in your Webflow account
3. Click "Register an App" to create a new OAuth application
4. Configure the application name, description, and redirect URI (CallbackURL)
5. Copy the Client ID and Client Secret for use in your connection

Required Connection Properties

• AuthScheme: Set this to OAuth (required)
• OAuthClientId: Client ID from your Webflow OAuth application (required)
• OAuthClientSecret: Client secret from your Webflow OAuth application (required)
• CallbackURL: Redirect URI specified in your OAuth application (required)
• InitiateOAuth: Set to GETANDREFRESH for automatic token management (recommended)

Required OAuth Scopes

The Webflow API Profile requires the following OAuth scopes:

• sites:read - Read access to site information and configuration
• pages:read - Read access to site pages
• cms:read - Read access to CMS collections and items
• forms:read - Read access to forms and form submissions
• assets:read - Read access to media assets and folders
• ecommerce:read - Read access to products, orders, and inventory
• authorized_user:read - Read access to the authorized user

After installing the CData Webflow Connector, follow the procedure below to install the other required modules and start accessing Webflow through Python objects.

Install Required Modules

Use the pip utility to install the required modules and frameworks:

pip install petl
pip install pandas

Build an ETL App for Webflow Data in Python

Once the required modules and frameworks are installed, we are ready to build our ETL app. Code snippets follow, but the full source code is available at the end of the article.

First, be sure to import the modules (including the CData Connector) with the following:

import petl as etl
import pandas as pd
import cdata.api as mod

You can now connect with a connection string. Use the connect function for the CData Webflow Connector to create a connection for working with Webflow data.

cnxn = mod.connect("Profile=C:\profiles\Webflow.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;")

Create a SQL Statement to Query Webflow

Use SQL to create a statement for querying Webflow. In this article, we read data from the Sites entity.

sql = "SELECT ,  FROM Sites WHERE Id = 'your_site_id'"

Extract, Transform, and Load the Webflow Data

With the query results stored in a DataFrame, we can use petl to extract, transform, and load the Webflow data. In this example, we extract Webflow data, sort the data by the column, and load the data into a CSV file.

Loading Webflow Data into a CSV File

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

etl.tocsv(table2,'sites_data.csv')

With the CData API Driver for Python, you can work with Webflow data just like you would with any database, including direct access to data in ETL packages like petl.

Free Trial & More Information

Download a free, 30-day trial of the CData API Driver for Python to start building Python apps and scripts with connectivity to Webflow data. Reach out to our Support Team if you have any questions.

Full Source Code

import petl as etl
import pandas as pd
import cdata.api as mod

cnxn = mod.connect("Profile=C:\profiles\Webflow.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;")

sql = "SELECT ,  FROM Sites WHERE Id = 'your_site_id'"

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'')

etl.tocsv(table2,'sites_data.csv')