How to work with an external API to seed your database

Seeding a database with data from an external API can be a super great way to populate your application with real world data. whether it be product information, user data or something else entirely like geographical details, using an external API can save you a lot of time and effort in the long run. In this blog I'm gonna kinda go over how to work with an external API specifically for seeding your DB (if that wasn't obvious from the title).

1, Choose the right API

First step might seem a bit obvious but you need to choose the right API for your application. A few key things to consider are the data accuracy, the limit rate, update frequency and the routes provided, not all API's are equal, some may be for the same thing, but one may provide much much more information than the other. In this example I'll just use a hypothetical API that provides information about books.

2, Setting up your project

Make sure that you alreadt have a database and project set up, I'll be going off of Flask with SQLAlchemy for this example so for that your project structure should look something like this

/myapp
    /models.py
    /app.py
    /seed.py
    /requirements.txt

3, Create your models

You need to define the database models that are going to store the API data, so like if you're working with the book data your model would look something like this

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(150), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    published_date = db.Column(db.String(10))
    isbn = db.Column(db.String(13))
    pages = db.Column(db.Integer)
    cover = db.Column(db.String(150))
    language = db.Column(db.String(50))

4, Fetch data from the API

To do this you need to write a script to fetch data from the external API. this script will basically just parse the API response and insert the data into your database, below is an example I wrote.

def fetch_books():
    response = requests.get('https://api.example.com/books')
    if response.status_code == 200:
        return response.json()
    else:
        raise Exception('Failed to fetch data from API')

def seed_books():
    books = fetch_books()
    for book in books:
        new_book = Book(
            title=book['title'],
            author=book['author'],
            published_date=book['published_date'],
            isbn=book['isbn'],
            pages=book['pages'],
            cover=book['cover'],
            language=book['language']
        )
        db.session.add(new_book)
    db.session.commit()

if __name__ == '__main__':
    seed_books()

5, Run the script

Now you just run the script using python seed.py in the terminal.

6, Verify everything is correct

Just check your database in vscode to make sure that the data you wanted was correctly inserted.

Conclusion

Seeding your database with the help of an external API can greatly enhance the functionality of your application as well as save you a lot of time tediously seeding your database manually. By following these steps you should be able to seamlessly integrate real world external data into your application.