🐍 آموزش جامع Python — پست ۱۸۶
تغییر داده‌ها در SQLite با UPDATE
تا اینجا یاد گرفتیم:

INSERT → اضافه کردن
SELECT → خواندن
WHERE  → فیلتر کردن

حالا می‌خواهیم اطلاعات موجود در جدول را تغییر دهیم.
برای این کار از:

UPDATE

استفاده می‌کنیم.
🔹 تغییر یک مقدار
فرض کنیم می‌خواهیم نمره Ali را به 20 تغییر دهیم:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    """
    UPDATE students
    SET score = ?
    WHERE name = ?
    """,
    (20, "Ali")
)

connection.commit()
connection.close()

اینجا:

SET   → مشخص می‌کند چه مقداری تغییر کند
WHERE → مشخص می‌کند کدام رکورد تغییر کند

🔹 چرا WHERE مهم است؟
این کد:

UPDATE students
SET score = 20

می‌تواند نمره تمام دانش‌آموزان را به 20 تغییر دهد.
اما:

UPDATE students
SET score = 20
WHERE name = 'Ali'

فقط رکوردهای مطابق شرط را تغییر می‌دهد.
پس هنگام استفاده از UPDATE باید به WHERE دقت کنیم.
🔹 تغییر چند ستون
می‌توانیم هم‌زمان چند مقدار را تغییر دهیم:

cursor.execute(
    """
    UPDATE students
    SET name = ?, score = ?
    WHERE id = ?
    """,
    ("Ali Ahmadi", 19.5, 1)
)

connection.commit()

در اینجا name و score رکوردی که id آن 1 است تغییر می‌کنند.
🔹 تعداد رکوردهای تغییرکرده
بعد از UPDATE می‌توانیم بررسی کنیم چند رکورد تغییر کرده است:

print(cursor.rowcount)

مثلاً:

1

یعنی یک رکورد تحت تأثیر قرار گرفته است.
⚠️ نکته مهم
مقدارها را مستقیم داخل SQL قرار نده.
❌ نامناسب:

cursor.execute(
    f"UPDATE students SET score = {score} WHERE name = '{name}'"
)

✅ بهتر:

cursor.execute(
    "UPDATE students SET score = ? WHERE name = ?",
    (score, name)
)

این روش Parameterized Query است.
🧠 خلاصه

UPDATE → تغییر داده
SET    → مقدار جدید
WHERE  → تعیین رکوردهای موردنظر

الگوی اصلی:

cursor.execute(
    """
    UPDATE table_name
    SET column = ?
    WHERE condition = ?
    """,
    (new_value, condition_value)
)

🎯 تمرین
جدول students را در نظر بگیر.
می‌خواهیم نمره دانش‌آموزی با نام Sara را از هر مقداری که هست به 20 تغییر دهیم.
کد UPDATE را بنویس و در پایان:

connection.commit()

را اجرا کن.
پست ۱۸۶ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۸۷
حذف رکوردها در SQLite با DELETE
در پست قبل با UPDATE یاد گرفتیم چطور اطلاعات یک رکورد را تغییر دهیم.
حالا می‌خواهیم یاد بگیریم چطور یک یا چند رکورد را از جدول حذف کنیم.
برای این کار از دستور:

DELETE

استفاده می‌کنیم.
🔹 حذف یک رکورد
فرض کنیم جدول students داریم:

id | name  | score
1  | Ali   | 18
2  | Sara  | 15
3  | Reza  | 20

می‌توانیم رکورد مربوط به Sara را حذف کنیم:

cursor.execute(
    "DELETE FROM students WHERE name = ?",
    ("Sara",)
)

connection.commit()

بعد از اجرا:

id | name | score
1  | Ali  | 18
3  | Reza | 20

⚠️ WHERE بسیار مهم است
اگر بنویسیم:

DELETE FROM students;

هیچ WHEREای وجود ندارد.
در نتیجه تمام رکوردهای جدول حذف می‌شوند.
پس هنگام حذف رکورد، معمولاً باید مشخص کنیم دقیقاً کدام رکورد یا رکوردها باید حذف شوند:

DELETE FROM students
WHERE id = ?;

🔹 حذف بر اساس id
بهتر است وقتی شناسه یکتا داریم، حذف را بر اساس id انجام دهیم:

student_id = 3

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (student_id,)
)

connection.commit()

🔹 بررسی تعداد رکوردهای حذف‌شده
می‌توانیم از rowcount استفاده کنیم:

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (3,)
)

connection.commit()

print(cursor.rowcount)

اگر رکورد وجود داشته باشد:

1

و اگر رکوردی با این شرط پیدا نشود:

0

🔹 حذف چند رکورد
DELETE می‌تواند چند رکورد را هم حذف کند.
مثلاً تمام دانش‌آموزانی که نمره کمتر از 10 دارند:

cursor.execute(
    "DELETE FROM students WHERE score < ?",
    (10,)
)

connection.commit()

تمام رکوردهایی که شرط را داشته باشند حذف می‌شوند.
⚠️ از f-string برای SQL استفاده نکن
این روش مناسب نیست:

name = input("Name: ")

cursor.execute(
    f"DELETE FROM students WHERE name = '{name}'"
)

به‌جای آن از Parameterized Query استفاده کن:

cursor.execute(
    "DELETE FROM students WHERE name = ?",
    (name,)
)

این روش امن‌تر و استانداردتر است.
🧠 خلاصه

DELETE FROM table
WHERE condition;

مثلاً:

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (5,)
)

نکات مهم:

DELETE        → حذف رکورد
WHERE         → مشخص کردن رکوردهای موردنظر
rowcount      → تعداد رکوردهای حذف‌شده
commit()      → ذخیره تغییرات
?             → پارامتر امن برای مقدار

🎯 تمرین
کد زیر را کامل کن تا دانش‌آموزی با id = 2 حذف شود:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    "DELETE FROM students WHERE ______ = ?",
    (2,)
)

connection.commit()

print(cursor.rowcount)

connection.close()

پست ۱۸۷ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۸۸
تراکنش‌ها در SQLite | commit() و rollback()
در پست‌های قبل با INSERT، UPDATE و DELETE آشنا شدیم.
حالا باید یاد بگیریم اگر چند تغییر را با هم انجام دادیم و یکی از آن‌ها با خطا مواجه شد، چطور تغییرات را لغو کنیم.
برای این کار از مفهوم Transaction استفاده می‌کنیم.
🔹 Transaction چیست؟
Transaction یعنی چند عملیات مرتبط را به‌عنوان یک واحد در نظر بگیریم.
مثلاً:

cursor.execute(
    "UPDATE students SET score = ? WHERE id = ?",
    (19, 1)
)

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (3,)
)

connection.commit()

تا قبل از commit()، تغییرات هنوز به‌صورت نهایی ثبت نشده‌اند.
با:

connection.commit()

تغییرات تأیید و ذخیره می‌شوند.
🔹 اگر خطایی رخ دهد چه کنیم؟
می‌توانیم از rollback() استفاده کنیم:

try:
    cursor.execute(
        "UPDATE students SET score = ? WHERE id = ?",
        (19, 1)
    )

    cursor.execute(
        "DELETE FROM students WHERE id = ?",
        (3,)
    )

    connection.commit()

except sqlite3.Error:
    connection.rollback()

اگر یکی از عملیات‌ها با خطا مواجه شود:

UPDATE
  ↓
DELETE
  ↓
Error
  ↓
rollback()

تغییرات این Transaction لغو می‌شوند.
🔹 تفاوت commit() و rollback()

commit()   → تأیید و ذخیره تغییرات
rollback() → لغو تغییرات Transaction

مثلاً:

connection.commit()

یعنی:

تغییرات را نهایی کن.

اما:

connection.rollback()

یعنی:

تغییرات انجام‌شده در Transaction را برگردان.

🔥 مثال کامل

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

try:
    cursor.execute(
        "UPDATE students SET score = ? WHERE id = ?",
        (20, 1)
    )

    cursor.execute(
        "DELETE FROM students WHERE id = ?",
        (3,)
    )

    connection.commit()
    print("Transaction completed")

except sqlite3.Error as error:
    connection.rollback()
    print("Transaction failed:", error)

finally:
    connection.close()

در این ساختار:

try
 ↓
اجرای عملیات
 ↓
commit()
 ↓
موفقیت

و در صورت خطا:

Exception
 ↓
rollback()
 ↓
لغو تغییرات

⚠️ نکته مهم
rollback() فقط تغییراتی را برمی‌گرداند که هنوز commit() نشده‌اند.
بعد از:

connection.commit()

دیگر نمی‌توانیم همان تغییرات را با rollback() به حالت قبل برگردانیم.
🔹 چه زمانی Transaction مهم است؟
وقتی چند عملیات به هم وابسته‌اند.
مثلاً در یک سیستم بانکی فرضی:

کم شدن موجودی حساب A
        +
اضافه شدن موجودی حساب B

اگر عملیات اول انجام شود ولی عملیات دوم شکست بخورد، نمی‌خواهیم فقط بخشی از عملیات ثبت شود.
در چنین شرایطی Transaction اهمیت زیادی دارد.
🧠 خلاصه

Transaction
    ↓
چند عملیات مرتبط
    ↓
موفقیت → commit()
خطا    → rollback()

الگوی رایج:

try:
    # database operations

    connection.commit()

except sqlite3.Error:
    connection.rollback()

🎯 تمرین
کد زیر را کامل کن تا اگر هر یک از دو عملیات با خطا مواجه شد، تغییرات لغو شوند:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

try:
    cursor.execute(
        "UPDATE students SET score = ? WHERE id = ?",
        (18, 1)
    )

    cursor.execute(
        "DELETE FROM students WHERE id = ?",
        (2,)
    )

    # تغییرات را نهایی کن

except sqlite3.Error:
    # تغییرات را لغو کن

finally:
    connection.close()

باید جای خالی را با:

connection.commit()

و:

connection.rollback()

تکمیل کنی.
پست ۱۸۸ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۸۹
کار با NULL در SQLite | IS NULL و IS NOT NULL
در SQLite ممکن است بعضی ستون‌ها مقدار نداشته باشند.
برای نمایش نبودن مقدار از:

NULL

استفاده می‌شود.
⚠️ NULL با 0، رشته خالی "" یا False یکی نیست.
🔹 ایجاد رکورد با NULL
فرض کنیم جدول students این ستون‌ها را دارد:

id | name | score

می‌توانیم دانش‌آموزی بدون نمره ثبت کنیم:

cursor.execute(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    ("Ali", None)
)

connection.commit()

در Python مقدار:

None

به NULL در SQLite تبدیل می‌شود.
🔹 بررسی NULL با IS NULL
برای پیدا کردن رکوردهایی که مقدار score ندارند:

cursor.execute(
    "SELECT * FROM students WHERE score IS NULL"
)

students = cursor.fetchall()

for student in students:
    print(student)

اینجا از:

IS NULL

استفاده می‌کنیم.
⚠️ = NULL اشتباه است
این روش درست نیست:

WHERE score = NULL

برای بررسی NULL باید بنویسیم:

WHERE score IS NULL

دلیلش این است که NULL به معنی نامشخص / نبود مقدار است و با مقایسه معمولی = بررسی نمی‌شود.
🔹 پیدا کردن مقدارهای غیر NULL
اگر بخواهیم فقط رکوردهایی را پیدا کنیم که نمره دارند:

cursor.execute(
    "SELECT * FROM students WHERE score IS NOT NULL"
)

students = cursor.fetchall()

یعنی:

IS NULL     → مقدار وجود ندارد
IS NOT NULL → مقدار وجود دارد

🔹 تغییر مقدار NULL
می‌توانیم مقدار NULL را هم با UPDATE تغییر دهیم.
مثلاً:

cursor.execute(
    "UPDATE students SET score = ? WHERE score IS NULL",
    (10,)
)

connection.commit()

تمام دانش‌آموزانی که نمره نداشتند، نمره 10 می‌گیرند.
🔹 حذف رکوردهای دارای NULL
می‌توانیم از DELETE هم استفاده کنیم:

cursor.execute(
    "DELETE FROM students WHERE score IS NULL"
)

connection.commit()

در اینجا تمام رکوردهایی که score آن‌ها NULL است حذف می‌شوند.
🧠 None در Python و NULL در SQLite
وقتی با sqlite3 کار می‌کنیم:

Python          SQLite

None      →     NULL

مثلاً:

value = None

cursor.execute(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    ("Sara", value)
)

در SQLite مقدار score برابر NULL خواهد بود.
⚠️ نکته مهم
این مقادیر با NULL متفاوت‌اند:

NULL
0
""
False

مثلاً:

score = 0

یعنی مقدار نمره واقعاً 0 است.
اما:

score = None

یعنی نمره‌ای ثبت نشده است.
🧠 خلاصه

None          → NULL
IS NULL       → پیدا کردن مقدارهای NULL
IS NOT NULL   → پیدا کردن مقدارهای غیر NULL

و مهم‌تر از همه:

WHERE column IS NULL

نه:

WHERE column = NULL

🎯 تمرین
کد زیر را کامل کن تا فقط دانش‌آموزانی که نمره ندارند نمایش داده شوند:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM students WHERE score ______ NULL"
)

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

کلمه مناسب را جایگزین کن:

IS

پست ۱۸۹ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۰
جست‌وجوی متنی در SQLite با LIKE
در پست قبل با NULL و IS NULL آشنا شدیم.
حالا می‌خواهیم یاد بگیریم چطور در SQLite به دنبال بخشی از یک متن بگردیم.
برای این کار از:

LIKE

استفاده می‌کنیم.
🔹 جست‌وجوی متن دقیق
اگر بخواهیم نام دقیق Ali را پیدا کنیم، می‌توانیم از = استفاده کنیم:

cursor.execute(
    "SELECT * FROM students WHERE name = ?",
    ("Ali",)
)

اما اگر فقط بخشی از نام را بدانیم، LIKE کاربرد دارد.
🔹 % در LIKE
علامت % یعنی هر تعداد کاراکتر.
مثلاً:

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("%Ali%",)
)

یعنی هر نامی که عبارت Ali را در هر جای خود داشته باشد.
مثلاً:

Ali
Alireza
Mohali

می‌توانند با این الگو مطابقت داشته باشند.
🔹 شروع شدن با یک متن
اگر بخواهیم نام‌هایی را پیدا کنیم که با Ali شروع می‌شوند:

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("Ali%",)
)

مثلاً:

Ali
Alireza
AliReza

🔹 پایان یافتن با یک متن
اگر بخواهیم نام‌هایی را پیدا کنیم که به Ali ختم می‌شوند:

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("%Ali",)
)

در این حالت متن موردنظر باید در انتهای مقدار باشد.
🔹 _ یعنی یک کاراکتر
در الگوی LIKE، علامت _ به معنی دقیقاً یک کاراکتر است.
مثلاً:

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("A_i",)
)

می‌تواند با نامی مثل:

Ali
Ari
Ami

مطابقت داشته باشد.
چون _ فقط یک کاراکتر را جایگزین می‌کند.
🔥 مثال کامل
فرض کنیم جدول شامل این داده‌ها باشد:

Ali
Alireza
Sara
Reza

حالا:

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("Ali%",)
)

students = cursor.fetchall()

for student in students:
    print(student)

رکوردهایی که با Ali شروع شوند پیدا می‌شوند.
⚠️ نکته مهم: Parameterized Query
مثل همیشه بهتر است مقدار جست‌وجو را با ? به Query بدهیم:

name = input("Search name: ")

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    (f"%{name}%",)
)

نه اینکه مقدار کاربر را مستقیماً داخل SQL قرار دهیم.
🧠 الگوهای مهم LIKE

%Ali%   → Ali در هر جای متن
Ali%    → متن با Ali شروع شود
%Ali    → متن با Ali تمام شود
A_i     → بین A و i دقیقاً یک کاراکتر

مثلاً:

LIKE "%python%"

یعنی متن python می‌تواند در هر جای مقدار قرار داشته باشد.
🎯 تمرین
کد زیر را کامل کن تا تمام دانش‌آموزانی که نامشان با A شروع می‌شود نمایش داده شوند:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM students WHERE name LIKE ?",
    ("____",)
)

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

الگوی مناسب را بنویس تا نام‌هایی که با A شروع می‌شوند پیدا شوند.
پست ۱۹۰ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۱
جست‌وجوی چند مقدار با IN در SQLite
در پست قبل با LIKE یاد گرفتیم چطور بخشی از یک متن را جست‌وجو کنیم.
حالا می‌خواهیم ببینیم چطور می‌توانیم بررسی کنیم یک ستون، یکی از چند مقدار مشخص باشد.
برای این کار از:

IN

استفاده می‌کنیم.
🔹 مثال ساده
فرض کنیم می‌خواهیم دانش‌آموزانی را پیدا کنیم که نامشان Ali یا Sara است:

cursor.execute(
    "SELECT * FROM students WHERE name IN (?, ?)",
    ("Ali", "Sara")
)

students = cursor.fetchall()

for student in students:
    print(student)

یعنی:

name = "Ali"
یا
name = "Sara"

🔹 مقایسه با OR
بدون IN می‌توانستیم بنویسیم:

cursor.execute(
    """
    SELECT * FROM students
    WHERE name = ? OR name = ?
    """,
    ("Ali", "Sara")
)

اما IN برای این نوع جست‌وجو خواناتر است:

cursor.execute(
    "SELECT * FROM students WHERE name IN (?, ?)",
    ("Ali", "Sara")
)

🔹 استفاده با چند مقدار
می‌توانیم بیشتر از دو مقدار هم داشته باشیم:

cursor.execute(
    "SELECT * FROM students WHERE name IN (?, ?, ?)",
    ("Ali", "Sara", "Reza")
)

در اینجا فقط رکوردهایی انتخاب می‌شوند که name یکی از این سه مقدار باشد.
🔹 استفاده با عدد
IN فقط برای رشته‌ها نیست.
مثلاً می‌توانیم چند id مشخص را پیدا کنیم:

cursor.execute(
    "SELECT * FROM students WHERE id IN (?, ?, ?)",
    (1, 3, 5)
)

یعنی:

id = 1
یا
id = 3
یا
id = 5

🔹 ساختن Placeholderها به‌صورت پویا
گاهی تعداد مقادیر از قبل مشخص نیست.
مثلاً:

names = ["Ali", "Sara", "Reza"]

نمی‌توانیم به‌صورت ثابت بنویسیم:

IN (?, ?, ?)

اگر تعداد نام‌ها تغییر کند.
می‌توانیم Placeholderها را بسازیم:

names = ["Ali", "Sara", "Reza"]

placeholders = ",".join("?" for _ in names)

query = f"""
    SELECT * FROM students
    WHERE name IN ({placeholders})
"""

cursor.execute(query, names)

در اینجا:

names = 3 مقدار
        ↓
?, ?, ?

ساخته می‌شود.
⚠️ خود مقادیر همچنان با ? به Query داده می‌شوند.
🔹 NOT IN
اگر بخواهیم رکوردهایی را پیدا کنیم که مقدارشان در لیست موردنظر نباشد، از:

NOT IN

استفاده می‌کنیم.
مثلاً:

cursor.execute(
    "SELECT * FROM students WHERE name NOT IN (?, ?)",
    ("Ali", "Sara")
)

یعنی:

دانش‌آموزانی را پیدا کن که نامشان نه

Ali

باشد و نه

Sara

.

🔥 مثال کاربردی
فرض کنیم فقط می‌خواهیم دانش‌آموزان سه کلاس مشخص را بررسی کنیم:

class_names = ["Python", "Django", "Flask"]

placeholders = ",".join("?" for _ in class_names)

cursor.execute(
    f"""
    SELECT * FROM courses
    WHERE name IN ({placeholders})
    """,
    class_names
)

courses = cursor.fetchall()

این روش زمانی مفید است که تعداد مقادیر جست‌وجو متغیر باشد.
⚠️ نکته مهم
برای مقدارهای متغیر، از این روش استفاده نکن:

query = f"""
SELECT * FROM students
WHERE name IN ({names})
"""

مقدارهای کاربر را مستقیماً داخل SQL قرار نده.
روش درست:

placeholders = ",".join("?" for _ in names)

cursor.execute(
    f"SELECT * FROM students WHERE name IN ({placeholders})",
    names
)

🧠 خلاصه

IN      → یکی از چند مقدار
NOT IN  → هیچ‌کدام از چند مقدار

مثلاً:

WHERE id IN (?, ?, ?)

یا:

WHERE name NOT IN (?, ?)

و برای تعداد متغیر:

placeholders = ",".join("?" for _ in values)

🎯 تمرین
کد زیر را کامل کن تا فقط دانش‌آموزانی که id آن‌ها 1، 3 یا 5 است نمایش داده شوند:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

ids = [1, 3, 5]

placeholders = ",".join("?" for _ in ids)

cursor.execute(
    f"""
    SELECT * FROM students
    WHERE id ______ ({placeholders})
    """,
    ids
)

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

کلمه مناسب را جایگزین کن:

IN

پست ۱۹۱ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۲
محاسبه اطلاعات آماری با COUNT، SUM و AVG در SQLite
تا اینجا بیشتر با دریافت و تغییر رکوردها کار کردیم.
اما در پروژه‌های واقعی گاهی لازم است به‌جای نمایش تک‌تک رکوردها، روی داده‌ها محاسبه انجام دهیم.
SQLite چند تابع مهم برای این کار دارد:

COUNT
SUM
AVG
MIN
MAX

در این پست با سه مورد اول آشنا می‌شویم.
🔹 COUNT
COUNT برای شمارش رکوردها استفاده می‌شود.
مثلاً تعداد دانش‌آموزان:

cursor.execute(
    "SELECT COUNT(*) FROM students"
)

result = cursor.fetchone()

print(result[0])

اگر 10 دانش‌آموز داشته باشیم:

10

🔹 چرا fetchone()؟
چون COUNT(*) فقط یک نتیجه برمی‌گرداند:

(10,)

به همین دلیل:

result[0]

عدد 10 را از Tuple دریافت می‌کند.
🔹 شمارش با شرط
می‌توانیم فقط رکوردهایی را بشماریم که یک شرط مشخص دارند.
مثلاً تعداد دانش‌آموزانی که نمره حداقل 18 دارند:

cursor.execute(
    "SELECT COUNT(*) FROM students WHERE score >= ?",
    (18,)
)

count = cursor.fetchone()[0]

print(count)

🔹 SUM
SUM مجموع مقدارهای یک ستون را محاسبه می‌کند.
مثلاً مجموع نمره‌ها:

cursor.execute(
    "SELECT SUM(score) FROM students"
)

total_score = cursor.fetchone()[0]

print(total_score)

اگر نمره‌ها:

18
15
20
17

باشند:

70

🔹 AVG
AVG میانگین مقدارهای یک ستون را محاسبه می‌کند.

cursor.execute(
    "SELECT AVG(score) FROM students"
)

average_score = cursor.fetchone()[0]

print(average_score)

برای نمره‌های:

18
15
20
17

نتیجه:

17.5

🔥 ترکیب چند تابع
می‌توانیم چند تابع آماری را در یک Query استفاده کنیم:

cursor.execute(
    """
    SELECT
        COUNT(*),
        SUM(score),
        AVG(score)
    FROM students
    """
)

result = cursor.fetchone()

print("Count:", result[0])
print("Total:", result[1])
print("Average:", result[2])

مثلاً:

Count: 4
Total: 70
Average: 17.5

🔹 استفاده همراه با WHERE
می‌توانیم محاسبات را فقط روی رکوردهای موردنظر انجام دهیم:

cursor.execute(
    """
    SELECT AVG(score)
    FROM students
    WHERE score >= ?
    """,
    (10,)
)

average = cursor.fetchone()[0]

print(average)

در اینجا فقط دانش‌آموزانی که نمره‌شان حداقل 10 است در محاسبه میانگین قرار می‌گیرند.
⚠️ نکته مهم درباره NULL
در بسیاری از توابع آماری SQLite، مقدارهای NULL در محاسبات عددی نادیده گرفته می‌شوند.
مثلاً اگر داشته باشیم:

18
15
NULL
17

AVG(score) میانگین سه مقدار عددی را محاسبه می‌کند، نه چهار مقدار.
اما:

COUNT(*)

تعداد تمام رکوردها را می‌شمارد.
این موضوع را با:

COUNT(score)

می‌توانیم مقایسه کنیم:

cursor.execute(
    """
    SELECT COUNT(*), COUNT(score)
    FROM students
    """
)

result = cursor.fetchone()

print("All rows:", result[0])
print("Rows with score:", result[1])

COUNT(score) فقط مقدارهای غیر NULL را می‌شمارد.
🧠 خلاصه

COUNT(*) → تعداد رکوردها
SUM()    → مجموع
AVG()    → میانگین

مثلاً:

SELECT COUNT(*) FROM students;

یا:

SELECT SUM(score) FROM students;

یا:

SELECT AVG(score) FROM students;

🎯 تمرین
کد زیر را کامل کن تا تعداد دانش‌آموزان، مجموع نمره‌ها و میانگین نمره‌ها را نمایش دهد:

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute(
    """
    SELECT
        COUNT(*),
        ______(score),
        ______(score)
    FROM students
    """
)

result = cursor.fetchone()

print("Count:", result[0])
print("Total:", result[1])
print("Average:", result[2])

connection.close()

جای خالی‌ها را با توابع مناسب پر کن.
پست ۱۹۲ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۳
پیدا کردن کمترین و بیشترین مقدار با MIN و MAX در SQLite
در پست قبل با توابع آماری:

COUNT
SUM
AVG

آشنا شدیم.
حالا دو تابع بسیار کاربردی دیگر را یاد می‌گیریم:

MIN
MAX

🔹 MIN
تابع MIN() کمترین مقدار یک ستون را برمی‌گرداند.
مثلاً کمترین نمره:

cursor.execute(
    "SELECT MIN(score) FROM students"
)

minimum = cursor.fetchone()[0]

print(minimum)

اگر نمره‌ها این‌ها باشند:

18
15
20
17

خروجی:

15

🔹 MAX
تابع MAX() بیشترین مقدار یک ستون را برمی‌گرداند.

cursor.execute(
    "SELECT MAX(score) FROM students"
)

maximum = cursor.fetchone()[0]

print(maximum)

خروجی:

20

🔹 استفاده با WHERE
مثل سایر توابع آماری، می‌توانیم شرط هم داشته باشیم.
مثلاً بیشترین نمره بین دانش‌آموزانی که نمره‌شان حداقل 10 است:

cursor.execute(
    """
    SELECT MAX(score)
    FROM students
    WHERE score >= ?
    """,
    (10,)
)

maximum = cursor.fetchone()[0]

print(maximum)

🔥 استفاده از چند تابع با هم
می‌توانیم کمترین، بیشترین و میانگین را در یک Query دریافت کنیم:

cursor.execute(
    """
    SELECT
        MIN(score),
        MAX(score),
        AVG(score)
    FROM students
    """
)

result = cursor.fetchone()

print("Minimum:", result[0])
print("Maximum:", result[1])
print("Average:", result[2])

مثلاً:

Minimum: 15
Maximum: 20
Average: 17.5

🔹 MIN و MAX فقط برای عدد نیستند
این توابع می‌توانند روی داده‌های متنی هم استفاده شوند.
مثلاً:

cursor.execute(
    "SELECT MIN(name), MAX(name) FROM students"
)

result = cursor.fetchone()

print(result)

در این حالت SQLite بر اساس ترتیب مقایسه رشته‌ها، مقدار حداقل و حداکثر را تعیین می‌کند.
⚠️ نکته مهم درباره NULL
در محاسباتی مانند:

MIN(score)
MAX(score)
AVG(score)
SUM(score)

مقدارهای NULL در محاسبه نادیده گرفته می‌شوند.
مثلاً اگر:

18
NULL
15
20

داشته باشیم:

MIN → 15
MAX → 20
AVG → میانگین 18، 15 و 20

🔹 پیدا کردن رکورد مربوط به بیشترین نمره
MAX(score) فقط خود مقدار را برمی‌گرداند.
اگر بخواهیم اطلاعات دانش‌آموزی که بیشترین نمره را دارد هم دریافت کنیم، می‌توانیم از:

cursor.execute(
    """
    SELECT name, score
    FROM students
    WHERE score = (SELECT MAX(score) FROM students)
    """
)

students = cursor.fetchall()

for student in students:
    print(student)

مثلاً:

('Reza', 20)

اگر چند دانش‌آموز بالاترین نمره یکسان داشته باشند، همه آن‌ها می‌توانند برگردانده شوند.
🧠 خلاصه

COUNT() → تعداد
SUM()   → مجموع
AVG()   → میانگین
MIN()   → کمترین مقدار
MAX()   → بیشترین مقدار

مثلاً:

SELECT MIN(score), MAX(score)
FROM students;

🎯 تمرین
کد زیر را کامل کن تا کمترین و بیشترین قیمت محصولات را پیدا کند:

import sqlite3

connection = sqlite3.connect("shop.db")
cursor = connection.cursor()

cursor.execute(
    """
    SELECT
        ______(price),
        ______(price)
    FROM products
    """
)

result = cursor.fetchone()

print("Minimum:", result[0])
print("Maximum:", result[1])

connection.close()

توابع مناسب را جایگزین کن.
پست ۱۹۳ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۴
گروه‌بندی داده‌ها با GROUP BY در SQLite
تا اینجا با توابعی مثل:

COUNT()
SUM()
AVG()
MIN()
MAX()

کار کردیم.
اما گاهی نمی‌خواهیم این محاسبات را روی کل جدول انجام دهیم؛ بلکه می‌خواهیم داده‌ها را بر اساس یک ستون گروه‌بندی کنیم.
برای این کار از:

GROUP BY

استفاده می‌کنیم.
🔹 GROUP BY چیست؟
فرض کنیم جدول students شامل این اطلاعات باشد:

name   | class   | score
Ali    | Python  | 18
Sara   | Python  | 20
Reza   | Django  | 15
Mina   | Django  | 17

اگر بخواهیم میانگین نمره هر کلاس را محاسبه کنیم:

cursor.execute(
    """
    SELECT class, AVG(score)
    FROM students
    GROUP BY class
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

خروجی:

('Django', 16.0)
('Python', 19.0)

SQLite ابتدا رکوردها را بر اساس class گروه‌بندی می‌کند و سپس AVG() را برای هر گروه محاسبه می‌کند.
🔹 تعداد اعضای هر گروه
می‌توانیم از COUNT() هم استفاده کنیم.
مثلاً تعداد دانش‌آموزان هر کلاس:

cursor.execute(
    """
    SELECT class, COUNT(*)
    FROM students
    GROUP BY class
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

خروجی:

('Django', 2)
('Python', 2)

🔹 مجموع هر گروه با SUM()
مثلاً مجموع نمره‌های هر کلاس:

cursor.execute(
    """
    SELECT class, SUM(score)
    FROM students
    GROUP BY class
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

خروجی:

('Django', 32)
('Python', 38)

🔥 ترکیب چند تابع
می‌توانیم چند محاسبه را هم‌زمان انجام دهیم:

cursor.execute(
    """
    SELECT
        class,
        COUNT(*),
        AVG(score),
        MIN(score),
        MAX(score)
    FROM students
    GROUP BY class
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

هر ردیف نتیجه شامل اطلاعات آماری یک کلاس خواهد بود.
🔹 GROUP BY با چند ستون
می‌توانیم بیشتر از یک ستون را هم گروه‌بندی کنیم.
مثلاً:

cursor.execute(
    """
    SELECT class, gender, COUNT(*)
    FROM students
    GROUP BY class, gender
    """
)

در این حالت گروه‌ها بر اساس ترکیب:

class + gender

تشکیل می‌شوند.
🔹 تفاوت WHERE و GROUP BY
این دو وظیفه متفاوتی دارند.
WHERE ابتدا رکوردها را فیلتر می‌کند:

WHERE score >= 10

اما:

GROUP BY class

رکوردهای باقی‌مانده را گروه‌بندی می‌کند.
مثلاً:

cursor.execute(
    """
    SELECT class, AVG(score)
    FROM students
    WHERE score >= ?
    GROUP BY class
    """,
    (10,)
)

ترتیب منطقی:

FROM
 ↓
WHERE
 ↓
GROUP BY
 ↓
AVG()

یعنی ابتدا رکوردهای نامعتبر حذف می‌شوند، سپس داده‌های باقی‌مانده گروه‌بندی و محاسبه می‌شوند.
⚠️ نکته مهم
وقتی از GROUP BY استفاده می‌کنیم، معمولاً ستون گروه‌بندی‌شده را همراه با یک تابع تجمیعی (Aggregate Function) انتخاب می‌کنیم:

SELECT class, AVG(score)
FROM students
GROUP BY class;

نه اینکه یک ستون نامرتبط را بدون دلیل وارد نتیجه کنیم.
🧠 خلاصه

GROUP BY → گروه‌بندی رکوردها
COUNT()  → تعداد هر گروه
SUM()    → مجموع هر گروه
AVG()    → میانگین هر گروه
MIN()    → کمترین مقدار هر گروه
MAX()    → بیشترین مقدار هر گروه

مثلاً:

SELECT class, AVG(score)
FROM students
GROUP BY class;

یعنی:

میانگین نمره را برای هر کلاس جداگانه محاسبه کن.

🎯 تمرین
جدول products را در نظر بگیر:

name     | category | price
Laptop   | PC       | 1200
Mouse    | PC       | 50
Phone    | Mobile   | 800
Tablet   | Mobile   | 500

کد را کامل کن تا میانگین قیمت هر دسته‌بندی نمایش داده شود:

cursor.execute(
    """
    SELECT category, ______(price)
    FROM products
    GROUP BY ______
    """
)

دو جای خالی را کامل کن.
پست ۱۹۴ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۵
فیلتر کردن گروه‌ها با HAVING در SQLite
در پست قبل با GROUP BY یاد گرفتیم داده‌ها را گروه‌بندی کنیم.
حالا می‌خواهیم فقط گروه‌هایی را که یک شرط مشخص دارند نمایش دهیم.
برای این کار از:

HAVING

استفاده می‌کنیم.
🔹 تفاوت WHERE و HAVING
این تفاوت بسیار مهم است:

WHERE   → فیلتر کردن رکوردها
HAVING  → فیلتر کردن گروه‌ها

مثلاً:

WHERE score >= 10

قبل از گروه‌بندی روی رکوردها اعمال می‌شود.
اما:

HAVING AVG(score) >= 15

بعد از گروه‌بندی، گروه‌ها را بررسی می‌کند.
🔹 مثال ساده
فرض کنیم:

class   | score
Python  | 18
Python  | 20
Django  | 10
Django  | 12
Flask   | 17
Flask   | 19

می‌خواهیم فقط کلاس‌هایی را نمایش دهیم که میانگین نمره‌شان حداقل 15 باشد:

cursor.execute(
    """
    SELECT class, AVG(score)
    FROM students
    GROUP BY class
    HAVING AVG(score) >= ?
    """,
    (15,)
)

results = cursor.fetchall()

for row in results:
    print(row)

نتیجه:

('Python', 19.0)
('Flask', 18.0)

کلاس Django حذف می‌شود چون میانگین آن:

11.0

است.
🔹 استفاده از COUNT() با HAVING
مثلاً می‌خواهیم فقط دسته‌بندی‌هایی را ببینیم که حداقل ۲ محصول دارند:

cursor.execute(
    """
    SELECT category, COUNT(*)
    FROM products
    GROUP BY category
    HAVING COUNT(*) >= ?
    """,
    (2,)
)

در اینجا:

GROUP BY  → محصولات را بر اساس category گروه‌بندی می‌کند
COUNT()   → تعداد محصولات هر گروه
HAVING    → فقط گروه‌های دارای حداقل ۲ محصول

🔥 ترکیب WHERE و HAVING
می‌توانیم هر دو را در یک Query داشته باشیم.
مثلاً:

ابتدا محصولاتی که قیمتشان حداقل 100 است را انتخاب کن؛ سپس فقط دسته‌بندی‌هایی را نمایش بده که میانگین قیمتشان حداقل 500 باشد.

cursor.execute(
    """
    SELECT category, AVG(price)
    FROM products
    WHERE price >= ?
    GROUP BY category
    HAVING AVG(price) >= ?
    """,
    (100, 500)
)

ترتیب منطقی:

FROM
 ↓
WHERE
 ↓
GROUP BY
 ↓
HAVING
 ↓
SELECT

یعنی:
رکوردها انتخاب می‌شوند.
رکوردها فیلتر می‌شوند.
رکوردهای باقی‌مانده گروه‌بندی می‌شوند.
گروه‌ها فیلتر می‌شوند.
نتیجه نهایی برگردانده می‌شود.
⚠️ اشتباه رایج
اگر بخواهیم بر اساس یک Aggregate Function مثل AVG() گروه‌ها را فیلتر کنیم، نمی‌توانیم آن شرط را در WHERE قرار دهیم:
❌ نادرست:

WHERE AVG(score) >= 15

✅ درست:

HAVING AVG(score) >= 15

چون AVG() بعد از تشکیل گروه‌ها محاسبه می‌شود.
🔹 HAVING بدون GROUP BY
در بعضی Queryها می‌توان HAVING را بدون GROUP BY هم استفاده کرد، اما برای این مرحله بهتر است تمرکزمان روی الگوی رایج باشد:

GROUP BY ...
HAVING ...

🧠 خلاصه

WHERE  → فیلتر رکوردها
GROUP BY → ساخت گروه‌ها
HAVING → فیلتر گروه‌ها

مثلاً:

SELECT category, AVG(price)
FROM products
WHERE price >= 100
GROUP BY category
HAVING AVG(price) >= 500;

🎯 تمرین
کد زیر را کامل کن تا فقط دسته‌بندی‌هایی نمایش داده شوند که:
حداقل ۲ محصول دارند.
میانگین قیمتشان حداقل 500 است.

cursor.execute(
    """
    SELECT category, COUNT(*), AVG(price)
    FROM products
    GROUP BY category
    HAVING COUNT(*) >= ?
       AND AVG(price) >= ?
    """,
    (______, ______)
)

مقادیر مناسب را جایگزین کن.
پست ۱۹۵ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۶
اتصال چند جدول در SQLite با JOIN
تا اینجا بیشتر با یک جدول کار کردیم.
اما در پروژه‌های واقعی معمولاً اطلاعات را در چند جدول مرتبط نگهداری می‌کنیم.
مثلاً:

students
courses

و بین آن‌ها یک ارتباط وجود دارد.
برای ترکیب اطلاعات این جدول‌ها از:

JOIN

استفاده می‌کنیم.
🔹 مثال
فرض کنیم جدول students:

id | name  | course_id
1  | Ali   | 10
2  | Sara  | 20
3  | Reza  | 10

و جدول courses:

id | name
10 | Python
20 | Django

در جدول students، ستون course_id به courses.id اشاره می‌کند.
🔹 INNER JOIN
رایج‌ترین نوع JOIN، INNER JOIN است.
می‌خواهیم نام دانش‌آموز و نام دوره او را در یک نتیجه ببینیم:

cursor.execute(
    """
    SELECT students.name, courses.name
    FROM students
    INNER JOIN courses
        ON students.course_id = courses.id
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

خروجی:

('Ali', 'Python')
('Sara', 'Django')
('Reza', 'Python')

🔹 ON چه کاری انجام می‌دهد؟
این بخش:

ON students.course_id = courses.id

به SQLite می‌گوید کدام رکوردهای دو جدول به یکدیگر مرتبط هستند.
یعنی:

students.course_id
        ↓
courses.id

🔹 چرا از نام جدول استفاده کردیم؟
هر دو جدول ممکن است ستونی به نام id یا name داشته باشند.
مثلاً:

students.name
courses.name

با نوشتن نام جدول، مشخص می‌کنیم دقیقاً کدام ستون را می‌خواهیم.
🔹 استفاده از Alias
برای Queryهای طولانی می‌توانیم نام کوتاه‌تری برای جدول‌ها تعیین کنیم:

cursor.execute(
    """
    SELECT s.name, c.name
    FROM students AS s
    INNER JOIN courses AS c
        ON s.course_id = c.id
    """
)

اینجا:

s → students
c → courses

پس:

s.name

یعنی:

students.name

🔥 ترکیب JOIN با WHERE
می‌توانیم بعد از اتصال جدول‌ها، نتیجه را فیلتر کنیم.
مثلاً فقط دانش‌آموزان دوره Python:

cursor.execute(
    """
    SELECT s.name, c.name
    FROM students AS s
    INNER JOIN courses AS c
        ON s.course_id = c.id
    WHERE c.name = ?
    """,
    ("Python",)
)

results = cursor.fetchall()

for row in results:
    print(row)

خروجی:

('Ali', 'Python')
('Reza', 'Python')

🔹 JOIN با سه جدول
حتی می‌توانیم چند جدول را به هم متصل کنیم.
مثلاً:

students
courses
teachers

و Query:

cursor.execute(
    """
    SELECT s.name, c.name, t.name
    FROM students AS s
    INNER JOIN courses AS c
        ON s.course_id = c.id
    INNER JOIN teachers AS t
        ON c.teacher_id = t.id
    """
)

در این حالت اطلاعات از سه جدول ترکیب می‌شوند.
⚠️ نکته مهم
در INNER JOIN فقط رکوردهایی برگردانده می‌شوند که در هر دو جدول تطابق داشته باشند.
مثلاً اگر دانش‌آموزی course_id داشته باشد که در جدول courses وجود نداشته باشد، آن دانش‌آموز در نتیجه INNER JOIN نمایش داده نمی‌شود.
در پست بعدی سراغ نوع مهم دیگری از JOIN یعنی LEFT JOIN می‌رویم.
🧠 خلاصه

JOIN      → اتصال جدول‌ها
ON        → مشخص کردن رابطه
INNER JOIN → فقط رکوردهای دارای تطابق
AS        → ساخت Alias برای جدول

ساختار کلی:

SELECT ...
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

🎯 تمرین
دو جدول زیر را در نظر بگیر:

users
id | name | city_id

cities
id | name

کد را کامل کن تا نام کاربر و نام شهر او نمایش داده شود:

cursor.execute(
    """
    SELECT u.name, c.name
    FROM users AS u
    INNER JOIN cities AS c
        ON u.______ = c.______
    """
)

دو جای خالی را طوری کامل کن که رابطه بین users و cities برقرار شود.
پست ۱۹۶ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۷
اتصال جدول‌ها با LEFT JOIN در SQLite
در پست قبل با INNER JOIN آشنا شدیم.
INNER JOIN فقط رکوردهایی را برمی‌گرداند که در هر دو جدول تطابق داشته باشند.
اما گاهی می‌خواهیم:

تمام رکوردهای جدول اصلی را نگه داریم، حتی اگر در جدول دوم تطابقی وجود نداشته باشد.

برای این کار از:

LEFT JOIN

استفاده می‌کنیم.
🔹 مثال ساده
فرض کنیم جدول users:

id | name  | city_id
1  | Ali   | 10
2  | Sara  | 20
3  | Reza  | NULL

و جدول cities:

id | name
10 | Tehran
20 | Baku
30 | Tabriz

حالا:

cursor.execute(
    """
    SELECT u.name, c.name
    FROM users AS u
    LEFT JOIN cities AS c
        ON u.city_id = c.id
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

نتیجه:

('Ali', 'Tehran')
('Sara', 'Baku')
('Reza', None)

🔹 چرا Reza هم نمایش داده شد؟
چون در LEFT JOIN، تمام رکوردهای جدول سمت چپ حفظ می‌شوند.
اینجا:

FROM users AS u

جدول users سمت چپ است.
پس حتی اگر:

u.city_id → NULL

باشد، رکورد Reza حذف نمی‌شود.
چون شهر متناظر وجود ندارد، ستون‌های جدول cities مقدار:

None

خواهند داشت.
🔥 مقایسه INNER JOIN و LEFT JOIN
با INNER JOIN:

FROM users AS u
INNER JOIN cities AS c
    ON u.city_id = c.id

نتیجه:

Ali  → Tehran
Sara → Baku

Reza نمایش داده نمی‌شود.
اما با:

FROM users AS u
LEFT JOIN cities AS c
    ON u.city_id = c.id

نتیجه:

Ali  → Tehran
Sara → Baku
Reza → None

پس:

INNER JOIN → فقط رکوردهای دارای تطابق
LEFT JOIN  → تمام رکوردهای جدول چپ + تطابق‌های جدول راست

🔹 پیدا کردن رکوردهای بدون تطابق
یکی از کاربردهای بسیار مهم LEFT JOIN پیدا کردن رکوردهایی است که رکورد مرتبط ندارند.
مثلاً می‌خواهیم کاربرانی را پیدا کنیم که شهر معتبری برایشان ثبت نشده است:

cursor.execute(
    """
    SELECT u.name
    FROM users AS u
    LEFT JOIN cities AS c
        ON u.city_id = c.id
    WHERE c.id IS NULL
    """
)

users = cursor.fetchall()

for user in users:
    print(user)

نتیجه:

('Reza',)

اینجا:

WHERE c.id IS NULL

یعنی در جدول cities رکورد متناظری پیدا نشده است.
🔹 LEFT JOIN با شرط
می‌توانیم نتیجه را با WHERE فیلتر کنیم:

cursor.execute(
    """
    SELECT u.name, c.name
    FROM users AS u
    LEFT JOIN cities AS c
        ON u.city_id = c.id
    WHERE c.name = ?
    """,
    ("Baku",)
)

در این حالت فقط کاربرانی که شهرشان Baku است برگردانده می‌شوند.
⚠️ توجه کن که قرار دادن شرط روی جدول سمت راست در WHERE می‌تواند باعث شود رفتار Query شبیه INNER JOIN شود، چون رکوردهای دارای NULL در ستون‌های جدول راست حذف می‌شوند.
🔹 شرط داخل ON
گاهی می‌خواهیم ویژگی LEFT JOIN را حفظ کنیم و شرط مربوط به جدول دوم را داخل ON قرار دهیم:

cursor.execute(
    """
    SELECT u.name, c.name
    FROM users AS u
    LEFT JOIN cities AS c
        ON u.city_id = c.id
       AND c.name = ?
    """,
    ("Baku",)
)

در این حالت تمام کاربران جدول users همچنان حفظ می‌شوند.
🧠 خلاصه

INNER JOIN
    ↓
فقط رکوردهای دارای تطابق

LEFT JOIN
    ↓
تمام رکوردهای جدول چپ
+
رکوردهای مطابق از جدول راست

ساختار:

SELECT ...
FROM table1
LEFT JOIN table2
    ON table1.column = table2.column;

و برای پیدا کردن موارد بدون تطابق:

WHERE table2.id IS NULL

🎯 تمرین
دو جدول زیر را در نظر بگیر:

orders
id | user_id | amount

users
id | name

کدی بنویس که تمام سفارش‌ها را نمایش دهد و در کنار هر سفارش، نام کاربر مربوطه را هم نشان دهد.
اگر یک سفارش user_id نامعتبر داشته باشد، آن سفارش باید همچنان نمایش داده شود و نام کاربر None باشد.
راهنما:

cursor.execute(
    """
    SELECT o.id, o.amount, u.name
    FROM orders AS o
    ______ JOIN users AS u
        ON o.user_id = u.id
    """
)

پست ۱۹۷ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۸
اتصال چند جدول و انواع JOIN در SQLite
در پست‌های قبل با INNER JOIN و LEFT JOIN آشنا شدیم.
حالا می‌خواهیم تفاوت انواع مهم JOIN را بهتر بشناسیم و ببینیم چطور می‌توانیم چند جدول را هم‌زمان به هم متصل کنیم.
🔹 INNER JOIN
فقط رکوردهایی را برمی‌گرداند که در دو جدول تطابق داشته باشند.
مثلاً:

SELECT u.name, c.name
FROM users AS u
INNER JOIN cities AS c
    ON u.city_id = c.id;

اگر کاربری شهر معتبر نداشته باشد، در نتیجه نمایش داده نمی‌شود.
🔹 LEFT JOIN
تمام رکوردهای جدول سمت چپ را نگه می‌دارد:

SELECT u.name, c.name
FROM users AS u
LEFT JOIN cities AS c
    ON u.city_id = c.id;

اگر شهر متناظر پیدا نشود:

user → name
city → None

🔹 چند JOIN در یک Query
فرض کنیم سه جدول داریم:

users
orders
products

ساختار:

users
  ↓
orders
  ↓
products

مثلاً:

cursor.execute(
    """
    SELECT
        u.name,
        p.name,
        o.amount
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    INNER JOIN products AS p
        ON o.product_id = p.id
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

در این Query اطلاعات سه جدول با هم ترکیب می‌شوند.
🔹 چرا JOINها را مرحله‌به‌مرحله می‌نویسیم؟
می‌توانیم Query را به شکل زیر تصور کنیم:

users
  ↓
JOIN orders
  ↓
JOIN products
  ↓
نتیجه نهایی

هر JOIN یک جدول دیگر را به نتیجه اضافه می‌کند.
🔥 ترکیب JOIN با WHERE
مثلاً می‌خواهیم فقط سفارش‌های یک کاربر خاص را ببینیم:

cursor.execute(
    """
    SELECT
        u.name,
        p.name,
        o.amount
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    INNER JOIN products AS p
        ON o.product_id = p.id
    WHERE u.id = ?
    """,
    (1,)
)

اینجا:

JOIN  → جدول‌ها را متصل می‌کند
WHERE → نتیجه را فیلتر می‌کند

🔹 JOIN با ORDER BY
مثلاً سفارش‌ها را بر اساس مبلغ، از بیشترین به کمترین مرتب کنیم:

cursor.execute(
    """
    SELECT
        u.name,
        p.name,
        o.amount
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    INNER JOIN products AS p
        ON o.product_id = p.id
    ORDER BY o.amount DESC
    """
)

🔹 JOIN با GROUP BY
می‌توانیم اطلاعات چند جدول را ابتدا متصل کنیم و سپس آن‌ها را گروه‌بندی کنیم.
مثلاً مجموع سفارش‌های هر کاربر:

cursor.execute(
    """
    SELECT
        u.name,
        SUM(o.amount)
    FROM users AS u
    INNER JOIN orders AS o
        ON u.id = o.user_id
    GROUP BY u.id, u.name
    """
)

results = cursor.fetchall()

for row in results:
    print(row)

مثلاً:

Ali   → 1500
Sara  → 800
Reza  → 420

🔹 RIGHT JOIN و FULL OUTER JOIN
در SQL استاندارد، انواع دیگری مانند:

RIGHT JOIN
FULL OUTER JOIN

هم وجود دارند.
اما باید تفاوت نسخه SQLite را در نظر بگیریم و در طراحی Queryها صرفاً بر اساس SQL عمومی فرض نکنیم که تمام رفتارها در همه نسخه‌ها یکسان است.
برای این دوره فعلاً تمرکز اصلی ما روی:

INNER JOIN
LEFT JOIN

است؛ چون این دو در پروژه‌های SQLite بسیار پرکاربرد هستند.
⚠️ نکته مهم: نام ستون‌ها
وقتی چند جدول ستون‌های مشابه دارند، بهتر است همیشه از Alias استفاده کنیم:

SELECT
    u.id,
    u.name,
    o.id,
    o.amount

به‌جای اینکه Query را مبهم بنویسیم.
Alias باعث می‌شود Query خواناتر و احتمال اشتباه کمتر شود:

u → users
o → orders
p → products

🧠 خلاصه

INNER JOIN → فقط رکوردهای دارای تطابق
LEFT JOIN  → تمام رکوردهای جدول چپ + تطابق‌های جدول راست

و می‌توانیم چند JOIN را پشت سر هم استفاده کنیم:

FROM users AS u
JOIN orders AS o
    ON u.id = o.user_id
JOIN products AS p
    ON o.product_id = p.id

سپس آن را با:

WHERE
GROUP BY
HAVING
ORDER BY

ترکیب کنیم.
🎯 تمرین
سه جدول زیر را در نظر بگیر:

users
id | name

orders
id | user_id | product_id

products
id | name | price

کد را کامل کن تا نام کاربر، نام محصول و مبلغ سفارش نمایش داده شود:

cursor.execute(
    """
    SELECT
        u.name,
        p.name,
        o.amount
    FROM users AS u
    ______ JOIN orders AS o
        ON u.id = o.user_id
    ______ JOIN products AS p
        ON o.product_id = p.id
    """
)

هر دو جای خالی را با نوع مناسب JOIN کامل کن.
پست ۱۹۸ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۱۹۹
محدود کردن نتیجه Query با OFFSET در SQLite
تا اینجا با LIMIT آشنا شدیم.
LIMIT مشخص می‌کند چند رکورد برگردانده شود.
اما اگر بخواهیم از یک نقطه مشخص شروع کنیم و چند رکورد را رد کنیم، از:

OFFSET

استفاده می‌کنیم.
🔹 OFFSET چیست؟
فرض کنیم جدول students این اطلاعات را داشته باشد:

id | name
1  | Ali
2  | Sara
3  | Reza
4  | Mina
5  | Arman

اگر بنویسیم:

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY id
    LIMIT 2 OFFSET 2
    """
)

دو رکورد اول نادیده گرفته می‌شوند:

Ali
Sara

و نتیجه:

Reza
Mina

خواهد بود.
🔹 ترکیب LIMIT و OFFSET
ساختار کلی:

SELECT ...
FROM table
LIMIT number
OFFSET number;

مثلاً:

LIMIT 10 OFFSET 20

یعنی:

۲۰ رکورد اول را رد کن و سپس حداکثر ۱۰ رکورد برگردان.

🔥 کاربرد مهم: Pagination
LIMIT و OFFSET در ساخت صفحه‌بندی (Pagination) بسیار کاربردی هستند.
مثلاً اگر در هر صفحه 10 رکورد نمایش دهیم:

Page 1 → OFFSET 0
Page 2 → OFFSET 10
Page 3 → OFFSET 20
Page 4 → OFFSET 30

فرمول:

OFFSET = (page - 1) × page_size

مثلاً برای صفحه 3:

(3 - 1) × 10 = 20

پس:

LIMIT 10 OFFSET 20

🔹 پیاده‌سازی با Python

page = 3
page_size = 10

offset = (page - 1) * page_size

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY id
    LIMIT ? OFFSET ?
    """,
    (page_size, offset)
)

students = cursor.fetchall()

for student in students:
    print(student)

در این مثال:

page      → 3
page_size → 10
offset    → 20

پس رکوردهای صفحه سوم دریافت می‌شوند.
⚠️ ORDER BY مهم است
برای Pagination بهتر است ترتیب نتایج را مشخص کنیم:

ORDER BY id

چون بدون ترتیب مشخص، نباید روی ترتیب طبیعی بازگشت رکوردها حساب کنیم.
پس بهتر است بنویسیم:

SELECT *
FROM students
ORDER BY id
LIMIT ? OFFSET ?;

🔹 OFFSET به‌تنهایی
در SQLite می‌توان OFFSET را همراه LIMIT استفاده کرد.
مثلاً:

LIMIT -1 OFFSET 5

یعنی:

پنج رکورد اول را رد کن و بقیه را برگردان.

اما در پروژه‌های معمول، استفاده از:

LIMIT + OFFSET

برای Pagination رایج‌تر و خواناتر است.
🧠 خلاصه

LIMIT  → تعداد رکوردها
OFFSET → تعداد رکوردهایی که از ابتدای نتیجه رد می‌شوند

مثلاً:

LIMIT 10 OFFSET 20

یعنی:

۲۰ رکورد اول → رد
۱۰ رکورد بعدی → نتیجه

و برای صفحه‌بندی:

OFFSET = (page - 1) × page_size

🎯 تمرین
کد زیر را کامل کن تا صفحه چهارم را با 5 رکورد در هر صفحه دریافت کند:

page = 4
page_size = 5

offset = (page - 1) * page_size

cursor.execute(
    """
    SELECT *
    FROM students
    ORDER BY id
    LIMIT ? OFFSET ?
    """,
    (page_size, ______)
)

مقدار مناسب را جایگزین کن.
پست ۱۹۹ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۰
محدودیت‌های جدول در SQLite | PRIMARY KEY و NOT NULL
تا اینجا یاد گرفتیم چطور داده‌ها را در SQLite ذخیره، جست‌وجو، تغییر و حذف کنیم.
اما در یک Database واقعی فقط ذخیره کردن داده کافی نیست؛ باید مشخص کنیم چه داده‌ای مجاز است وارد جدول شود.
برای این کار از Constraintها استفاده می‌کنیم.
در این پست با دو Constraint مهم آشنا می‌شویم:

PRIMARY KEY
NOT NULL

🔹 PRIMARY KEY
PRIMARY KEY مشخص می‌کند هر رکورد با چه مقداری به‌صورت یکتا شناسایی شود.
مثلاً:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT
    )
    """
)

اینجا:

id → شناسه اصلی رکورد

هر رکورد باید شناسه منحصربه‌فرد داشته باشد.
مثلاً:

id | name
1  | Ali
2  | Sara
3  | Reza

🔹 جلوگیری از شناسه تکراری
اگر بخواهیم دوباره id = 1 را وارد کنیم:

cursor.execute(
    "INSERT INTO users (id, name) VALUES (?, ?)",
    (1, "Mina")
)

SQLite خطایی از نوع IntegrityError ایجاد می‌کند، چون مقدار 1 قبلاً به‌عنوان PRIMARY KEY استفاده شده است.
🔹 NOT NULL
گاهی یک ستون نباید بدون مقدار باشد.
مثلاً:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
    """
)

اینجا:

name → نباید NULL باشد

پس این مورد معتبر است:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

اما این مورد:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    (None,)
)

باعث IntegrityError می‌شود.
🔥 ترکیب PRIMARY KEY و NOT NULL
می‌توانیم چند Constraint را هم‌زمان استفاده کنیم:

cursor.execute(
    """
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL NOT NULL
    )
    """
)

در این جدول:

id     → شناسه اصلی و یکتا
name   → نباید NULL باشد
price  → نباید NULL باشد

🔹 بررسی خطای Constraint
می‌توانیم خطا را مدیریت کنیم:

import sqlite3

try:
    cursor.execute(
        "INSERT INTO products (name, price) VALUES (?, ?)",
        (None, 1200)
    )

    connection.commit()

except sqlite3.IntegrityError as error:
    print("Constraint error:", error)

چون name دارای:

NOT NULL

است، درج None مجاز نیست.
⚠️ NOT NULL با رشته خالی یکی نیست
این نکته مهم است.

name = None

در SQLite:

NULL

است.
اما:

name = ""

یک رشته خالی است.
بنابراین:

None → NULL
""   → empty string

NOT NULL جلوی NULL را می‌گیرد، اما الزاماً جلوی رشته خالی را نمی‌گیرد.
🔹 تعریف PRIMARY KEY در هنگام ساخت جدول
روش رایج:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

بعد می‌توانیم رکورد اضافه کنیم:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

connection.commit()

در این حالت SQLite می‌تواند برای INTEGER PRIMARY KEY مقدار id را به‌صورت خودکار تعیین کند.
🧠 خلاصه

PRIMARY KEY → شناسایی یکتای رکورد
NOT NULL    → جلوگیری از مقدار NULL

مثلاً:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)

یعنی:

id   → شناسه اصلی
name → باید مقدار داشته باشد

🎯 تمرین
جدول students را طوری کامل کن که:
id کلید اصلی باشد.
name نتواند NULL باشد.
score هم نتواند NULL باشد.

cursor.execute(
    """
    CREATE TABLE students (
        id ______,
        name TEXT ______,
        score INTEGER ______
    )
    """
)

سه جای خالی را با Constraint مناسب کامل کن.
پست ۲۰۰ از دوره جامع Python 🐍

💻🕶 شبیه‌ساز هک با Python
این بار یک پروژه متفاوت ساختم؛ یک Hack Simulator با Python که فضای یک عملیات هکری را به‌صورت شبیه‌سازی‌شده نمایش می‌دهد. 😎
در این پروژه اتفاقاتی مثل:
🔹 بررسی هدف
🔹 نمایش مراحل عملیات
🔹 شبیه‌سازی تست‌های امنیتی
🔹 نمایش وضعیت‌ها و نتایج
🔹 و افکت‌های ترمینالی
به‌صورت نمایشی و آموزشی اجرا می‌شوند.
⚠️ نکته مهم: این پروژه برای آموزش Python، آشنایی با مفاهیم امنیت و ساخت شبیه‌ساز طراحی شده و به‌خودی‌خود به معنی هک واقعی یا دسترسی غیرمجاز به سیستم‌ها نیست.
🐍 هدف اصلی پروژه اینه که ببینیم چطور می‌توانیم با Python یک پروژه جذاب و تعاملی بسازیم.
اگر دوست دارید، می‌تونم نسخه‌های پیشرفته‌تر این شبیه‌ساز رو هم توسعه بدم؛ مثلاً با GUI، لاگ‌های واقعی‌تر، مراحل تعاملی و سناریوهای مختلف امنیتی.

پست آموزشی دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۱
جلوگیری از داده‌های تکراری با UNIQUE در SQLite
در پست قبل با PRIMARY KEY و NOT NULL آشنا شدیم.
حالا می‌خواهیم با یک Constraint مهم دیگر آشنا شویم:

UNIQUE

UNIQUE مشخص می‌کند که مقدار یک ستون نباید در چند رکورد تکرار شود.
🔹 مثال ساده
فرض کنیم برای کاربران یک username داریم:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        username TEXT UNIQUE
    )
    """
)

حالا می‌توانیم این کاربر را اضافه کنیم:

cursor.execute(
    "INSERT INTO users (username) VALUES (?)",
    ("ali",)
)

connection.commit()

اما اگر دوباره بخواهیم:

cursor.execute(
    "INSERT INTO users (username) VALUES (?)",
    ("ali",)
)

connection.commit()

SQLite خطای IntegrityError ایجاد می‌کند؛ چون username دارای UNIQUE است و مقدار ali قبلاً وجود دارد.
🔹 UNIQUE چه تفاوتی با PRIMARY KEY دارد؟
هر دو می‌توانند از تکراری شدن مقدار جلوگیری کنند، اما کاربردشان متفاوت است.

PRIMARY KEY → شناسه اصلی رکورد
UNIQUE      → جلوگیری از مقدار تکراری در یک ستون

مثلاً:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE
)

اینجا:

id       → شناسه اصلی
username → باید یکتا باشد

🔹 مثال کاربردی
برای ایمیل کاربران نیز معمولاً نمی‌خواهیم یک ایمیل چند بار ثبت شود:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        username TEXT UNIQUE,
        email TEXT UNIQUE
    )
    """
)

حالا دو کاربر نمی‌توانند یک username یا email یکسان داشته باشند.
🔹 مدیریت خطای UNIQUE
می‌توانیم خطا را مدیریت کنیم:

import sqlite3

try:
    cursor.execute(
        "INSERT INTO users (username) VALUES (?)",
        ("ali",)
    )

    connection.commit()

except sqlite3.IntegrityError:
    print("Username already exists")

این روش در برنامه‌های واقعی بسیار کاربردی است.
🔹 UNIQUE برای چند ستون
گاهی نمی‌خواهیم هر ستون به‌تنهایی یکتا باشد؛ بلکه ترکیب چند ستون باید یکتا باشد.
مثلاً:

cursor.execute(
    """
    CREATE TABLE enrollments (
        student_id INTEGER,
        course_id INTEGER,
        UNIQUE(student_id, course_id)
    )
    """
)

در این حالت یک دانشجو می‌تواند در چند دوره ثبت‌نام کند و یک دوره هم چند دانشجو داشته باشد.
اما ترکیب:

student_id + course_id

نباید دوبار تکرار شود.
⚠️ نکته مهم
UNIQUE با NOT NULL یکی نیست.
مثلاً:

username TEXT UNIQUE

یعنی مقدارهای غیر NULL نباید تکراری باشند.
اما اگر بخواهیم مقدار حتماً وجود داشته باشد:

username TEXT UNIQUE NOT NULL

استفاده می‌کنیم.
یعنی:

UNIQUE   → تکراری نباشد
NOT NULL → خالی/NULL نباشد

🧠 خلاصه

PRIMARY KEY → شناسه اصلی و یکتا
NOT NULL    → جلوگیری از NULL
UNIQUE      → جلوگیری از مقدار تکراری

مثلاً:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL
)

یعنی:

id       → شناسه اصلی
username → نه NULL باشد و نه تکراری

🎯 تمرین
جدول users را طوری کامل کن که:
id کلید اصلی باشد.
username مقدار NULL نداشته باشد.
username تکراری نباشد.

cursor.execute(
    """
    CREATE TABLE users (
        id ______,
        username TEXT ______ ______
    )
    """
)

سه جای خالی را با Constraint مناسب کامل کن.
پست ۲۰۱ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۲
مقدار پیش‌فرض و اعتبارسنجی با DEFAULT و CHECK
در پست‌های قبل با Constraintهای:

PRIMARY KEY
NOT NULL
UNIQUE

آشنا شدیم.
حالا دو Constraint مهم دیگر را یاد می‌گیریم:

DEFAULT
CHECK

🔹 DEFAULT
با DEFAULT می‌توانیم برای یک ستون مقدار پیش‌فرض تعیین کنیم.
مثلاً:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        active INTEGER DEFAULT 1
    )
    """
)

اگر هنگام درج کاربر مقدار active را مشخص نکنیم:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

SQLite به‌صورت خودکار مقدار پیش‌فرض را قرار می‌دهد:

active → 1

🔹 مقدار پیش‌فرض برای متن
DEFAULT فقط برای اعداد نیست.
مثلاً:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        role TEXT DEFAULT 'user'
    )
    """
)

حالا:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

مقدار role به‌صورت خودکار:

user

خواهد بود.
🔹 CHECK
با CHECK می‌توانیم مشخص کنیم یک مقدار باید یک شرط مشخص را رعایت کند.
مثلاً فرض کنیم امتیاز دانش‌آموز باید بین 0 و 20 باشد:

cursor.execute(
    """
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        score INTEGER CHECK(score >= 0 AND score <= 20)
    )
    """
)

این مقدار معتبر است:

cursor.execute(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    ("Ali", 18)
)

اما این مقدار:

cursor.execute(
    "INSERT INTO students (name, score) VALUES (?, ?)",
    ("Sara", 25)
)

باعث IntegrityError می‌شود؛ چون:

25 >= 0  → True
25 <= 20 → False

پس شرط CHECK برقرار نیست.
🔹 چند Constraint در یک ستون
می‌توانیم چند Constraint را ترکیب کنیم:

cursor.execute(
    """
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        price REAL NOT NULL CHECK(price >= 0)
    )
    """
)

اینجا:

id    → PRIMARY KEY
name  → NOT NULL + UNIQUE
price → NOT NULL + CHECK

بنابراین:
نام محصول نباید NULL باشد.
نام محصول نباید تکراری باشد.
قیمت نباید NULL باشد.
قیمت نباید منفی باشد.
🔥 یک مثال کاربردی
فرض کنیم جدول حساب‌های بانکی داریم:

cursor.execute(
    """
    CREATE TABLE accounts (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        balance REAL DEFAULT 0 CHECK(balance >= 0)
    )
    """
)

حالا:

cursor.execute(
    "INSERT INTO accounts (username) VALUES (?)",
    ("ali",)
)

چون balance مشخص نشده، مقدار آن:

0

خواهد بود.
اما این مقدار معتبر نیست:

cursor.execute(
    "INSERT INTO accounts (username, balance) VALUES (?, ?)",
    ("sara", -100)
)

چون:

balance >= 0

برقرار نیست.
⚠️ نکته مهم
DEFAULT با NOT NULL متفاوت است.
مثلاً:

age INTEGER DEFAULT 18

یعنی اگر مقدار age ارسال نشود، مقدار 18 استفاده شود.
اما:

age INTEGER NOT NULL

یعنی مقدار NULL برای age مجاز نیست.
می‌توانیم هر دو را با هم داشته باشیم:

age INTEGER NOT NULL DEFAULT 18

🧠 خلاصه

PRIMARY KEY → شناسه اصلی و یکتا
NOT NULL    → جلوگیری از NULL
UNIQUE      → جلوگیری از مقدار تکراری
DEFAULT     → مقدار پیش‌فرض
CHECK       → بررسی یک شرط روی مقدار

مثلاً:

price REAL NOT NULL DEFAULT 0 CHECK(price >= 0)

یعنی:

price
 ↓
نباید NULL باشد
 ↓
اگر مقدار نداشت → 0
 ↓
نباید منفی باشد

🎯 تمرین
جدول products را طوری کامل کن که:
id کلید اصلی باشد.
name اجباری و یکتا باشد.
price اجباری باشد.
اگر قیمت مشخص نشد، 0 قرار بگیرد.
قیمت منفی مجاز نباشد.

cursor.execute(
    """
    CREATE TABLE products (
        id ______,
        name TEXT ______ ______,
        price REAL ______ ______ ______
    )
    """
)

Constraintهای مناسب را در جای خالی قرار بده.
پست ۲۰۲ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۳
ارتباط بین جدول‌ها با FOREIGN KEY
تا اینجا Constraintهای مختلفی را یاد گرفتیم:

PRIMARY KEY
NOT NULL
UNIQUE
DEFAULT
CHECK

حالا می‌رسیم به یکی از مهم‌ترین بخش‌های Database:

FOREIGN KEY

FOREIGN KEY برای ایجاد ارتباط بین جدول‌ها استفاده می‌شود.
🔹 یک مثال ساده
فرض کنیم دو جدول داریم:

users
orders

هر سفارش متعلق به یک کاربر است.
جدول کاربران:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
    """
)

جدول سفارش‌ها:

cursor.execute(
    """
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """
)

اینجا:

users.id
   ↑
   │
orders.user_id

یعنی user_id در جدول orders به id در جدول users اشاره می‌کند.
🔹 درج داده معتبر
ابتدا یک کاربر ایجاد می‌کنیم:

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

فرض کنیم id کاربر 1 شده است.
حالا می‌توانیم سفارش او را ثبت کنیم:

cursor.execute(
    "INSERT INTO orders (user_id) VALUES (?)",
    (1,)
)

این ارتباط معتبر است:

User 1
  ↓
Order 1

⚠️ چرا Foreign Key مهم است؟
فرض کنیم کاربری با id = 999 وجود ندارد.
اگر بخواهیم:

cursor.execute(
    "INSERT INTO orders (user_id) VALUES (?)",
    (999,)
)

در یک Database با Foreign Key فعال، این عملیات مجاز نیست؛ چون کاربر 999 وجود ندارد.
این کار از ایجاد رکوردهای بدون مرجع معتبر جلوگیری می‌کند.
🔹 فعال کردن Foreign Key در SQLite
یک نکته بسیار مهم درباره SQLite وجود دارد.
برای فعال بودن بررسی Foreign Key باید آن را برای Connection فعال کنیم:

import sqlite3

connection = sqlite3.connect("app.db")

connection.execute("PRAGMA foreign_keys = ON")

بعد از آن می‌توانیم جدول‌ها را ایجاد کنیم.
🔹 مثال کامل

import sqlite3

connection = sqlite3.connect("app.db")
connection.execute("PRAGMA foreign_keys = ON")

cursor = connection.cursor()

cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
    """
)

cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """
)

cursor.execute(
    "INSERT INTO users (name) VALUES (?)",
    ("Ali",)
)

user_id = cursor.lastrowid

cursor.execute(
    "INSERT INTO orders (user_id) VALUES (?)",
    (user_id,)
)

connection.commit()

در این مثال:

users
  ↓
id = 1

orders
  ↓
user_id = 1

🔹 Foreign Key و JOIN
بعداً می‌توانیم اطلاعات دو جدول را با JOIN کنار هم قرار دهیم.
مثلاً:

cursor.execute(
    """
    SELECT users.name, orders.id
    FROM orders
    JOIN users ON orders.user_id = users.id
    """
)

rows = cursor.fetchall()

for row in rows:
    print(row)

خروجی:

('Ali', 1)

یعنی سفارش را به کاربر مربوط به آن متصل کرده‌ایم.
🧠 خلاصه

PRIMARY KEY
      ↓
شناسه اصلی جدول

FOREIGN KEY
      ↓
اشاره به رکورد جدول دیگر

مثلاً:

FOREIGN KEY (user_id)
REFERENCES users(id)

یعنی:

مقدار

user_id

باید به یک

id

معتبر در جدول

users

اشاره کند.

و در SQLite بهتر است صریحاً فعال کنیم:

connection.execute("PRAGMA foreign_keys = ON")

🎯 تمرین
دو جدول زیر را کامل کن:

import sqlite3

connection = sqlite3.connect("school.db")
connection.execute("PRAGMA foreign_keys = ON")

cursor = connection.cursor()

cursor.execute(
    """
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
    """
)

cursor.execute(
    """
    CREATE TABLE courses (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        student_id INTEGER,
        ______________________________
    )
    """
)

طوری FOREIGN KEY را تعریف کن که:

courses.student_id
        ↓
students.id

را مرجع قرار دهد.
پست ۲۰۳ از دوره جامع Python 🐍