How to store data with Flask App + Supabase

So before store results into supabase on a flask its important to have data or a algorithm that is producing new sets of results that can be record and retrieved for users on the platform if you don’t have you can see the previous section. 

Store the results in Supabase PostgreSQL

Store the results in Supabase PostgreSQL

Supabase is a cloud database as a cloud-based backend to store & retrieve data.

When you got the project create

Then click the gear in the bottom left

then click API

then copy both the project URL and the Project API keys you need this URL to give supabase library code to know which database you are hitting and you need the secret key to access the database to insert and pull data.

then were going to back our .env file and create two new environmental variables

fill in Supabase_url and Supabase_key

we need to pip install supabase

pip install supabase

then in the app.py import statements we going add

from supabase import create_client

it should look like this with the supabase library and import functions from datetime and date. We also need import pytz for timezone database or tz database

import os
from flask import Flask, render_template, request, jsonify
from dotenv import load_dotenv
from flask_cors import CORS
from supabase import create_client
from datetime import datetime, date
import pytz

# Define the timezone, for example, UTC
tz = pytz.timezone('UTC')

from models.model_call import call_model

so it should like this the same way we called Alpha Vantage Key we call Supabase

#Load environment variables
load_dotenv()

# Initialize Flask App
app = Flask(__name__)
# then CORS class is used to wrap the app object to add  CORS headers to all responses from the app 
CORS(app)

app.config['DEBUG'] = os.environ.get('DEBUG')

# Import API keys
API_KEY = os.environ.get('API_KEY')
supabase_url = os.getenv("SUPABASE_URL")
supabase_key = os.getenv("SUPABASE_KEY")

then below this code, we have to call function create_client and past

# then below 
supabase = create_client(supabase_url, supabase_key)

Then inside the route call_model were going create json structure with data the user made

@app.route('/call-model', methods=['POST'])
def call_model_route():
    form_data = request.form

    # So before we past data to call model we going to same data to supabase
    # we need to structure the data
     data_input = { 
        'inserted_at' : datetime.now(tz).isoformat(),
        'updated_at' : datetime.now(tz).isoformat(),
        'username' : 'guest_id',
        'ticker' : form_data['ticker'],
        'investment' : float(form_data['dollars']),
        'months' : form_data['num_months'],
        'iterations' : form_data['num_iter'],
    }

    # Ok then we have to create a schema in supabase 

    # Your logic here, using the form values
    model_result = call_model(API_KEY, form_data)

    return jsonify(model_result)

then were going write our schema to match the json structure when you make changes to json structure here or data structure you need to update the database schema.

You should be redirect to the create table page

side bar appears we scroll down.

Then we make columns to match the JSON structure to

   'inserted_at' : datetime.now(tz).isoformat(),
        'updated_at' : datetime.now(tz).isoformat(),
        'username' : 'guest_id',
        'ticker' : form_data['ticker'],
        'investment' : float(form_data['dollars']),
        'months' : form_data['num_months'],
        'iterations' : form_data['num_iter'],

We need to define data types of each column

• So inserted_at is timestamptz
• so updated_at is timestamptz
• so ticker is Text
• so investment is Float8
• so months is Int8
• so iterations is Int8

You should see this

then when you save and you should see this new table

now go back to the VScode below the json structure. We wrap the supabase.table method call in a try statement

    # Ok then we have to create a schema in supabase 
    try:
        supabase.table('user_upload').insert(data_input).execute()
    except Exception as e:
        print("An exeption occured", e)
        return jsonify({'insert into database  failed error': str(e)})

were going to start the app locally and submit data into our UI inputs

we click submit and we should see new row of data appear

if you have any errors it could be json structure is mismatched with the schema

Now that we finally learned how to save data to a database we can deploy the application again and test it on deployment.

See the next section.