Examples of how you can import data to, or export data from Pigment using the Pigment API and Python.
1. Generate Pigment Import/Export API key
https://community.pigment.com/security-permissions-82/manage-api-keys-226
2. For Imports into Pigment (to understand the process)
https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230
3. For Exports from Pigment (to understand the process)
4. To import data using Python:
import requests
import csv
# Replace with your actual Pigment API key and import ID
API_KEY = 'YOUR_PIGMENT_IMPORT_API_KEY'
IMPORT_ID = 'YOUR_IMPORT_ID'
API_URL = f'https://pigment.app/api/import/push/csv?configurationID={IMPORT_ID}'
# Path to your CSV file
CSV_FILE_PATH = 'yourfile.csv'
# Read the CSV file
with open(CSV_FILE_PATH, 'r') as file:
csv_data = file.read()
# Set up the request headers and payload
headers = {
'Authorization': f'Bearer {API_KEY}',
'Content-Type': 'application/csv'
}
payload = csv_data
# Send the POST request to Pigment API
response = requests.post(API_URL, headers=headers, data=payload)
# Check the response
if response.status_code == 200:
print('Data successfully imported into Pigment.')
else:
print(f'Error importing data to Pigment: {response.status_code} - {response. Text}')
Explanation:
-
API Key and Import ID: Replace
YOUR_PIGMENT_IMPORT_API_KEY
andYOUR_IMPORT_ID
with your actual Pigment API key and import ID. -
CSV File Path: Specify the path to your CSV file containing the data you want to export, in this example
yourfile.csv
. This must match the defined import definition, talk to your Pigment team. -
Read CSV Data: The script reads the CSV file and prepares it for export.
-
Set Up Request: The script sets up the request headers and payload.
-
Send Request: The script sends a POST request to the Pigment API to push the data.
-
Check Response: The script checks the response from the Pigment API and prints a success or error message.
5. To export data using Python:
import requests
import csv
# Replace with your actual Pigment API key and view ID
API_KEY = 'YOUR_PIGMENT_EXPORT_API_KEY'
VIEW_ID = 'YOUR_VIEW_ID'
API_URL = f'https://pigment.app/api/export/view/{VIEW_ID}'
# Set up the request headers
headers = {
'Authorization': f'Bearer {API_KEY}'
}
# Send the GET request to Pigment API
response = requests.get(API_URL, headers=headers)
# Check the response
if response.status_code == 200:
csv_data = response.text
# Save the CSV data to a file
with open('exported_data.csv', 'w', newline='') as file:
writer = csv.writer(file)
for line in csv_data.splitlines():
writer.writerow(line.split(';')) # Adjust delimiter if needed
print('Data successfully exported from Pigment.')
else:
print(f'Error exporting data from Pigment: {response.status_code} - {response. Text}')
Explanation:
-
API Key and View ID: Replace
YOUR_PIGMENT_EXPORT_API_KEY
andYOUR_VIEW_ID
with your actual Pigment API key and view ID. -
Fetch Data: The script sends a GET request to the Pigment API to fetch the data.
-
Save Data: The script saves the fetched data to a CSV file named
exported_data.csv
. It splits the data by lines and then by semicolons (;
) to handle the CSV format.
5. To export raw data (table) using Python:
There are occasions when the volume of data contained within a view is too large export, in this situation you can this method.
This Community article explains the different types of ‘raw’ exports. Please read this, this is very important in understanding the raw export types.
- If Table then
API_EXPORT_URL
=https://pigment.app/api/export/table/ - If Metric then
API_EXPORT_URL
=https://pigment.app/api/export/metric/ - If List then
API_EXPORT_URL
=https://pigment.app/api/export/List/
Note: This is a far more complex example than the previously shown.
import requests
import json
import logging
# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
# API KEY and API URL of Pigment
API_EXPORT_KEY = 'YOUR_PIGMENT_EXPORT_API_KEY'
API_EXPORT_URL = 'https://pigment.app/api/export/table/'
OUTPUT_FILE = 'exported_data.csv' # CSV file name
CHUNK_SIZE = 10*1024*1024 # 10Mbytes for streaming to reduce POST requests
PIGMENT_METRIC_ID = 'YOUR_PIGMENT_TABLE_ID'
headers = {
'Authorization': f'Bearer {API_EXPORT_KEY}',
'Content-type': 'application/json;charset=utf-8'
}
datareq = {
"friendlyHeaders": True # use the Pigment metric names
}
logging.info(f"Starting export of {PIGMENT_METRIC_ID}...")
try:
with requests.post(API_EXPORT_URL + PIGMENT_TABLE_ID, headers=headers, data=json.dumps(datareq), stream=True) as s:
s.raise_for_status()
with open(OUTPUT_FILE, 'w', encoding="utf-8") as f:
for chunk in s.iter_content(chunk_size=CHUNK_SIZE):
if chunk:
f.write(chunk.decode('utf-8'))
logging.info('Data successfully exported to %s', OUTPUT_FILE)
except requests.exceptions.HTTPError as http_err:
logging.error('HTTP error occurred: %s', http_err)
except Exception as err:
logging.error('An error occurred: %s', err)
Explanation:
-
Import Libraries:
-
requests
: For making HTTP requests to the Pigment API. -
json
: For handling JSON data. -
logging
: For logging information and errors.
-
-
API Key and Import ID: Replace
YOUR_PIGMENT_IMPORT_API_KEY
andYOUR_PIGMENT_TABLE_ID
with your actual Pigment API key and metric ID. -
Save Data: The script saves the fetched data to a CSV file named
exported_data.csv
. It splits the data by lines and then by semicolons (;
) to handle the CSV format. -
datareq: Contains the values from your payload generation step. Please note that Python uses ‘True’ and ‘False’ rather than ‘true’ and ‘false’ for the ‘friendlyHeaders’.
-
Streaming the Response: The
stream=True
parameter tells therequests
library to stream the response content. This is useful for handling large responses that you don't want to load entirely into memory at once. The benefits of streaming are:-
Memory Efficiency: Streaming allows you to process large amounts of data without loading it all into memory at once, which is crucial for handling big datasets.
-
Performance: By processing data in chunks, you can start working with the data as soon as the first chunk is received, rather than waiting for the entire response to be downloaded.
-
Please note this example only refers to a Table raw export, if you need to export Lists or Metrics, please read the article above, adjust the payload and Export URL in the Python code.