SQL SELECT, INSERT, UPDATE, DELETE — Make Your Data Dance (in Flask + SQLite)

You already set up SQLite, normalized your schema, and built Flask routes and templates. Nice. Now it's time to actually move data: fetch it, add it, change it, and remove it. In database purgatory we call this CRUD: Create, Read, Update, Delete — aka INSERT, SELECT, UPDATE, DELETE. This guide gives you the practical SQL syntax, safe usage in Flask with sqlite3, and the same patterns in an ORM style (SQLAlchemy). Expect code, warnings, and a joke or two.

---

Why this matters (and when you’ll cry if you get it wrong)

• Your app will be useless unless users can see (SELECT) and change (INSERT/UPDATE/DELETE) data.
• Forgetting to commit = invisible writes. Missing WHERE = accidental apocalypse (mass-update/delete).
• Normalization gave you separate tables; now you’ll JOIN them or write queries that stitch the pieces back together.

"This is the moment where the concept finally clicks." — when your SELECT returns exactly the rows you need.

---

SQL basics: the four verbs

SELECT — Read the world

What it does: fetch rows and columns.

Basic form:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 DESC
LIMIT 10;

Micro explanation: SELECT picks the columns. FROM picks the table. WHERE filters. ORDER BY/LIMIT control result ordering and size.

Example: Get the 5 most recent posts in a blog:

SELECT id, title, created_at
FROM posts
WHERE published = 1
ORDER BY created_at DESC
LIMIT 5;

Parameterization in Python (sqlite3):

db.execute("SELECT id, title FROM posts WHERE author_id = ?", (author_id,))
rows = db.fetchall()

Why parameterize? Prevents SQL injection and keeps types sane.

---

INSERT — Create new rows

Basic form:

INSERT INTO table_name (col1, col2) VALUES (?, ?);

SQLite in Flask (sqlite3):

cur = db.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    (username, email)
)
db.commit()
user_id = cur.lastrowid

Notes:

• Always call commit() on the connection (unless you use autocommit). Otherwise the write disappears when the connection closes.
• In normalized schemas, you often INSERT into parent table then child table using the parent id.

---

UPDATE — Change existing rows

Basic form:

UPDATE table_name
SET column1 = ?, column2 = ?
WHERE id = ?;

Important warning: Missing WHERE = update every row. This is the fastest way to ruin your data.

Example: Mark a comment as moderated:

UPDATE comments
SET moderated = 1, moderated_by = ?
WHERE id = ?;

Use transactions for multi-step updates across tables. If one update fails, you can rollback.

---

DELETE — Remove rows (or don’t)

Basic form:

DELETE FROM table_name WHERE id = ?;

Soft delete alternative (recommended in many apps):

UPDATE table_name SET deleted = 1 WHERE id = ?;

Why soft delete? Keeps history and avoids surprises when foreign keys reference rows. Denormalization sometimes complicates hard deletes — you might need to clean multiple tables.

Caution: Never run DELETE FROM table_name; unless you really intend to empty the whole table.

---

Quick Flask route examples (sqlite3)

Display rows (SELECT) and render with Jinja:

@app.route('/posts')
def posts():
    cur = get_db().execute('SELECT id, title FROM posts ORDER BY created_at DESC')
    posts = cur.fetchall()
    return render_template('posts.html', posts=posts)

Create a post (INSERT):

@app.route('/posts/new', methods=['POST'])
def create_post():
    title = request.form['title']
    body = request.form['body']
    db = get_db()
    db.execute('INSERT INTO posts (title, body) VALUES (?, ?)', (title, body))
    db.commit()
    return redirect(url_for('posts'))

Update and delete are similar: run parameterized query, commit, and redirect.

---

ORM patterns (SQLAlchemy) — same ideas, different syntax

ORM equivalents are helpful to avoid raw SQL and map tables to classes.

Model example:

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200))
    body = db.Column(db.Text)

CRUD with SQLAlchemy:

# Create
p = Post(title='Hi', body='...')
db.session.add(p)
db.session.commit()

# Read
posts = Post.query.filter_by(published=True).order_by(Post.created_at.desc()).limit(5).all()

# Update
p = Post.query.get(id)
p.title = 'New title'
db.session.commit()

# Delete
p = Post.query.get(id)
db.session.delete(p)
db.session.commit()

Micro explanation: ORM translates these actions into SQL. It's comfortable but you should still understand the SQL it produces (performance matters).

---

Common pitfalls and best practices

• Always parameterize queries (use ? or named params) — prevents SQL injection.
• Commit after INSERT/UPDATE/DELETE when using sqlite3.
• Be careful with WHERE — test updates/deletes with a SELECT first.
• Prefer soft deletes when you need an audit trail or worry about foreign keys.
• Watch out for N+1 query problems when using ORMs; eager load when needed.
• Consider transactions for multi-step changes so failures don’t leave your DB inconsistent.

---

Quick cheat sheet

• SELECT: read (use WHERE, ORDER BY, LIMIT)
• INSERT: create (commit, use lastrowid if you need the new id)
• UPDATE: modify (use WHERE!)
• DELETE: remove (consider soft delete)

"If you're careful with WHERE and commits, SQL won't ruin your day. But missing one of those is a reliable heartbreak."

---

Final practice (do this now)

1. In your Flask app, make a form route that INSERTs a row and redirects to a page that SELECTs and lists rows.
2. Add an edit route that UPDATEs a row. Test what happens when you omit WHERE (don’t actually do that — test with a small local copy).
3. Implement soft delete and then a separate admin view that permanently DELETEs.

---

Key takeaways

• SELECT/INSERT/UPDATE/DELETE are the building blocks of any dynamic app.
• Use parameterized queries and transactions; obey WHERE; commit your writes.
• ORMs give convenience but learn the underlying SQL for performance and safety.

You’ve already built routes and templates. Now make those routes manipulate real data responsibly. Go break nothing — then fix it and learn something.

Tags: beginner, web, python, sqlite