Working with Databases (SQLite, PostgreSQL, MySQL)

Databases are the backbone of web applications, storing and managing data efficiently. In this tutorial, we’ll explore how to use SQLite, PostgreSQL, and MySQL with Flask and Django.

1. Databases in Django

Django comes with built-in ORM (Object-Relational Mapping), which makes it easy to interact with databases. By default, Django uses SQLite, but it also supports PostgreSQL and MySQL.

1.1 Configuring the Database in Django

Using SQLite (Default Database)

Django uses SQLite by default. The configuration in settings.py looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / "db.sqlite3",
    }
}

No extra setup needed. SQLite is great for small projects and local development.

Using PostgreSQL in Django

1.  Install the PostgreSQL adapter for Python:

pip install psycopg2-binary

2. Update settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_database',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': 'localhost',  
        'PORT': '5432',  
    }
}

3. Apply migrations and create a superuser:

python manage.py migrate
python manage.py createsuperuser

PostgreSQL is great for large-scale applications needing advanced features like JSONB and full-text search.

Using MySQL in Django

1.  Install the MySQL adapter for Python:

pip install mysqlclient

2. Update settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'your_database',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': 'localhost',
        'PORT': '3306',
    }
}

3. Apply migrations:

python manage.py migrate

MySQL is best for applications that require speed and reliability, such as e-commerce sites.

2. Databases in Flask

Flask is more flexible than Django but requires additional setup for databases.

Using SQLite in Flask

1.  Install SQLite support:

pip install flask_sqlalchemy

2. Configure the database in app.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

3. Define a model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)

4. Create the database:

python
>>> from app import db
>>> db.create_all()

SQLite is great for small apps or local development.

Using PostgreSQL in Flask

1.  Install PostgreSQL adapter:

pip install psycopg2-binary

2. Configure app.py:

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://your_user:your_password@localhost/your_database'

3.  Apply the same steps as SQLite to create the database.

PostgreSQL is useful for large applications needing scalability and complex queries.

Using MySQL in Flask

1.  Install MySQL adapter:

pip install pymysql

2. Configure app.py:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://your_user:your_password@localhost/your_database'

3.  Apply the same steps as above.

MySQL is ideal for performance-driven applications.

3. Django vs Flask Database Handling

Django's ORM is easier for beginners. 
Flask with SQLAlchemy gives more control over queries.

Conclusion

We explored how to use SQLite, PostgreSQL, and MySQL in both Django and Flask.

• SQLite is great for small projects.
• PostgreSQL is best for large-scale applications.
• MySQL is ideal for high-performance applications.