Curious Mints

Setting Up SQLite with Flask

Amit Jotwani -

I wanted to set up SQLite with Flask for a simple, lightweight database solution that doesn't need a separate server process. Perfect for prototyping or smaller-scale applications.

Here's how I did it.

1. Installing SQLite

SQLite usually comes pre-installed with Python, so you likely don't need to install anything extra.

2. Configuring SQLite with Flask

First, we need to configure the SQLite database within our Flask app. We'll create a connection string and set it in the Flask app's configuration.

main.py (or your app's main file)

from flask import Flask
import os

app = Flask(__name__)
db_path = os.path.join(app.root_path, 'mydatabase.db')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_path

Here, 'mydatabase.db' is the name of the database file.

3. Defining Models

Create models that define the tables in your SQLite database using SQLAlchemy.

models.py

from your_app import db

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

4. Creating the Database

You'll need a separate script to create the database file.

create_db.py

from your_app import db

with app.app_context():
    db.create_all()

Run this script to create the database file.

5. Finding the Connection URL

To find the connection URL for the SQLite database:

  • Identify the Database File: Look for the line where you set 'SQLALCHEMY_DATABASE_URI' in your Flask app configuration.
  • Compose the URL: It will follow the pattern sqlite:///<path-to-database-file>.

You can print it using:

print(app.config['SQLALCHEMY_DATABASE_URI'])

6. Interacting with the Database

You can now interact with the database using standard SQLAlchemy operations. Create, query, update, and delete records as needed.

Conclusion

SQLite is a great choice for simple applications where a full-fledged database server is not necessary. With Flask, integrating SQLite is straightforward. You can now manage your data in a light, efficient way.

Remember, the connection URL for the SQLite database is simply pointing to the file on your filesystem. Ensure the path is correct and enjoy working with this convenient database system!