چرا `SQL`، وقتی `pandas` را بلدی

در این فصل چه یاد می‌گیری#

یک پرسشِ ساده: در این کتابخانه چند کتاب هست؟ دو راه برای جواب دادن هست و هر دو به یک عددِ درست می‌رسند — ۱۲۰۰. ولی یکی از آن دو راه برای گرفتنِ آن یک عدد، ۱۵٬۶۰۰ خانهٔ داده را از پایگاه داده به پایتون می‌آورد و دیگری یک خانه. کلِ این فصل دربارهٔ همان نسبتِ ۱۵٬۶۰۰ به ۱ است، و دربارهٔ اینکه چرا وقتی جدول هزار برابر بزرگ‌تر شود، آن نسبت هم هزار برابر می‌شود.

pandas را کنار نمی‌گذاریم. pandas ابزارِ کار کردن با داده‌ای است که از قبل در حافظه است. پرسشِ این دوره یک قدم عقب‌تر است: آن داده اصلاً چطور به حافظه می‌آید، و چه کسی تصمیم می‌گیرد کدام بخشش بیاید.

آخر این فصل می‌توانی:

• به یک فایلِ پایگاه داده وصل شوی و بدونِ اینکه از قبل چیزی از محتوایش بدانی، فهرستِ جدول‌ها و ستون‌هایش را دربیاوری
• تفاوتِ «همه‌چیز را بردار و بعد بشمار» را با «بگذار پایگاه داده بشمارد» با عدد نشان بدهی
• بگویی یک سطر از یک جدول دقیقاً یعنی چه
• بگویی پرسشی که می‌پرسی دربارهٔ کدام سطرها نیست

قبل از شروع#

از سرنخ ترمِ ۱ و ۲: پایتون در حدِ list و dict و for، و pandas در حدِ DataFrame. همین کافی است.

هیچ آشناییِ قبلی با پایگاه داده فرض نشده — نه «جدول»، نه «سطر»، نه SQL. اگر تا امروز داده را فقط داخلِ CSV دیده‌ای، دقیقاً همان‌جایی هستی که این فصل از آن شروع می‌کند.

سلولِ راه‌اندازیِ نوت‌بوک سه تابع می‌سازد که در کلِ این دوره همین نام و همین امضا را دارند. در این فصل به دو تای اول کار داری: q("...") یک پرسش می‌گیرد، اجرا می‌کند، و نتیجه را به‌شکلِ یک DataFrame برمی‌گرداند؛ و timed(...) میانهٔ چند اجرای همان پرسش را برحسبِ میلی‌ثانیه می‌دهد. سومی — plan — از ترمِ ۴ لازم می‌شود.

💡 نکته: هیچ چیزی نصب نمی‌کنی و به هیچ سروری وصل نمی‌شوی. sqlite3 جزوِ کتابخانهٔ استانداردِ پایتون است و یک پایگاه دادهٔ کامل داخلِ یک فایل است. همین کلِ زیرساختِ این دوره است.

📓 نوت‌بوک: نوت‌بوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

این داده ساختگی است، نه واقعی. یک شبکهٔ کتابخانهٔ خیالی با شش شعبه، که با یک SEED ثابت ساخته می‌شود و روی هر ماشینی دقیقاً همان است. عمداً چند عیبِ واقعی هم دارد — مقدارِ جاافتاده، عددی که به‌شکلِ متن ذخیره شده، و نامی که با صفحه‌کلیدِ عربی وارد شده. آن عیب‌ها موضوعِ فصل‌های ۴ تا ۷ هستند.

۱. یک پرسش، دو راه، و اختلافی که با عدد دیده می‌شود#

پرسش: چند کتاب داریم؟

راهِ اول همان کاری است که با یک CSV می‌کردی — همه‌چیز را بردار، بعد بشمار. راهِ دوم این است که خودِ پرسش را به پایگاه داده بدهی و بگذاری او بشمارد.

متنِ این دو پرسش را فعلاً فقط اجرا کن؛ فصلِ ۲ کلمه‌به‌کلمه بازشان می‌کند.

everything = q("SELECT * FROM books")
just_the_number = q("SELECT COUNT(*) FROM books")

print("راه ۱ — همه‌چیز را بردار، بعد بشمار")
print("   جواب:", len(everything),
      "| خانه‌هایی که از پایگاه داده به پایتون آمد:",
      everything.shape[0] * everything.shape[1])
print("راه ۲ — بگذار پایگاه داده بشمارد")
print("   جواب:", int(just_the_number.iloc[0, 0]),
      "| خانه‌هایی که از پایگاه داده به پایتون آمد:",
      just_the_number.shape[0] * just_the_number.shape[1])

راه ۱ — همه‌چیز را بردار، بعد بشمار
   جواب: 1200 | خانه‌هایی که از پایگاه داده به پایتون آمد: 15600
راه ۲ — بگذار پایگاه داده بشمارد
   جواب: 1200 | خانه‌هایی که از پایگاه داده به پایتون آمد: 1

همان عدد، و پانزده‌هزار و ششصد برابر داده‌ای که جابه‌جا شد.

۱۵٬۶۰۰ از کجا آمد؟ ۱٬۲۰۰ سطر ضربدر ۱۳ ستون. هر خانهٔ آن جدول از دیسک خوانده شد، به شیءِ پایتونی تبدیل شد، در حافظه جا گرفت — و بعد len() همه‌شان را دور ریخت و فقط تعدادشان را نگه داشت.

زمانش را هم ببینیم. timed میانهٔ پنج اجراست، نه یک اجرا؛ اولین اجرا همیشه کندتر است و یک عددِ تکی در این دوره شاهد نیست.

print("همه‌چیز را بردار:", timed("SELECT * FROM books"), "میلی‌ثانیه")
print("بگذار بشمارد   :", timed("SELECT COUNT(*) FROM books"), "میلی‌ثانیه")

همه‌چیز را بردار: 3.05 میلی‌ثانیه
بگذار بشمارد   : 0.06 میلی‌ثانیه

عددِ دقیقِ زمان روی ماشینِ تو فرق می‌کند — به همین دلیل در یک بلوکِ جدا آمده و ادعای قطعی نیست. چیزی که فرق نمی‌کند نسبت است: روی همین جدولِ کوچک، شمردن ده‌ها برابر ارزان‌تر از برداشتن است. و این جدول ۱٬۲۰۰ سطر دارد. جدولِ واقعیِ یک کتابخانه میلیون‌ها سطر دارد.

۲. زیرِ کاپوت: connect و cursor و fetchall#

q یک تابعِ راحتی است که ما نوشتیم. زیرش سه چیزِ استاندارد است و باید یک بار مستقیم ببینی‌شان، چون پیامِ خطاها از همین لایه می‌آیند.

sqlite3.connect(...) یک اتصال به فایلِ پایگاه داده باز می‌کند. از روی اتصال یک cursor می‌سازی — یک نشانگر که پرسش را اجرا می‌کند و نتیجه را قدم‌به‌قدم نگه می‌دارد. execute پرسش را می‌فرستد و fetchall هرچه برگشته را یک‌جا به پایتون می‌آورد.

cur = con.cursor()
cur.execute("SELECT * FROM books")
rows = cur.fetchall()

print("چند سطر در دست پایتون است؟", len(rows))
print("هر سطر یک", type(rows[0]).__name__, "است با", len(rows[0]), "خانه")
for row in rows[:2]:
    print(row)

چند سطر در دست پایتون است؟ 1200
هر سطر یک tuple است با 13 خانه
(1, 'روزهای پل', 'مهدی سلیمی', 'رمان', 'نسیم', 730, 1989, 1, 33, 4.6, '308000', None, '2022-07-06')
(2, 'مه در باران', 'رویا رحیمی', 'شعر', 'دانش', 564, 1985, 4, 20, 4.6, '357000', None, '2019-03-16')

به rows[:2] خوب نگاه کن، چون بندِ اصلیِ این فصل همین‌جاست. برای دیدنِ دو سطر، fetchall() هر ۱٬۲۰۰ سطر را آورد و بعد پایتون ۱٬۱۹۸تای آن‌ها را دور ریخت. کارِ برش زدن در پایتون انجام شد، بعد از اینکه هزینه‌اش پرداخت شده بود.

و دو چیزِ دیگر در همان دو سطر هست که فصل‌های بعد سراغشان می‌آید: None در ستونِ یکی‌مانده‌به‌آخر (فصل ۶)، و '308000' که با کوتیشن آمده، یعنی متن است نه عدد (فصل ۵).

✅ چک کن: len(rows) باید همان ۱۲۰۰ باشد که COUNT(*) گفت. اگر این دو با هم نخوانند، یعنی سلولِ راه‌اندازی را دوباره اجرا کرده‌ای و پایگاه داده از نو ساخته شده در حالی که rows مالِ نسخهٔ قبلی است — همان دامِ «حالتِ پنهان» که در سرنخ دیدی. راهِ‌حل همیشه یکی است: Restart session and run all.

۳. پایگاه داده خودش می‌داند چه دارد#

این تفاوتِ دوم با یک فایل است، و در عمل بیشتر از تفاوتِ اول به کارت می‌آید. یک CSV فقط بایت است؛ برای فهمیدنِ اینکه چه ستون‌هایی دارد باید بازش کنی. یک پایگاه داده اسکیمای خودش را در خودش نگه می‌دارد و می‌شود ازش پرسید.

اول ستون‌ها. cursor بعد از هر execute می‌داند چه ستون‌هایی برگردانده:

print([column[0] for column in cur.description])

['id', 'title', 'author', 'category', 'branch', 'pages', 'year', 'edition', 'borrowed', 'rating', 'price_text', 'audio_minutes', 'added_at']

سیزده ستون — همان ۱۳ که در ضربِ ۱۵٬۶۰۰ دیدی.

و حالا فهرستِ چیزهایی که در این فایل هست. SQLite این فهرست را در جدولی به نامِ sqlite_master نگه می‌دارد، و آن هم مثلِ هر جدولِ دیگری خواندنی است:

print(q("SELECT type, name FROM sqlite_master"))

    type                         name
0  table                     branches
1  index  sqlite_autoindex_branches_1
2  table                        books

دو جدول داریم: books و branches. سطرِ وسط یک index است که SQLite خودش ساخته — فعلاً همین‌قدر بدان که چیزی است برای سریع‌تر پیدا کردن؛ ترمِ ۵ کاملاً بازش می‌کند.

این کار را روی هر پایگاه دادهٔ ناشناسی که به دستت می‌رسد اول از همه انجام بده. قبل از هر پرسشی، اول ببین چه جدول‌هایی هست و هر جدول چه ستون‌هایی دارد.

۴. q(...) و چرا نامش در کلِ دوره ثابت است#

pd.read_sql_query(sql, con) پرسش را اجرا می‌کند و نتیجه را مستقیم به DataFrame تبدیل می‌کند. q دقیقاً همین است با یک نام کوتاه‌تر:

print(q("SELECT * FROM branches"))

    name      city   opened_at  seats
0  مرکزی     تهران  1998-03-21    240
1   نسیم      مشهد  2004-06-11    120
2   دانش     شیراز  2009-09-02     90
3   بهار     تبریز  2013-01-19     75
4   کوشا    اصفهان  2017-05-27     60
5   پویا  کرمانشاه  2021-11-08     45

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

یک نکتهٔ فنی که همین‌جا باید گفته شود: سازندهٔ نوت‌بوکِ این دوره فقط بلوک‌های python را به سلولِ اجراشدنی تبدیل می‌کند. اگر جایی متنِ یک پرسش را در بلوکی با برچسبِ sql دیدی، آن متن برای خواندن است و اجرا نمی‌شود. هر پرسشی که نتیجه‌اش را در این کتاب می‌بینی، از داخلِ q اجرا شده — یعنی خروجی‌اش واقعاً همان چیزی است که خودت هم می‌گیری.

🔧 اگر کار نکرد: رایج‌ترین خطای روزِ اول، یک حرفِ جاافتاده در نامِ جدول است. SQLite هیچ حدسی نمی‌زند:

try:
    con.execute("SELECT COUNT(*) FROM book").fetchone()
except sqlite3.OperationalError as error:
    print("sqlite3.OperationalError:", error)

sqlite3.OperationalError: no such table: book

جدولِ ما books است، با s. پیامِ خطا دقیقاً همین را می‌گوید و راهِ درستِ برخورد با آن این است: به sqlite_master برگرد و نامِ واقعی را از آنجا بردار، نه از حافظه‌ات.

۵. یک سطر یعنی چه — و چه کسی از قلم افتاد#

یک سطر از books یعنی: یک عنوانِ کتاب که در یکی از شعبه‌ها ثبت شده. نه «یک نسخهٔ فیزیکی»، نه «یک امانت»، نه «یک نویسنده».

بدونِ این جمله، عددِ ۱۲۰۰ سه معنیِ متفاوت دارد و هر سه هم قابلِ باورند: ۱۲۰۰ عنوانِ متمایز، ۱۲۰۰ جلدِ فیزیکی، یا ۱۲۰۰ ثبت در فهرست. معنیِ درست، سومی است.

و حالا پرسشِ سومِ این دوره: چه کسی از قلم افتاد؟

in_the_database = int(q("SELECT COUNT(*) FROM books").iloc[0, 0])
on_the_disk = len(CSV_PATH.read_text(encoding="utf-8").splitlines()) - 1

print("کتاب‌های داخل پایگاه داده:", in_the_database)
print("سطرهای arrivals.csv که هنوز وارد نشده‌اند:", on_the_disk)
print("هر پرسشی امروز بپرسی، درباره", in_the_database, "کتاب است، نه",
      in_the_database + on_the_disk)

کتاب‌های داخل پایگاه داده: 1200
سطرهای arrivals.csv که هنوز وارد نشده‌اند: 240
هر پرسشی امروز بپرسی، درباره 1200 کتاب است، نه 1440

دویست‌وچهل کتاب روی دیسکِ همین ماشین هستند و در هیچ پرسشی از این ترم دیده نمی‌شوند تا فصلِ ۷ که واردشان می‌کنیم. گزارشی که دربارهٔ «همهٔ کتاب‌ها» حرف می‌زند، در واقع دربارهٔ همهٔ کتاب‌هایی است که وارد شده‌اند.

📏 اندازه بگیر: یک سطر یعنی چه؟ یک عنوانِ ثبت‌شده در یک شعبه. با کدام شمارشِ دوم سنجیدی؟ با دو مسیرِ کاملاً مستقل: COUNT(*) که داخلِ پایگاه داده شمرد، و len(rows) که در پایتون روی سطرهای برگشته شمرد. هر دو ۱۲۰۰. چه کسی از قلم افتاد؟ ۲۴۰ سطرِ arrivals.csv که هنوز وارد نشده‌اند، و همچنین هر کتابی که هرگز در فهرست ثبت نشده — که دربارهٔ آن حتی نمی‌توانیم بگوییم چندتاست.

۶. وقتی داده در حافظه جا نمی‌شود#

جدولِ ما ۱٬۲۰۰ سطر دارد و راحت در حافظه جا می‌شود؛ اگر امروز کارِ تو همین اندازه داده است، pandas به‌تنهایی کاملاً کافی است. مسئله آنجا شروع می‌شود که جدول بزرگ شود. عددهای زیر ضرب و تقسیمِ ساده‌اند، نه اندازه‌گیری:

cells_now = everything.shape[0] * everything.shape[1]
print("اگر جدول این‌قدر برابر بزرگ‌تر شود:")
for factor in (1, 100, 10_000):
    print(f"   ×{factor:<6} → fetchall خانه می‌آورد: {cells_now * factor:>12,}"
          f"   | COUNT(*) خانه می‌آورد: 1")

اگر جدول این‌قدر برابر بزرگ‌تر شود:
   ×1      → fetchall خانه می‌آورد:       15,600   | COUNT(*) خانه می‌آورد: 1
   ×100    → fetchall خانه می‌آورد:    1,560,000   | COUNT(*) خانه می‌آورد: 1
   ×10000  → fetchall خانه می‌آورد:  156,000,000   | COUNT(*) خانه می‌آورد: 1

ستونِ چپ با اندازهٔ داده رشد می‌کند، ستونِ راست نمی‌کند. این کلِ ماجراست.

پایگاه داده فایل را صفحه‌صفحه از دیسک می‌خواند و فقط همان چیزی را در حافظه نگه می‌دارد که برای جواب لازم است. برای همین می‌شود از جدولی که ده گیگابایت است پرسید «چند سطر داری؟» بدونِ اینکه ده گیگابایت را به حافظه بیاوری. با read_csv این کار ممکن نیست، چون read_csv هیچ راهی ندارد جز خواندنِ کلِ فایل.

🤖 از دستیارت بپرس: «فایلِ .db در SQLite دقیقاً چه چیزی داخلش دارد و چرا یک فایل می‌تواند چند جدول باشد؟» بعد این را بپرس: «چرا SQLite می‌تواند COUNT(*) را روی جدولی که در حافظه جا نمی‌شود اجرا کند، ولی pandas نمی‌تواند؟» — دنبالِ کلمهٔ «صفحه» (page) در جواب بگرد؛ اگر جواب آن را نداشت، ناقص است.

واژه‌های تازهٔ این فصل#

در فصل بعد#

در این فصل دو پرسش را اجرا کردیم بدونِ اینکه بگوییم چرا این شکلی نوشته شده‌اند. فصلِ بعد همان بدهی را می‌پردازد: SELECT و FROM و LIMIT، انتخابِ ستون به‌جای *، و اولین عددهای واقعی از این جدول — ۹۴٬۱۹۱ امانت روی ۱٬۲۰۰ کتاب، و میانگینی که با ۱۶ رقمِ اعشار چاپ می‌شود و باید بفهمی چرا.