دادهٔ واقعی را وارد کن

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

از فصلِ ۱ یک فایل روی دیسک منتظر است: arrivals.csv با ۲۴۰ کتابِ تازه‌رسیده که در هیچ پرسشی از این ترم دیده نشده‌اند. این فصل واردشان می‌کند.

و در همان لحظهٔ ورود، ۲۷ سطر ستونِ yearشان به‌جای عدد، متن ذخیره می‌شود — همان تلهٔ فصلِ ۵، این بار جلوی چشممان. نتیجه‌اش قابلِ اندازه‌گیری است: پرسشِ «چند کتاب از ۲۰۲۳ به بعد؟» روی جدولِ خام ۱۰۴ می‌دهد و روی همان داده، وقتی درست وارد شده باشد، ۸۲.

درسِ اصلی همین است: بهترین جا برای پاک کردنِ داده، لحظهٔ ورودش است. هر چیزی که همان‌جا درست نشود، باید در تک‌تکِ پرسش‌های بعدی وصله شود — و اولین باری که یادت برود، عددت غلط می‌شود.

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

• با CREATE TABLE جدول بسازی و برایش PRIMARY KEY بگذاری
• با INSERT و پارامترِ ? سطر اضافه کنی، و بگویی چرا چسباندنِ مقدار ممنوع است
• با executemany صدها سطر را با یک فرمان وارد کنی
• با PRAGMA table_info ببینی پایگاه داده فکر می‌کند چه ساخته‌ای
• ثابت کنی که ورودِ یک ستون خراب شده، و درستش کنی

قبل از شروع#

از فصلِ ۵: type affinity — ستونی که INTEGER اعلام شده ولی مقدارِ متنی را همان‌طور که هست نگه می‌دارد.

بدهیِ فصلِ ۳ اینجا پرداخت می‌شود: چهار فصل است هر مقدار را با ? فرستادیم و گفتیم «فصلِ ۷ می‌گوید چرا». بخشِ ۳ دلیلش را با یک خطای واقعی نشان می‌دهد.

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

۱. اول فایل را ببین#

قاعدهٔ سرنخ که اینجا هم پابرجاست: اول داده را ببین. فایل را با کتابخانهٔ csv می‌خوانیم چون می‌خواهیم دقیقاً همان چیزی را ببینیم که در فایل هست، بدونِ هیچ تفسیری.

import csv

with CSV_PATH.open(encoding="utf-8") as fh:
    incoming = list(csv.DictReader(fh))

print("سطرهای فایل:", len(incoming))
print("ستون‌ها     :", list(incoming[0]))
print("سطر نمونه   :", incoming[0])

سطرهای فایل: 240
ستون‌ها     : ['title', 'author', 'category', 'pages', 'year', 'price_text', 'donor']
سطر نمونه   : {'title': 'سنگ در باران', 'author': 'سعید رحیمی', 'category': 'شعر', 'pages': '619', 'year': '2023', 'price_text': '492000', 'donor': 'رضا صادقی'}

به کوتیشن‌ها نگاه کن: '619' و '2023' هر دو رشته‌اند. یک فایلِ CSV نوع ندارد؛ همه‌چیزش متن است. هر نوعی که در پایگاه داده بگیرند، تصمیمِ ماست.

۲. CREATE TABLE#

ARRIVALS_TABLE = """
CREATE TABLE arrivals (
    id         INTEGER PRIMARY KEY,
    title      TEXT,
    author     TEXT,
    category   TEXT,
    pages      INTEGER,
    year       INTEGER,
    price_text TEXT,
    donor      TEXT
)
"""

con.execute("DROP TABLE IF EXISTS arrivals")
con.execute(ARRIVALS_TABLE)
con.commit()
print(q("SELECT type, name FROM sqlite_master"))

    type                         name
0  table                     branches
1  index  sqlite_autoindex_branches_1
2  table                        books
3  table                     arrivals

جدولِ چهارم ساخته شد و در همان sqlite_masterِ فصلِ ۱ نشست.

id INTEGER PRIMARY KEY یعنی این ستون یکتاست و اگر مقداری برایش ندهی، SQLite خودش شماره می‌دهد. فعلاً همین‌قدر بدان؛ ترمِ ۴ فصلِ ۲ کاملاً بازش می‌کند — چه چیزی می‌تواند کلید باشد، کِی کلیدِ طبیعی بهتر است، و چرا کلیدی که فکر می‌کردی یکتاست معمولاً نیست.

و DROP TABLE IF EXISTS را جدی بگیر: بدونِ آن، اجرای دوبارهٔ همین سلول خطا می‌دهد. متنِ ساختِ جدول را در یک متغیرِ پایتونی نگه داشتیم تا بعداً بتوانیم دوباره اجرایش کنیم — این نگه داشتنِ متنِ پرسش است، نه چسباندنِ مقدار به آن. بخشِ بعد دقیقاً همین مرز را نشان می‌دهد.

۳. INSERT با ? — و چرا چسباندنِ مقدار ممنوع است#

con.execute(
    "INSERT INTO arrivals (title, author, category, pages, year) VALUES (?, ?, ?, ?, ?)",
    ("کتاب آزمایشی", "نام آزمایشی", "علم", 100, 2024),
)
con.commit()
print(q("SELECT id, title, pages, year, donor FROM arrivals"))

   id         title  pages  year donor
0   1  کتاب آزمایشی    100  2024  None

id را ندادیم و SQLite خودش ۱ گذاشت. donor را هم ندادیم و NULL شد — چون ستونی که مقدار نگیرد، خالی می‌ماند، نه صفر و نه رشتهٔ خالی.

con.commit() را جا نینداز. sqlite3ِ پایتون خودش تراکنش را باز می‌کند ولی خودش نمی‌بنددش. بدونِ commit، نوشته‌ات با بسته شدنِ اتصال از بین می‌رود و هیچ خطایی هم نمی‌گیری.

و حالا همان چیزی که چهار فصل عقب افتاده بود. فرض کن مقدار را به‌جای ? مستقیم داخلِ متنِ پرسش بچسبانیم:

tricky = "کتابِ 'نقل قول'دار"
sql_by_hand = "SELECT COUNT(*) FROM arrivals WHERE title = '" + tricky + "'"
print("پرسشی که با چسباندن مقدار ساخته شد:")
print(sql_by_hand)
print()
try:
    q(sql_by_hand)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
print()
print("همان پرسش با پارامتر، بدون هیچ دردسری:",
      int(q("SELECT COUNT(*) FROM arrivals WHERE title = ?", (tricky,)).iloc[0, 0]))

پرسشی که با چسباندن مقدار ساخته شد:
SELECT COUNT(*) FROM arrivals WHERE title = 'کتابِ 'نقل قول'دار'

pandas.errors.DatabaseError
Execution failed on sql 'SELECT COUNT(*) FROM arrivals WHERE title = 'کتابِ 'نقل قول'دار'': near "نقل": syntax error

همان پرسش با پارامتر، بدون هیچ دردسری: 0

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

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

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

۴. executemany: ۲۴۰ سطر با یک فرمان#

con.execute("DROP TABLE IF EXISTS arrivals")
con.execute(ARRIVALS_TABLE)

rows = [(r["title"], r["author"], r["category"], r["pages"], r["year"],
         r["price_text"], r["donor"]) for r in incoming]
con.executemany("""
    INSERT INTO arrivals (title, author, category, pages, year, price_text, donor)
    VALUES (?, ?, ?, ?, ?, ?, ?)
""", rows)
con.commit()

print("سطرهای وارد شده:", int(q("SELECT COUNT(*) FROM arrivals").iloc[0, 0]))
print("سطرهای فایل    :", len(incoming))

سطرهای وارد شده: 240
سطرهای فایل    : 240

✅ چک کن: این دو عدد همیشه باید یکی باشند و این اولین شمارشِ کنترلیِ هر واردکردنی است. اگر عددِ اول کمتر بود، سطرهایی رد شده‌اند؛ اگر بیشتر بود، سلول را دو بار اجرا کرده‌ای.

🔧 اگر کار نکرد: پرتکرارترین خطای executemany این است که تعدادِ ?ها با تعدادِ خانه‌های هر tuple نخواند:

try:
    con.executemany("""
        INSERT INTO arrivals (title, author, category, pages, year, price_text, donor)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, [(r["title"], r["author"], r["category"], r["pages"], r["year"])
          for r in incoming[:1]])
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)

sqlite3.ProgrammingError
Incorrect number of bindings supplied. The current statement uses 7, and there are 5 supplied.

پیام دقیقاً می‌گوید چند تا انتظار داشت و چند تا گرفت. همین خطاست که در فصلِ ۳ گفتیم اگر ("شعر",) را بدونِ کاما بنویسی می‌گیری — آن‌وقت پایتون یک رشته می‌بیند و sqlite3 هر حرفش را یک مقدار می‌شمارد.

۵. PRAGMA table_info: پایگاه داده چه فکر می‌کند دارد#

print(q("PRAGMA table_info(arrivals)"))

   cid        name     type  notnull dflt_value  pk
0    0          id  INTEGER        0       None   1
1    1       title     TEXT        0       None   0
2    2      author     TEXT        0       None   0
3    3    category     TEXT        0       None   0
4    4       pages  INTEGER        0       None   0
5    5        year  INTEGER        0       None   0
6    6  price_text     TEXT        0       None   0
7    7       donor     TEXT        0       None   0

هشت ستون، و ستونِ pk می‌گوید فقط id کلیدِ اصلی است. این پرسش را روی هر جدولی که خودت نساخته‌ای بزن؛ پنج ثانیه وقت می‌گیرد و اسکیمای واقعی را به‌ت می‌گوید، نه اسکیمایی که فکر می‌کنی هست.

ولی حواست باشد این جدول فقط «اعلام»ها را نشان می‌دهد، نه واقعیتِ داده. ستونِ year اینجا INTEGER نوشته شده. بخشِ بعد نشان می‌دهد که این ادعا برای ۲۷ سطر درست نیست.

۶. ستونی که موقعِ ورود خراب شد#

for kind in ("integer", "text"):
    n_year = int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(year) = ?", (kind,)).iloc[0, 0])
    n_pages = int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(pages) = ?", (kind,)).iloc[0, 0])
    print(f"  {kind:<8} → year: {n_year:>4} سطر | pages: {n_pages:>4} سطر")
print()
print(q("""
    SELECT id, year, typeof(year) AS kind
    FROM arrivals
    WHERE typeof(year) = ?
    ORDER BY id
    LIMIT 3
""", ("text",)))

  integer  → year:  213 سطر | pages:  240 سطر
  text     → year:   27 سطر | pages:    0 سطر

   id  year  kind
0   9  ۲۰۲۲  text
1  19  ۲۰۲۴  text
2  36  ۲۰۲۳  text

ستونِ pages سالم درآمد و ستونِ year در ۲۷ سطر خراب شد — با اینکه هر دو از یک فایل و با یک فرمان وارد شدند.

تفاوت در خودِ داده است: در فایل، همهٔ صفحه‌ها با رقمِ انگلیسی تایپ شده بودند و ۲۷ سال با رقمِ فارسی. SQLite رشتهٔ '619' را به عدد تبدیل کرد و رشتهٔ '۲۰۲۲' را نتوانست، پس همان‌طور که بود نگه داشت. بی‌صدا، بدونِ هشدار.

و حالا هزینه‌اش:

recent = int(q("SELECT COUNT(*) FROM arrivals WHERE year >= ?", (2023,)).iloc[0, 0])
old = int(q("SELECT COUNT(*) FROM arrivals WHERE year < ?", (2023,)).iloc[0, 0])
broken = int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(year) = ?", ("text",)).iloc[0, 0])

print("year >= 2023 :", recent)
print("year <  2023 :", old)
print("جمع دو تکه   :", recent + old, "| سطرهای جدول: 240")
print("و چند سطر از دستهٔ اول اصلاً متن‌اند؟", broken)

year >= 2023 : 104
year <  2023 : 136
جمع دو تکه   : 240 | سطرهای جدول: 240
و چند سطر از دستهٔ اول اصلاً متن‌اند؟ 27

دقیقاً همان الگوی فصلِ ۵: جمعِ دو تکه درست است و هر دو تکه غلط. هر ۲۷ سطرِ متنی در دستهٔ «۲۰۲۳ به بعد» افتادند، چون متن از عدد بزرگ‌تر شمرده می‌شود — حتی متنی که مقدارش ۲۰۱۸ است.

۷. پاک‌سازی در لحظهٔ ورود#

راهِ درست این نیست که در هر پرسش replace بنویسیم. راهِ درست این است که یک بار، موقعِ ساختنِ tupleها، داده را درست کنیم.

digits = dict(zip("۰۱۲۳۴۵۶۷۸۹", "0123456789"))
clean_rows = []
for r in incoming:
    year = "".join(digits.get(ch, ch) for ch in r["year"])
    clean_rows.append((r["title"], r["author"], r["category"], int(r["pages"]),
                       int(year), r["price_text"], r["donor"] or None))

con.execute("DROP TABLE IF EXISTS arrivals_clean")
con.execute(ARRIVALS_TABLE.replace("arrivals", "arrivals_clean"))
con.executemany("""
    INSERT INTO arrivals_clean (title, author, category, pages, year, price_text, donor)
    VALUES (?, ?, ?, ?, ?, ?, ?)
""", clean_rows)
con.commit()

print("سطرهای متنی در year — جدول خام :",
      int(q("SELECT COUNT(*) FROM arrivals WHERE typeof(year) = ?", ("text",)).iloc[0, 0]))
print("سطرهای متنی در year — جدول پاک :",
      int(q("SELECT COUNT(*) FROM arrivals_clean WHERE typeof(year) = ?", ("text",)).iloc[0, 0]))
print("year >= 2023 — جدول خام        :",
      int(q("SELECT COUNT(*) FROM arrivals WHERE year >= ?", (2023,)).iloc[0, 0]))
print("year >= 2023 — جدول پاک        :",
      int(q("SELECT COUNT(*) FROM arrivals_clean WHERE year >= ?", (2023,)).iloc[0, 0]))
print("donor خالی که NULL شد          :",
      int(q("SELECT COUNT(*) FROM arrivals_clean WHERE donor IS NULL").iloc[0, 0]))

سطرهای متنی در year — جدول خام : 27
سطرهای متنی در year — جدول پاک : 0
year >= 2023 — جدول خام        : 104
year >= 2023 — جدول پاک        : 82
donor خالی که NULL شد          : 38

۱۰۴ در برابرِ ۸۲. بیست‌ودو سطر بی‌جا در دستهٔ «تازه» نشسته بودند.

سه کار در حلقهٔ بالا انجام شد و هر سه‌شان تصمیم‌اند، نه تشریفات:

1. رقمِ فارسی به انگلیسی — همان ۲۷ سطر را نجات داد.
2. int(...) روی pages و year — تبدیل را از حدسِ SQLite گرفتیم و صریح کردیم. اگر مقداری واقعاً عدد نباشد، حالا خطا می‌گیریم به‌جای اینکه بی‌صدا متن بماند.
3. r["donor"] or None — رشتهٔ خالی را به NULL تبدیل کرد. ۳۸ سطر. این یک انتخاب است، نه یک قاعده: «اهداکننده را نمی‌دانیم» با «اهداکننده‌ای نبوده» فرق دارد، و اینجا تصمیم گرفتیم اولی باشد چون فایل بینشان تفاوتی نگذاشته بود. در گزارش باید همین جمله را بنویسی.

📏 اندازه بگیر: یک سطر یعنی چه؟ یک کتابِ تازه‌رسیده در محمولهٔ این فایل — نه یک کتاب در فهرستِ کتابخانه؛ books و arrivals هنوز دو جدولِ جدا هستند. با کدام شمارشِ دوم سنجیدی؟ سه‌تا: تعدادِ سطرهای فایل در برابرِ سطرهای جدول (۲۴۰ و ۲۴۰)، شمارشِ typeof روی هر ستون (۲۱۳ و ۲۷)، و همان پرسش روی جدولِ خام در برابرِ جدولِ پاک (۱۰۴ و ۸۲). چه کسی از قلم افتاد؟ در جدولِ خام، ۲۲ سطر در دستهٔ اشتباه بودند. و در هر دو جدول، ۳۸ کتاب اهداکننده‌شان نامعلوم است — که حالا NULL است و در هر شرطی روی donor کنار می‌رود.

۸. df.to_sql: راهِ کوتاه، و چیزی که خودش تصمیم می‌گیرد#

frame = pd.DataFrame(incoming)
frame.to_sql("arrivals_auto", con, index=False, if_exists="replace")
con.commit()

print(q("PRAGMA table_info(arrivals_auto)"))
print()
for kind in ("integer", "text"):
    n = int(q("SELECT COUNT(*) FROM arrivals_auto WHERE typeof(pages) = ?", (kind,)).iloc[0, 0])
    print(f"  pages از نوع {kind:<8}: {n:>4} سطر")

   cid        name  type  notnull dflt_value  pk
0    0       title  TEXT        0       None   0
1    1      author  TEXT        0       None   0
2    2    category  TEXT        0       None   0
3    3       pages  TEXT        0       None   0
4    4        year  TEXT        0       None   0
5    5  price_text  TEXT        0       None   0
6    6       donor  TEXT        0       None   0

  pages از نوع integer :    0 سطر
  pages از نوع text    :  240 سطر

یک خط کد، و هر هفت ستون TEXT شد — حتی pages که در روشِ دستی سالم درآمده بود. و هیچ کلیدِ اصلی‌ای هم ساخته نشد.

این باگِ pandas نیست: to_sql نوعِ ستون‌های DataFrame را ترجمه می‌کند، و DataFrameی که از رشته‌های CSV ساخته شده، همه‌اش رشته است. to_sql اسکیما را از دادهٔ ورودی می‌سازد، و اگر دادهٔ ورودی نوعِ درستی نداشته باشد، اسکیما هم ندارد.

پس کِی از هرکدام استفاده کنیم؟ to_sql برای وقتی که جدول یک‌بارمصرف است یا DataFrame از قبل نوعِ درست دارد. CREATE TABLE + executemany برای هر جدولی که قرار است بماند — چون آنجا تو تصمیم می‌گیری هر ستون چه نوعی باشد، نه یک تابع.

🤖 از دستیارت بپرس: «SQL injection چیست و چرا پارامتر جلویش را می‌گیرد؟» بعد این را بپرس: «چرا نمی‌شود نامِ جدول را هم با ? فرستاد؟» — جواب به تفاوتِ «ساختارِ پرسش» و «مقدار» اشاره می‌کند: پرسش قبل از رسیدنِ مقدارها تحلیل می‌شود، پس ساختارش باید همان موقع معلوم باشد.

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

در فصل بعد#

هفت فصل ابزار جمع کردیم. فصلِ آخرِ این ترم هیچ چیزِ تازه‌ای یاد نمی‌دهد و به‌جایش یک کارِ کامل تحویل می‌دهد: سه پرسشِ واقعی روی همین ۲۴۰ کتاب، هرکدام با یک شمارشِ دوم و یک جملهٔ صریح دربارهٔ اینکه چه کسی از قلم افتاد. و یکی از آن سه پرسش دو پاسخِ کاملاً درست دارد — یکی ۹۹ و دیگری ۸ — که تفاوتشان فقط در این است که «یک سطر یعنی چه».