Python ও SQLite: CRUD সহ to-do অ্যাপ বানান (পরীক্ষিত)

প্রায় প্রতিটি অ্যাপ data রাখে, আর শুরু করার জন্য সবচেয়ে সহজ আসল database হলো SQLite। এটি একটিমাত্র ফাইলে থাকে, আলাদা server লাগে না, এবং Python-এর সঙ্গে sqlite3 module আসে। এই প্রজেক্টে একটি to-do তালিকা বানিয়ে CRUD অনুশীলন করবেন: Create (তৈরি), Read (পড়া), Update (বদলানো), Delete (মোছা)। সঙ্গে শিখবেন SQL injection ঠেকানোর একটি অভ্যাস। কোড ও আউটপুট প্রকাশের আগে Python 3-এ চালানো হয়েছে।

আমরা কী বানাচ্ছি

চারটি কমান্ডের command-line to-do তালিকা: add, list (চাইলে শুধু বাকি কাজ), done ও delete। কাজগুলো todo.db ফাইলে জমা হয়, তাই পরেরবারও থাকে।

কোড

todo.py নামে সংরক্ষণ করুন:

#!/usr/bin/env python3
"""A to-do list stored in SQLite: add, list, done and delete tasks."""
import argparse
import sqlite3

DB_FILE = "todo.db"


def connect(path=DB_FILE):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute(
        """CREATE TABLE IF NOT EXISTS tasks (
               id INTEGER PRIMARY KEY AUTOINCREMENT,
               title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
               done INTEGER NOT NULL DEFAULT 0,
               created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
           )"""
    )
    return conn


def add_task(conn, title):
    with conn:
        cur = conn.execute("INSERT INTO tasks (title) VALUES (?)", (title,))
    return cur.lastrowid


def list_tasks(conn, only_open=False):
    sql = "SELECT id, title, done FROM tasks"
    if only_open:
        sql += " WHERE done = 0"
    return conn.execute(sql + " ORDER BY id").fetchall()


def mark_done(conn, task_id):
    with conn:
        cur = conn.execute("UPDATE tasks SET done = 1 WHERE id = ?", (task_id,))
    return cur.rowcount == 1


def delete_task(conn, task_id):
    with conn:
        cur = conn.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
    return cur.rowcount == 1


def main(argv=None):
    parser = argparse.ArgumentParser(description="SQLite to-do list")
    sub = parser.add_subparsers(dest="command", required=True)
    sub.add_parser("add").add_argument("title")
    sub.add_parser("list").add_argument("--open", action="store_true", help="hide finished tasks")
    sub.add_parser("done").add_argument("id", type=int)
    sub.add_parser("delete").add_argument("id", type=int)
    args = parser.parse_args(argv)

    conn = connect()
    if args.command == "add":
        try:
            print(f"Added task {add_task(conn, args.title)}")
        except sqlite3.IntegrityError:
            parser.error("title must be 1 to 200 characters")
    elif args.command == "list":
        for row in list_tasks(conn, args.open):
            print(f'{row["id"]:>3} [{"x" if row["done"] else " "}] {row["title"]}')
    else:
        action = mark_done if args.command == "done" else delete_task
        if not action(conn, args.id):
            parser.exit(1, f"No task with id {args.id}\n")
        print("OK")


if __name__ == "__main__":
    main()

চালিয়ে দেখুন

$ python3 todo.py add "Learn SQL"
Added task 1
$ python3 todo.py add "Write tests"
Added task 2
$ python3 todo.py add "Deploy app"
Added task 3
$ python3 todo.py done 1
OK
$ python3 todo.py list
  1 [x] Learn SQL
  2 [ ] Write tests
  3 [ ] Deploy app
$ python3 todo.py list --open
  2 [ ] Write tests
  3 [ ] Deploy app
$ python3 todo.py delete 3
OK
$ python3 todo.py done 42
No task with id 42
$ python3 todo.py add ""
todo.py: error: title must be 1 to 200 characters

এটি কীভাবে কাজ করে

টেবিল। CREATE TABLE IF NOT EXISTS প্রথমবার টেবিল বানায়, পরে কিছু করে না। id database নিজে তৈরি করে। NOT NULL, DEFAULT ও CHECK নিয়ম রাখে database-এই, তাই অন্য কোড যাচাই ভুলে গেলেও খারাপ data ঢোকে না।

Parameterised query। VALUES (?)-এর পর (title,) দেখুন। মান SQL লেখা থেকে আলাদা করে পাঠানো হয়, তাই database সেটিকে কখনো কোড ধরে না। ব্যবহারকারীর ইনপুট থেকে f-string বা + দিয়ে SQL বানাবেন না। এভাবেই SQL injection হয়। test-এ প্রমাণ আছে: x'); DROP TABLE tasks; -- শিরোনাম নিরীহ লেখা হিসেবে জমা হয়।

Transaction। with conn: ব্লক সফল হলে commit করে, error হলে rollback করে। commit ছাড়া প্রোগ্রাম বন্ধ হলে আপনার insert হারিয়ে যেত।

rowcount। UPDATE বা DELETE-এর পর cur.rowcount বলে কয়টি row বদলেছে। ১ না হলে id ছিল না, আর কমান্ড কাজ হয়েছে ভান না করে ব্যবহারকারীকে জানায়।

row_factory = sqlite3.Row অবস্থানের বদলে নাম ধরে কলাম পড়তে দেয় (row["title"])।

আলাদা function। প্রতিটি database কাজ ছোট function, যা connection প্যারামিটার হিসেবে নেয়। এতেই test করা সহজ হয়।

Test যোগ করুন

Test-এ :memory: ব্যবহার করি, যে database শুধু RAM-এ থাকে এবং পরে মুছে যায়, তাই test দ্রুত চলে এবং আপনার আসল data ছোঁয় না। test_todo.py নামে সংরক্ষণ করুন:

import sqlite3
import unittest

import todo


class TodoTest(unittest.TestCase):
    def setUp(self):
        self.conn = todo.connect(":memory:")

    def test_add_list_done_delete(self):
        first = todo.add_task(self.conn, "Learn SQL")
        todo.add_task(self.conn, "Write tests")
        self.assertTrue(todo.mark_done(self.conn, first))
        self.assertEqual([r["title"] for r in todo.list_tasks(self.conn, only_open=True)], ["Write tests"])
        self.assertTrue(todo.delete_task(self.conn, first))
        self.assertEqual(len(todo.list_tasks(self.conn)), 1)

    def test_missing_id_returns_false(self):
        self.assertFalse(todo.mark_done(self.conn, 999))

    def test_sql_injection_text_is_stored_as_plain_text(self):
        evil = "x'); DROP TABLE tasks; --"
        todo.add_task(self.conn, evil)
        self.assertEqual(todo.list_tasks(self.conn)[0]["title"], evil)

    def test_empty_title_rejected_by_database(self):
        with self.assertRaises(sqlite3.IntegrityError):
            todo.add_task(self.conn, "")


if __name__ == "__main__":
    unittest.main()

$ python3 -m unittest -v
test_add_list_done_delete ... ok
test_empty_title_rejected_by_database ... ok
test_missing_id_returns_false ... ok
test_sql_injection_text_is_stored_as_plain_text ... ok

Ran 4 tests in 0.004s

OK

জানা সীমা

• সবকিছু একটি ফাইলে, নাম কোডে বাঁধা todo.db, বর্তমান folder-এ।
• SQLite একসাথে একজন লেখক মানে। ব্যক্তিগত টুল বা ছোট সাইটে এটা ঠিক, কিন্তু ব্যস্ত বহু-ব্যবহারকারীর সেবায় সাধারণত PostgreSQL বা MySQL লাগে।
• এখনো কাজের শিরোনাম সম্পাদনা বা শেষ তারিখ নেই।
• আসল প্রজেক্টে লঞ্চের পর টেবিল নিরাপদে বদলাতে শুধু CREATE TABLE IF NOT EXISTS নয়, migration ব্যবহার হয়।

চ্যালেঞ্জ

১. শিরোনাম বদলানোর edit কমান্ড যোগ করুন। ২. due কলাম ও list --overdue filter যোগ করুন। ৩. ১ থেকে ৩ CHECK সহ priority কলাম যোগ করে তা দিয়ে সাজান। ৪. WHERE title LIKE ? দিয়ে search কমান্ড যোগ করুন (placeholder রাখবেন)। ৫. এই একই function-এর ওপর ছোট ওয়েব পাতা বানান, যেমন আগের প্রজেক্টের Node বা Python API দিয়ে।

মূল কথা: CRUD মানে চারটি SQL বিবৃতি। প্রতিবার parameterised query ব্যবহার করুন, নিয়ম database-এ রাখুন, লেখা transaction-এ মুড়ুন, এবং database কোড ছোট test-যোগ্য function-এ রাখুন।

---

হাতে-কলমে প্রজেক্ট সিরিজ, পর্ব ১ (ছোট পরীক্ষিত প্রজেক্ট): 4 নম্বর (মোট 5)। আগের লেখা: Downloads folder সাজাতে Python file organiser বানান.