Harnessing the Power of Flask-SQLAlchemy: A Guide to Fetching External API Data

In the dynamic realm of web development, the ability to seamlessly integrate external API data into your Flask application can be a transformative game-changer. Flask, a lightweight web framework for Python, when coupled with SQLAlchemy for efficient database management, opens up a seamless avenue to retrieve, store, and utilize data from external APIs. In this comprehensive guide, we will walk you through the process of fetching information from an external API using Flask-SQLAlchemy, empowering your backend Python code with real-time and dynamic data.

Flask-SQLAlchemy Setup

Before delving into the intricacies of API integration, ensure that Flask, Flask-SQLAlchemy, Flask-Migrate, Flask-RESTful, and Flask-CORS are installed in your Python environment. If not, install them using:

pip install Flask Flask-SQLAlchemy Flask-Migrate Flask-RESTful Flask-CORS

Next, initialize your Flask app and configure the SQLAlchemy database connection. In this simplified example, I initialize the app in a config.py file and pull the app/db/api variables into the models.py file and into the app.py file as needed:

# config.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from sqlalchemy import MetaData
from flask_restful import Api
from flask_cors import CORS

# Instantiate app
app = Flask(__name__)

# Configure SQLAlchemy app
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'  # Use preferred database name
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Define metadata
metadata = MetaData(naming_convention={
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
})

# Instantiate db, Initialize Flask-Migrate
db = SQLAlchemy(metadata=metadata)
migrate = Migrate(app, db)
db.init_app(app)

# Initialize Flask-RESTful
api = Api(app)

# Enable CORS
CORS(app)

Defining the Model

The foundation of any Flask-SQLAlchemy project lies in defining a model that mirrors the structure of the data you will be fetching and storing. This model essentially serves as a blueprint for your data. For instance, if you are dealing with information about restaurants, your model might look like this:

# models.py
from config import db

class Restaurant(db.Model):
    __tablename__ = 'restaurants'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    address = db.Column(db.String, nullable=False)
    # Add more fields as needed

Fetching Data from the API

With the groundwork laid, the next step involves creating a Flask Route to fetch data from the external API. To accomplish this, the requests library proves invaluable. In this example I am pulling information from the Yelp API when the user performs a search on my site. You use this same basic template to apply it to other APIs just make sure to put your actual API_KEY given to you by the API:

# app.py
import requests
from flask import request, session
from flask_restful import Resource
from urllib.parse import quote
from urllib.error import HTTPError
import sys
from config import app, db, api

class Results(Resource):
    def post(self):
        API_KEY = 'Given to you by the API you signed up with' # use your API key
        API_URL = 'https://api.yelp.com/v3/businesses/search'
        SEARCH_LIMIT = 20
        def requester(api_url, api_key, url_params=None):
            url_params = url_params or {}
            headers = {'Authorization': f'Bearer {api_key}'} # Yelp API requires 'Bearer ', but other APIs may use something else
            print(f'Querying {api_url} ...')
            response = requests.request('GET', api_url, headers=headers, params=url_params)
            return response.json()
        def search(api_key, term, location):
            url_params = {
                'term': term.replace(' ', '+'),
                'location': location.replace(' ', '+'),
                'limit': SEARCH_LIMIT,
                'offset': request.get_json().get('offset')
            }
            return requester(API_URL, api_key, url_params=url_params)
        def query_api(term, location):
            response = search(API_KEY, term, location)
            businesses = response.get('businesses')
            if not businesses:
                print(f'No businesses for {term} in {location} found.')
                return None
            return response
        def main():
            input_values = request.get_json()
            if session.get('query') and session.get('location') and not input_values.get('restaurant'):
                return query_api(session['query'], session['location'])
            try:
                session['query'] = input_values.get('restaurant')
                session['location'] = input_values.get('location')
                return query_api(input_values.get('restaurant'), input_values.get('location'))
            except HTTPError as error:
                sys.exit(f'Encountered HTTP error {error.code} on {error.url}:\n {error.read()}\nAbort program.')
        return main()

api.add_resource(Results, '/results')

Storing Data in the Database

Now equipped with data from the API, the next logical step is to store it in your Flask-SQLAlchemy database if it i snot already there. You can store each restaurant into your database with a separate Flask Route during a post request. This post request will either store it if it isn't in your database, or update the session with the current restaurant if it is already in your database:

#app.py

class Restaurant(Resource):
    def post(self):
        restaurant = Restaurant.query.filter_by(name = request.get_json().get('name')).filter_by(address = request.get_json().get('address')).first()
        if restaurant:
            session['restaurant_id'] = restaurant.id
        else :
            new_restuarant = Restaurant(name = request.get_json().get('name') , address = request.get_json().get('address'))
            db.session.add(new_restuarant)
            db.session.commit()
            session['restaurant_id'] = new_restuarant.id

api.add_resource(Restaurant, '/restaurant', endpoint='restaurant')

Integrating Everything via these Flask Routes

By visiting the /Results route through a post request in order to send up the search information, such as the restaurant name/type and a location. At this point, no restaurant information is being stored in your database yet. In order to optimize your database, only trigger the storing when the user goes to look at an individual restaurant's reviews or make a new review for that restaurant. At that point, the user will trigger a post request to the /Restaurant route which will store that restaurant in your database if not already there. If it is already in your database, it will update the session. This way, you can effortlessly trigger the process of fetching data from the API and storing it in your Flask-SQLAlchemy database while optimizing what gets stored.

Conclusion

In conclusion, Flask-SQLAlchemy provides a robust foundation for handling external API data within your Flask application seamlessly. This guide has equipped you with the knowledge to set up your Flask app, define a model, fetch data from an external API, and store that data in your SQLAlchemy database. This powerful combination empowers your Python backend to remain dynamically connected to the ever-changing landscape of external data sources, enhancing the functionality and richness of your web application. As you continue to explore the vast possibilities of Flask-SQLAlchemy, may your coding journey be both enlightening and rewarding.