Bu darsda SQLite3 ishlatgan holda to'liq Todo App loyihasini yaratamiz. Bu loyiha barcha o'rganilgan SQLite3 texnikalarini qo'llaydi.
todo_app/
├── app.py # Asosiy Flask app
├── database.py # Ma'lumotlar bazasi funksiyalari
├── models.py # Data modellar
├── templates/ # HTML template-lar
│ ├── base.html
│ ├── index.html
│ ├── add_task.html
│ └── edit_task.html
├── static/ # CSS va JS fayllar
│ ├── style.css
│ └── script.js
├── requirements.txt # Dependencies
└── todo.db # SQLite ma'lumotlar bazasi# database.py
import sqlite3
from datetime import datetime
class TodoDatabase:
def __init__(self, db_path='todo.db'):
self.db_path = db_path
self.init_database()
def init_database(self):
"""Ma'lumotlar bazasini ishga tushirish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
# Tasks jadvali
cursor.execute('''
CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT,
priority TEXT DEFAULT 'medium',
status TEXT DEFAULT 'pending',
due_date DATE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
# Categories jadvali
cursor.execute('''
CREATE TABLE IF NOT EXISTS categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
color TEXT DEFAULT '#007bff',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
# Task categories jadvali
cursor.execute('''
CREATE TABLE IF NOT EXISTS task_categories (
task_id INTEGER,
category_id INTEGER,
PRIMARY KEY (task_id, category_id),
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
)
''')
# Foreign Key yoqish
cursor.execute('PRAGMA foreign_keys = ON')
# Default categories qo'shish
cursor.execute('''
INSERT OR IGNORE INTO categories (name, color) VALUES
('Work', '#28a745'),
('Personal', '#007bff'),
('Shopping', '#ffc107'),
('Health', '#dc3545')
''')
conn.commit()
def create_task(self, title, description=None, priority='medium', due_date=None, category_ids=None):
"""Yangi task yaratish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO tasks (title, description, priority, due_date)
VALUES (?, ?, ?, ?)
''', (title, description, priority, due_date))
task_id = cursor.lastrowid
# Categories qo'shish
if category_ids:
for category_id in category_ids:
cursor.execute('''
INSERT INTO task_categories (task_id, category_id)
VALUES (?, ?)
''', (task_id, category_id))
conn.commit()
return task_id
def get_all_tasks(self, status=None, priority=None, category_id=None):
"""Barcha tasklarni olish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
query = '''
SELECT t.id, t.title, t.description, t.priority, t.status,
t.due_date, t.created_at, t.updated_at,
GROUP_CONCAT(c.name) as categories
FROM tasks t
LEFT JOIN task_categories tc ON t.id = tc.task_id
LEFT JOIN categories c ON tc.category_id = c.id
WHERE 1=1
'''
params = []
if status:
query += ' AND t.status = ?'
params.append(status)
if priority:
query += ' AND t.priority = ?'
params.append(priority)
if category_id:
query += ' AND t.id IN (SELECT task_id FROM task_categories WHERE category_id = ?)'
params.append(category_id)
query += ' GROUP BY t.id ORDER BY t.created_at DESC'
cursor.execute(query, params)
return cursor.fetchall()
def get_task_by_id(self, task_id):
"""Task ma'lumotlarini olish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
cursor.execute('''
SELECT t.id, t.title, t.description, t.priority, t.status,
t.due_date, t.created_at, t.updated_at
FROM tasks t
WHERE t.id = ?
''', (task_id,))
task = cursor.fetchone()
if task:
# Categories olish
cursor.execute('''
SELECT c.id, c.name, c.color
FROM categories c
INNER JOIN task_categories tc ON c.id = tc.category_id
WHERE tc.task_id = ?
''', (task_id,))
categories = cursor.fetchall()
return task, categories
return None, []
def update_task(self, task_id, title=None, description=None, priority=None,
status=None, due_date=None, category_ids=None):
"""Task ma'lumotlarini yangilash"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
# Yangilanish maydonlarini aniqlash
updates = []
params = []
if title is not None:
updates.append('title = ?')
params.append(title)
if description is not None:
updates.append('description = ?')
params.append(description)
if priority is not None:
updates.append('priority = ?')
params.append(priority)
if status is not None:
updates.append('status = ?')
params.append(status)
if due_date is not None:
updates.append('due_date = ?')
params.append(due_date)
if updates:
updates.append('updated_at = CURRENT_TIMESTAMP')
params.append(task_id)
query = f"UPDATE tasks SET {', '.join(updates)} WHERE id = ?"
cursor.execute(query, params)
# Categories yangilash
if category_ids is not None:
# Eski categories o'chirish
cursor.execute('DELETE FROM task_categories WHERE task_id = ?', (task_id,))
# Yangi categories qo'shish
for category_id in category_ids:
cursor.execute('''
INSERT INTO task_categories (task_id, category_id)
VALUES (?, ?)
''', (task_id, category_id))
conn.commit()
return cursor.rowcount > 0
def delete_task(self, task_id):
"""Task o'chirish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
cursor.execute('DELETE FROM tasks WHERE id = ?', (task_id,))
conn.commit()
return cursor.rowcount > 0
def get_categories(self):
"""Barcha categories olish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM categories ORDER BY name')
return cursor.fetchall()
def create_category(self, name, color):
"""Yangi category yaratish"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO categories (name, color) VALUES (?, ?)
''', (name, color))
conn.commit()
return cursor.lastrowid
def get_task_statistics(self):
"""Task statistikasi"""
with sqlite3.connect(self.db_path) as conn:
cursor = conn.cursor()
# Umumiy statistika
cursor.execute('''
SELECT
COUNT(*) as total_tasks,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_tasks,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_tasks,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_tasks
FROM tasks
''')
stats = cursor.fetchone()
# Priority bo'yicha statistika
cursor.execute('''
SELECT priority, COUNT(*) as count
FROM tasks
GROUP BY priority
''')
priority_stats = cursor.fetchall()
# Category bo'yicha statistika
cursor.execute('''
SELECT c.name, COUNT(tc.task_id) as count
FROM categories c
LEFT JOIN task_categories tc ON c.id = tc.category_id
GROUP BY c.id, c.name
ORDER BY count DESC
''')
category_stats = cursor.fetchall()
return {
'total': stats[0],
'pending': stats[1],
'completed': stats[2],
'cancelled': stats[3],
'priority_stats': priority_stats,
'category_stats': category_stats
}# app.py
from flask import Flask, render_template, request, redirect, url_for, flash, jsonify
from database import TodoDatabase
from datetime import datetime
app = Flask(__name__)
app.secret_key = 'your-secret-key-here'
# Database instance
db = TodoDatabase()
@app.route('/')
def index():
"""Asosiy sahifa"""
# Filter parametrlari
status = request.args.get('status')
priority = request.args.get('priority')
category_id = request.args.get('category_id')
# Tasklarni olish
tasks = db.get_all_tasks(status=status, priority=priority, category_id=category_id)
# Categories olish
categories = db.get_categories()
# Statistika
stats = db.get_task_statistics()
return render_template('index.html',
tasks=tasks,
categories=categories,
stats=stats,
current_filters={
'status': status,
'priority': priority,
'category_id': category_id
})
@app.route('/add', methods=['GET', 'POST'])
def add_task():
"""Yangi task qo'shish"""
if request.method == 'POST':
title = request.form['title']
description = request.form.get('description', '')
priority = request.form.get('priority', 'medium')
due_date = request.form.get('due_date')
category_ids = request.form.getlist('categories')
# Category IDs ni integer ga o'tkazish
category_ids = [int(cid) for cid in category_ids if cid]
# Task yaratish
task_id = db.create_task(
title=title,
description=description,
priority=priority,
due_date=due_date,
category_ids=category_ids
)
flash('Task muvaffaqiyatli yaratildi!', 'success')
return redirect(url_for('index'))
# Categories olish
categories = db.get_categories()
return render_template('add_task.html', categories=categories)
@app.route('/edit/', methods=['GET', 'POST'])
def edit_task(task_id):
"""Task tahrirlash"""
if request.method == 'POST':
title = request.form['title']
description = request.form.get('description', '')
priority = request.form.get('priority', 'medium')
status = request.form.get('status', 'pending')
due_date = request.form.get('due_date')
category_ids = request.form.getlist('categories')
# Category IDs ni integer ga o'tkazish
category_ids = [int(cid) for cid in category_ids if cid]
# Task yangilash
success = db.update_task(
task_id=task_id,
title=title,
description=description,
priority=priority,
status=status,
due_date=due_date,
category_ids=category_ids
)
if success:
flash('Task muvaffaqiyatli yangilandi!', 'success')
else:
flash('Task yangilanmadi!', 'error')
return redirect(url_for('index'))
# Task ma'lumotlarini olish
task_data = db.get_task_by_id(task_id)
if not task_data:
flash('Task topilmadi!', 'error')
return redirect(url_for('index'))
task, task_categories = task_data
# Categories olish
categories = db.get_categories()
return render_template('edit_task.html',
task=task,
categories=categories,
task_categories=task_categories)
@app.route('/delete/')
def delete_task(task_id):
"""Task o'chirish"""
success = db.delete_task(task_id)
if success:
flash('Task muvaffaqiyatli o''chirildi!', 'success')
else:
flash('Task o''chirilmadi!', 'error')
return redirect(url_for('index'))
@app.route('/complete/')
def complete_task(task_id):
"""Task ni tugatish"""
success = db.update_task(task_id, status='completed')
if success:
flash('Task tugatildi!', 'success')
else:
flash('Task tugatilmadi!', 'error')
return redirect(url_for('index'))
@app.route('/api/tasks')
def api_tasks():
"""API - Tasklar"""
status = request.args.get('status')
priority = request.args.get('priority')
category_id = request.args.get('category_id')
tasks = db.get_all_tasks(status=status, priority=priority, category_id=category_id)
# JSON formatga o'tkazish
tasks_data = []
for task in tasks:
tasks_data.append({
'id': task[0],
'title': task[1],
'description': task[2],
'priority': task[3],
'status': task[4],
'due_date': task[5],
'created_at': task[6],
'updated_at': task[7],
'categories': task[8] if task[8] else ''
})
return jsonify(tasks_data)
@app.route('/api/statistics')
def api_statistics():
"""API - Statistika"""
stats = db.get_task_statistics()
return jsonify(stats)
if __name__ == '__main__':
app.run(debug=True)
{% block title %}Todo App{% endblock %}
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
{{ message }}
{% endfor %}
{% endif %}
{% endwith %}
{% block content %}{% endblock %}
{% block scripts %}{% endblock %}
{% extends "base.html" %}
{% block title %}Todo App - Bosh sahifa{% endblock %}
{% block content %}
Mening tasklarim
Yangi task
{% if tasks %}
{% for task in tasks %}
{% endfor %}
{% else %}
{% endif %}
Statistika
{{ stats.total }}
Jami
{{ stats.pending }}
Kutilmoqda
{{ stats.completed }}
Tugatilgan
Kategoriyalar
{% for category in categories %}
{{ category[1] }}
{{ category[3] if category[3] else 0 }} task
{% endfor %}
{% endblock %}# requirements.txt
Flask==2.3.3
Werkzeug==2.3.7
# Loyihani ishga tushirish
pip install -r requirements.txt
python app.py
# Brauzerda ochish
# http://localhost:5000