🐍 آموزش جامع 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 🐍

🐍 آموزش جامع Python — پست ۲۰۴
رفتار FOREIGN KEY هنگام حذف و تغییر رکورد
در پست قبل با FOREIGN KEY آشنا شدیم.
حالا می‌خواهیم ببینیم اگر رکورد اصلی حذف یا تغییر کند، رکورد وابسته چه اتفاقی می‌افتد.
برای این کار از:

ON DELETE
ON UPDATE

استفاده می‌کنیم.
🔹 ON DELETE CASCADE
اگر کاربر حذف شود، رکوردهای وابسته او نیز حذف می‌شوند:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
)

مثلاً:

User 1
 ├── Order 10
 └── Order 11

Delete User 1
      ↓
Order 10 ❌
Order 11 ❌

🔹 ON DELETE SET NULL
اگر نمی‌خواهیم سفارش حذف شود، می‌توانیم ارتباط آن را NULL کنیم:

FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE SET NULL

پس:

User 1 حذف می‌شود
       ↓
Order باقی می‌ماند
       ↓
user_id = NULL

⚠️ در این حالت user_id نباید NOT NULL باشد.
🔹 ON DELETE RESTRICT
می‌توانیم حذف کاربر را تا زمانی که رکورد وابسته وجود دارد، ممنوع کنیم:

FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT

اگر کاربر سفارش داشته باشد:

Delete User
     ↓
❌ حذف مجاز نیست

🔹 ON UPDATE CASCADE
اگر مقدار کلید اصلی والد تغییر کند، مقدار Foreign Key وابسته نیز تغییر می‌کند:

FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON UPDATE CASCADE

مثلاً:

users.id = 100
orders.user_id = 100

100 → 200

users.id = 200
orders.user_id = 200

⚠️ نکته مهم در SQLite
برای فعال بودن Foreign Key باید آن را روی Connection فعال کنیم:

connection.execute("PRAGMA foreign_keys = ON")

🧠 خلاصه

ON DELETE CASCADE
→ حذف رکوردهای وابسته

ON DELETE SET NULL
→ حفظ رکورد وابسته و قرار دادن Foreign Key روی NULL

ON DELETE RESTRICT
→ جلوگیری از حذف رکورد والد

ON UPDATE CASCADE
→ به‌روزرسانی Foreign Key هنگام تغییر کلید والد

🎯 تمرین
کد زیر را کامل کن تا با حذف کاربر، سفارش‌های او هم حذف شوند:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    ______________________________
)

همچنین:

connection.execute("PRAGMA foreign_keys = ON")

را فراموش نکن.
پست ۲۰۴ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۵
بررسی ساختار جدول با PRAGMA table_info()
تا اینجا یاد گرفتیم چطور جدول بسازیم و Constraintهایی مثل:

PRIMARY KEY
NOT NULL
UNIQUE
DEFAULT
CHECK
FOREIGN KEY

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

PRAGMA table_info()

استفاده کنیم.
🔹 بررسی ساختار جدول
فرض کنیم جدول زیر را داریم:

cursor.execute(
    """
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        age INTEGER DEFAULT 18
    )
    """
)

حالا:

cursor.execute("PRAGMA table_info(users)")

columns = cursor.fetchall()

for column in columns:
    print(column)

خروجی شامل اطلاعات مربوط به ستون‌های جدول خواهد بود.
مثلاً:

(0, 'id', 'INTEGER', 0, None, 1)
(1, 'username', 'TEXT', 1, None, 0)
(2, 'age', 'INTEGER', 0, '18', 0)

🔹 معنی اطلاعات خروجی
هر ردیف اطلاعات یک ستون را نشان می‌دهد.
ساختار خروجی table_info به شکل کلی:

(cid, name, type, notnull, dflt_value, pk)

یعنی:

cid         → شماره ستون
name        → نام ستون
type        → نوع داده
notnull     → آیا NOT NULL است؟
dflt_value  → مقدار پیش‌فرض
pk          → آیا PRIMARY KEY است؟

مثلاً:

(1, 'username', 'TEXT', 1, None, 0)

یعنی:

name     → username
type     → TEXT
notnull  → 1
pk       → 0

پس username دارای NOT NULL است و Primary Key نیست.
🔹 بررسی یک جدول با Python
می‌توانیم اطلاعات را خواناتر نمایش دهیم:

cursor.execute("PRAGMA table_info(users)")

for column in cursor.fetchall():
    cid, name, data_type, not_null, default_value, primary_key = column

    print(
        f"Name: {name}, "
        f"Type: {data_type}, "
        f"NOT NULL: {bool(not_null)}, "
        f"Default: {default_value}, "
        f"Primary Key: {bool(primary_key)}"
    )

خروجی تقریباً:

Name: id, Type: INTEGER, NOT NULL: False, Default: None, Primary Key: True
Name: username, Type: TEXT, NOT NULL: True, Default: None, Primary Key: False
Name: age, Type: INTEGER, NOT NULL: False, Default: 18, Primary Key: False

🔹 چرا PRAGMA مهم است؟
فرض کنید برنامه‌ای داریم که Database آن قبلاً ساخته شده است.
به‌جای اینکه حدس بزنیم جدول چه ساختاری دارد، می‌توانیم آن را از خود SQLite بررسی کنیم.
مثلاً:

def show_table_structure(cursor, table_name):
    cursor.execute(f"PRAGMA table_info({table_name})")

    for column in cursor.fetchall():
        print(column)

حالا:

show_table_structure(cursor, "users")

⚠️ نکته مهم درباره نام جدول
در مثال بالا table_name مستقیماً داخل SQL قرار گرفته است:

f"PRAGMA table_info({table_name})"

برای مقادیر باید از Placeholder استفاده کنیم:

WHERE name = ?

اما نام جدول و ستون در SQLite به شکل معمول با ? به‌عنوان parameter قابل جایگزینی نیست.
بنابراین اگر نام جدول از ورودی کاربر می‌آید، نباید آن را بدون اعتبارسنجی مستقیماً وارد SQL کنیم.
🔹 مثال کاربردی
می‌توانیم بررسی کنیم آیا یک ستون وجود دارد یا نه:

cursor.execute("PRAGMA table_info(users)")

columns = cursor.fetchall()

column_names = [column[1] for column in columns]

if "username" in column_names:
    print("username exists")

خروجی:

username exists

این روش در ابزارهای مدیریت Database و Migrationها می‌تواند مفید باشد.
🧠 خلاصه

PRAGMA table_info(table_name)
             ↓
بررسی ساختار جدول
             ↓
نام ستون
نوع داده
NOT NULL
DEFAULT
PRIMARY KEY

ساختار هر ستون:

cid
name
type
notnull
dflt_value
pk

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

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

با PRAGMA table_info(products) برنامه‌ای بنویس که برای هر ستون این اطلاعات را چاپ کند:

نام ستون
نوع داده
NOT NULL
PRIMARY KEY
DEFAULT

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

❌ پایان پروژه CodeBox
دوستان، پروژه CodeBox | Telegram Coding Lab به‌طور کامل متوقف و از دسترس خارج خواهد شد.
بعد از بررسی معماری و ریسک‌های امنیتی پروژه، تصمیم گرفتم ادامه این پروژه در شکل فعلی منطقی نیست. به‌خصوص برای پروژه‌ای که هدف آن فراهم کردن محیط کدنویسی و در آینده اجرای کد کاربران بود، امنیت و ایزوله‌سازی مناسب اهمیت بسیار زیادی دارد.
به همین دلیل ترجیح دادم به‌جای ادامه دادن با یک معماری که از نظر امنیتی به اندازه کافی مطمئن نیست، پروژه را به‌طور کامل کنار بگذارم.
🔐 امنیت کاربران و اطلاعات همیشه اولویت دارد.
از تمام دوستانی که در مدت کوتاه فعالیت CodeBox از ربات استفاده کردند یا پروژه را دنبال کردند، ممنونم.
این پروژه به پایان رسید؛ اما تجربه‌ای که از ساخت و طراحی آن به دست آمد، در پروژه‌های آینده استفاده خواهد شد.
CodeBox — Project Closed.

🐍 آموزش جامع Python — پست ۲۰۶
آشنایی با Index در SQLite | CREATE INDEX
وقتی تعداد رکوردهای یک جدول زیاد می‌شود، جست‌وجو ممکن است کندتر شود.
SQLite برای سریع‌تر کردن بعضی جست‌وجوها از Index استفاده می‌کند.
🔹 ساخت Index
فرض کنیم جدول کاربران را داریم:

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

اگر زیاد بر اساس username جست‌وجو کنیم، می‌توانیم برای آن Index بسازیم:

cursor.execute(
    "CREATE INDEX idx_users_username ON users(username)"
)

ساختار کلی:

CREATE INDEX index_name
ON table_name(column_name);

🔹 جست‌وجو با Index
حالا می‌توانیم مثل قبل جست‌وجو کنیم:

cursor.execute(
    "SELECT * FROM users WHERE username = ?",
    ("ali",)
)

users = cursor.fetchall()

نیازی نیست در دستور SELECT نام Index را مشخص کنیم؛ SQLite می‌تواند در صورت مناسب بودن از آن استفاده کند.
🔹 Index برای چند ستون
گاهی جست‌وجو بر اساس چند ستون انجام می‌شود:

cursor.execute(
    """
    CREATE INDEX idx_users_name_age
    ON users(username, age)
    """
)

این Index برای جست‌وجوهایی که با username شروع می‌شوند، می‌تواند مفید باشد؛ مثلاً:

SELECT * FROM users
WHERE username = ? AND age = ?;

اما لزوماً برای جست‌وجویی که فقط بر اساس age است، به همان اندازه مفید نیست.
🔹 جلوگیری از Index تکراری
برای اینکه اگر Index از قبل وجود داشت خطا نگیریم:

cursor.execute(
    """
    CREATE INDEX IF NOT EXISTS idx_users_username
    ON users(username)
    """
)

⚠️ نکته مهم
Index همیشه مفید نیست:
جست‌وجو و فیلتر بعضی ستون‌ها را سریع‌تر می‌کند.
فضای ذخیره‌سازی بیشتری مصرف می‌کند.
درج، حذف و تغییر داده‌ها را ممکن است کمی کندتر کند.
همچنین اگر ستونی از قبل PRIMARY KEY یا UNIQUE باشد، SQLite معمولاً برای اعمال این محدودیت‌ها Index مربوط را ایجاد می‌کند؛ بنابراین Index مشابه و غیرضروری نساز.
🧠 خلاصه

CREATE INDEX → ایجاد Index
IF NOT EXISTS → جلوگیری از خطا در صورت وجود Index

🎯 تمرین
برای جدول products یک Index با نام idx_products_name روی ستون name بساز.

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

# کد ساخت Index را اینجا بنویس

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

🐍 آموزش جامع Python — پست ۲۰۷
حذف و بررسی Index در SQLite
در پست قبل یاد گرفتیم با CREATE INDEX جست‌وجو در بعضی ستون‌ها را سریع‌تر کنیم.
حالا می‌خواهیم یاد بگیریم چطور Indexها را بررسی و حذف کنیم و بفهمیم SQLite از چه Indexهایی استفاده می‌کند.
🔹 حذف Index با DROP INDEX
اگر دیگر به یک Index نیاز نداشته باشیم، می‌توانیم آن را حذف کنیم:

cursor.execute(
    "DROP INDEX idx_users_username"
)

برای جلوگیری از خطا در صورت نبودن Index:

cursor.execute(
    "DROP INDEX IF EXISTS idx_users_username"
)

⚠️ حذف Index، ستون یا رکوردهای جدول را حذف نمی‌کند؛ فقط خود Index حذف می‌شود.
🔹 مشاهده Indexهای یک جدول
برای بررسی Indexهای جدول users از PRAGMA index_list() استفاده می‌کنیم:

cursor.execute("PRAGMA index_list(users)")

for index in cursor.fetchall():
    print(index)

این دستور اطلاعات Indexهای جدول را نمایش می‌دهد؛ از جمله نام Index و اینکه آیا یکتا است یا نه.
🔹 بررسی ستون‌های یک Index
با PRAGMA index_info() می‌توانیم ببینیم یک Index روی کدام ستون‌ها ساخته شده است:

cursor.execute(
    "PRAGMA index_info(idx_users_username)"
)

for column in cursor.fetchall():
    print(column)

برای مثال، اگر Index روی username باشد، اطلاعات همان ستون نمایش داده می‌شود.
🔹 بررسی استفاده از Index با EXPLAIN QUERY PLAN
می‌توانیم از SQLite بپرسیم برای اجرای یک Query چه برنامه‌ای انتخاب کرده است:

cursor.execute(
    """
    EXPLAIN QUERY PLAN
    SELECT * FROM users
    WHERE username = ?
    """,
    ("ali",)
)

for row in cursor.fetchall():
    print(row)

در خروجی ممکن است عبارتی مانند SEARCH یا SCAN ببینیم.
SEARCH معمولاً یعنی SQLite از یک روش جست‌وجوی هدفمند استفاده می‌کند.
SCAN یعنی SQLite رکوردها یا بخش بزرگی از آن‌ها را پیمایش می‌کند.
این‌ها به‌تنهایی معیار قطعی سرعت نیستند؛ برای ارزیابی عملکرد باید Query و داده‌های واقعی را هم در نظر بگیریم.
🧠 خلاصه

DROP INDEX          → حذف Index
PRAGMA index_list() → فهرست Indexهای جدول
PRAGMA index_info() → بررسی ستون‌های Index
EXPLAIN QUERY PLAN  → مشاهده برنامه اجرای Query

🎯 تمرین
فرض کن Index زیر را ساخته‌ای:

cursor.execute(
    """
    CREATE INDEX IF NOT EXISTS idx_products_name
    ON products(name)
    """
)

حالا کدی بنویس که:
Indexهای جدول products را نمایش دهد.
مشخص کند Index idx_products_name روی کدام ستون ساخته شده است.
در پایان، همان Index را با DROP INDEX IF EXISTS حذف کند.
پست ۲۰۷ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۸
دریافت رکوردها به شکل دیکشنری با sqlite3.Row
تا اینجا با fetchone() و fetchall() آشنا شدیم. به‌صورت پیش‌فرض، نتیجه Queryها معمولاً به شکل Tuple برمی‌گردد.
مثلاً:

cursor.execute(
    "SELECT id, username, age FROM users"
)

user = cursor.fetchone()

print(user[1])

اینجا برای دسترسی به username باید شماره ستون را بدانیم.
اما راه خواناتری هم وجود دارد: sqlite3.Row.
🔹 فعال کردن sqlite3.Row
کافی است row_factory اتصال را تنظیم کنیم:

import sqlite3

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

cursor = connection.cursor()

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

cursor.execute(
    "SELECT id, username, age FROM users"
)

user = cursor.fetchone()

if user is not None:
    print(user["username"])
    print(user["age"])

دیگر لازم نیست به خاطر بسپاریم username در کدام موقعیت قرار دارد.
🔹 دریافت همه رکوردها

cursor.execute(
    "SELECT id, username, age FROM users"
)

users = cursor.fetchall()

for user in users:
    print(user["id"], user["username"])

این روش برای نمایش اطلاعات کاربران، محصولات و سفارش‌ها خواناتر است.
🔹 دسترسی با شماره ستون هم ممکن است
sqlite3.Row همچنان امکان دسترسی با Index را فراهم می‌کند:

print(user[0])
print(user["username"])

هر دو روش قابل استفاده‌اند.
⚠️ نکته مهم
نامی که داخل براکت قرار می‌دهیم باید با نام یکی از ستون‌های نتیجه Query مطابقت داشته باشد.
مثلاً:

SELECT username FROM users

در این نتیجه می‌توانیم بنویسیم:

user["username"]

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

connection.row_factory = sqlite3.Row

بعد از آن:

user["username"]
user["age"]

به‌جای وابستگی به شماره ستون‌ها، از نام آن‌ها استفاده می‌کنیم.
🎯 تمرین
جدول products را در نظر بگیر:

id | name | price

اتصال را طوری تنظیم کن که نتیجه Query به شکل sqlite3.Row باشد. سپس تمام محصولات را بخوان و برای هر محصول، نام و قیمت را با استفاده از نام ستون‌ها چاپ کن.
پست ۲۰۸ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۰۹
مدیریت تراکنش‌ها با with در SQLite
در پست‌های قبل یاد گرفتیم با commit() تغییرات را ذخیره کنیم و با rollback() تغییرات ذخیره‌نشده را لغو کنیم.
اما Python راهی دارد که مدیریت تراکنش‌ها را ساده‌تر می‌کند: استفاده از with روی Connection.
🔹 استفاده از with

import sqlite3

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

with connection:
    connection.execute(
        "INSERT INTO users (username) VALUES (?)",
        ("ali",)
    )

در این ساختار، اگر بلوک بدون خطا تمام شود، تراکنش Commit می‌شود.
اگر خطایی رخ دهد، تغییرات تراکنش Rollback می‌شوند.
🔹 مثال با خطا

import sqlite3

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

try:
    with connection:
        connection.execute(
            "INSERT INTO users (username) VALUES (?)",
            ("sara",)
        )

        connection.execute(
            "INSERT INTO users (username) VALUES (?)",
            (None,)
        )

except sqlite3.IntegrityError:
    print("Transaction rolled back")

connection.close()

فرض می‌کنیم ستون username دارای NOT NULL است.
دستور اول اجرا می‌شود، اما دستور دوم خطا ایجاد می‌کند. در نتیجه تغییرات تراکنش لغو می‌شوند و درج اول هم در آن تراکنش باقی نمی‌ماند.
⚠️ نکته مهم
with connection: اتصال را به‌صورت خودکار نمی‌بندد؛ فقط تراکنش را مدیریت می‌کند.
برای بستن اتصال باید از این دستور استفاده کنیم:

connection.close()

اگر بخواهیم Connection هم به‌صورت خودکار بسته شود، می‌توانیم از contextlib.closing() استفاده کنیم:

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("app.db")) as connection:
    with connection:
        connection.execute(
            "INSERT INTO users (username) VALUES (?)",
            ("reza",)
        )

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

with connection:
    ...

پایان موفق بلوک → COMMIT
وقوع خطا → ROLLBACK
بستن اتصال → باید جداگانه مدیریت شود.
🎯 تمرین
برنامه‌ای بنویس که دو رکورد را داخل یک تراکنش درج کند. اگر درج رکورد دوم خطا داد، درج رکورد اول نیز لغو شود. برای مدیریت تراکنش از with connection: استفاده کن.
پست ۲۰۹ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۰
مدیریت خطاهای SQLite با sqlite3.Error
در پروژه‌های واقعی، ممکن است هنگام کار با Database خطاهایی مثل نقض Constraint، اشتباه در دستور SQL یا مشکل اتصال رخ دهد.
برای مدیریت این خطاها می‌توانیم از Exceptionهای ماژول sqlite3 استفاده کنیم.
🔹 مدیریت خطای عمومی SQLite
کلاس پایه بیشتر خطاهای SQLite، sqlite3.Error است:

import sqlite3

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

try:
    connection.execute("SELECT * FROM unknown_table")

except sqlite3.Error as error:
    print("Database error:", error)

finally:
    connection.close()

چون جدول unknown_table وجود ندارد، خطا مدیریت می‌شود و اتصال در بخش finally بسته می‌شود.
🔹 تفاوت IntegrityError و OperationalError
هر خطا معنای متفاوتی دارد.
۱. IntegrityError
وقتی محدودیت‌های داده نقض شوند، مثلاً مقدار تکراری برای یک ستون UNIQUE:

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

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

۲. OperationalError
ممکن است به دلیل مشکلات عملیاتی، مانند وجود نداشتن جدول، رخ دهد:

try:
    connection.execute("SELECT * FROM unknown_table")

except sqlite3.OperationalError as error:
    print("Operation failed:", error)

OperationalError فقط مخصوص جدول ناموجود نیست و می‌تواند دلایل دیگری هم داشته باشد.
🔹 مدیریت تراکنش هنگام خطا
اگر چند عملیات باید با هم موفق شوند، بهتر است آن‌ها را داخل یک تراکنش قرار دهیم:

import sqlite3

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

try:
    with connection:
        connection.execute(
            "INSERT INTO users (username) VALUES (?)",
            ("sara",)
        )

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

except sqlite3.Error as error:
    print("Database operation failed:", error)

finally:
    connection.close()

اگر یکی از دستورها خطا بدهد، تغییرات تراکنش لغو می‌شوند.
⚠️ نکته مهم
برای مدیریت خطاهای SQLite معمولاً از این ساختار استفاده می‌کنیم:

try:
    # عملیات Database
    ...
except sqlite3.Error as error:
    # مدیریت خطا
    ...

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

sqlite3.Error
    ├── IntegrityError
    ├── OperationalError
    └── سایر خطاهای SQLite

IntegrityError → نقض محدودیت‌های داده
OperationalError → مشکلات عملیاتی
finally → اجرای عملیات پایانی، مثل بستن اتصال
🎯 تمرین
برنامه‌ای بنویس که:
به Database متصل شود.
یک دستور SELECT روی جدولی ناموجود اجرا کند.
خطا را با sqlite3.Error مدیریت کند.
اتصال را در finally ببندد.
پست ۲۱۰ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۱
استفاده از executemany() برای عملیات دسته‌ای در SQLite
در پست‌های قبل یاد گرفتیم با execute() یک دستور SQL را اجرا کنیم.
اما اگر بخواهیم تعداد زیادی رکورد را وارد Database کنیم، می‌توانیم از executemany() استفاده کنیم.
🔹 مثال ساده
فرض کنیم جدول کاربران را داریم:

import sqlite3

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

connection.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL,
        age INTEGER
    )
""")

حالا چند کاربر را با یک فراخوانی درج می‌کنیم:

users = [
    ("ali", 25),
    ("sara", 22),
    ("reza", 30)
]

connection.executemany(
    "INSERT INTO users (username, age) VALUES (?, ?)",
    users
)

connection.commit()
connection.close()

هر Tuple داده‌های یک رکورد را مشخص می‌کند.
🔹 تفاوت execute() و executemany()
با execute():

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

با executemany():

cursor.executemany(
    "INSERT INTO users (username, age) VALUES (?, ?)",
    [
        ("ali", 25),
        ("sara", 22),
        ("reza", 30)
    ]
)

پس:
execute() برای اجرای یک دستور با مجموعه پارامترهای مشخص استفاده می‌شود.
executemany() همان دستور را برای چند مجموعه پارامتر اجرا می‌کند.
🔹 استفاده همراه با تراکنش
اگر بخواهیم درج همه رکوردها در یک تراکنش انجام شود:

with connection:
    connection.executemany(
        "INSERT INTO users (username, age) VALUES (?, ?)",
        users
    )

اگر اجرای تراکنش با خطا مواجه شود، تغییرات انجام‌شده در همان تراکنش Rollback می‌شوند.
⚠️ نکته مهم
همیشه از Placeholderهای ? برای ارسال مقادیر استفاده کن؛ مقادیر را مستقیماً داخل رشته SQL قرار نده.
همچنین executemany() تضمین نمی‌کند که هر عملیات در همه شرایط سریع‌تر از اجرای جداگانه باشد؛ مزیت آن معمولاً ساده‌تر شدن درج دسته‌ای و امکان مدیریت یک‌جای تراکنش است.
🧠 خلاصه

execute()      → اجرای دستور با یک مجموعه پارامتر
executemany()  → اجرای دستور با چند مجموعه پارامتر
commit()       → ثبت تغییرات
rollback()     → لغو تغییرات تراکنش

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

products = [
    ("Keyboard", 50),
    ("Mouse", 25),
    ("Monitor", 200)
]

با executemany() هر سه محصول را در جدولی با ستون‌های name و price ذخیره کن و در پایان تغییرات را ثبت کن.
پست ۲۱۱ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۲
دریافت شناسه رکورد جدید با lastrowid
گاهی بعد از درج یک رکورد در SQLite، لازم داریم بدانیم چه idای به آن اختصاص داده شده است.
برای این کار می‌توانیم از ویژگی lastrowid استفاده کنیم.
🔹 مثال ساده

import sqlite3

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

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

cursor.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    ("Keyboard", 50)
)

connection.commit()

print(cursor.lastrowid)

connection.close()

اگر این اولین رکورد باشد، خروجی معمولاً چنین است:

1

SQLite شناسه رکورد درج‌شده را از طریق lastrowid در اختیار ما قرار می‌دهد.
🔹 استفاده از شناسه برای عملیات بعدی
مثلاً می‌توانیم شناسه محصول جدید را ذخیره کنیم:

cursor.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    ("Mouse", 25)
)

product_id = cursor.lastrowid

connection.commit()

print("New product ID:", product_id)

این شناسه می‌تواند برای ثبت اطلاعات مرتبط با محصول در جدول دیگری استفاده شود.
🔹 استفاده در جدول مرتبط
فرض کنیم جدولی برای ثبت رویدادهای محصولات داریم:

cursor.execute("""
    CREATE TABLE IF NOT EXISTS product_logs (
        id INTEGER PRIMARY KEY,
        product_id INTEGER,
        action TEXT
    )
""")

حالا محصول را درج می‌کنیم و شناسه‌اش را می‌گیریم:

cursor.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    ("Monitor", 200)
)

product_id = cursor.lastrowid

cursor.execute(
    "INSERT INTO product_logs (product_id, action) VALUES (?, ?)",
    (product_id, "created")
)

connection.commit()

به این ترتیب، رویداد به محصول درست مرتبط می‌شود.
⚠️ نکته مهم
lastrowid برای گرفتن شناسه درج‌شده توسط یک عملیات مناسب است؛ اما برای درج دسته‌ای با executemany() نباید به آن به‌عنوان فهرست شناسه تمام رکوردها تکیه کنیم.
همچنین اگر از ستون INTEGER PRIMARY KEY استفاده کنیم، SQLite معمولاً شناسه را خودکار تعیین می‌کند؛ مگر اینکه خودمان مقدار آن را مشخص کرده باشیم.
🧠 خلاصه

cursor.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    ("Keyboard", 50)
)

product_id = cursor.lastrowid

lastrowid شناسه آخرین رکورد درج‌شده توسط آن Cursor را در شرایط پشتیبانی‌شده برمی‌گرداند.
🎯 تمرین
جدول students را با ستون‌های id و name بساز. سپس دانشجویی با نام Ali درج کن و شناسه او را با lastrowid دریافت و چاپ کن.
پست ۲۱۲ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۳
ساختار پروژه با SQLite | جداسازی Database از منطق برنامه
تا اینجا یاد گرفتیم چطور با SQLite داده‌ها را ذخیره، جست‌وجو، ویرایش و حذف کنیم.
در پروژه‌های بزرگ‌تر، بهتر است تمام کدهای Database را داخل یک فایل یا تابع اصلی قرار ندهیم.
می‌توانیم مسئولیت‌ها را جدا کنیم تا کد خواناتر، قابل‌تست‌تر و قابل‌نگهداری‌تر شود.
🔹 ساختار پیشنهادی پروژه

project/
├── main.py
└── database.py

database.py برای عملیات مربوط به Database
main.py برای اجرای برنامه و منطق اصلی
🔹 فایل database.py

import sqlite3

DB_NAME = "app.db"


def connect():
    return sqlite3.connect(DB_NAME)


def create_table():
    with connect() as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                username TEXT NOT NULL UNIQUE
            )
        """)


def add_user(username):
    with connect() as connection:
        connection.execute(
            "INSERT INTO users (username) VALUES (?)",
            (username,)
        )


def get_users():
    with connect() as connection:
        return connection.execute(
            "SELECT id, username FROM users ORDER BY id"
        ).fetchall()

در این فایل، عملیات ساخت جدول، افزودن کاربر و دریافت کاربران قرار دارد.
🔹 فایل main.py

import database

database.create_table()

database.add_user("ali")

users = database.get_users()

for user in users:
    print(user)

خروجی نمونه:

(1, 'ali')

شناسه به شرایط Database بستگی دارد؛ اگر رکوردهای دیگری وجود داشته باشند، ممکن است متفاوت باشد.
🔹 مزیت این ساختار چیست؟
اگر بعداً بخواهیم روش ذخیره‌سازی یا Queryها را تغییر دهیم، لازم نیست تمام منطق برنامه را جست‌وجو و اصلاح کنیم.
همچنین می‌توانیم توابع Database را جداگانه تست کنیم.
⚠️ نکته مهم
در این مثال، with connection: تراکنش را مدیریت می‌کند، اما اتصال را نمی‌بندد. برای پروژه‌های واقعی باید چرخه عمر اتصال را نیز مشخص کنیم.
برای نمونه‌های کوچک، باز کردن و بستن اتصال در هر عملیات می‌تواند ساده باشد؛ در پروژه‌های بزرگ‌تر ممکن است مدیریت اتصال را در یک لایه جداگانه قرار دهیم.
همچنین اجرای دوباره add_user("ali") به‌دلیل UNIQUE بودن username خطا می‌دهد؛ پس باید این حالت را مدیریت کنیم یا از روش درج مناسب دیگری استفاده کنیم.
🧠 خلاصه

main.py
   ↓
فراخوانی توابع Database
   ↓
database.py
   ↓
اجرای Queryهای SQLite

جداسازی مسئولیت‌ها، پایه مهمی برای ساخت پروژه‌های قابل‌نگهداری است.
🎯 تمرین
در فایل database.py تابعی به نام delete_user(user_id) بساز که کاربر را بر اساس شناسه حذف کند. سپس آن را از main.py فراخوانی کن.
برای مقدار user_id از Placeholder یعنی ? استفاده کن.
پست ۲۱۳ از دوره جامع Python 🐍