ساخت و مدیریت Database با پایتون

ویدیو مربوط به این مقاله: (کلیک کن)

دیتابیس (Database) چیه؟ دیتابیس یه مجموعه‌ایی از اطلاعاته که به صورت منظم ذخیره و مدیریت میشن.

Database دو نوع اصلی داره:
1 - دیتابیس‌های رابطه‌ای (Relational Database): این نوع دیتابیس‌ها معمولا از جدول‌ها استفاده می‌کنن. مهم‌ترین ویژگی این نوع دیتابیس‌ها اینه که میتونی داده‌ها رو با استفاده از SQL جستجو کنی و تغییر بدی.

2 - دیتابیس‌های غیر رابطه‌ای: این نوع دیتابیس‌ها برای ذخیره داده‌هایی که مرتب و سازماندهی شده نیستن، خیلی مفیدن. معمولا از داده‌های Key-Value استفاده می‌کنن.

تو این مقاله قراره با دیتابیس SQLite که یه دیتابیس رابطه‌ای هست آشنا بشیم.

برای کار با SQLite باید از کتابخانه sqlite3 استفاده کنیم که بطور پیشفرض داخل پایتون وجود داره. قدم اول:

import sqlite3

import sqlite3

# اتصال به دیتابیس مشخص شده (اگه دیتابیس وجود نداشته باشه، خودش می‌سازه)
conn = sqlite3.connect("mydatabase.db")
# این متغیر نماینده دیتابیسه

cursor = conn.cursor()

# کدهای مربوط به دیتابیس

conn.close()

از خط 6 به بعد متغیر cursor واسط من و دیتابیسه و دستوراتم رو با کمک این متغیر به دیتابیس ارسال میکنم.
نکته. همیشه باید در انتهای کارمون، ارتباطمون رو با دیتابیس ببندیم تا منابع سیستم آزاد بشن (به خط آخر دقت کنید)

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

این جدول دیتابیسه. کلماتی که داخل کادر آبی رنگ قرار دارن ستون های جدول هستن.
وقتی شما قراره جدولی ایجاد کنی، مثل عکس میای ستون های مختلفی رو مینویسی و بعدش میای پایین ستون ها، توی هر ردیف مقادیر رو مینویسی. برای درک بهتر:

این عکس مربوط به یه فاکتور فروشه و کلمات داخل کادر سبز رنگ، ستون هستن و همیشه ثابت. هر ستون براش تعیین میشه که چه مقداری باید نگه داره، مثلا ستون <نام کالا> متن نگه میداره ولی ستون <تعداد> عدد نگه میداره.
درنهایت، ستون ها داخل جدول تعریف شدن، حالا باید هر محصولی رو داخل یه ردیف قرار بدم! مثلا ردیف اول مربوط به شیر هست که تعداد 15 تا فروخته شده و مابقی اطلاعاتش هم تو ستون مربوطش قرار داره.
از طرفی ردیف دوم برای یه محصول دیگس تااا آخر.
چطور با پایتون جدول و ستون‌ها رو بسازم؟

import sqlite3

conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER,
    name TEXT,
    age INTEGER
)
''')

# تغییرات رو ذخیره میکنیم
conn.commit()

متد execute دستور sql رو میگیره و به database تحویل میده تا اجراش کنه.
وقتی من با متد execute تغییری داخل دیتابیس اعمال میکنم، اون تغییر تا زمانی که متد commit صدا زده نشه بصورت واقعی اعمال نمیشه. درنتیجه، بعد از هر تغییری داخل دیتابیس این متد باید صدا زده بشه.
کد sql داخل متد execute چیه؟
در ابتدا این نکته رو بگم که دستورات SQL با حروف بزرگ نوشته میشن و هرچیز دیگه‌ایی با حروف کوچیک.
ابتدای کار نوشتم CREATE TABLE IF NOT EXISTS students که یعنی یه جدول به اسم students ایجاد کن مگر اینکه از قبل وجود داشته باشه. بعد از این عبارت پرانتز باز-بسته قرار دادم و داخلش باید ستون های دیتابیسم رو مشخص کنم.
در ابتدا نوشتم id که اسم ستونه و بعدش با حرف بزرگ نوشتم INTEGER که یعنی این ستون باید مقدار int بگیره. در آخر , قرار دادم و ستون بعدی (با مقداری که باید بگیره) رو مشخص کردم.

اضافه کردن داده به جدول:

import sqlite3

conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER,
    name TEXT,
    age INTEGER
)
''')

conn.commit()

cursor.execute("INSERT INTO students (id, name, age) VALUES (1, 'Amirhosein', 21)")
# or
new_student = (2, "Mehdi", 35)
cursor.execute("INSERT INTO students (id, name, age) VALUES (?, ?, ?)", new_student)

conn.commit()

conn.close()

تو خط 16 مجدد execute رو صدا زدم و یه دستور sql بهش دادم. تو این دستور نوشتم INSERT INTO studnets یعنی میخوام یه ردیف داخل جدول students مقداردهی کنم. بعدش پرانتز قرار دادم و ستون هایی که باید مقدار بگیرن رو مشخص کردم (اسم ستون ها با , از هم جدا میشه) در آخر نوشتم VALUES که یعنی مقادیر ستون ها بعد از این کلمست و مجدد پرانتز قرار دادم و داخلش مقادیر رو با همون ترتیب پرانتز اول وارد میکنم.
به خط پایین ترش دقت کنید. اونجا مقدار رو مستقیم داخل دستور sql قرار ندادم و از ? استفاده کردم تا مقدار رو جدا بهش بدم.
به این صورت هم میشه عمل کرد:

cursor.execute("INSERT INTO students VALUES (1, 'Amirhosein', 21)")

اینجا خود sql بصورت پیشفرض میاد مقادیر رو با همون ترتیب به ستون ها پاس میده.
میتونم مقدار برخی از ستون هارو ندم. مثال:

cursor.execute("INSERT INTO students (id, age) VALUES (4, 25)")

خوندن داده از جدول:

cursor.execute("SELECT * FROM students")

تو کد بالا گفتم تمام ردیف های موجود داخل جدول students رو برام برگردون (چون این دستور تغییری ایجاد نکرده به commit احتیاجی نیست)
بعد از اجرای دستور بالا، داده هایی که برگشت خوردن در اختیار cursor قرار میگیرن و بدین شکل میتونم خروجی رو نمایش بدم:

cursor.execute("SELECT * FROM students")

print(cursor.fetchall())

conn.close()

خروجی:

[(1, 'Amirhosein', 21), (2, 'Mehdi', 35)]

متد fetchall همه سطرها رو به صورت یه لیست از تاپل‌ها برمی‌گردونه. در کنار fetchall دو متد دیگه هم وجود داره:
fetchone:‌ وقتی فقط به اولین نتیجه نیاز داری یا مطمئنی فقط یه نتیجه وجود داره.
fetchmany(size): وقتی می‌خوای تعداد محدودی نتیجه بگیری (مخصوصا برای داده‌های حجیم).
برای مثال فرض کن سطر های زیر داخل جدول students وجود داره:

(1, "Amirhosein", 21)
(2, "Mehdi", 35) 
(3, "Sara", 19)
(4, "Ali", 22)
(5, "Fatemeh", 20)

کد:

cursor.execute("SELECT * FROM students")

print(cursor.fetchone())  # (1, 'Amirhosein', 21)

print("fetchmany(2):")
print(cursor.fetchmany(2))  # [(2, 'Mehdi', 35), (3, 'Sara', 19)]

print("fetchall():")
print(cursor.fetchall())  # [(4, 'Ali', 22), (5, 'Fatemeh', 20)]

نکته. cursor مثل یه pointer عمل می‌کنه! بعد از هر fetch، متد cursor به سمت نتایج بعدی حرکت می‌کنه. درنهایت:

cursor.execute("SELECT * FROM students")

first = cursor.fetchone()  # اولین نتیجه
next_two = cursor.fetchmany(2)  # دو نتیجه بعدی
rest = cursor.fetchall()  # بقیه نتایج

یکم بیشتر با حالت های مختلف SELECT کردن آشنا بشیم...

cursor.execute("SELECT name, age FROM students")

اینجا گفتم مقادیر مربوط به ستون name و age رو از تمام ردیف ها استخراج کن.

cursor.execute("SELECT * FROM students WHERE age > 20")

cursor.execute("SELECT * FROM students WHERE name = 'Amirhosein'")

cursor.execute("SELECT * FROM students WHERE age > 20 AND name = 'Mehdi'")

دستور WHERE برای فیلتر کردن داده‌ها استفاده میشه. یعنی فقط رکوردهایی رو نشون بده که شرایط خاصی رو دارن.
تو خط اول گفتم تمام ردیف هایی رو برگردون که مقدار ستون age آنها بزرگ تر از 20 باشه.
تو خط سوم گفتم تمام ردیف هایی رو برگردون که مقدار age آنها بزرگ تر از 20 و مقدار name آنها برابر با Mehdi باشه.
عملگرهای پرکاربرد:
= (مساوی)
> (بزرگتر از)
< (کوچکتر از)
=< (بزرگتر یا مساوی)
=> (کوچکتر یا مساوی)
=! یا <> (مخالف)
AND (و)
OR (یا)
BETWEEN (بین دو مقدار): مثل این میمونه که بگی همه دانشجوهای بین 20 تا 30 سال رو بهم نشون بده. مثال:

cursor.execute("SELECT * FROM students WHERE age BETWEEN 20 AND 30")
# or
cursor.execute("SELECT * FROM students WHERE name BETWEEN 'A' AND 'M'")

# دستور "بیت ون" رو بدین شکل هم میتونم بنویسم
cursor.execute("SELECT * FROM students WHERE age >= 20 AND age <= 30")

# میتونم با عملگر "نات" حالت معکوسش رو بنویسم
# دانشجوهای خارج از بازه 20 تا 30 سال
cursor.execute("SELECT * FROM students WHERE age NOT BETWEEN 20 AND 30")
# معادل: cursor.execute("SELECT * FROM students WHERE age < 20 OR age > 30")

# یه مثال دیگه
# دانشجوهای بین 20 تا 30 سال با اسم امیرحسین
cursor.execute("""
SELECT * FROM students 
WHERE age BETWEEN 20 AND 30 
AND name = 'Amirhosein'
""")

# یه مثال دیگه
min_age = 20
max_age = 30
cursor.execute("SELECT * FROM students WHERE age BETWEEN ? AND ?", (min_age, max_age))

LIKE (جستجوی الگویی): برای پیدا کردن مقادیری که با یه الگوی خاص مطابقت دارن استفاده میشه. مثال:

%: جایگزین صفر یا چند کاراکتر
_ : جایگزین دقیقا یک کاراکتر


# اسم‌هایی که با "A" شروع میشن
cursor.execute("SELECT * FROM students WHERE name LIKE 'A%'")

# اسم‌هایی که با "n" تموم میشن  
cursor.execute("SELECT * FROM students WHERE name LIKE '%n'")

# اسم‌هایی که "mi" دارن (در هر جاییش)
cursor.execute("SELECT * FROM students WHERE name LIKE '%mi%'")

# اسم‌هایی که حرف دومشون "a" هست
cursor.execute("SELECT * FROM students WHERE name LIKE '_a%'")

# اسم‌هایی که دقیقا 4 حرفی و با "A" شروع میشن
cursor.execute("SELECT * FROM students WHERE name LIKE 'A___'")

IN: برای پیدا کردن مقادیری که در لیست مشخصی وجود دارن. مثال:

# دانشجوهای با سن 20، 25 یا 30 سال
cursor.execute("SELECT * FROM students WHERE age IN (20, 25, 30)")

# دانشجوهایی با اسم‌های مشخص
cursor.execute("SELECT * FROM students WHERE name IN ('Amirhosein', 'Sara', 'Ali')")

IS NULL (بررسی مقادیر خالی): برای پیدا کردن رکوردهایی که مقدار خالی (NULL) دارن. مثال:

# دانشجوهایی که ایمیل ندارن
cursor.execute("SELECT * FROM students WHERE email IS NULL")

# دانشجوهایی که ایمیل دارن ! چون از "نات" استفاده کردم
cursor.execute("SELECT * FROM students WHERE email IS NOT NULL")

(NULL یعنی ناشناخته یا تعریف نشده، نه خالی یا صفر)
LIMIT (محدود کردن تعداد نتایج): دستور LIMIT برای محدود کردن تعداد نتایج برگشتی از کوئری استفاده میشه. مثل این میمونه که بگی فقط 5 مورد اول رو بهم نشون بده. مثال:

# فرض کن این سطر ها داخل دیتابیس وجود داره
(1, 'Amirhosein', 21)
(2, 'Mehdi', 35)
(3, 'Sara', 19) 
(4, 'Ali', 22)
(5, 'Fatemeh', 20)

cursor.execute("SELECT * FROM students LIMIT 2")
# [(1, 'Amirhosein', 21), (2, 'Mehdi', 35)] # فقط 2 نتیجه اول

cursor.execute("SELECT * FROM students LIMIT 2 OFFSET 2")
# [(3, 'Sara', 19), (4, 'Ali', 22)]
(ردیف‌ها از 0 شمارش میشن)

انواع داده‌های مهم (Data Types) برای تعریف ستون جدول:
INTEGER یا اعداد صحیح:

# اعداد مثبت و منفی
cursor.execute('''
CREATE TABLE examples (
    id INTEGER,           # شماره دانشجو
    age INTEGER,          # سن
    temperature INTEGER   # دما (مثبت و منفی)
)
''')

REAL یا اعداد اعشاری:

cursor.execute('''
CREATE TABLE products (
    price REAL,        # قیمت کالا
    rating REAL        # امتیاز
)
''')

NUMERIC یا اعداد (اعم از صحیح و اعشاری):

cursor.execute('''
CREATE TABLE financial (
    salary NUMERIC,    # حقوق
    bonus NUMERIC     # پاداش
)
''')

TEXT یا رشته‌های متنی:

cursor.execute('''
CREATE TABLE users (
    username TEXT,      # نام کاربری
    email TEXT         # ایمیل
)
''')

BLOB یا داده‌های باینری:

cursor.execute('''
CREATE TABLE files (
    file_data BLOB,        # محتوای فایل
    image_data BLOB       # داده تصویر
)
''')

# کاربرد: ذخیره فایل‌ها، تصاویر، داده‌های رمزنگاری شده

DATETIME یا تاریخ و زمان:

cursor.execute('''
CREATE TABLE logs (
    login_time DATETIME,    # زمان ورود
    access_time DATETIME   # زمان دسترسی
)
''')

# فرمت: YYYY-MM-DD HH:MM:SS مثال
# 2024-01-15 14:30:00

نوع داده BOOLEAN یا true/false:

cursor.execute('''
CREATE TABLE settings (
    is_active BOOLEAN,        # فعال/غیرفعال
    is_admin BOOLEAN          # مدیر/کاربر عادی
)
''')

Constraints: قوانینی که برای ستون‌های جدول تعریف میکنی تا از درست بودن داده‌ها مطمئن بشی. مثل نگهبانِ دیتابیس که مراقبه داده‌های غلط وارد نشن. انواع Constraints:
NOT NULL یا اجبار به پر بودن: نمیذاره ستون خالی بمونه. مثال:

cursor.execute('''
CREATE TABLE users (
    id INTEGER,
    name TEXT NOT NULL,        # حتما باید پر بشه
    age INTEGER NOT NULL,      # حتما باید پر بشه
    email TEXT                 # میتونه خالی باشه
)
''')

اگه موقع مقدار دادن این رو بنویسم: INSERT INTO users (id, age) VALUES (1, 20) خطا دریافت میکنم چون name مقدار دهی نشده.
UNIQUE یا منحصر به فرد بودن: یعنی فقط یکی باشه. مثل اینکه بگی تو این کلاس، هر دانش‌آموز فقط می‌تونه یه شماره دانش‌آموزی منحصر به فرد داشته باشه. مثال:

cursor.execute('''
CREATE TABLE students (
    student_id INTEGER UNIQUE,   # شماره دانش‌آموز تکراری نباید باشه
    name TEXT,
    phone TEXT UNIQUE            # شماره تلفن تکراری نباید باشه
)
''')

مقدار دهی:

# ✅ درست
cursor.execute("INSERT INTO students (student_id, name, phone) VALUES (1, 'Amir', '0912')")
cursor.execute("INSERT INTO students (student_id, name, phone) VALUES (2, 'Ali', '0913')")

# ❌ خطا - شماره دانش‌آموز تکراری
cursor.execute("INSERT INTO students (student_id, name, phone) VALUES (1, 'Sara', '0914')")

# ❌ خطا - شماره تلفن تکراری  
cursor.execute("INSERT INTO students (student_id, name, phone) VALUES (3, 'Maryam', '0912')")

PRIMARY KEY یا شناسه اصلی: PRIMARY KEY ترکیب NOT NULL و UNIQUE هست. مثال:

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,    # همیشه پر باشه، همیشه منحصر به فرد باشه
    name TEXT,
    age INTEGER
)
''')

مقدار دهی:

# ✅ درست
cursor.execute("INSERT INTO users (id, name, age) VALUES (1, 'Amir', 20)")
cursor.execute("INSERT INTO users (id, name, age) VALUES (2, 'Ali', 22)")

# ❌ خطا - id تکراری
cursor.execute("INSERT INTO users (id, name, age) VALUES (1, 'Sara', 19)")

# ❌ خطا - id خالی
cursor.execute("INSERT INTO users (name, age) VALUES ('Maryam', 21)")

CHECK یا بررسی شرط: فقط مقادیری رو قبول میکنه که شرط تو پرانتز رو رعایت کنن. مثال:

cursor.execute('''
CREATE TABLE students (
    age INTEGER CHECK (age >= 18),          # سن باید حداقل 18 باشه
    grade TEXT CHECK (grade IN ('A', 'B', 'C')),  # فقط A, B یا C مجازه
    score INTEGER CHECK (score >= 0 AND score <= 20)  # نمره بین 0 تا 20
)
''')

اگه اینطوری مقدار دهی کنم: INSERT INTO students (age) VALUES (17) خطا دریافت میکنم.
DEFAULT یا مقدار پیش‌فرض: اگه مقداری نذاری، خودش یه مقدار استاندارد میذاره. مثال:

cursor.execute('''
CREATE TABLE logs (
    status INTEGER DEFAULT 1,                       # پیش‌فرض 1 میذاره
    attempts INTEGER DEFAULT 0                      # پیش‌فرض 0 میذاره
)
''')