قواعد بيانات SQL

الجداول والاستعلامات ولماذا قاعدة بيانات

الصفقات والأسعار والمراكز والبيانات المرجعية جداول: صفوف بالأعمدة نفسها. وقاعدة البيانات العلائقية تخزنها على القرص وتسمح لبرامج كثيرة بالقراءة والكتابة معًا وتحافظ على اتساقها إن فشل شيء في منتصف العملية وتجيب عن أسئلة مكتوبة بـ SQL دون أن تحمّل كل شيء في الذاكرة. وsqlite3 تأتي مع بايثون وتحفظ قاعدة كاملة في ملف واحد وهذا يكفي للبحث؛ وتفعل PostgreSQL وخوادم مشابهة الشيء نفسه في الإنتاج. والعبارات الأساسية CREATE TABLE وINSERT وSELECT ... WHERE ... ORDER BY ... LIMIT وUPDATE وDELETE.

# A database in memory with sqlite3: create, insert, query
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trades (symbol TEXT, price REAL, qty INTEGER)")
db.executemany("INSERT INTO trades (symbol, price, qty) VALUES (?, ?, ?)",
               [("AAPL", 184.25, 100), ("MSFT", 410.5, 50), ("AAPL", 184.5, 25)])

rows = db.execute("SELECT symbol, price FROM trades WHERE price > ? ORDER BY price DESC LIMIT 2", (184.3,)).fetchall()
print(rows)                                          # [('MSFT', 410.5), ('AAPL', 184.5)]

معاملات لا لصق نصوص

لا تبنِ استعلامًا بلصق قيمة في النص: f"... WHERE symbol = '{name}'" يتيح لقيمة مثل x' OR '1'='1 إعادة كتابة الاستعلام (حقن SQL)، وعلامات الاقتباس داخل قيمة مشروعة تكسره. ضع علامة ? مكان القيمة ومرّر القيم منفصلة؛ يرسلها المشغّل بيانات لا تُقرأ أبدًا كـ SQL، وتستطيع القاعدة إعادة استعمال خطة الاستعلام. والعلامات تمثل القيم فقط. أما أسماء الجداول والأعمدة فلا يمكن أن تكون معاملات، فإن جاء اسم من الخارج فتحقق منه بنمط صارم (حروف وأرقام وشرطة سفلية) أو بقائمة أسماء مسموحة قبل وضعه في النص.

# A pasted value rewrites the query; a placeholder keeps it data
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, secret TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("alice", "a1"), ("bob", "b2")])

evil = "x' OR '1'='1"
pasted = db.execute(f"SELECT secret FROM users WHERE name = '{evil}'").fetchall()
safe = db.execute("SELECT secret FROM users WHERE name = ?", (evil,)).fetchall()
print(pasted)                                        # [('a1',), ('b2',)]  the injected condition matched every row
print(safe)                                          # []                  the same text is just an unknown name

التجميعات والربط والفهارس

يضم GROUP BY الصفوف التي تتشارك مفتاحًا ويحسب SUM أو AVG أو COUNT أو MIN أو MAX لكل مجموعة: الكمية الإجمالية المتداولة لكل رمز مثلًا. ويجمع JOIN جدولين على عمود مشترك؛ ويُبقي INNER JOIN الصفوف التي لها نظير في الجانبين فقط ويُبقي LEFT JOIN كل صفوف اليسار. وإجراء ذلك داخل القاعدة أسرع بكثير من حلقة بايثون. وللجداول الكبيرة أضف فهرسًا على الأعمدة التي تصفّي أو تربط بها (CREATE INDEX idx_trades_symbol ON trades(symbol)): فتقفز القاعدة إلى الصفوف المطابقة بدل مسح الجدول كله ويُظهر EXPLAIN QUERY PLAN ما اختارته. وغلّف الكتابات المترابطة في معاملة لتحدث كلها أو لا يحدث شيء.

# GROUP BY and JOIN done by the database
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trades (symbol TEXT, qty INTEGER)")
db.execute("CREATE TABLE sectors (symbol TEXT, sector TEXT)")
db.executemany("INSERT INTO trades VALUES (?, ?)", [("AAPL", 100), ("MSFT", 50), ("AAPL", 25), ("XOM", 10)])
db.executemany("INSERT INTO sectors VALUES (?, ?)", [("AAPL", "tech"), ("MSFT", "tech")])

print(db.execute("SELECT symbol, SUM(qty) FROM trades GROUP BY symbol ORDER BY symbol").fetchall())
# [('AAPL', 125), ('MSFT', 50), ('XOM', 10)]
print(db.execute("SELECT s.sector, SUM(t.qty) FROM trades t JOIN sectors s ON t.symbol = s.symbol GROUP BY s.sector").fetchall())
# [('tech', 175)]   XOM has no sector, so the inner join drops it

# An index turns a full scan into a lookup; the query plan shows which one the database uses
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE ticks (symbol TEXT, ts INTEGER, price REAL)")
db.executemany("INSERT INTO ticks VALUES (?, ?, ?)", [("S%d" % (i % 50), i, 100.0 + i % 7) for i in range(5000)])

query = "EXPLAIN QUERY PLAN SELECT price FROM ticks WHERE symbol = 'S7'"
before = " ".join(row[-1] for row in db.execute(query))
db.execute("CREATE INDEX idx_ticks_symbol ON ticks(symbol)")
after = " ".join(row[-1] for row in db.execute(query))
print("SCAN" in before, "INDEX" in after)            # True True   a full scan before the index, an index search after

• تخزن القاعدة العلائقية الجداول على القرص وتجيب عن استعلامات SQL؛ وsqlite3 تحفظ قاعدة كاملة في ملف واحد.
• مرّر القيم معاملات ? ولا تلصقها في النص أبدًا (حقن SQL)؛ وتحقق من أسماء الجداول والأعمدة بنمط صارم.
• استعمل GROUP BY وJOIN والفهارس ليتولى القاعدة العمل؛ وأضف قيودًا وأبقِ الطوابع بتوقيت UTC والمال دقيقًا واحتفظ بنسخ احتياطية.

التالي: Parquet وArrow، صيغ عمودية للبيانات التحليلية الكبيرة.

المختبر: ما الذي يُقيَّم

• لا تلصق القيم أبدًا: insert_statement25 نقطة
• ركّب استعلامًا باستخدام build_select25 نقطة
• اجمع حسب المفتاح باستخدام group_sum25 نقطة
• طابق جدولين باستخدام inner_join25 نقطة