Build a Simple REST API Using Python Flask and SQLite (With Tests)

You can find the complete project and test files for this tutorial on GitHub if you’d like to follow along or extend the code further.

In this tutorial we are going to build a REST API using Python Flask. We will create a simple Flask application that serves a single endpoint, GET /items, which returns a list of items from a SQLite database. 

This guide is written for beginners and will walk you through each step in a straightforward way. By the end, you will have a working API, a basic test file to verify the API, and a project structure that you can zip and upload to GitHub.

Prerequisites and Setup

Before we start, make sure you have the following:

• Python installed on your system (Python 3 recommended)
• Flask library installed (pip install flask)
• Basic knowledge of Python (functions, lists, dictionaries)
• A folder on your computer for this project (e.g., flask_api_project)

Project Overview

We will use Flask to handle HTTP requests and SQLite as a lightweight database to store our items. The API will have one endpoint, GET /items, which returns JSON data for all items in the database. We’ll also write a simple test to ensure the endpoint works as expected.

Creating the Flask Application (app.py)

Let’s start by creating our Flask application file. Open a new file called app.py in your project directory and add the following code:

import sqlite3
from flask import Flask, jsonify

# Initialize Flask app
app = Flask(__name__)

# Initialize database and create table if it doesn't exist
conn = sqlite3.connect('items.db')  # this will create 'items.db' if it doesn't exist
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
# Insert some sample data if the table is empty
cursor.execute("SELECT COUNT(*) FROM items")
count = cursor.fetchone()[0]
if count == 0:
    cursor.execute("INSERT INTO items (name) VALUES ('Sample Item 1')")
    cursor.execute("INSERT INTO items (name) VALUES ('Sample Item 2')")
    conn.commit()
conn.close()

# Define the GET /items endpoint
@app.route('/items', methods=['GET'])
def get_items():
    # Connect to the database and fetch all items
    conn = sqlite3.connect('items.db')
    cursor = conn.cursor()
    cursor.execute("SELECT id, name FROM items")
    rows = cursor.fetchall()
    conn.close()
    # Convert the query result into a list of dictionaries
    items = []
    for row in rows:
        items.append({"id": row[0], "name": row[1]})
    # Return the list of items as JSON
    return jsonify(items)

# Run the app on local development server
if __name__ == '__main__':
    app.run(debug=True)

Explanation

• We import sqlite3 to interact with the SQLite database, and Flask and jsonify from the Flask library.
• We create a Flask app instance.
• We connect to a SQLite database file named items.db. If this file doesn’t exist, SQLite will create it. We then create a table named items with two columns: an auto-incrementing ID (primary key) and a name (text).
• We insert two sample items (“Sample Item 1” and “Sample Item 2”) into the table if and only if the table is empty. This ensures that when we first run the app, we have some data to retrieve.
• We define a route @app.route('/items', methods=['GET']) that maps to the get_itemsfunction. When a GET request is sent to /items, this function will execute:
  • It opens a new connection to the database, selects all rows from the items table, and closes the connection.
  • It then iterates over the result rows and builds a list of dictionaries, where each dictionary has an ID and a name key.
  • Finally, it uses jsonify(items) to convert the Python list to JSON and sends it as the response.

• The if __name__ == '__main__': block will run the Flask development server on http://127.0.0.1:5000 when you execute python app.py. We set debug=True for easier debugging (this will auto-reload the server on code changes and show errors if any occur).

Running the Application Locally

Now that we have app.py ready, let’s run our Flask API and see it in action.

1. Start the Flask Server

In your terminal or command prompt, make sure you’re in the project directory (where app.py is located) and run:

python app.py

You should see output indicating the server is running, for example:

* Serving Flask app "app"
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

This means your API is up and listening on port 5000.

2. Access the /items Endpoint

Open a web browser and navigate to http://127.0.0.1:5000/items or use a command-line tool like curl:

curl http://127.0.0.1:5000/items

You should receive a JSON response containing the list of items from the database. For example, you might see:

[
  {"id": 1, "name": "Sample Item 1"},
  {"id": 2, "name": "Sample Item 2"}
]

This JSON output is the data our Flask API returned. Each item in the list is a JSON object with an ID and a name. If you see this output, congratulations! Your GET /items endpoint is working correctly.

3. Stop the Server

When you’re done, go back to the terminal and press CTRL+C to stop the Flask development server.

Tip: If you encounter an error like “Address already in use” or “OSError: [Errno 98] Address already in use”, it means something is already running on port 5000. You can either stop that process or change the port by modifying app.run(debug=True) to app.run(debug=True, port=5001) (or another port).

Writing Tests for the API (test_app.py)

Testing your API is a good practice, even for simple projects. We’ll create a test file using Python’s built-in unittest framework to ensure that our endpoint behaves as expected. Create a file named test_app.py in the same directory and add the following code:

import unittest
from app import app  # import the Flask app instance from app.py

class ItemsApiTestCase(unittest.TestCase):
    def setUp(self):
        # Runs before each test. Set up a test client for our Flask app.
        app.testing = True  # put Flask in testing mode
        self.client = app.test_client()

    def test_get_items_status_code(self):
        """GET /items should respond with HTTP 200 OK."""
        response = self.client.get('/items')
        self.assertEqual(response.status_code, 200)

    def test_get_items_returns_list(self):
        """GET /items should return a JSON list of items."""
        response = self.client.get('/items')
        data = response.get_json()  # parse JSON response into Python data
        # Verify that the response is a list
        self.assertIsInstance(data, list)
        # There should be at least one item in the list (we added 2 by default)
        self.assertGreaterEqual(len(data), 1)

    def test_items_have_id_and_name(self):
        """Each item in the list should have 'id' and 'name' fields."""
        response = self.client.get('/items')
        data = response.get_json()
        if len(data) > 0:
            item = data[0]
            # Check that item is a dict with the required keys
            self.assertIsInstance(item, dict)
            self.assertIn('id', item)
            self.assertIn('name', item)

    def test_sample_item_present(self):
        """The default 'Sample Item 1' should be part of the response."""
        response = self.client.get('/items')
        data = response.get_json()
        # Collect all item names from the response
        names = [item['name'] for item in data]
        # Check that "Sample Item 1" is in the list of names
        self.assertIn('Sample Item 1', names)

if __name__ == '__main__':
    unittest.main()

Explanation

• We import unittest and the app object from our app.py file.
• In the setUp method, which runs before each test, we set app.testing = True to switch Flask to testing mode (this disables error catching so we can see errors in tests) and then create a test client with app.test_client(). The test client allows us to simulate HTTP requests to our Flask application without running a server separately.
• test_get_items_status_code: This test uses the client to perform a GET request to /items and checks that the status code of the response is 200 (OK).
• test_get_items_returns_list: This test checks that the response JSON is a list and that the list has at least one item. We use response.get_json() to get the JSON data as a Python object (which should be a list in our case).
• test_items_have_id_and_name: This test ensures that each item in the returned list is a dictionary containing at least the keys 'id' and 'name'.
• test_sample_item_present: This test specifically checks that "Sample Item 1" (one of the items we inserted by default) is present in the response. We extract all item names from the response and verify that the expected name is in that list.

To run the tests, make sure the Flask app is not currently running (stop the server if it’s still running), then execute the test file:

python test_app.py

You should see output from the unittest framework, for example:

....
----------------------------------------------------------------------
Ran 4 tests in 0.0?s

OK

Project Structure

By now, you should have the following files in your project directory:

flask_api_project/  
├── app.py               # Flask application with GET /items endpoint  
├── test_app.py          # Test cases for the Flask API  
├── requirements.txt     # (Optional) Python dependencies for the project  
└── items.db             # SQLite database file (created when you first run app.py)  

• app.py – Contains our Flask app and the endpoint logic.
• test_app.py – Contains tests that verify the API’s behavior.
• requirements.txt – Lists project dependencies (in our case, just Flask). This is useful if you want to install the same versions of libraries or share the project with others.
• items.db – SQLite database storing the items. This file is created automatically when you run the app. Initially, it will contain the two sample items we inserted.

You can add other files as needed (for example, a README.md using the content of this tutorial, or a .gitignore file if using Git), but the above are the essentials for our simple API.

Packaging and Next Steps

Now that everything is working, you might want to package your project or upload it to GitHub. To package the project into a zip file, you can simply compress the project folder (flask_api_project) into a .zip archive. For example, on the command line, you could run:

zip -r flask_api_project.zip flask_api_project/

This will create flask_api_project.zip containing your app.py, test_app.py, requirements.txt, and (after running the app at least once) the items.db file. This zip package can be shared or uploaded to GitHub for others to download and run.

Next Steps

This tutorial covered a basic read-only endpoint. From here, you can try to expand the API by adding new endpoints, such as:

• POST /items to add a new item to the database.
• PUT /items/ to update an existing item.
• DELETE /items/ to remove an item.

You could also explore using an ORM like SQLAlchemy for database operations, or a framework like Flask-RESTful or FastAPI for building more complex APIs. But the fundamentals you learned here (routing, database interaction, JSON responses, and testing) will apply to those scenarios as well.

Congratulations! You have built a simple REST API with Flask and SQLite, written tests for it, and packaged the project. This is a solid foundation for building more complex APIs and web applications. Happy coding!