Pandas offers a wide range of functions and methods for data manipulation, making it a powerful tool for handling large datasets. One of the most common tasks in data manipulation is retrieving data from an external source, such as a REST API, and uploading it to a database. This can be done easily with pandas using the read_csv() function, which allows you to read data from a CSV file or a URL.

To retrieve data from AmetricX’s REST API, you will need to have the URL of the API and an API Key. Once you have this information, you can use the read_csv() function to retrieve the data and store it in a pandas DataFrame. From there, you can use pandas’ built-in functions and methods to manipulate the data as needed.

If you did not sign up for AmetricX yet, do so with this link, it is FREE.

AmetricX

1. Obtain an API Key

2. Retrieve a CSV file with Python

3. Define Database connection

4. Append data to existing table

All in one script

AmetricX

AmetricX

1. Obtain an API Key

2. Retrieve a CSV file with Python

3. Define Database connection

4. Append data to existing table

All in one script

For more information read the Generate API Key documentation

Navigate to settings from the top bar

Under API KEYS click on Add Api Key

Click on Add Api Key to create a new key).The actual key will display only once and cannot be retrieved afterward.

Start Sharing CSV files FREE today

For additional details, read the CSV File Exchange API documentation

import pandas as pd
file_id = <FILE ID>   # See examples to get the FILE ID https://docs.ametricx.com/file_api/reference/#curl-example_1
df = pd.read_csv(
     'https://trial.ametricx.com/api/v1/file/download/{file_id}'.format(file_id=file_id),
     storage_options={'Authorization': 'Bearer <YOUR API KEY>)

We use sqlalchemy to create and “engine” to pass to pandas

import sqlalchemy as sa
engine = sa.create_engine('postgresql://ametricx:ametricx@localhost:5432/ametricx')

For this example, we append the data to an existing table. Please read the pandas.Dataframe.to_sql documentation for additional details

df.to_sql('metrics_store', engine, if_exists='append', index=False)

import pandas as pd
import sqlalchemy as sa

file_id = FILE_ID   # See examples to get the FILE ID https://docs.ametricx.com/file_api/reference/#curl-example_1
df = pd.read_csv(
     "https://trial.ametricx.com/api/v1/file/download/{FILE_ID}".format(FILE_ID=FILE_ID),
     storage_options={'Authorization': 'Bearer {API_KEY}'.format(API_KEY=API_KEY)})

engine = sa.create_engine('postgresql://ametricx:ametricx@localhost:5432/ametricx')

df.to_sql('metrics_store', engine, if_exists='append', index=False)