SQL + Python (pandas)

12-bob 12 kod bloki 2641 so'z

Bu bob — butun qo'llanmaning sharniri. Undan oldingi hamma narsa natijani database ichida hosil qilardi; undan keyingi hamma narsaga esa o'sha natija Python ichida, Seaborn chiza oladigan DataFrame sifatida kerak.

SQL qayerda tugab, pandas boshlanadi

Talabalar doim so'raydi: "pandas groupby qila olsa, nega men GROUP BY ni o'rgandim?" Javob ish qayerda bajarilishi haqida.

# ❌ hammasini tortadi, keyin ko'p qismini tashlab yuboradi
df = pd.read_sql("SELECT * FROM enrollments", conn)
result = df[df.status == 'completed'].groupby('course_id')['grade'].mean()

# ✅ ishni database bajaradi; tarmoqdan atigi 5 ta row o'tadi
result = pd.read_sql("""
    SELECT course_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE status = 'completed'
    GROUP BY course_id
""", conn)

sqlite3 Python bilan birga keladi — hech narsa o'rnatish kerak emas.

import sqlite3

conn = sqlite3.connect("school.db")     # fayl; vaqtinchalik DB uchun ":memory:"
cur  = conn.cursor()

# query ni bajarish va row larni olish
cur.execute("SELECT first_name, city FROM students WHERE city = 'Tashkent'")
for row in cur.fetchall():
    print(row)                          # ('Aziza', 'Tashkent') — oddiy tuple

# bitta row uchun fetchone(), guruh uchun fetchmany(n)

# yozishlar commit qilinishi kerak
cur.execute("UPDATE students SET city = 'Tashkent' WHERE student_id = 7")
conn.commit()

conn.close()

:::tip 💡 Hayotni osonlashtiradigan ikkita sozlama ``

python
# row lar tuple emas, dict kabi ishlaydi
conn.row_factory = sqlite3.Row
row = conn.execute("SELECT * FROM students LIMIT 1").fetchone()
print(row["first_name"])        # 'Aziza'

# foreign key lar standart holatda O'CHIQ (10-bob)
conn.execute("PRAGMA foreign_keys = ON")

# context manager muvaffaqiyatda commit, xatoda rollback qiladi
with conn:
    conn.execute("DELETE FROM enrollments WHERE enrollment_id = 20")

`` :::

pandas read_sql

Bitta qator har qanday query ni DataFrame ga aylantiradi.

import pandas as pd
import sqlite3

conn = sqlite3.connect("school.db")

df = pd.read_sql("""
    SELECT
        s.first_name || ' ' || s.last_name AS student,
        c.title                            AS course,
        e.grade,
        e.status,
        e.enrolled_at
    FROM enrollments e
    JOIN students s ON e.student_id = s.student_id
    JOIN courses  c ON e.course_id  = c.course_id
""", conn)

print(df.shape)      # (20, 5)
print(df.dtypes)
df.head()

:::gotcha ⚠️ pandas kirish yo'lida o'zgartiradigan uchta narsa - SQL NULL NaN ga aylanadi — va NaN float bo'lgani uchun NULL li integer column float64 bo'lib keladi. Shuning uchun grade 92 emas, 92.0 ko'rinadi. - Sanalar string bo'lib keladi. SQLite da date type yo'q, shuning uchun enrolled_at — object column. Uni o'giring, aks holda vaqtga oid hech narsa ishlamaydi: df["enrolled_at"] = pd.to_datetime(df["enrolled_at"]) Yoki pandas ning o'zi qilsin: pd.read_sql(..., parse_dates=["enrolled_at"]). - Column nomlari sizning alias laringizdan olinadi. Alias siz hisoblangan column nomi sifatida xunuk ifoda oladi — doim AS yozishning yana bir sababi. :::

Foydali parametrlar:

pd.read_sql(query, conn, index_col="student_id")          # index ni belgilash
pd.read_sql(query, conn, parse_dates=["enrolled_at"])      # sanalarni yo'lda o'girish
pd.read_sql(query, conn, params=(60,))                     # parametrlar — pastga qarang
pd.read_sql_table("students", engine)                      # butun table (faqat SQLAlchemy)

Parametrlar va SQL injection Bu bobdagi eng muhim xavfsizlik mavzusi, va uni har bir analyst tushunishi kerak — hatto hech qachon web ilova qurmasa ham.

# ❌ SQL ni HECH QACHON f-string yoki + bilan qurmang
city = "Tashkent"
df = pd.read_sql(f"SELECT * FROM students WHERE city = '{city}'", conn)

# ✅ qiymatlarni parametr sifatida uzating
df = pd.read_sql("SELECT * FROM students WHERE city = ?", conn, params=(city,))

# to'g'ridan-to'g'ri sqlite3 da
cur.execute("SELECT * FROM students WHERE city = ? AND gender = ?", ("Tashkent", "F"))

# bir nechta qiymat bo'lganda nomlangan parametrlar o'qishliroq
cur.execute("SELECT * FROM enrollments WHERE grade > :min AND status = :st",
            {"min": 80, "st": "completed"})

:::gotcha ⚠️ Nega f-string variant xavfli Faraz qiling, qiymat forma orqali keladi va kimdir shuni yozadi:

city = "Tashkent'; DROP TABLE students; --" f-string shuni quradi va database ikkala ifodani ham itoatkorlik bilan bajaradi:

SELECT * FROM students WHERE city = 'Tashkent'; DROP TABLE students; --' Bu — SQL injection. ? bilan esa qiymat query matnidan alohida yuboriladi va data deb qaraladi, hech qachon SQL deb emas — hujum g'alati nomli shaharni qidirishga aylanadi.

Gap faqat hujumchilarda emas: O'Brien kabi mutlaqo begunoh ism ham f-string query ni buzadi, chunki tutuq belgisi string ni yopib qo'yadi. Parametrlar buni ham hal qiladi. :::

DataFrame ni qaytarib yozish (to_sql)

summary = pd.read_sql("""
    SELECT course_id, COUNT(*) AS n, AVG(grade) AS avg_grade
    FROM enrollments GROUP BY course_id
""", conn)

summary.to_sql("course_summary", conn, if_exists="replace", index=False)

if_existsXatti-harakati

'fail' (standart)Table mavjud bo'lsa xato beradi 'replace'Table ni DROP qiladi va qaytadan yaratadi 'append'Mavjud table ga row qo'shadi

:::gotcha ⚠️ if_exists='replace' table ni o'chiradi U table ni bo'shatmaydi — uni DROP qiladi va DataFrame ning dtype laridan yangisini quradi. Har bir constraint, index va foreign key yo'q bo'ladi. Uni hech qachon real table ga yo'naltirmang; faqat o'zingizga tegishli vaqtinchalik/xulosa table lari uchun ishlating.

Shuningdek, DataFrame index i column bo'lib yozilishini xohlamasangiz index=False uzating — database dagi ortiqcha "index" column i klassik bezovtalik. :::

SQLAlchemy va boshqa database lar

sqlite3 faqat SQLite tilida gapiradi. SQLAlchemy pandas ga har qanday database uchun yagona interfeys beradi, ya'ni bir xil kod production da Postgres bilan, darsda SQLite bilan ishlaydi.

from sqlalchemy import create_engine
import pandas as pd

# o'zgaradigan yagona narsa — connection string
engine = create_engine("sqlite:///school.db")
# engine = create_engine("postgresql+psycopg2://user:pass@localhost:5432/school")
# engine = create_engine("mysql+pymysql://user:pass@localhost:3306/school")

df = pd.read_sql("SELECT * FROM students", engine)
df.to_sql("students_copy", engine, if_exists="replace", index=False)

:::gotcha ⚠️ Parolni hech qachon kodga yozmang git ga commit qilingan real parolli connection string — bu haqiqiy xavfsizlik hodisasi, va bu doim sodir bo'ladi. Uni environment dan o'qing:

import os
engine = create_engine(
    f"postgresql+psycopg2://{os.environ['DB_USER']}:{os.environ['DB_PASS']}"
    f"@{os.environ['DB_HOST']}/school"
)

Qiymatlarni .gitignore da ko'rsatilgan .env faylida saqlang. :::

Katta natijalar va chunking

Agar natija haqiqatan xotiraga sig'masa, uni bo'laklab o'qing:

total = 0
for chunk in pd.read_sql("SELECT * FROM huge_table", conn, chunksize=100_000):
    total += chunk["amount"].sum()      # bir vaqtda 100k row ni qayta ishlash
print(total)

:::tip 💡 Chunking ga qo'l cho'zishdan oldin bitta savol bering "Menga haqiqatan har bir row kerakmi?" Yuz holatning to'qson to'qqiztasida javob — yo'q, va SQL dagi GROUP BY 50 million o'rniga 500 ta row qaytaradi. Chunking — haqiqiy istisnolar uchun: eksport, row-ma-row o'zgartirish, model ni partiyalab oziqlantirish. :::

SQL ↔ pandas mosliklari

Tarjima jadvali — talabalarni ikkalasida ham ravon qilishning eng tez yo'li.

Query dan grafikgacha

To'liq topshiriq, boshidan oxirigacha. Qo'llanmaning II qismi aynan shu pattern ustiga quriladi:

import sqlite3
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

conn = sqlite3.connect("school.db")

# 1. Join va qisqartirishni SQL bajaradi — atigi 14 ta row qaytadi
df = pd.read_sql("""
    SELECT
        c.title    AS course,
        c.category AS category,
        e.grade    AS grade,
        e.status   AS status
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
    WHERE e.grade IS NOT NULL
""", conn)
conn.close()

# 2. Chizishni Seaborn bajaradi
sns.set_theme(style="whitegrid")
plt.figure(figsize=(9, 5))
sns.boxplot(data=df, x="course", y="grade", hue="category")
plt.xticks(rotation=20)
plt.title("Course lar bo'yicha baho taqsimoti")
plt.tight_layout()
plt.savefig("grades_by_course.png", dpi=150)

:::info 📌 Seaborn xohlaydigan shakl Seaborn long (tidy) format ni kutadi: har bir kuzatuv uchun bitta row, har bir o'zgaruvchi uchun bitta column. Normalizatsiya qilingan database va JOIN aynan shuni tabiiy ravishda hosil qiladi — shuning uchun 9-bobdagi UNION ALL unpivot usuli mavjud, ya'ni data ingiz allaqachon keng bo'lgan kamdan-kam holatlar uchun.

II qismdagi har bir x=, y=, hue=, col= argumenti shunchaki shu DataFrame dagi column nomi. Asosiy ish — SQL ni to'g'ri yozish; chizish esa bitta qator. :::

Mashqlar to'plami

# 1. Ulanib, setup script dan database ni tuzing
import sqlite3, pandas as pd
conn = sqlite3.connect("school.db")
with open("school_setup.sql", encoding="utf-8") as f:
    conn.executescript(f.read())

# 2. Butun table ni o'qing
students = pd.read_sql("SELECT * FROM students", conn)

# 3. Parametrli query — hech qachon f-string emas
min_grade = 80
top = pd.read_sql("SELECT * FROM enrollments WHERE grade >= ?", conn, params=(min_grade,))

# 4. pandas da emas, SQL da aggregate qiling
by_course = pd.read_sql("""
    SELECT c.title, COUNT(*) AS n, ROUND(AVG(e.grade),1) AS avg_grade
    FROM enrollments e JOIN courses c USING (course_id)
    WHERE e.grade IS NOT NULL
    GROUP BY c.course_id, c.title ORDER BY avg_grade DESC
""", conn)

# 5. Sanalarni yo'lda o'giring
enr = pd.read_sql("SELECT * FROM enrollments", conn, parse_dates=["enrolled_at"])
print(enr.enrolled_at.dt.month.value_counts())

# 6. Xulosa table ini qaytarib yozing
by_course.to_sql("course_summary", conn, if_exists="replace", index=False)

# 7. Bir nechta row ni xavfsiz qo'shing
conn.executemany(
    "INSERT OR IGNORE INTO courses (course_id, title, category, credits, price) VALUES (?,?,?,?,?)",
    [(10, 'Power BI', 'Data', 3, 1600000), (11, 'Excel', 'Data', 2, 800000)])
conn.commit()

# 8. O'sha query SQLAlchemy orqali
from sqlalchemy import create_engine
engine = create_engine("sqlite:///school.db")
pd.read_sql("SELECT COUNT(*) AS n FROM students", engine)

conn.close()

Intervyu savollari

S: Filtrlashni SQL da qilish kerakmi yoki pandas da?

SQL da. Database da index lar bor va u avval data ni ko'chirishi shart emas; pandas esa filtrlashdan oldin hammasini RAM ga yuklashi kerak. SQL da qisqartiring, pandas da tahlil qiling.

S: SQL injection nima va undan qanday himoyalanasiz?

Query ni string birlashtirish bilan qurish foydalanuvchi kiritmasini bajariladigan SQL ga aylantiradi. Undan parametrli query lar (? yoki nomlangan parametrlar) bilan himoyalaning — ular qiymatni query matnidan alohida yuboradi.

S: Nega integer column ba'zan DataFrame da float bo'lib qoladi?

Chunki SQL NULL NaN ga aylanadi, NaN esa float — shuning uchun butun column float64 ga ko'tariladi. Butun son kerak bo'lsa, nullable integer dtype (Int64) ishlating yoki NULL larni to'ldiring.

S: to_sql(..., if_exists='replace') nima qiladi?

Mavjud table ni o'chiradi va DataFrame dan qaytadan yaratadi — barcha constraint va index lar yo'qoladi. Bu DELETE ham, TRUNCATE ham emas. Faqat vaqtinchalik table larda ishlating.

S: sqlite3 va SQLAlchemy?

sqlite3 Python bilan birga keladi, lekin faqat SQLite tilida gapiradi. SQLAlchemy PostgreSQL, MySQL, SQL Server va boshqalar uchun yagona interfeys hamda connection pooling beradi — ya'ni bir xil tahlil kodi hamma joyda ishlaydi.

S: INSERT ingiz xatosiz bajarildi, lekin data yo'q. Nega?

conn.commit() qilinmagan. Connection yopilganda transaction rollback qilingan. Avtomatik commit uchun with conn: ishlating.

S: Xotiraga sig'maydigan natijani qanday boshqarasiz?

Avval har bir row haqiqatan kerakmi, shuni tekshiring — odatda haqiqiy javob SQL dagi aggregate. Chindan kerak bo'lsa, chunksize bilan bo'laklab qayta ishlang.

Xulosa

• SQL da qisqartiring, pandas da tahlil qiling. Filtrlash, join va aggregation database ga, chizish va modellashtirish Python ga tegishli.
• sqlite3 Python bilan birga keladi. conn.commit() ni eslang — yoki with conn: ishlating, u commit va rollback ni o'zi qiladi.
• pd.read_sql(query, conn) har qanday query ni DataFrame ga aylantiradi. parse_dates= dan foydalaning va hisoblangan column laringizga doim alias bering.
• SQL NULL → NaN, bu integer column larni float qiladi. SQLite sanalari string bo'lib keladi.
• SQL ni hech qachon f-string bilan qurmang. ? parametrlarini ishlating — bu SQL injection dan himoya qiladi va ismlardagi tutuq belgisini ham hal qiladi. Ommaviy insert uchun executemany.
• to_sql(..., if_exists='replace') table ni o'chiradi; index=False uzating.
• SQLAlchemy har qanday database ga yagona interfeys beradi — faqat connection string o'zgaradi. Parollarni environment dan o'qing, hech qachon kodga yozmang.
• chunksize ni faqat har bir row haqiqatan kerak bo'lganda ishlating.
• Seaborn long/tidy format ni xohlaydi, JOIN esa uni tabiiy hosil qiladi. II qismdagi har bir x=/y=/hue= — query ingizdagi column nomi.

Keyingi: 13-bob SQL qismini yopadi — barcha gotcha lar bitta katalogda, klassik intervyu masalalari ishlab chiqilgan holda va uslub qo'llanmasi.