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

🐍 آموزش جامع Python — پست ۲۱۴
جلوگیری از درج رکورد تکراری با INSERT OR IGNORE
گاهی می‌خواهیم داده‌ای را در SQLite ذخیره کنیم، اما اگر آن داده با محدودیتی مثل UNIQUE تداخل داشت، برنامه متوقف نشود.
برای این کار می‌توانیم از INSERT OR IGNORE استفاده کنیم.
🔹 مثال ساده

import sqlite3

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

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

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

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

connection.commit()

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

print(users)

connection.close()

خروجی:

[('ali',)]

نام ali دو بار درج نمی‌شود؛ چون ستون username محدودیت UNIQUE دارد.
🔹 چه اتفاقی می‌افتد؟
در این دستور:

INSERT OR IGNORE INTO users (username) VALUES (?)

اگر درج رکورد با بعضی از محدودیت‌های SQLite، مانند UNIQUE، PRIMARY KEY یا NOT NULL تداخل داشته باشد، SQLite آن رکورد را نادیده می‌گیرد.
بنابراین، به‌جای متوقف شدن عملیات به‌دلیل این تعارض‌ها، اجرای برنامه می‌تواند ادامه پیدا کند.
🔹 بررسی تعداد رکوردهای درج‌شده
می‌توانیم از rowcount استفاده کنیم:

cursor = connection.execute(
    "INSERT OR IGNORE INTO users (username) VALUES (?)",
    ("ali",)
)

print(cursor.rowcount)

در این مثال، چون ali از قبل وجود دارد، معمولاً خروجی 0 است.
اگر رکورد جدیدی درج شود، مقدار معمولاً 1 خواهد بود.
⚠️ نکته مهم
OR IGNORE برای همه خطاهای ممکن راه‌حل نیست؛ فقط برخی تعارض‌های محدودیتی را نادیده می‌گیرد. خطاهایی مثل خطاهای عملیاتی یا مشکلات اتصال همچنان ممکن است رخ دهند.
همچنین این روش رکورد موجود را به‌روزرسانی نمی‌کند؛ فقط از درج رکورد متعارض صرف‌نظر می‌کند.
🧠 خلاصه

INSERT OR IGNORE INTO users (username)
VALUES (?);

رکورد جدید و معتبر باشد → درج می‌شود.
با محدودیتی مانند UNIQUE تداخل داشته باشد → نادیده گرفته می‌شود.
رکورد موجود باشد → مقدار آن تغییر نمی‌کند.
🎯 تمرین
جدولی با نام products بساز که ستون code آن UNIQUE باشد.
سپس دو بار محصولی با کد "P100" درج کن؛ بار دوم از INSERT OR IGNORE استفاده کن و بررسی کن که فقط یک رکورد با این کد وجود داشته باشد.
پست ۲۱۴ از دوره جامع Python 🐍

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