Building a REST API with Python FastAPI and DuckDB

Introduction

In this tutorial, we will guide you through the process of creating a REST API using Python FastAPI and DuckDB. We will connect to a DuckDB database named coffeecms and create two endpoints:

• api/GetAllUser: This endpoint will display a list of all the records in the users table.
• api/UpdateUser: This endpoint will update the fullname and email fields in the users table for a given user_id.

Prerequisites

Before you begin, you will need to have the following installed on your system:

• Python 3.6 or later
• DuckDB
• FastAPI

Creating the DuckDB Database

1. Create a directory to store your DuckDB database files.
2. Create a file named coffeecms.duckdb in the directory you created in step 1.
3. Open the coffeecms.duckdb file in a text editor and create the users table:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  fullname VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

4. Insert some data into the users table:

INSERT INTO users (fullname, email) VALUES
  ('John Doe', '[email protected]'),
  ('Jane Doe', '[email protected]'),
  ('Peter Jones', '[email protected]');

Creating the FastAPI Application

1. Create a new directory for your FastAPI application.
2. Create a file named main.py in the directory you created in step 1.
3. Add the following code to the main.py file:

from fastapi import FastAPI
import duckdb

app = FastAPI()

# Connect to the DuckDB database
con = duckdb.connect('coffeecms.duckdb')

# Define the GetAllUser endpoint
@app.get('/api/GetAllUser')
async def get_all_users():
  # Execute the SQL query to get all users
  users = con.query('SELECT * FROM users').fetch('all')

  # Convert the DuckDB results to a JSON format
  json_users = []
  for user in users:
    json_user = {
      'user_id': user[0],
      'fullname': user[1],
      'email': user[2]
    }
    json_users.append(json_user)

  # Return the JSON response
  return json_users

# Define the UpdateUser endpoint
@app.put('/api/UpdateUser')
async def update_user(user: User):
  # Get the user_id, fullname, and email from the request body
  user_id = user.user_id
  fullname = user.fullname
  email = user.email

  # Execute the SQL query to update the user
  con.query(f'UPDATE users SET fullname = "{fullname}", email = "{email}" WHERE user_id = {user_id}')

  # Return a success message
  return {'message': 'User updated successfully'}

# Define the User model
class User:
  user_id: int
  fullname: str
  email: str

# Run the FastAPI application
if __name__ == '__main__':
  import uvicorn
  uvicorn.run(app, host='0.0.0.0', port=8000)

Running the Application

1. Open a terminal window and navigate to the directory where you saved the main.py file.
2. Run the following command to start the FastAPI application:

uvicorn main:app --host='0.0.0.0' --port=8000

3. The application will run on port 8000 by default. You can access the endpoints using the following URLs:

• http://localhost:8000/api/GetAllUser
• http://localhost:8000/api/UpdateUser

To test the api/GetAllUser endpoint, you can use the following cURL command:

curl http://localhost:8000/api/GetAllUser

To test the api/UpdateUser endpoint, you can use the following cURL command:

“`
curl -X PUT -H ‘Content-Type: application/json’ -d ‘{“user_id