Flask and PostgreSQL Connection Examples

Connecting Flask to a PostgreSQL Database

Flask is a popular micro web framework for Python that makes it easy to build web applications. When developing applications, it is often necessary to connect to a database for data storage and retrieval. PostgreSQL is a powerful, open-source relational database that works seamlessly with Flask. In this article, we will present three diverse examples of connecting Flask to a PostgreSQL database.

Example 1: Basic Connection with Flask and PostgreSQL

In this example, we will create a simple Flask application that connects to a PostgreSQL database. This is an essential first step for any Flask application that requires database interactions.

To begin, ensure that you have the necessary packages installed:

pip install Flask psycopg2

Next, create a file named app.py with the following code:

from flask import Flask
import psycopg2

app = Flask(__name__)

## Database connection parameters
DATABASE = "your_database"
USER = "your_username"
PASSWORD = "your_password"
HOST = "localhost"
PORT = "5432"

## Create a connection to the PostgreSQL database
try:
    connection = psycopg2.connect(
        database=DATABASE,
        user=USER,
        password=PASSWORD,
        host=HOST,
        port=PORT
    )
    print("Database connection successful")
except Exception as e:
    print(f"Error connecting to database: {e}")

@app.route('/')
def index():
    return "Hello, Flask!"

if __name__ == '__main__':
    app.run(debug=True)

This code establishes a connection to the PostgreSQL database using the psycopg2 library. Adjust the database parameters as needed. Running this Flask application will print a success message on a successful connection.

Example 2: Querying Data from a PostgreSQL Database

Now that we have established a basic connection, let’s create a more complex example where we query data from the database. This example will retrieve user information from a hypothetical users table.

Ensure that the users table is created in your PostgreSQL database:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

Update the app.py file as follows:

from flask import Flask, jsonify
import psycopg2

app = Flask(__name__)

DATABASE = "your_database"
USER = "your_username"
PASSWORD = "your_password"
HOST = "localhost"
PORT = "5432"

@app.route('/users')
def get_users():
    try:
        connection = psycopg2.connect(
            database=DATABASE,
            user=USER,
            password=PASSWORD,
            host=HOST,
            port=PORT
        )
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM users;")
        rows = cursor.fetchall()
        users = []
        for row in rows:
            users.append({"id": row[0], "username": row[1], "email": row[2]})
        return jsonify(users)
    except Exception as e:
        return jsonify({"error": str(e)})
    finally:
        cursor.close()
        connection.close()

if __name__ == '__main__':
    app.run(debug=True)

In this example, we added a new route /users that queries the users table and returns the data as a JSON response. Ensure that the database connection is properly closed after executing the query to prevent any resource leaks.

Example 3: Inserting Data into a PostgreSQL Database

In this final example, we will create a route that allows us to insert new user data into the users table. This functionality is essential for applications that allow user registrations.

Update the app.py file:

from flask import Flask, request, jsonify
import psycopg2

app = Flask(__name__)

DATABASE = "your_database"
USER = "your_username"
PASSWORD = "your_password"
HOST = "localhost"
PORT = "5432"

@app.route('/add_user', methods=['POST'])
def add_user():
    username = request.json.get('username')
    email = request.json.get('email')
    try:
        connection = psycopg2.connect(
            database=DATABASE,
            user=USER,
            password=PASSWORD,
            host=HOST,
            port=PORT
        )
        cursor = connection.cursor()
        cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s) RETURNING id;", (username, email))
        user_id = cursor.fetchone()[0]
        connection.commit()
        return jsonify({"id": user_id, "username": username, "email": email}), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 400
    finally:
        cursor.close()
        connection.close()

if __name__ == '__main__':
    app.run(debug=True)

In this example, we introduced a new route /add_user that accepts POST requests to insert new users into the database. The route expects a JSON payload with username and email. The RETURNING id clause allows us to retrieve the newly created user’s ID after insertion. Again, ensure that the connection and cursor are properly managed to avoid leaks.

Conclusion

These examples demonstrate various ways of connecting Flask to a PostgreSQL database, from establishing a connection to querying and inserting data. By leveraging the psycopg2 library, you can create robust applications that interact with PostgreSQL efficiently.