Building a REST API with Python Flask and PostgreSQL

Introduction

In this tutorial, we will guide you through the process of creating a REST API using Python Flask and PostgreSQL. We will connect to a PostgreSQL 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
• PostgreSQL
• Flask

Creating the PostgreSQL Database

1. Create a PostgreSQL database named coffeecms. You can do this using the following command:

psql -U postgres -c "CREATE DATABASE coffeecms"

2. Create a table named users in the coffeecms database:

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

3. Insert some data into the users table:

INSERT INTO users (fullname, email) VALUES
  ('John Doe', 'johndoe@example.com'),
  ('Jane Doe', 'janedoe@example.com'),
  ('Peter Jones', 'peterjones@example.com');

Creating the Flask Application

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

from flask import Flask, jsonify, request
import psycopg2

app = Flask(__name__)

# Connect to the PostgreSQL database
conn = psycopg2.connect(dbname='coffeecms', user='postgres', password='password', host='localhost')
cursor = conn.cursor()

# Define the GetAllUser endpoint
@app.route('/api/GetAllUser')
def get_all_users():
  # Execute the SQL query to get all users
  cursor.execute('SELECT * FROM users')
  users = cursor.fetchall()

  # Convert the PostgreSQL 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 jsonify(json_users)

# Define the UpdateUser endpoint
@app.route('/api/UpdateUser', methods=['PUT'])
def update_user():
  # Get the user_id, fullname, and email from the request body
  user_id = request.json['user_id']
  fullname = request.json['fullname']
  email = request.json['email']

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

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

# Run the Flask application
if __name__ == '__main__':
  app.run(debug=True)

Running the Application

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

python app.py

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

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

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

curl http://localhost:5000/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": 1, "fullname": "John Doe Updated", "email": "johndoeupdated@example.com"}' http://localhost:5000/api/UpdateUser

Conclusion

In this tutorial, we have