Flask Database Integration (SQLite, MySQL, PostgreSQL)

1. Setting Up Flask and SQLAlchemy

Step 1: Install Dependencies

First, install Flask and SQLAlchemy using pip:

pip install flask flask-sqlalchemy pymysql psycopg2

flask-sqlalchemy: Provides ORM support.
pymysql: Required for MySQL.
psycopg2: Required for PostgreSQL.

2. Configuring the Database

Step 2: Set Up Flask App and Database Connection

Create a file app.py and configure the database:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# Uncomment the database you want to use:

# SQLite (lightweight, for local development)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///mydatabase.db"

# MySQL (use your MySQL username, password, and database name)
# app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:password@localhost/mydatabase"

# PostgreSQL (use your PostgreSQL username, password, and database name)
# app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://user:password@localhost/mydatabase"

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False  # To suppress warning

db = SQLAlchemy(app)

3. Creating a Database Model

Step 3: Define a User Model

Create a User model representing a database table:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(150), unique=True, nullable=False)

    def __repr__(self):
        return f"<User {self.name}>"

Run the following command in Python to create the database and tables:

from app import db
db.create_all()
print("Database Created!")

4. Performing CRUD Operations

Step 4: Insert Data into the Database

Modify app.py to add a route for inserting user data:

@app.route("/add_user")
def add_user():
    new_user = User(name="John Doe", email="john@example.com")
    db.session.add(new_user)
    db.session.commit()
    return "User added successfully!"

Step 5: Retrieve Data from the Database

@app.route("/users")
def get_users():
    users = User.query.all()
    return {user.id: {"name": user.name, "email": user.email} for user in users}

Step 6: Update a User Record

@app.route("/update_user/<int:id>")
def update_user(id):
    user = User.query.get(id)
    if user:
        user.name = "Updated Name"
        db.session.commit()
        return "User updated successfully!"
    return "User not found!"

Step 7: Delete a User Record

@app.route("/delete_user/<int:id>")
def delete_user(id):
    user = User.query.get(id)
    if user:
        db.session.delete(user)
        db.session.commit()
        return "User deleted successfully!"
    return "User not found!"

5. Running the Flask App

Step 8: Start the Flask Server

Run the Flask app:

python app.py

Visit http://127.0.0.1:5000/add_user to add a user.
Visit http://127.0.0.1:5000/users to retrieve user data.
Visit http://127.0.0.1:5000/update_user/1 to update a user.
Visit http://127.0.0.1:5000/delete_user/1 to delete a user.

Conclusion

In this guide, you learned how to:

✅ Set up a Flask app with SQLAlchemy
✅ Connect Flask to SQLite, MySQL, and PostgreSQL
✅ Create and manage database tables
✅ Perform CRUD (Create, Read, Update, Delete) operations

Now, you can integrate a database into your Flask applications efficiently! 🚀

PYTHON FLASK