🐍 آموزش جامع Python — پست ۲۱۵
به‌روزرسانی رکورد تکراری با UPSERT در SQLite
در پست قبل یاد گرفتیم با INSERT OR IGNORE از درج رکورد تکراری جلوگیری کنیم.
اما گاهی می‌خواهیم اگر رکورد از قبل وجود داشت، مقدار آن به‌روزرسانی شود.
برای این کار از ON CONFLICT ... DO UPDATE استفاده می‌کنیم که به آن UPSERT هم می‌گویند.
🔹 مثال ساده
فرض کنیم کد هر محصول باید یکتا باشد:

import sqlite3

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

connection.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        code TEXT UNIQUE,
        price REAL
    )
""")

connection.execute("""
    INSERT INTO products (code, price)
    VALUES (?, ?)
    ON CONFLICT(code)
    DO UPDATE SET price = excluded.price
""", ("P100", 250))

connection.commit()
connection.close()

اگر محصول P100 وجود نداشته باشد، درج می‌شود.
اگر از قبل وجود داشته باشد، قیمت آن به 250 تغییر می‌کند.
🔹 excluded چیست؟
در عبارت زیر:

DO UPDATE SET price = excluded.price

excluded.price به مقدار جدیدی اشاره می‌کند که قرار بود درج شود، اما به‌دلیل تعارض با رکورد موجود، عملیات به‌روزرسانی انجام می‌شود.
مثلاً اگر قیمت قبلی 200 و قیمت جدید 250 باشد، قیمت نهایی 250 خواهد بود.
🔹 به‌روزرسانی چند ستون
می‌توانیم چند ستون را هم‌زمان تغییر دهیم:

INSERT INTO products (code, price)
VALUES (?, ?)
ON CONFLICT(code)
DO UPDATE SET
    price = excluded.price;

در صورت نیاز می‌توانیم ستون‌های دیگری را نیز به بخش SET اضافه کنیم.
⚠️ نکته مهم
ON CONFLICT(code) مشخص می‌کند تعارض مربوط به کدام ستون یکتا است.
ستون code باید دارای محدودیت UNIQUE یا شرایط مناسب برای تشخیص تعارض باشد.
این قابلیت به SQLite نسخه 3.24.0 یا جدیدتر نیاز دارد.
مقادیر ورودی را با ? ارسال کن؛ آن‌ها را مستقیماً داخل SQL قرار نده.
🧠 خلاصه
دستور
رفتار هنگام تعارض
INSERT
معمولاً خطا می‌دهد
INSERT OR IGNORE
رکورد متعارض را نادیده می‌گیرد
ON CONFLICT ... DO UPDATE
رکورد موجود را به‌روزرسانی می‌کند
🎯 تمرین
جدولی به نام users بساز که ستون username آن UNIQUE باشد و ستون age را هم داشته باشد.
برنامه‌ای بنویس که کاربر ali را با سن 20 درج کند. سپس همان نام را با سن 25 دوباره ارسال کند؛ اگر کاربر وجود داشت، سن او را به‌روزرسانی کن.
در پایان، سن کاربر ali را با SELECT نمایش بده.
خروجی مورد انتظار:

25

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

🐍 آموزش جامع Python — پست ۲۱۶
دریافت اطلاعات رکورد درج‌شده با RETURNING در SQLite
گاهی پس از درج یا به‌روزرسانی یک رکورد، می‌خواهیم اطلاعات همان رکورد را بلافاصله دریافت کنیم؛ بدون اینکه یک SELECT جداگانه بنویسیم.
SQLite از نسخه 3.35.0 به بعد، از عبارت RETURNING پشتیبانی می‌کند.
🔹 استفاده از RETURNING در INSERT

import sqlite3

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

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

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

product = cursor.fetchone()

connection.commit()

print(product)

connection.close()

خروجی نمونه:

(1, 'Keyboard', 50.0)

شناسه 1 فقط یک نمونه است؛ مقدار واقعی به وضعیت جدول بستگی دارد.
🔹 دریافت یک ستون مشخص
اگر فقط شناسه را لازم داشته باشیم:

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

product_id = cursor.fetchone()[0]

connection.commit()

print(product_id)

با fetchone() اولین ردیف نتیجه را دریافت می‌کنیم.
🔹 استفاده با UPDATE
RETURNING فقط برای درج نیست؛ می‌توانیم اطلاعات رکورد به‌روزشده را نیز دریافت کنیم:

cursor = connection.execute("""
    UPDATE products
    SET price = ?
    WHERE name = ?
    RETURNING id, name, price
""", (60, "Keyboard"))

updated_product = cursor.fetchone()

connection.commit()

print(updated_product)

اگر محصول پیدا شود، اطلاعات آن برگردانده می‌شود.
اگر هیچ رکوردی با نام Keyboard وجود نداشته باشد، fetchone() مقدار None می‌دهد.
🔹 استفاده با DELETE
حتی هنگام حذف رکورد هم می‌توانیم اطلاعات رکورد حذف‌شده را دریافت کنیم:

cursor = connection.execute("""
    DELETE FROM products
    WHERE name = ?
    RETURNING id, name, price
""", ("Mouse",))

deleted_product = cursor.fetchone()

connection.commit()

print(deleted_product)

این قابلیت برای ثبت گزارش یا نمایش اطلاعات رکورد حذف‌شده مفید است.
⚠️ نکات مهم
RETURNING به SQLite نسخه 3.35.0 یا جدیدتر نیاز دارد.
برای بررسی نسخه SQLite مورد استفاده در Python:

import sqlite3

print(sqlite3.sqlite_version)

اگر چند رکورد تغییر کنند، ممکن است چند ردیف برگردانده شود؛ در این حالت می‌توان از fetchall() استفاده کرد.
ترتیب ردیف‌های خروجی RETURNING تضمین‌شده نیست.
تغییرات را با commit() نهایی کن.
🧠 خلاصه

INSERT ... RETURNING ...
UPDATE ... RETURNING ...
DELETE ... RETURNING ...

با RETURNING می‌توانیم اطلاعات رکوردهای درج‌شده، به‌روزشده یا حذف‌شده را مستقیماً از همان دستور دریافت کنیم.
🎯 تمرین
جدول students را با ستون‌های id، name و score بساز.
سپس:
دانشجویی با نام Sara و نمره 18 درج کن.
با RETURNING شناسه و نام او را دریافت کن.
نمره‌اش را به 20 تغییر بده.
با RETURNING نمره جدید را نمایش بده.
پست ۲۱۶ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۷
تهیه نسخه پشتیبان از SQLite با Connection.backup()
در پروژه‌های واقعی، فقط ذخیره‌کردن اطلاعات کافی نیست؛ باید راهی برای تهیه نسخه پشتیبان (Backup) هم داشته باشیم.
Python در ماژول sqlite3 متدی به نام backup() دارد که می‌تواند از یک Database نسخه پشتیبان تهیه کند.
🔹 مثال ساده
فرض کنیم Database اصلی ما app.db است.

import sqlite3

source = sqlite3.connect("app.db")
destination = sqlite3.connect("app_backup.db")

source.backup(destination)

destination.close()
source.close()

print("Backup completed")

خروجی:

Backup completed

در این مثال، اطلاعات Database اصلی به فایل app_backup.db منتقل می‌شود.
اگر فایل مقصد وجود نداشته باشد، SQLite آن را ایجاد می‌کند.
🔹 چرا از backup() استفاده کنیم؟
روش ساده‌ای مثل کپی‌کردن فایل Database همیشه بهترین انتخاب نیست؛ به‌خصوص وقتی Database در حال استفاده است یا از قابلیت‌هایی مثل WAL استفاده می‌کند.
متد backup() برای تهیه نسخه پشتیبان از Database فعال طراحی شده است و می‌تواند هنگام باز بودن اتصال‌ها نیز استفاده شود.
🔹 تهیه Backup با مدیریت اتصال
می‌توانیم از contextlib.closing استفاده کنیم تا اتصال‌ها در پایان بسته شوند:

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("app.db")) as source:
    with closing(sqlite3.connect("app_backup.db")) as destination:
        source.backup(destination)

print("Backup completed")

این ساختار تضمین می‌کند که اتصال‌ها پس از خروج از بلوک‌ها بسته شوند.
🔹 بازیابی اطلاعات از Backup
برای استفاده از نسخه پشتیبان، می‌توانیم آن را به‌عنوان Database باز کنیم:

import sqlite3

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

users = connection.execute(
    "SELECT name FROM users"
).fetchall()

print(users)

connection.close()

این مثال فرض می‌کند جدول users داخل نسخه پشتیبان وجود دارد.
اگر لازم باشد نسخه پشتیبان را جایگزین Database اصلی کنیم، باید این کار را با دقت انجام دهیم تا اطلاعات جدیدتر از بین نروند.
⚠️ نکات مهم
فایل پشتیبان را ترجیحاً در مسیری جدا از فایل اصلی ذخیره کن.
وجود فایل Backup به‌تنهایی کافی نیست؛ گاهی باید بازیابی آن را هم آزمایش کنی.
اگر برنامه داده‌های مهمی ذخیره می‌کند، نسخه پشتیبان را به‌صورت دوره‌ای تهیه کن.
Backup جایگزین کنترل دسترسی، امنیت فایل‌ها و نگهداری نسخه‌های متعدد نیست.
🧠 خلاصه

source.backup(destination)

یعنی Database مبدأ را در Database مقصد پشتیبان‌گیری کن.
🎯 تمرین
برنامه‌ای بنویس که:
فایل school.db را باز کند.
یک نسخه پشتیبان با نام school_backup.db بسازد.
هر دو اتصال را به‌درستی ببندد.
پیام Backup completed را نمایش دهد.
پست ۲۱۷ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۸
مشاهده جدول‌های Database با sqlite_schema
گاهی لازم داریم بدانیم یک فایل SQLite چه جدول‌ها، Viewها یا Indexهایی دارد.
برای این کار می‌توانیم از جدول سیستمی sqlite_schema استفاده کنیم.
این جدول اطلاعات مربوط به ساختار Database را نگهداری می‌کند.
🔹 نمایش نام جدول‌ها

import sqlite3

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

tables = connection.execute("""
    SELECT name
    FROM sqlite_schema
    WHERE type = 'table'
""").fetchall()

for table in tables:
    print(table[0])

connection.close()

خروجی نمونه:

students
courses
teachers

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

objects = connection.execute("""
    SELECT type, name
    FROM sqlite_schema
    WHERE type IN ('table', 'view')
    ORDER BY type, name
""").fetchall()

for object_type, name in objects:
    print(object_type, name)

خروجی نمونه:

table students
table teachers
view student_scores

🔹 مشاهده دستور ساخت جدول
یکی از کاربردهای مفید sqlite_schema، مشاهده دستور CREATE TABLE است:

result = connection.execute("""
    SELECT sql
    FROM sqlite_schema
    WHERE type = 'table'
      AND name = ?
""", ("students",)).fetchone()

if result:
    print(result[0])
else:
    print("Table not found")

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

indexes = connection.execute("""
    SELECT name
    FROM sqlite_schema
    WHERE type = 'index'
""").fetchall()

for index in indexes:
    print(index[0])

SQLite ممکن است برای بعضی محدودیت‌ها، مانند PRIMARY KEY یا UNIQUE، Indexهایی را به‌صورت خودکار ایجاد کند.
⚠️ نکات مهم
sqlite_schema اطلاعات ساختاری Database را نگهداری می‌کند، نه رکوردهای عادی کاربران.
جدول‌های داخلی SQLite با نام‌هایی مانند sqlite_sequence نیز ممکن است وجود داشته باشند.
نام جدول را در Queryهای این مثال با ? ارسال کردیم؛ اما برای استفاده از نام جدول در یک دستور SQL واقعی، Placeholder معمولی برای نام شناسه‌ها کاربرد ندارد.
🧠 خلاصه

SELECT name
FROM sqlite_schema
WHERE type = 'table';

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

table → جدول
view  → نمای ذخیره‌شده
index → ساختار Index
trigger → تریگر

🎯 تمرین
برنامه‌ای بنویس که:
فایل school.db را باز کند.
نام تمام جدول‌های آن را دریافت کند.
نام هر جدول را در یک خط چاپ کند.
اگر هیچ جدولی وجود نداشت، پیام No tables found را نمایش دهد.
پست ۲۱۸ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۱۹
بهینه‌سازی فضای Database با VACUUM
وقتی رکوردهایی را از SQLite حذف یا داده‌هایی را به‌روزرسانی می‌کنیم، ممکن است فایل Database همچنان فضای استفاده‌نشده‌ای داشته باشد.
برای بازسازی فایل و آزادسازی فضای قابل‌بازیابی، می‌توانیم از دستور VACUUM استفاده کنیم.
🔹 مثال ساده

import sqlite3

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

connection.execute("VACUUM")

connection.close()

print("Database optimized")

این دستور فایل Database را بازسازی می‌کند و می‌تواند اندازه آن را کاهش دهد.
🔹 چه زمانی به VACUUM نیاز داریم؟
فرض کنیم تعداد زیادی رکورد درج کرده‌ایم:

connection.executemany(
    "INSERT INTO students (name) VALUES (?)",
    [("Ali",), ("Sara",), ("Reza",)]
)

connection.commit()

بعد تعدادی رکورد را حذف می‌کنیم:

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

connection.commit()

حذف رکورد لزوماً باعث کوچک‌شدن فوری فایل Database نمی‌شود.
SQLite ممکن است فضای آزادشده را برای درج‌های بعدی دوباره استفاده کند. اگر بخواهیم فایل را فشرده و بازسازی کنیم، VACUUM می‌تواند مفید باشد.
🔹 بررسی اندازه فایل
می‌توانیم اندازه فایل را با pathlib بررسی کنیم:

from pathlib import Path

database = Path("school.db")

if database.exists():
    print("Size:", database.stat().st_size, "bytes")

برای مقایسه، اندازه فایل را قبل و بعد از اجرای VACUUM بررسی کن.
توجه داشته باش که اندازه فایل همیشه کاهش پیدا نمی‌کند؛ نتیجه به وضعیت Database بستگی دارد.
⚠️ نکات مهم
VACUUM را روی اتصال فعال به Database اجرا کن.
این دستور ممکن است زمان‌بر باشد و فضای موقت بیشتری نیاز داشته باشد.
هنگام اجرای آن، محدودیت‌هایی برای هم‌زمانی با سایر عملیات وجود دارد.
اگر تراکنش باز باشد، اجرای VACUUM معمولی ممکن است با خطا مواجه شود؛ ابتدا تراکنش را پایان بده.
برای فایل‌های WAL، مدیریت فضای فایل‌های جانبی و Checkpoint نیز می‌تواند اهمیت داشته باشد.
🧠 خلاصه

VACUUM;

برای بازسازی Database و بازیابی فضای قابل‌آزادسازی استفاده می‌شود؛ اما لازم نیست بعد از هر DELETE آن را اجرا کنیم.
🎯 تمرین
برنامه‌ای بنویس که:
اندازه فایل school.db را قبل از بهینه‌سازی چاپ کند.
دستور VACUUM را اجرا کند.
اندازه فایل را دوباره چاپ کند.
تفاوت اندازه را بررسی کند.
پست ۲۱۹ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۰
تحلیل آمار Queryها با ANALYZE
در پست قبل با VACUUM آشنا شدیم که برای بازسازی فایل Database استفاده می‌شود.
حالا سراغ ابزار دیگری می‌رویم که به بهینه‌سازی اجرای Queryها کمک می‌کند: ANALYZE.
SQLite می‌تواند با جمع‌آوری آمار درباره جدول‌ها و Indexها، تصمیم‌های بهتری برای اجرای Query بگیرد.
🔹 اجرای ANALYZE

import sqlite3

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

connection.execute("ANALYZE")

connection.close()

print("Analysis completed")

این دستور آمار مربوط به Database را جمع‌آوری می‌کند و معمولاً آن‌ها را در جدول‌های آماری داخلی SQLite ذخیره می‌کند.
🔹 چرا آمار مهم است؟
فرض کنیم جدول بزرگی از دانشجویان داریم و روی ستون city یک Index ساخته‌ایم:

CREATE INDEX IF NOT EXISTS idx_students_city
ON students(city);

حالا Query زیر را اجرا می‌کنیم:

SELECT *
FROM students
WHERE city = 'Baku';

SQLite باید تصمیم بگیرد برای پیدا کردن رکوردها از Index استفاده کند یا روش دیگری را انتخاب کند.
آمار جمع‌آوری‌شده توسط ANALYZE می‌تواند به این تصمیم کمک کند.
🔹 بررسی برنامه اجرای Query
برای مشاهده روش انتخاب‌شده توسط SQLite از EXPLAIN QUERY PLAN استفاده می‌کنیم:

plan = connection.execute("""
    EXPLAIN QUERY PLAN
    SELECT *
    FROM students
    WHERE city = ?
""", ("Baku",)).fetchall()

for row in plan:
    print(row)

خروجی بسته به ساختار جدول، Indexها و نسخه SQLite متفاوت است.
اگر Index مناسب باشد، ممکن است در خروجی عبارتی مانند SEARCH و نام Index دیده شود.
🔹 اجرای ANALYZE برای یک جدول
لازم نیست همیشه کل Database را تحلیل کنیم. می‌توانیم آمار یک جدول مشخص را هم به‌روزرسانی کنیم:

ANALYZE students;

این کار برای جمع‌آوری آمار مربوط به جدول students استفاده می‌شود.
⚠️ نکات مهم
ANALYZE خودش Index جدید ایجاد نمی‌کند.
اجرای آن تضمین نمی‌کند که همه Queryها سریع‌تر شوند.
برای Databaseهایی که داده‌هایشان مرتب تغییر می‌کند، به‌روزرسانی آمار ممکن است مفید باشد.
EXPLAIN QUERY PLAN برای بررسی روش اجرای Query است؛ اما زمان اجرای واقعی را اندازه‌گیری نمی‌کند.
🧠 خلاصه

ANALYZE
   ↓
جمع‌آوری آمار Database
   ↓
کمک به انتخاب بهتر Query Plan
   ↓
بررسی با EXPLAIN QUERY PLAN

🎯 تمرین
در Database خود:
روی ستون city جدول students یک Index بساز.
برنامه اجرای Query جست‌وجوی شهر Baku را بررسی کن.
دستور ANALYZE را اجرا کن.
دوباره EXPLAIN QUERY PLAN را اجرا و خروجی‌ها را مقایسه کن.
توجه کن که تغییر خروجی در همه Databaseها تضمین‌شده نیست.
پست ۲۲۰ از دوره جامع Python 🐍

🌸 ۱۱ اکتبر | روز جهانی دختر 💜
امروز روز دخترانی است که رؤیاهای بزرگ دارند، سؤال می‌پرسند، یاد می‌گیرند و برای ساختن آینده‌ای بهتر تلاش می‌کنند.
هر دختر شایسته‌ی فرصت برابر برای آموزش، امنیت، رشد و دنبال‌کردن آرزوهایش است. ✨
بیایید دنیایی بسازیم که در آن هیچ دختری به‌خاطر دختر بودن، از یادگیری، پیشرفت یا رسیدن به اهدافش بازنماند.
💜 دختران امروز، سازندگان فردای جهان‌اند.
به امید روزی که هر دختر بتواند با اعتمادبه‌نفس بگوید:

«من می‌توانم یاد بگیرم، رشد کنم و آینده‌ام را بسازم.» 🌱
🌍 روز جهانی دختر مبارک!

🐍 آموزش جامع Python — پست ۲۲۱
بررسی تغییرات Database با total_changes
گاهی در برنامه لازم داریم بدانیم از زمان باز شدن اتصال SQLite، چند ردیف تحت تأثیر عملیات تغییر داده‌ها قرار گرفته‌اند.
برای این کار می‌توانیم از ویژگی total_changes استفاده کنیم.
🔹 مثال ساده

import sqlite3

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

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

connection.executemany(
    "INSERT INTO students (name) VALUES (?)",
    [("Ali",), ("Sara",), ("Reza",)]
)

print(connection.total_changes)

connection.close()

خروجی:

3

چون سه ردیف درج شده است.
🔹 بررسی تغییرات پس از چند عملیات

import sqlite3

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

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

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

print(connection.total_changes)

connection.execute(
    "UPDATE users SET username = ? WHERE username = ?",
    ("ali_new", "ali")
)

print(connection.total_changes)

connection.close()

در یک اجرای تازه و بدون رکورد قبلی، خروجی معمولاً چنین است:

1
2

مقدار total_changes پس از درج یک ردیف، 1 و پس از به‌روزرسانی یک ردیف دیگر، 2 می‌شود.
🔹 تفاوت total_changes و rowcount
این دو را با هم اشتباه نکن:
connection.total_changes تعداد تغییرات ردیفی ثبت‌شده در طول عمر اتصال را گزارش می‌کند.
cursor.rowcount تعداد ردیف‌های تحت تأثیر یک دستور مشخص را گزارش می‌کند؛ البته رفتار آن به نوع دستور بستگی دارد.
مثال:

cursor = connection.execute(
    "DELETE FROM users WHERE username = ?",
    ("ali_new",)
)

print("This query:", cursor.rowcount)
print("Total:", connection.total_changes)

اگر یک ردیف حذف شود، rowcount معمولاً 1 خواهد بود؛ اما total_changes مجموع تغییرات قبلی و این حذف را نشان می‌دهد.
⚠️ نکته مهم
total_changes تعداد ردیف‌های تغییرکرده را گزارش می‌کند، نه تعداد Queryها را. همچنین تغییرات ناشی از Triggerها نیز می‌توانند در آن محاسبه شوند.
🧠 خلاصه

connection.total_changes

برای بررسی مجموع تغییرات ردیفی از زمان بازشدن اتصال مفید است.
🎯 تمرین
جدول products را بساز و سه محصول درج کن. سپس یکی را به‌روزرسانی و یکی را حذف کن.
بعد از هر عملیات، مقدار connection.total_changes را چاپ کن و تغییرات را با cursor.rowcount مقایسه کن.
پست ۲۲۱ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۲
ذخیره داده‌های باینری در SQLite با BLOB
تا اینجا بیشتر با داده‌های متنی و عددی در SQLite کار کردیم.
اما گاهی لازم است داده‌های باینری (Binary Data) مانند محتوای یک تصویر یا فایل را هم در Database ذخیره کنیم.
برای این کار از نوع داده BLOB استفاده می‌کنیم.
🔹 BLOB چیست؟
BLOB مخفف Binary Large Object است و برای نگهداری داده‌های باینری استفاده می‌شود.
مثلاً:
محتوای تصویر
فایل‌های کوچک
داده‌های باینری تولیدشده توسط برنامه
🔹 ذخیره یک فایل در SQLite
فرض کنیم فایلی به نام image.png داریم.

import sqlite3
from pathlib import Path

image_data = Path("image.png").read_bytes()

with sqlite3.connect("files.db") as connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY,
            filename TEXT NOT NULL,
            content BLOB NOT NULL
        )
    """)

    connection.execute(
        "INSERT INTO files (filename, content) VALUES (?, ?)",
        ("image.png", image_data)
    )

print("File saved")

در این مثال، read_bytes() محتوای فایل را به شکل bytes می‌خواند و SQLite آن را در ستون BLOB ذخیره می‌کند.
🔹 بازیابی فایل
برای دریافت داده‌ها و بازسازی فایل:

import sqlite3
from pathlib import Path

with sqlite3.connect("files.db") as connection:
    row = connection.execute(
        "SELECT content FROM files WHERE filename = ?",
        ("image.png",)
    ).fetchone()

if row is not None:
    Path("restored.png").write_bytes(row[0])
    print("File restored")
else:
    print("File not found")

اگر رکورد وجود داشته باشد، فایل restored.png ساخته می‌شود.
🔹 چرا از ? استفاده می‌کنیم؟
در این دستور:

connection.execute(
    "INSERT INTO files (filename, content) VALUES (?, ?)",
    ("image.png", image_data)
)

مقادیر به‌صورت پارامتری ارسال می‌شوند. این روش از قراردادن مستقیم داده‌ها در متن SQL جلوگیری می‌کند و برای داده‌های باینری نیز مناسب است.
⚠️ نکات مهم
BLOB برای داده‌های باینری است؛ TEXT برای متن.
فایل‌های بزرگ می‌توانند حجم Database و هزینه خواندن و نوشتن را افزایش دهند.
در پروژه‌های بزرگ، ممکن است نگهداری فایل‌ها در فضای ذخیره‌سازی جداگانه و ذخیره مسیر یا شناسه آن‌ها در Database مناسب‌تر باشد.
نام فایل را به‌تنهایی معیار مطمئنی برای شناسایی یک رکورد در نظر نگیر؛ در پروژه واقعی بهتر است از id یا یک شناسه یکتا استفاده کنی.
🧠 خلاصه

Path.read_bytes()
       ↓
      bytes
       ↓
SQLite BLOB
       ↓
     bytes
       ↓
Path.write_bytes()

🎯 تمرین
برنامه‌ای بنویس که فایل document.pdf را بخواند، محتوای آن را در جدول documents با ستون‌های id، filename و content ذخیره کند و سپس با استفاده از id، فایل را بازیابی کند.
پست ۲۲۲ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۳
ذخیره و بازیابی تاریخ و زمان در SQLite
در برنامه‌هایی مثل فروشگاه، سیستم حضور و غیاب و مدیریت سفارش‌ها، معمولاً لازم است تاریخ و زمان را در Database ذخیره کنیم.
SQLite نوع داده اختصاصی برای تاریخ و زمان ندارد؛ اما می‌توانیم آن‌ها را به شکل متن یا عدد ذخیره کنیم.
در Python نیز ماژول datetime ابزارهای لازم را در اختیارمان قرار می‌دهد.
🔹 دریافت تاریخ و زمان فعلی

from datetime import datetime

now = datetime.now()

print(now)

خروجی نمونه:

2026-10-12 18:30:00.123456

خروجی واقعی به ساعت سیستم بستگی دارد.
🔹 ذخیره تاریخ و زمان به‌صورت متن
یک روش رایج، استفاده از قالب ISO است:

import sqlite3
from datetime import datetime

now = datetime.now()
created_at = now.isoformat(timespec="seconds")

with sqlite3.connect("shop.db") as connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY,
            product TEXT NOT NULL,
            created_at TEXT NOT NULL
        )
    """)

    connection.execute(
        "INSERT INTO orders (product, created_at) VALUES (?, ?)",
        ("Keyboard", created_at)
    )

نمونه مقدار ذخیره‌شده:

2026-10-12T18:30:00

🔹 خواندن و تبدیل دوباره به datetime

import sqlite3
from datetime import datetime

with sqlite3.connect("shop.db") as connection:
    row = connection.execute("""
        SELECT created_at
        FROM orders
        ORDER BY id DESC
        LIMIT 1
    """).fetchone()

if row is not None:
    created_at = datetime.fromisoformat(row[0])
    print(created_at.year)
    print(created_at.month)
    print(created_at.day)

fromisoformat() متن ISO را به یک شیء datetime تبدیل می‌کند.
🔹 فقط تاریخ یا فقط ساعت
اگر فقط تاریخ لازم باشد:

from datetime import date

today = date.today()
print(today.isoformat())

نمونه خروجی:

2026-10-12

برای ذخیره فقط ساعت نیز می‌توان از datetime.time استفاده کرد.
⚠️ نکته مهم درباره منطقه زمانی
datetime.now() به‌صورت پیش‌فرض زمان محلی سیستم را برمی‌گرداند و منطقه زمانی را به‌طور صریح مشخص نمی‌کند.
اگر برنامه کاربران مناطق زمانی مختلفی دارد، بهتر است زمان را به شکل آگاه از منطقه زمانی (Timezone-aware) ذخیره کنیم؛ برای مثال با زمان UTC:

from datetime import datetime, timezone

now_utc = datetime.now(timezone.utc)
print(now_utc.isoformat())

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

datetime.now()
      ↓
isoformat()
      ↓
ذخیره در SQLite به‌صورت TEXT
      ↓
datetime.fromisoformat()
      ↓
بازیابی به‌صورت datetime

🎯 تمرین
جدولی به نام events بساز که ستون‌های id، title و event_time داشته باشد.
یک رویداد با زمان فعلی UTC ذخیره کن، سپس آن را بخوان و سال و ماه آن را چاپ کن.
پست ۲۲۳ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۴
مدیریت هم‌زمانی در SQLite با حالت WAL
وقتی چند بخش از برنامه به‌طور هم‌زمان از یک Database استفاده می‌کنند، مدیریت خواندن و نوشتن اهمیت زیادی پیدا می‌کند.
SQLite برای این موضوع حالتی به نام WAL (Write-Ahead Logging) دارد.
در این حالت، تغییرات ابتدا در فایل لاگ ثبت می‌شوند و بعداً می‌توانند به فایل اصلی Database منتقل شوند.
🔹 فعال‌کردن WAL

import sqlite3

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

mode = connection.execute(
    "PRAGMA journal_mode = WAL"
).fetchone()[0]

print(mode)

connection.close()

خروجی معمولاً:

wal

SQLite حالت Journal را به WAL تغییر می‌دهد و نام حالت فعال را برمی‌گرداند.
🔹 WAL چه مزیتی دارد؟
در حالت معمول، خواندن و نوشتن ممکن است بیشتر یکدیگر را مسدود کنند.
WAL معمولاً اجازه می‌دهد:
خواننده‌ها هم‌زمان با یک نویسنده فعال باشند.
خواندن و نوشتن در بسیاری از شرایط روان‌تر انجام شود.
تغییرات ابتدا در فایل -wal ثبت شوند.
اما نکته مهم این است که WAL چند نویسنده هم‌زمان را ممکن نمی‌کند؛ SQLite همچنان در هر لحظه فقط یک نویسنده فعال دارد.
🔹 مشاهده فایل‌های Database
وقتی WAL فعال باشد، ممکن است کنار فایل اصلی این فایل‌ها را ببینی:

shop.db
shop.db-wal
shop.db-shm

shop.db فایل اصلی Database است.
shop.db-wal تغییرات ثبت‌شده در WAL را نگه می‌دارد.
shop.db-shm برای هماهنگی دسترسی‌ها استفاده می‌شود.
وجود این فایل‌ها در زمان فعال بودن WAL طبیعی است.
🔹 Checkpoint چیست؟
Checkpoint فرایندی است که تغییرات موجود در WAL را به فایل اصلی Database منتقل می‌کند.
برای اجرای Checkpoint می‌توانیم از دستور زیر استفاده کنیم:

import sqlite3

with sqlite3.connect("shop.db") as connection:
    result = connection.execute(
        "PRAGMA wal_checkpoint(PASSIVE)"
    ).fetchone()

    print(result)

حالت PASSIVE تلاش می‌کند بدون منتظر ماندن برای خواننده‌های مزاحم، تا حد ممکن Checkpoint انجام دهد.
معمولاً لازم نیست در هر عملیات این دستور را دستی اجرا کنیم؛ SQLite می‌تواند Checkpoint را خودکار مدیریت کند.
⚠️ نکات مهم
WAL برای بسیاری از برنامه‌های محلی و چندخواننده‌ای مفید است.
WAL برای دسترسی مستقیم چند رایانه از طریق فایل‌سیستم شبکه‌ای مناسب نیست.
هنگام پشتیبان‌گیری، فایل اصلی را به‌تنهایی و بدون درنظرگرفتن وضعیت WAL کپی نکن؛ برای Backup مطمئن می‌توانی از Connection.backup() استفاده کنی.
فعال‌کردن WAL به معنی حذف محدودیت یک نویسنده هم‌زمان نیست.
🧠 خلاصه

WAL
 ↓
ثبت تغییرات در فایل لاگ
 ↓
امکان خواندن هم‌زمان با نوشتن
 ↓
Checkpoint
 ↓
انتقال تغییرات به Database اصلی

🎯 تمرین
برنامه‌ای بنویس که:
به shop.db متصل شود.
حالت WAL را فعال کند.
حالت فعال را چاپ کند.
یک رکورد درج کند.
اتصال را به‌درستی مدیریت کند.
پست ۲۲۴ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۵
ساخت دیتابیس موقت با :memory:
گاهی برای آزمایش کد یا اجرای تست‌ها به Database نیاز داریم، اما نمی‌خواهیم فایلی روی دیسک ساخته شود.
SQLite امکان ساخت دیتابیس در حافظه (In-Memory Database) را فراهم می‌کند.
🔹 ساخت دیتابیس در حافظه
به‌جای نام فایل، از :memory: استفاده می‌کنیم:

import sqlite3

connection = sqlite3.connect(":memory:")

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

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

users = connection.execute(
    "SELECT * FROM users"
).fetchall()

print(users)

connection.close()

خروجی:

[(1, 'Ali')]

تمام عملیات روی یک دیتابیس موقت انجام می‌شود و فایل shop.db یا فایل دیگری ساخته نمی‌شود.
🔹 چرا برای تست مناسب است؟
فرض کنیم می‌خواهیم تابعی را آزمایش کنیم که اطلاعات کاربران را از SQLite می‌خواند.
می‌توانیم یک Database کوچک در حافظه بسازیم، داده‌های آزمایشی را داخل آن قرار دهیم و نتیجه تابع را بررسی کنیم.
این روش باعث می‌شود تست‌ها:
سریع‌تر اجرا شوند.
به فایل Database واقعی وابسته نباشند.
داده‌های آزمایشی را در فایل اصلی ذخیره نکنند.
🔹 هر اتصال، دیتابیس جداگانه‌ای دارد
این نکته بسیار مهم است:

import sqlite3

connection1 = sqlite3.connect(":memory:")
connection2 = sqlite3.connect(":memory:")

connection1.execute("CREATE TABLE users (name TEXT)")

tables = connection2.execute("""
    SELECT name
    FROM sqlite_schema
    WHERE type = 'table'
""").fetchall()

print(tables)

connection1.close()
connection2.close()

خروجی:

[]

هر اتصال به :memory: یک دیتابیس مستقل می‌سازد؛ بنابراین جدول اتصال اول در اتصال دوم وجود ندارد.
🔹 اطلاعات چه زمانی از بین می‌روند؟
وقتی اتصال دیتابیس حافظه‌ای بسته شود، اطلاعات آن نیز از بین می‌روند.

connection = sqlite3.connect(":memory:")

این دیتابیس برای ذخیره‌سازی دائمی مناسب نیست.
اگر برنامه را ببندیم یا اتصال را ببندیم، نمی‌توانیم انتظار داشته باشیم اطلاعات برای اجرای بعدی باقی بمانند.
⚠️ نکته مهم
اگر چند اتصال باید به یک دیتابیس حافظه‌ای مشترک دسترسی داشته باشند، SQLite روش‌های دیگری مانند Shared In-Memory Database دارد؛ اما برای شروع، :memory: را به‌عنوان دیتابیس مستقل هر اتصال در نظر بگیر.
🧠 خلاصه

connection = sqlite3.connect(":memory:")

ساخت دیتابیس بدون فایل
مناسب برای تست و آزمایش
اطلاعات موقت
مستقل بودن دیتابیس هر اتصال
🎯 تمرین
یک دیتابیس حافظه‌ای بساز و جدول products را با ستون‌های id و name ایجاد کن.
دو محصول درج کن، همه محصولات را با SELECT نمایش بده و در پایان اتصال را ببند.
پست ۲۲۵ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۶
استفاده از ATTACH DATABASE برای اتصال چند دیتابیس SQLite
گاهی اطلاعات پروژه در چند فایل SQLite جداگانه ذخیره شده‌اند؛ مثلاً اطلاعات کاربران در یک فایل و سفارش‌ها در فایلی دیگر.
با دستور ATTACH DATABASE می‌توانیم یک دیتابیس دیگر را به اتصال فعلی اضافه کنیم و از هر دو در Queryها استفاده کنیم.
🔹 اتصال به دیتابیس دوم
فرض کنیم دو فایل داریم:

project/
├── users.db
└── orders.db

می‌توانیم هر دو را از طریق یک اتصال مدیریت کنیم:

import sqlite3

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

connection.execute(
    "ATTACH DATABASE ? AS orders_db",
    ("orders.db",)
)

print("Second database attached")

connection.close()

در این مثال، users.db دیتابیس اصلی و orders_db نام مستعاری است که برای دیتابیس دوم انتخاب کرده‌ایم.
🔹 خواندن اطلاعات از دیتابیس دوم
فرض کنیم در orders.db جدولی به نام orders داریم.
پس از اتصال دیتابیس دوم، می‌توانیم اطلاعات آن را بخوانیم:

import sqlite3

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

connection.execute(
    "ATTACH DATABASE ? AS orders_db",
    ("orders.db",)
)

rows = connection.execute("""
    SELECT *
    FROM orders_db.orders
""").fetchall()

for row in rows:
    print(row)

connection.close()

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

database_alias.table_name

بنابراین:

orders_db.orders

به جدول orders در دیتابیس دوم اشاره می‌کند.
🔹 ترکیب اطلاعات دو دیتابیس
فرض کنیم جدول users در دیتابیس اصلی و جدول orders در دیتابیس دوم قرار دارد و هر سفارش شامل user_id است.
می‌توانیم آن‌ها را با JOIN ترکیب کنیم:

rows = connection.execute("""
    SELECT users.name, orders_db.orders.amount
    FROM users
    JOIN orders_db.orders
        ON users.id = orders_db.orders.user_id
""").fetchall()

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

connection.execute("DETACH DATABASE orders_db")

این کار دیتابیس را از اتصال فعلی جدا می‌کند؛ فایل آن را حذف نمی‌کند.
⚠️ نکات مهم
فایل دیتابیس دوم باید قابل دسترسی باشد؛ در غیر این صورت اتصال ممکن است با خطا مواجه شود.
از نام مستعار برای مشخص‌کردن دیتابیس موردنظر استفاده می‌کنیم.
هنگام استفاده از چند دیتابیس، نام جدول‌ها را دقیق مشخص کن تا ابهام ایجاد نشود.
اگر به اتمی‌بودن تراکنش بین چند دیتابیس نیاز داری، محدودیت‌های SQLite را بررسی کن؛ به‌خصوص هنگام استفاده از WAL.
🧠 خلاصه

ATTACH DATABASE → اتصال دیتابیس دوم
alias.table     → دسترسی به جدول دیتابیس دوم
JOIN            → ترکیب اطلاعات
DETACH DATABASE → جداکردن دیتابیس

🎯 تمرین
دو دیتابیس به نام‌های school.db و courses.db در نظر بگیر.
در school.db جدولی به نام students با ستون‌های id و name داشته باش.
در courses.db جدولی به نام courses با ستون‌های student_id و title داشته باش.
دیتابیس دوم را با نام مستعار course_db متصل کن.
با JOIN نام دانشجویان و عنوان دوره‌هایشان را نمایش بده.
پست ۲۲۶ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۷
مدیریت نسخه‌های ساختار Database با Migration
در پروژه‌های واقعی، ساختار دیتابیس ممکن است به‌مرور تغییر کند؛ مثلاً ابتدا جدول کاربران فقط id و name داشته باشد، اما بعداً بخواهیم ستون email را هم اضافه کنیم.
به‌جای تغییر دستی دیتابیس در هر اجرا، می‌توانیم از روشی به نام Database Migration استفاده کنیم.
Migration یعنی ثبت و اجرای مرحله‌به‌مرحله تغییرات ساختار دیتابیس.
🔹 چرا Migration لازم است؟
فرض کنیم نسخه اول جدول چنین باشد:

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

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

ALTER TABLE users
ADD COLUMN email TEXT;

اگر برنامه هر بار این دستور را بدون بررسی اجرا کند، اجرای دوباره آن باعث خطا می‌شود؛ چون ستون از قبل وجود دارد.
پس باید مشخص کنیم کدام تغییرات قبلاً اجرا شده‌اند.
🔹 ساخت جدول ثبت Migrationها
یک جدول برای نگهداری نسخه فعلی ساختار ایجاد می‌کنیم:

import sqlite3

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

connection.execute("""
    CREATE TABLE IF NOT EXISTS schema_migrations (
        version INTEGER PRIMARY KEY,
        applied_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")

connection.commit()
connection.close()

هر Migration یک شماره نسخه دارد.
🔹 اجرای Migration فقط یک بار
در مثال زیر، Migration شماره 1 ستون email را اضافه می‌کند:

import sqlite3

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

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

connection.execute("""
    CREATE TABLE IF NOT EXISTS schema_migrations (
        version INTEGER PRIMARY KEY,
        applied_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")

with connection:
    applied = connection.execute(
        "SELECT 1 FROM schema_migrations WHERE version = ?",
        (1,)
    ).fetchone()

    if applied is None:
        connection.execute(
            "ALTER TABLE users ADD COLUMN email TEXT"
        )

        connection.execute(
            "INSERT INTO schema_migrations (version) VALUES (?)",
            (1,)
        )

connection.close()

اگر Migration قبلاً اجرا شده باشد، دوباره اجرا نمی‌شود.
توجه: این مثال فرض می‌کند دیتابیس قبلی جدول users را بدون ستون email دارد و Migration شماره ۱ هنوز ثبت نشده است. برای یک سیستم Migration عمومی باید وضعیت واقعی ساختار نیز اعتبارسنجی شود.
🔹 ترتیب نسخه‌ها
در پروژه‌های بزرگ‌تر ممکن است چند تغییر داشته باشیم:

Migration 1 → ایجاد جدول users
Migration 2 → افزودن ستون email
Migration 3 → ایجاد Index برای email
Migration 4 → ایجاد جدول orders

هر نسخه باید به‌ترتیب اجرا و ثبت شود.
⚠️ نکات مهم
Migrationها را بعد از اجرا بی‌دلیل تغییر نده؛ ممکن است دیتابیس کاربران با نسخه قبلی ساخته شده باشد.
قبل از تغییرات مهم، Backup تهیه کن.
همه دستورهای SQLite الزاماً در یک تراکنش قابل استفاده نیستند؛ رفتار تراکنشی DDL را برای تغییرات موردنظر بررسی کن.
ثبت نسخه به‌تنهایی کافی نیست؛ خطاها و تفاوت ساختار واقعی دیتابیس نیز باید مدیریت شوند.
🧠 خلاصه

بررسی نسخه فعلی
       ↓
اجرای Migration جدید
       ↓
ثبت نسخه اجراشده
       ↓
ادامه برنامه

Migration کمک می‌کند ساختار دیتابیس در نسخه‌های مختلف برنامه به‌شکل قابل‌کنترل تغییر کند.
🎯 تمرین
برای جدول products یک Migration شماره 2 طراحی کن که ستون description TEXT را اضافه کند.
برنامه باید ابتدا بررسی کند آیا نسخه 2 اجرا شده است یا نه؛ اگر اجرا نشده بود، ستون را اضافه کند و نسخه را ثبت کند.
پست ۲۲۷ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۸
تست خودکار عملیات SQLite با unittest
تا اینجا یاد گرفتیم چطور با SQLite داده ذخیره کنیم، Query بنویسیم و ساختار Database را مدیریت کنیم.
حالا می‌خواهیم بررسی کنیم که کدهای Database واقعاً درست کار می‌کنند یا نه.
برای این کار می‌توانیم از ماژول استاندارد unittest در Python استفاده کنیم.
🔹 ساخت تابع مورد آزمایش
فایل database.py را بساز:

import sqlite3


def add_user(connection, name):
    connection.execute(
        "INSERT INTO users (name) VALUES (?)",
        (name,)
    )


def get_users(connection):
    return connection.execute(
        "SELECT name FROM users ORDER BY id"
    ).fetchall()

این توابع از یک اتصال آماده استفاده می‌کنند؛ بنابراین می‌توانیم آن‌ها را با یک دیتابیس آزمایشی تست کنیم.
🔹 نوشتن تست
فایل test_database.py:

import sqlite3
import unittest

from database import add_user, get_users


class TestDatabase(unittest.TestCase):

    def setUp(self):
        self.connection = sqlite3.connect(":memory:")

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

    def tearDown(self):
        self.connection.close()

    def test_add_user(self):
        add_user(self.connection, "Ali")

        users = get_users(self.connection)

        self.assertEqual(users, [("Ali",)])

    def test_multiple_users(self):
        add_user(self.connection, "Sara")
        add_user(self.connection, "Reza")

        users = get_users(self.connection)

        self.assertEqual(
            users,
            [("Sara",), ("Reza",)]
        )


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

🔹 اجرای تست‌ها
در ترمینال، داخل پوشه پروژه اجرا کن:

python -m unittest -v

اگر هر دو تست موفق باشند، نتیجه‌ای مشابه این می‌بینی:

test_add_user ... ok
test_multiple_users ... ok

Ran 2 tests
OK

🔹 setUp() و tearDown() چه هستند؟
setUp() قبل از هر تست اجرا می‌شود و محیط آزمایشی را آماده می‌کند.
tearDown() بعد از هر تست اجرا می‌شود و منابع را آزاد می‌کند.
assertEqual() نتیجه واقعی را با نتیجه مورد انتظار مقایسه می‌کند.
چون از :memory: استفاده کردیم، اطلاعات تست وارد فایل دیتابیس اصلی نمی‌شوند.
⚠️ نکته مهم
هر تست در این مثال اتصال و دیتابیس حافظه‌ای مستقل خودش را دارد. بنابراین نتیجه یک تست روی تست دیگر اثر نمی‌گذارد.
همچنین اگر تستی شکست بخورد، پیام خطا کمک می‌کند بفهمیم نتیجه واقعی با انتظارمان چه تفاوتی دارد.
🧠 خلاصه

setUp()
   ↓
آماده‌سازی دیتابیس آزمایشی
   ↓
اجرای تست
   ↓
assertEqual()
   ↓
tearDown()

تست خودکار کمک می‌کند بعد از تغییر کد، مطمئن شویم قابلیت‌های قبلی همچنان درست کار می‌کنند.
🎯 تمرین
تابعی به نام delete_user(connection, user_id) بساز که یک کاربر را با شناسه حذف کند.
سپس یک تست با unittest بنویس که:
دو کاربر درج کند.
یکی را حذف کند.
بررسی کند فقط کاربر باقی‌مانده در نتیجه get_users() وجود داشته باشد.
پست ۲۲۸ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۲۹
مدیریت امن خطاهای تراکنش در SQLite
در پست قبل تست‌کردن عملیات Database را یاد گرفتیم. حالا می‌خواهیم یکی از مهم‌ترین اصول کار با دیتابیس را بررسی کنیم: اگر یکی از عملیات شکست خورد، تغییرات ناقص باقی نمانند.
برای این کار از تراکنش (Transaction) و rollback() استفاده می‌کنیم.
🔹 مشکل اجرای چند عملیات
فرض کنیم می‌خواهیم دو حساب بانکی را به‌روزرسانی کنیم. اگر مبلغ از حساب اول کم شود اما افزایش موجودی حساب دوم با خطا مواجه شود، اطلاعات ناسازگار خواهد شد.
تراکنش کمک می‌کند عملیات مرتبط را به‌صورت یک واحد مدیریت کنیم.
🔹 استفاده از with connection:

import sqlite3

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

connection.execute("""
    CREATE TABLE IF NOT EXISTS accounts (
        id INTEGER PRIMARY KEY,
        balance INTEGER NOT NULL
    )
""")

connection.execute(
    "INSERT OR IGNORE INTO accounts (id, balance) VALUES (?, ?)",
    (1, 1000)
)

connection.execute(
    "INSERT OR IGNORE INTO accounts (id, balance) VALUES (?, ?)",
    (2, 500)
)

connection.commit()

try:
    with connection:
        connection.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (100, 1)
        )

        connection.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (100, 2)
        )

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

connection.close()

در این مثال، هر دو تغییر داخل یک تراکنش قرار می‌گیرند. اگر خطای مناسب رخ دهد، تراکنش بازگردانده می‌شود؛ در غیر این صورت تغییرات ثبت می‌شوند.
🔹 بررسی نتیجه
بعد از تراکنش می‌توانیم موجودی‌ها را بخوانیم:

with sqlite3.connect("bank.db") as connection:
    accounts = connection.execute("""
        SELECT id, balance
        FROM accounts
        ORDER BY id
    """).fetchall()

print(accounts)

اگر انتقال موفق باشد، موجودی حساب اول 900 و حساب دوم 600 خواهد بود.
🔹 چرا rollback() مهم است؟
فرض کنیم چند تغییر انجام شده، اما یکی از آن‌ها شکست می‌خورد.

شروع تراکنش
      ↓
کاهش موجودی حساب اول
      ↓
خطا در عملیات بعدی
      ↓
rollback()
      ↓
لغو تغییرات تأییدنشده

به این ترتیب، تغییرات ناقص به‌عنوان یک تراکنش موفق ثبت نمی‌شوند.
⚠️ نکته مهم
with connection: در صورت موفقیت تراکنش را تأیید می‌کند و در صورت خروج همراه با Exception، تغییرات تأییدنشده را بازمی‌گرداند.
این بلوک اتصال را نمی‌بندد؛ برای بستن اتصال باید جداگانه از close() یا مدیریت مناسب منابع استفاده کنیم.
خطاهایی که خودمان از نظر منطقی تشخیص نمی‌دهیم، لزوماً Exception ایجاد نمی‌کنند. مثلاً اگر UPDATE هیچ ردیفی را تغییر ندهد، باید تعداد ردیف‌های تحت تأثیر را بررسی کنیم.
🧠 خلاصه

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

🎯 تمرین
برنامه‌ای بنویس که در یک تراکنش:
از موجودی حساب اول 200 واحد کم کند.
به موجودی حساب دوم 200 واحد اضافه کند.
اگر هر عملیاتی خطا داد، تغییرات تراکنش را لغو کند.
در پایان موجودی هر دو حساب را چاپ کند.
پست ۲۲۹ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۳۰
جلوگیری از SQL Injection در SQLite
وقتی برنامه اطلاعات را از کاربر دریافت می‌کند، باید هنگام اجرای Queryها مراقب باشیم.
یکی از آسیب‌پذیری‌های مهم در کار با دیتابیس، SQL Injection است؛ زمانی که ورودی کنترل‌نشده کاربر باعث تغییر ساختار دستور SQL شود.
🔹 روش ناامن
فرض کنیم می‌خواهیم کاربری را بر اساس نام پیدا کنیم:

username = input("Username: ")

query = f"""
    SELECT * FROM users
    WHERE username = '{username}'
"""

cursor.execute(query)

در این روش، ورودی کاربر مستقیماً وارد دستور SQL می‌شود و ممکن است ساختار Query را تغییر دهد.
برای ساخت Query از f-string یا اتصال رشته‌ها با ورودی کاربر استفاده نکن.
🔹 روش امن با پارامترها
در sqlite3 از Placeholder استفاده می‌کنیم:

import sqlite3

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

username = input("Username: ")

cursor = connection.execute(
    "SELECT id, username FROM users WHERE username = ?",
    (username,)
)

print(cursor.fetchall())

connection.close()

علامت ? محل مقدار ورودی را مشخص می‌کند.
این بخش:

(username,)

یک Tuple تک‌عضوی است؛ به همین دلیل بعد از username کاما قرار دارد.
SQLite مقدار ورودی را به‌عنوان داده پردازش می‌کند، نه بخشی از ساختار SQL.
🔹 پارامترگذاری در INSERT
همین روش برای ثبت اطلاعات نیز کاربرد دارد:

connection.execute(
    "INSERT INTO users (username, age) VALUES (?, ?)",
    ("Ali", 25)
)

connection.commit()

هر مقدار در Placeholder مربوط به خودش قرار می‌گیرد.
🔹 پارامترگذاری در UPDATE

connection.execute(
    "UPDATE users SET age = ? WHERE username = ?",
    (26, "Ali")
)

connection.commit()

ترتیب مقادیر باید با ترتیب Placeholderها مطابقت داشته باشد.
⚠️ نکته مهم
پارامترها برای مقدارها هستند، نه نام جدول یا ستون.
مثلاً نمی‌توانیم نام ستون را با ? جایگزین کنیم:

cursor.execute(
    "SELECT ? FROM users",
    ("username",)
)

این دستور ستون username را انتخاب نمی‌کند؛ بلکه مقدار ثابت "username" را برای هر ردیف برمی‌گرداند.
اگر نام ستون باید پویا باشد، آن را از یک فهرست مجاز و از پیش تعریف‌شده انتخاب کن؛ نه مستقیماً از ورودی کاربر.
🧠 خلاصه

ناامن:
f"SELECT ... WHERE name = '{name}'"

امن:
"SELECT ... WHERE name = ?", (name,)

قانون اصلی: برای انتقال داده به Queryهای SQL از Parameterized Query استفاده کن.
🎯 تمرین
جدول products شامل ستون‌های id، name و price است.
کدی بنویس که نام محصول را از کاربر بگیرد و محصول را با استفاده از ? جست‌وجو کند.
شرط مهم: ورودی کاربر نباید با f-string وارد دستور SQL شود.
پست ۲۳۰ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۳۱
مدیریت هم‌زمانی در SQLite با timeout
وقتی چند برنامه یا اتصال هم‌زمان به یک دیتابیس SQLite دسترسی دارند، ممکن است یکی از آن‌ها در حال نوشتن باشد و اتصال دیگر نتواند بلافاصله تغییرات خود را ثبت کند.
در این شرایط ممکن است خطای database is locked رخ دهد.
یکی از راه‌های مدیریت این وضعیت، استفاده از پارامتر timeout است.
🔹 استفاده از timeout

import sqlite3

connection = sqlite3.connect(
    "app.db",
    timeout=10
)

در این مثال، SQLite در صورت قفل بودن دیتابیس تا حدود ۱۰ ثانیه برای آزاد شدن آن منتظر می‌ماند.
اگر دیتابیس در این مدت قابل دسترسی نشود، ممکن است خطای مربوط به قفل بودن دیتابیس رخ دهد.
🔹 مدیریت خطا
می‌توانیم خطای احتمالی را مدیریت کنیم:

import sqlite3

try:
    with sqlite3.connect("app.db", timeout=5) as connection:
        connection.execute(
            "UPDATE products SET price = ? WHERE id = ?",
            (120, 1)
        )

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

در اینجا:
timeout=5 برای منتظر ماندن جهت آزاد شدن قفل استفاده می‌شود.
with connection: تراکنش را مدیریت می‌کند.
OperationalError می‌تواند خطاهایی مانند قفل بودن دیتابیس را دریافت کند.
توجه کن که OperationalError فقط مخصوص قفل دیتابیس نیست و ممکن است دلایل دیگری هم داشته باشد.
🔹 آیا timeout همه مشکلات را حل می‌کند؟
خیر. اگر یک اتصال برای مدت طولانی قفل نوشتن را نگه دارد، افزایش timeout فقط باعث می‌شود اتصال دیگر مدت بیشتری منتظر بماند.
برای کاهش احتمال این مشکل:
تراکنش‌ها را کوتاه نگه دار.
داخل تراکنش عملیات غیرضروری انجام نده.
هنگام استفاده از چند اتصال، خطاهای احتمالی را مدیریت کن.
برای سناریوهای نوشتن هم‌زمان زیاد، محدودیت‌های SQLite را در طراحی برنامه در نظر بگیر.
⚠️ نکته مهم
SQLite چند خواننده هم‌زمان را پشتیبانی می‌کند، اما نوشتن هم‌زمان محدودیت دارد؛ در یک لحظه فقط یک تراکنش می‌تواند نویسنده باشد.
پارامتر timeout امکان انتظار برای آزاد شدن قفل را فراهم می‌کند، اما جایگزین طراحی درست تراکنش‌ها نیست.
🧠 خلاصه

چند اتصال به دیتابیس
        ↓
قفل بودن دیتابیس
        ↓
timeout
        ↓
انتظار برای آزاد شدن قفل
        ↓
موفقیت یا بروز خطا

🎯 تمرین
برنامه‌ای بنویس که:
به دیتابیس app.db با timeout=8 متصل شود.
یک دستور SELECT اجرا کند.
خطاهای sqlite3.OperationalError را مدیریت کند.
در صورت موفقیت، نتایج را چاپ کند.
پست ۲۳۱ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۳۲
کنترل شروع تراکنش با BEGIN IMMEDIATE
در پست قبل با timeout آشنا شدیم.
حالا می‌خواهیم روش دیگری برای مدیریت تراکنش‌ها در SQLite یاد بگیریم: BEGIN IMMEDIATE.
این دستور زمانی مفید است که می‌خواهیم یک تراکنش را برای انجام عملیات نوشتن آغاز کنیم.
🔹 تفاوت BEGIN و BEGIN IMMEDIATE
در SQLite، دستور معمولی:

BEGIN;

یک تراکنش را با حالت پیش‌فرض DEFERRED آغاز می‌کند. در این حالت، قفل نوشتن لزوماً از همان ابتدا گرفته نمی‌شود.
اما:

BEGIN IMMEDIATE;

تراکنش را با درخواست دسترسی نوشتن آغاز می‌کند.
اگر اتصال دیگری در حال نوشتن باشد، ممکن است دستور منتظر بماند یا پس از پایان timeout با خطا مواجه شود.
🔹 مثال با Python

import sqlite3

connection = sqlite3.connect(
    "app.db",
    timeout=5,
    isolation_level=None
)

try:
    connection.execute("BEGIN IMMEDIATE")

    connection.execute(
        "UPDATE products SET price = ? WHERE id = ?",
        (150, 1)
    )

    connection.execute("COMMIT")
    print("Transaction completed")

except sqlite3.Error as error:
    if connection.in_transaction:
        connection.execute("ROLLBACK")

    print("Transaction failed:", error)

finally:
    connection.close()

در این مثال:
isolation_level=None مدیریت ضمنی تراکنش را غیرفعال می‌کند تا شروع تراکنش را صریحاً کنترل کنیم.
BEGIN IMMEDIATE تراکنش را با درخواست قفل نوشتن آغاز می‌کند.
COMMIT تغییرات را ثبت می‌کند.
ROLLBACK تغییرات تأییدنشده را لغو می‌کند.
connection.in_transaction بررسی می‌کند که آیا تراکنشی فعال است یا نه.
🔹 چرا از BEGIN IMMEDIATE استفاده کنیم؟
فرض کن ابتدا موجودی یک حساب را می‌خوانیم و سپس می‌خواهیم آن را تغییر دهیم.
اگر چند اتصال هم‌زمان همین کار را انجام دهند، ممکن است بین خواندن و نوشتن تداخل ایجاد شود.
با شروع تراکنش به روش IMMEDIATE، درخواست نوشتن از ابتدای تراکنش مشخص می‌شود و احتمال مواجهه با بعضی خطاهای ارتقای تراکنش کاهش می‌یابد.
البته این روش جایگزین طراحی صحیح عملیات و بررسی داده‌ها نیست.
⚠️ نکته مهم
از BEGIN IMMEDIATE برای هر Query ساده استفاده نکن. این روش برای تراکنش‌هایی مناسب‌تر است که می‌دانیم قرار است عملیات نوشتن انجام دهند.
همچنین هنگام استفاده از آن، تراکنش را کوتاه نگه دار؛ چون تا پایان تراکنش، اتصال‌های دیگر ممکن است نتوانند بنویسند.
🧠 خلاصه

BEGIN
  → شروع تراکنش با حالت DEFERRED

BEGIN IMMEDIATE
  → شروع تراکنش با درخواست دسترسی نوشتن

COMMIT
  → ثبت تغییرات

ROLLBACK
  → لغو تغییرات تأییدنشده

🎯 تمرین
کدی بنویس که:
به app.db متصل شود.
با BEGIN IMMEDIATE تراکنش را آغاز کند.
قیمت محصولی با id = 1 را تغییر دهد.
در صورت موفقیت، تغییرات را ثبت کند.
در صورت بروز خطا، تراکنش فعال را لغو کند.
در پایان اتصال را ببندد.
پست ۲۳۲ از دوره جامع Python 🐍

🐍 آموزش جامع Python — پست ۲۳۳
کنترل تراکنش با SAVEPOINT در SQLite
در پست‌های قبل یاد گرفتیم با COMMIT تغییرات را ثبت و با ROLLBACK تغییرات تأییدنشده را لغو کنیم.
اما گاهی یک تراکنش شامل چند مرحله است و می‌خواهیم فقط بخشی از تغییرات را برگردانیم، بدون اینکه کل تراکنش لغو شود.
برای این کار از SAVEPOINT استفاده می‌کنیم.
🔹 ساخت Savepoint
با SAVEPOINT یک نقطه بازگشت داخل تراکنش ایجاد می‌کنیم:

import sqlite3

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

try:
    connection.execute("BEGIN")

    connection.execute(
        "UPDATE products SET price = ? WHERE id = ?",
        (100, 1)
    )

    connection.execute("SAVEPOINT second_step")

    connection.execute(
        "UPDATE products SET price = ? WHERE id = ?",
        (200, 2)
    )

    connection.execute("RELEASE SAVEPOINT second_step")
    connection.commit()

except sqlite3.Error:
    connection.rollback()
    raise

finally:
    connection.close()

در این مثال، ابتدا قیمت محصول اول تغییر می‌کند و سپس یک Savepoint ایجاد می‌شود.
🔹 بازگشت به Savepoint
فرض کنیم می‌خواهیم تغییر دوم را لغو کنیم، اما تغییر اول باقی بماند:

import sqlite3

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

try:
    connection.execute("BEGIN")

    connection.execute(
        "UPDATE products SET price = ? WHERE id = ?",
        (100, 1)
    )

    connection.execute("SAVEPOINT second_step")

    connection.execute(
        "UPDATE products SET price = ? WHERE id = ?",
        (200, 2)
    )

    connection.execute("ROLLBACK TO SAVEPOINT second_step")

    connection.execute("RELEASE SAVEPOINT second_step")

    connection.commit()

except sqlite3.Error:
    connection.rollback()
    raise

finally:
    connection.close()

اینجا تغییر قیمت محصول دوم لغو می‌شود، اما تغییر محصول اول می‌تواند ثبت شود.
🔹 تفاوت سه دستور مهم
دستور
کاربرد
SAVEPOINT
ایجاد نقطه بازگشت
ROLLBACK TO SAVEPOINT
بازگشت به نقطه مشخص
RELEASE SAVEPOINT
آزاد کردن Savepoint
نکته: ROLLBACK TO SAVEPOINT تراکنش اصلی را تمام نمی‌کند؛ تراکنش همچنان فعال است.
همچنین RELEASE SAVEPOINT به‌تنهایی تغییرات را به‌صورت نهایی ثبت نمی‌کند؛ برای ثبت تراکنش اصلی باید از COMMIT استفاده کنیم.
⚠️ نکته مهم
اگر خطایی رخ دهد و بخواهیم کل تراکنش را لغو کنیم، می‌توانیم از:

connection.rollback()

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

BEGIN
  ↓
عملیات اول
  ↓
SAVEPOINT
  ↓
عملیات دوم
  ↓
ROLLBACK TO SAVEPOINT
  ↓
ادامه تراکنش
  ↓
COMMIT

🎯 تمرین
برنامه‌ای بنویس که:
یک تراکنش آغاز کند.
قیمت محصول اول را تغییر دهد.
یک Savepoint ایجاد کند.
قیمت محصول دوم را تغییر دهد.
به Savepoint برگردد تا تغییر دوم لغو شود.
تراکنش را ثبت کند.
پست ۲۳۳ از دوره جامع Python 🐍