7.6-dars: Pandas va SQL

7-QISM — MA'LUMOT YIG'ISH · 6-dars

1. Kirish va motivatsiya

7.5-darsda SQL tilini o'rgandik. Endi savol: SQL bazasini Python (pandas) bilan qanday bog'lash? Data Scientist ish oqimi ko'pincha shunday: SQL bilan bazadan kerakli qismini olib (bazada kamaytir — tez, katta ma'lumot), keyin pandas bilan batafsil tahlil (xotirada — moslashuvchan, ML, vizualizatsiya). Bu darsda: pd.read_sql (SQL so'rov → DataFrame), df.to_sql (DataFrame → baza), parametrli so'rov (? — SQL in'yeksiya xavfsizligi — juda muhim!), va SQL/pandas ish oqimi. Bu ikki kuchli vositani birlashtiradi: bazaning tezligi va pandas'ning moslashuvchanligi. Nega muhim? (1) Bog'lash — SQL baza + Python tahlil; (2) Ish oqimi — bazada kamaytir → pandas tahlil; (3) Xavfsizlik — parametrli so'rov (in'yeksiya). Bu dars pandas va SQL'ni birga o'rgatadi — ikki vositani bog'lash.

Pandas va SQL — bazani Python bilan: pd.read_sql (SQL so'rov → DataFrame; sqlite3/SQLAlchemy ulanish), df.to_sql (DataFrame → baza jadval; if_exists), parametrli so'rov (? va params= — SQL in'yeksiya xavfsizlik; juda muhim), ish oqimi (bazada kamaytir SQL → pandas tahlil), ulanish (connect/close yoki with). Foydalanish: baza + Python tahlil, xavfsiz so'rov. Bu 7.5 (SQL), 7.4 (SQLite), 6.11 (quvur) bilan bog'liq. Pandas va SQL — bazani bog'lash. read_sql. Parametr.

Real vaziyat. Data Scientist kompaniya bazasidan (PostgreSQL — 50M qator) tahlil qilmoqchi. Ish oqimi: (1) SQL — bazada kamaytir (pd.read_sql("SELECT shahar, AVG(narx) FROM savdo WHERE yil=2024 GROUP BY shahar", conn) — 50M → 15 shahar; bazada hisob, tez), (2) pandas — natija DataFrame (batafsil — grafik, ML). Va xavf: foydalanuvchi kirishi shahar_kirish = "Toshkent'; DROP TABLE savdo; --" (SQL in'yeksiya — bazani o'chirishi mumkin!); hal — parametrli (pd.read_sql("... WHERE shahar = ?", conn, params=[shahar_kirish]) — xavfsiz). Data Scientist SQL kamaytirdi (bazada), pandas tahlil qildi, parametr bilan xavfsiz. Pandas va SQL — ikki vosita.

Bu darsda pandas va SQL'ni birga o'rganamiz.

Bu darsda:

• read_sql (SQL → DataFrame)
• to_sql (DataFrame → baza)
• Parametrli so'rov (in'yeksiya)
• SQL/pandas ish oqimi
• Ulanish (connect, with)
• Pandas+SQL amaliyoti
• Pandas+SQL tuzoqlari
• Amaliy: pandas va SQL

ℹ Misollar real sqlite3/pandas bilan (Python 3.14).

2. Nazariya — chuqur tushuntirish

2.1. read_sql (SQL → DataFrame)

So'rov natijasi jadvalga:

import sqlite3
import pandas as pd

# ulanish
conn = sqlite3.connect("loyiha.db")

# read_sql — SQL so'rov → DataFrame
df = pd.read_sql("SELECT * FROM savdo WHERE narx > 100", conn)

# butun jadval
df = pd.read_sql("SELECT * FROM savdo", conn)

# agregatsiya (bazada)
df = pd.read_sql("SELECT shahar, AVG(narx) AS ort FROM savdo GROUP BY shahar", conn)

conn.close()

read_sql (SQL → DataFrame) — so'rov natijasi: pd.read_sql(sorov, conn) — SQL so'rov natijasini DataFramega; conn (ulanish — sqlite3.connect, yoki SQLAlchemy PostgreSQL/MySQL); so'rov bazada bajariladi (7.5 — filtr/guruh/JOIN), natija pandas. Sabab: baza + pandas (SQL bazada kamaytiradi — tez, katta; natija DataFrame — batafsil tahlil); read_sql — ko'prik (SQL → pandas); har so'rov (SELECT/WHERE/GROUP BY/JOIN). read_sql (so'rov → DataFrame), conn (ulanish), bazada (so'rov bajariladi). read_sql — SQL → DataFrame (ko'prik). read_sql. So'rov.

2.2. to_sql (DataFrame → baza)

DataFrame'ni bazaga:

import sqlite3
import pandas as pd

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

# to_sql — DataFrame → baza jadval
df.to_sql("savdo", conn, index=False, if_exists="replace")

# if_exists: "fail" (xato), "replace" (o'chir+yoz), "append" (qo'sh)
df.to_sql("savdo", conn, index=False, if_exists="append")   # qo'shish

conn.close()

to_sql (DataFrame → baza) — jadvalga yozish: df.to_sql("jadval", conn, index=False, if_exists=...) — DataFrame'ni baza jadvalga; index=False (indeks yozilmasin — 7.2), if_exists (jadval bor bo'lsa: "fail" xato (standart), "replace" o'chir+yoz, "append" qo'sh). Sabab: saqlash (tozalangan/qayta ishlangan DataFrame → baza; ulashish, so'rov); if_exists (mavjud jadval — o'chir yoki qo'sh; ehtiyot — replace o'chiradi); index=False (indeks keraksiz — 7.4). to_sql (DataFrame → baza), if_exists (fail/replace/append), index=False (indeks yo'q). to_sql — DataFrame → baza (if_exists). to_sql. if_exists.

2.3. Parametrli so'rov (in'yeksiya)

Parametrli so'rov (in'yeksiya) — xavfsizlik: pd.read_sql("... WHERE x = ?", conn, params=[qiymat]) — ? joy tutuvchi (placeholder), params — qiymatlar; string birlashtirma (f"... WHERE x = '{kirish}'" — SQL in'yeksiya xavfi). Sabab: SQL in'yeksiya (foydalanuvchi kirishi so'rovga qo'shilsa — zararli SQL; "'; DROP TABLE savdo; --" — jadval o'chiriladi!); parametr (? — qiymat ma'lumot deb (SQL emas; baza qochiradi; xavfsiz)); "hech qachon string birlashtirma" (foydalanuvchi kirishi). ? placeholder (parametr), params=[...] (qiymatlar), in'yeksiya (string birlashtirma — xavf). Parametrli so'rov — ?/params (in'yeksiya xavfsizlik). Parametr. In'yeksiya.

2.4. SQL/pandas ish oqimi

SQL/pandas ish oqimi — bazada kamaytir → pandas: (1) SQL (bazada — WHERE/GROUP BY/JOIN; katta → kichik natija; tez, kam ko'chirish — 7.5), (2) read_sql (natija → DataFrame), (3) pandas (batafsil — tozalash, tahlil, vizualizatsiya, ML). Sabab: har vosita o'z kuchi (SQL — bazada kamaytir; pandas — moslashuvchan tahlil); "katta bazada (SQL), kichik xotirada (pandas)"; ko'chirish kamaytir (SQL avval — faqat kerak; 7.5 ko'chirish qimmat). SQL (kamaytir — bazada), read_sql (ko'prik), pandas (batafsil). SQL/pandas ish oqimi — bazada kamaytir → pandas tahlil. Oqim. Kamaytir.

2.5. Ulanish (connect, with)

Ulanish (connect, with) — bazaga: conn = sqlite3.connect("fayl.db") (ulanish; ":memory:" — xotirada), conn.close() (yopish — muhim; resurs), with sqlite3.connect(...) as conn: (avtomatik boshqarish — blok tugagach; lekin SQLite with — tranzaksiya, close emas), SQLAlchemy (PostgreSQL/MySQL — create_engine("postgresql://..."); pd.read_sql(sorov, engine)). Sabab: ulanish (bazaga bog'lanish — resurs; ochib-yopish); close (yopmasa — resurs sizishi; ko'p ulanish — muammo); SQLAlchemy (pandas — turli baza; engine). connect (ochish), close (yopish — muhim), SQLAlchemy (PostgreSQL/MySQL — engine). Ulanish — connect/close (resurs). Ulanish. close.

2.6. Pandas+SQL amaliyoti

Pandas+SQL amaliyoti: ulanish (sqlite3.connect yoki SQLAlchemy — baza turi); SQL kamaytir (read_sql — WHERE/GROUP BY; bazada; faqat kerak); parametr (?/params — foydalanuvchi kirishi; in'yeksiya); pandas tahlil (natija DataFrame — 6-qism tozalash, 5-qism grafik); yozish (to_sql — index=False, if_exists); yopish (close — resurs); oqim (SQL → pandas → vizualizatsiya/model). Tuzoqlar: string birlashtirma (in'yeksiya — ?), SELECT * (ko'p ma'lumot — 7.5), close unut (resurs), if_exists replace (o'chiradi), butun jadval (kamaytir — SQL). Amaliyot — ulanish, SQL, parametr, pandas. Pandas. SQL.

2.7. Pandas+SQL tuzoqlari

Pandas+SQL asosiy tuzoqlari: SQL in'yeksiya (foydalanuvchi kirishi string birlashtirma (f"... WHERE x = '{kirish}'") — zararli SQL ("'; DROP TABLE ...; --" — jadval o'chiradi; ma'lumot o'g'irlash); parametr ?/params — qochiradi); SELECT * (butun jadval — ko'p ma'lumot (xotira; 7.5); WHERE/kerak ustun — bazada kamaytir); close unut (conn.close() yo'q — resurs sizishi (ko'p ulanish — baza cheklov); with yoki close); if_exists default (to_sql standart "fail" (jadval bor — xato); yoki "replace" (o'chiradi — ehtiyot!); "append" (qo'sh)); butun jadval yuklash (10M qator SELECT * → pandas — xotira/sekin; SQL WHERE/GROUP BY kamaytir, keyin pandas); tur mos emas (baza tur ↔ pandas — sana matn bo'lishi mumkin; parse_dates=); SQLAlchemy (PostgreSQL/MySQL — sqlite3 emas; create_engine; pandas read_sql engine bilan); katta natija chunks (katta natija — chunksize= (qism-qism; 7.10)); tranzaksiya (to_sql — commit; with tranzaksiya); NULL↔NaN (baza NULL ↔ pandas NaN — avtomatik; hisobga ol). Sabab: pandas+SQL xavfsizlik/resurs/hajm nozik (in'yeksiya, close, * — xavf yoki muammo). Yechim: parametr (?), kerak ustun/WHERE, close/with, if_exists ehtiyot. Tuzoqlar — in'yeksiya, SELECT *, close, if_exists.

2.8. Pandas va SQL — ikki vositani bog'lash

Pandas va SQL asosiy g'oyasi — ikki vositani bog'lash: SQL baza 7.5-bob va Python pandas — birga (baza tezligi + pandas moslashuvchanligi); Data Scientist ish oqimi (bazada kamaytir → pandas tahlil). pd.read_sql(sorov, conn) (SQL so'rov → DataFrame — ko'prik; bazada bajariladi), df.to_sql("jadval", conn, index=False, if_exists=...) (DataFrame → baza; fail/replace/append), parametrli so'rov (?/params — SQL in'yeksiya xavfsizlik; string birlashtirma xavf — "'; DROP TABLE; --"), ish oqimi (SQL bazada kamaytir WHERE/GROUP BY/JOIN → read_sql → pandas batafsil tahlil), ulanish (connect/close; SQLAlchemy — PostgreSQL/MySQL). Foydalanish: baza + Python tahlil (SQL kamaytir, pandas batafsil), xavfsiz so'rov (parametr — in'yeksiya). Tuzoqlar: string birlashtirma (in'yeksiya — ?/params), SELECT * (ko'p ma'lumot — WHERE/kerak), close unut (resurs — with), if_exists replace (o'chiradi), butun jadval (kamaytir — SQL), tur (parse_dates), SQLAlchemy (PostgreSQL — engine). Bu 7.5 (SQL), 7.4 (SQLite), 6.11 (quvur), 7.10 (chunks) bilan. Pandas va SQL — ikki vositani bog'lash (read_sql/to_sql; parametr; oqim). Pandas. SQL. Parametr.

3. Tez ma'lumotnoma

import sqlite3
import pandas as pd

# ULANISH:
conn = sqlite3.connect("loyiha.db")   # yoki ":memory:"

# READ_SQL (SQL → DataFrame):
df = pd.read_sql("SELECT shahar, AVG(narx) AS ort FROM savdo GROUP BY shahar", conn)
df = pd.read_sql("SELECT * FROM savdo WHERE yil=?", conn, params=[2024], parse_dates=["sana"])

# PARAMETRLI (SQL in'yeksiya xavfsizligi — MUHIM):
kirish = "Toshkent"
df = pd.read_sql("SELECT * FROM savdo WHERE shahar = ?", conn, params=[kirish])
#   ASLO: f"... WHERE shahar = '{kirish}'"  ← in'yeksiya xavfi!

# TO_SQL (DataFrame → baza):
df.to_sql("savdo", conn, index=False, if_exists="replace")
#   if_exists: fail (xato) / replace (o'chir+yoz) / append (qo'sh)

conn.close()   # yopish (resurs) — yoki with

# ISH OQIMI: SQL (bazada kamaytir) → read_sql → pandas (batafsil)
QOIDA: parametr (?) · WHERE/kerak ustun · close/with · if_exists ehtiyot

Pandas va SQL xulosasi

Pandas va SQL — ikki vositani bog'lash (baza tez + pandas moslashuvchan)
read_sql — SQL so'rov → DataFrame (bazada bajariladi)
to_sql — DataFrame → baza (if_exists: fail/replace/append)
Parametrli — ? / params (SQL in'yeksiya xavfsizligi — MUHIM)
Ish oqimi — SQL kamaytir (bazada) → pandas tahlil (xotirada)

4. Batafsil misollar

Misollar real sqlite3/pandas bilan (Python 3.14).

Misol 1 — read_sql (SQL → DataFrame)

"""read_sql (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    pd.DataFrame({
        "shahar": ["Toshkent", "Samarqand", "Toshkent", "Buxoro"],
        "narx": [120, 80, 150, 70],
    }).to_sql("savdo", conn, index=False)

    print("=== 1. read_sql (filtr) ===")
    df = pd.read_sql("SELECT * FROM savdo WHERE narx > 100", conn)
    print(f"  narx > 100: {df['narx'].tolist()}")

    print("\n=== 2. Agregatsiya (bazada) ===")
    agg = pd.read_sql(
        "SELECT shahar, AVG(narx) AS ort FROM savdo GROUP BY shahar", conn)
    print(agg.to_string(index=False))

    print("\n=== 3. Natija — DataFrame ===")
    print(f"  turi: {type(agg).__name__}")

    print("\n=== 4. Endi pandas tahlil ===")
    print(f"  eng qimmat shahar: {agg.loc[agg['ort'].idxmax(), 'shahar']}")
    conn.close()
    print("  ⭐ read_sql — SQL → DataFrame")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. read_sql (filtr) ===
  narx > 100: [120, 150]

=== 2. Agregatsiya (bazada) ===
   shahar   ort
   Buxoro  70.0
Samarqand  80.0
 Toshkent 135.0

=== 3. Natija — DataFrame ===
  turi: DataFrame

=== 4. Endi pandas tahlil ===
  eng qimmat shahar: Toshkent
  ⭐ read_sql — SQL → DataFrame

Nima ko'rsatdi: 2.1-bo'lim.

Misol 2 — to_sql (DataFrame → baza)

"""to_sql (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    df = pd.DataFrame({"shahar": ["Toshkent", "Buxoro"], "narx": [120, 70]})

    print("=== 1. to_sql (replace) ===")
    df.to_sql("savdo", conn, index=False, if_exists="replace")
    soni = pd.read_sql("SELECT COUNT(*) AS n FROM savdo", conn)["n"][0]
    print(f"  qatorlar: {soni}")

    print("\n=== 2. append (qo'shish) ===")
    yangi = pd.DataFrame({"shahar": ["Xiva"], "narx": [95]})
    yangi.to_sql("savdo", conn, index=False, if_exists="append")
    soni2 = pd.read_sql("SELECT COUNT(*) AS n FROM savdo", conn)["n"][0]
    print(f"  qatorlar: {soni2} (1 qo'shildi)")

    print("\n=== 3. if_exists variantlari ===")
    print("  fail (xato), replace (o'chir+yoz), append (qo'sh)")

    print("\n=== 4. Tekshirish ===")
    hammasi = pd.read_sql("SELECT shahar FROM savdo", conn)
    print(f"  shaharlar: {hammasi['shahar'].tolist()}")
    conn.close()
    print("  ⭐ to_sql — DataFrame → baza")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. to_sql (replace) ===
  qatorlar: 2

=== 2. append (qo'shish) ===
  qatorlar: 3 (1 qo'shildi)

=== 3. if_exists variantlari ===
  fail (xato), replace (o'chir+yoz), append (qo'sh)

=== 4. Tekshirish ===
  shaharlar: ['Toshkent', 'Buxoro', 'Xiva']
  ⭐ to_sql — DataFrame → baza

Nima ko'rsatdi: 2.2-bo'lim.

Misol 3 — Parametrli so'rov (xavfsizlik)

"""Parametrli so'rov: xavfsizlik (real sqlite3/pandas)."""

import sqlite3
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    pd.DataFrame({
        "shahar": ["Toshkent", "Samarqand", "Buxoro"],
        "narx": [120, 80, 70],
    }).to_sql("savdo", conn, index=False)

    print("=== 1. Parametrli (xavfsiz) ===")
    kirish = "Toshkent"
    df = pd.read_sql("SELECT * FROM savdo WHERE shahar = ?", conn, params=[kirish])
    print(f"  natija: {df['narx'].tolist()}")

    print("\n=== 2. Ko'p parametr ===")
    df2 = pd.read_sql(
        "SELECT * FROM savdo WHERE narx > ? AND shahar != ?", conn, params=[60, "Buxoro"])
    print(f"  narx>60, Buxoro emas: {df2['shahar'].tolist()}")

    print("\n=== 3. In'yeksiya xavfi (string) ===")
    print("  f'... WHERE shahar = {kirish}' — XAVF")
    print("  zararli: \"'; DROP TABLE savdo; --\"")

    print("\n=== 4. Parametr qochiradi ===")
    zararli = "Toshkent'; DROP TABLE savdo; --"
    xavfsiz = pd.read_sql("SELECT * FROM savdo WHERE shahar = ?", conn, params=[zararli])
    print(f"  zararli kirish → {len(xavfsiz)} qator (jadval saqlandi)")
    conn.close()
    print("  ⭐ Parametr — in'yeksiya xavfsizligi")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. Parametrli (xavfsiz) ===
  natija: [120]

=== 2. Ko'p parametr ===
  narx>60, Buxoro emas: ['Toshkent', 'Samarqand']

=== 3. In'yeksiya xavfi (string) ===
  f'... WHERE shahar = {kirish}' — XAVF
  zararli: "'; DROP TABLE savdo; --"

=== 4. Parametr qochiradi ===
  zararli kirish → 0 qator (jadval saqlandi)
  ⭐ Parametr — in'yeksiya xavfsizligi

Nima ko'rsatdi: 2.3-bo'lim.

Misol 4 — Ish oqimi (SQL → pandas)

"""Ish oqimi: SQL → pandas (real sqlite3/pandas)."""

import sqlite3
import numpy as np
import pandas as pd


def main() -> None:
    conn = sqlite3.connect(":memory:")
    np.random.seed(0)
    katta = pd.DataFrame({
        "shahar": np.random.choice(["Toshkent", "Samarqand", "Buxoro"], 1000),
        "yil": np.random.choice([2023, 2024], 1000),
        "narx": np.random.randint(50, 200, 1000),
    })
    katta.to_sql("savdo", conn, index=False)

    print("=== 1. Katta jadval (baza) ===")
    print(f"  bazada: {len(katta)} qator")

    print("\n=== 2. SQL kamaytir (bazada) ===")
    agg = pd.read_sql("""
        SELECT shahar, AVG(narx) AS ort, COUNT(*) AS soni
        FROM savdo WHERE yil = 2024
        GROUP BY shahar
    """, conn)
    print(f"  natija: {len(agg)} qator (1000 → {len(agg)})")

    print("\n=== 3. Pandas tahlil (xotirada) ===")
    agg["ort"] = agg["ort"].round(1)
    print(agg.to_string(index=False))

    print("\n=== 4. Ish oqimi ===")
    print("  SQL (bazada kamaytir) → pandas (batafsil)")
    conn.close()
    print("  ⭐ Ish oqimi — SQL → pandas")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

=== 1. Katta jadval (baza) ===
  bazada: 1000 qator

=== 2. SQL kamaytir (bazada) ===
  natija: 3 qator (1000 → 3)

=== 3. Pandas tahlil (xotirada) ===
   shahar   ort  soni
   Buxoro 125.2   165
Samarqand 118.8   170
 Toshkent 120.4   184

=== 4. Ish oqimi ===
  SQL (bazada kamaytir) → pandas (batafsil)
  ⭐ Ish oqimi — SQL → pandas

Nima ko'rsatdi: 2.4-bo'lim.

5. To'g'ri va noto'g'ri tushunishlar

6. Keng tarqalgan xatolar va yechimlari

1. SQL in'yeksiya (string)

pd.read_sql(f"SELECT * FROM t WHERE x = '{kirish}'", conn)   # XAVF # ⚠️
pd.read_sql("SELECT * FROM t WHERE x = ?", conn, params=[kirish])  # ✅

2. SELECT * (katta)

pd.read_sql("SELECT * FROM savdo", conn)   # 10M qator (xotira)   # ⚠️
pd.read_sql("SELECT shahar, narx FROM savdo WHERE yil=2024", conn)  # ✅

3. close unutish

conn = sqlite3.connect("db")   # close yo'q (resurs)              # ⚠️
with sqlite3.connect("db") as conn: ...   # yoki conn.close()    # ✅

4. if_exists (o'chirish)

df.to_sql("savdo", conn)   # jadval bor → xato (yoki replace o'chir) # ⚠️
df.to_sql("savdo", conn, if_exists="append")   # ehtiyot         # ✅

5. Butun jadval → pandas

df = pd.read_sql("SELECT * FROM katta", conn)   # 50M (sekin)     # ⚠️
df = pd.read_sql("SELECT ... WHERE ... GROUP BY ...", conn)   # kamaytir # ✅

6. Sana tur

df = pd.read_sql("SELECT sana FROM t", conn)   # sana — matn      # ⚠️
df = pd.read_sql("SELECT sana FROM t", conn, parse_dates=["sana"])  # ✅

7. PostgreSQL sqlite3'da

conn = sqlite3.connect("postgresql://...")   # sqlite3 emas       # ⚠️
from sqlalchemy import create_engine; engine = create_engine(...)  # ✅

7. Integratsiya — bu bilim qayerda kerak bo'ladi

• 7.5-dars (o'tilgan): SQL asoslari
• 7.4-dars (o'tilgan): SQLite (baza)
• 6.11-dars (o'tilgan): Tozalash quvuri
• 7.10-dars: Katta fayllar (chunks)
• Ish: Ma'lumot quvuri (production)

8. Eng yaxshi amaliyotlar

1. Parametr — ?/params (in'yeksiya).

2. SQL kamaytir — WHERE/GROUP BY (bazada).

3. Kerak ustun — SELECT a, b (* emas).

4. close/with — resurs.

5. if_exists — ehtiyot (replace o'chiradi).

6. parse_dates — sana ustun.

7. SQLAlchemy — PostgreSQL/MySQL.

8. Oqim — SQL → pandas → tahlil.

9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

1.  # read_sql nima?
2.  # to_sql nima?
3.  # if_exists?
4.  # parametr nima?
5.  # SQL in'yeksiya?
6.  # ? nima?
7.  # ish oqimi?
8.  # nega SQL kamaytir?
9.  # close nega?
10. # SQLAlchemy?
11. # butun jadval xato?
12. # nega ikkalasi?

Vazifa 2: Xatolarni tuzating

1.  pd.read_sql(f"... WHERE x = '{kirish}'", conn)

2.  pd.read_sql("SELECT * FROM savdo", conn)   # 10M

3.  conn = sqlite3.connect("db")   # close yo'q

4.  df.to_sql("savdo", conn)   # jadval bor

5.  pd.read_sql("SELECT sana FROM t", conn)

1.  params=[kirish] (? parametr)

2.  WHERE/kerak ustun

3.  with yoki conn.close()

4.  if_exists="append"

5.  parse_dates=["sana"]

Vazifa 3: read_sql/to_sql

Modellang:

1. read_sql
2. to_sql
3. if_exists
4. conn

Vazifa 4: Xavfsizlik

Modellang:

1. Parametr
2. ?
3. In'yeksiya
4. Qochirish

Vazifa 5: Ish oqimi

Modellang:

1. SQL kamaytir
2. read_sql
3. pandas tahlil
4. close

Vazifa 6: Integratsiya

Modellang:

1. SQL (7.5)
2. SQLite (7.4)
3. Quvur (6.11)
4. Chunks (7.10)

Vazifa 7: O'ylash

SQL in'yeksiya — foydalanuvchi kiritgan ma'lumotni to'g'ridan so'rovga qo'shishdan kelib chiqadi (f"... WHERE x = '{kirish}'"). Zararli kirish ("'; DROP TABLE savdo; --") butun jadvalni o'chirishi mumkin. Parametrli so'rov (?) buni oldini oladi. Nima uchun "foydalanuvchi kiritgan ma'lumotga hech qachon ishonma" tamoyili xavfsizlikning asosi, va nima uchun bu Data Scientist uchun ham muhim (nafaqat dasturchi)?

Nimani mustahkamlaydi: 2.3, 2.7-bo'limlar.

Xulosa

Bu darsda pandas va SQL'ni birga o'rgandik.

Eng muhim uch fikr:

1. read_sql va to_sql. pd.read_sql(sorov, conn) — SQL so'rov → DataFrame (ko'prik; so'rov bazada bajariladi — filtr/guruh/JOIN 7.5; parse_dates sana). df.to_sql("jadval", conn, index=False, if_exists=...) — DataFrame → baza ("fail" xato, "replace" o'chir+yoz, "append" qo'sh; ehtiyot — replace o'chiradi).

2. Parametr va ish oqimi. Parametrli so'rov — pd.read_sql("... WHERE x = ?", conn, params=[qiymat]) (? placeholder; SQL in'yeksiya xavfsizlik — string birlashtirma xavf "'; DROP TABLE; --"; parametr qochiradi). Ish oqimi — SQL bazada kamaytir (WHERE/GROUP BY; katta → kichik) → read_sql → pandas batafsil (tahlil, ML, grafik).

3. Ikki vosita. Pandas va SQL — ikki vositani bog'lash (baza tezligi + pandas moslashuvchanligi). Ulanish — connect/close (resurs; with; SQLAlchemy — PostgreSQL/MySQL). Foydalanuvchi kirishiga hech qachon ishonma (xavfsizlik asosi — parametr, validatsiya; Data Scientist ham — ma'lumot/dashboard/production). Tuzoqlar: string birlashtirma (in'yeksiya — ?), SELECT * (ko'p ma'lumot — WHERE/kerak), close unut (resurs — with), if_exists replace (o'chiradi), butun jadval (SQL kamaytir), sana (parse_dates), PostgreSQL (SQLAlchemy engine).

Keyingi darsda API asoslari (requests)ni o'rganamiz: veb xizmatlardan jonli ma'lumot olish — requests.get, HTTP (status kod, JSON javob), so'rov parametrlari, autentifikatsiya (kalit); ob-havo, valyuta, ijtimoiy media ma'lumoti.