Python kursi/Malumot formatlari7/8-dars22 daqiqa

Mundarija (22)

16.7-dars: `sqlite3`

16-QISM — MA'LUMOT FORMATLARI · 7-dars

1. Kirish va motivatsiya

Shu paytgacha ma'lumotni fayllarda saqladik: JSON, CSV, pickle. Fayl yondashuvi kichik ma'lumot uchun yaxshi, lekin ma'lumot o'sganda muammolar chiqadi: butun faylni har safar qayta yozish, bir vaqtda ikki jarayon yozsa buzilish, "narxi 40000 dan katta kitoblarni muallif bo'yicha guruhlab" kabi so'rovni qo'lda yozish. Bularning yechimi — ma'lumotlar bazasi. Va Python'da bittasi allaqachon o'rnatilgan: sqlite3.

SQLite — butun bazani bitta faylda saqlaydigan, serversiz, dunyoda eng ko'p tarqalgan baza. U har bir Android/iOS telefonda, brauzerda, ko'p ish stoli ilovasida ishlaydi. Python bilan birga keladi — hech narsa o'rnatish shart emas.

Real vaziyat. Bir jamoa foydalanuvchi profillari va harakatlarini JSON fayllarda saqlardi. Ma'lumot 100 000 yozuvga yetganda: har yozuv uchun faylni to'liq o'qib-yozish sekinlashdi, ikki so'rov bir vaqtda kelganda fayl buzildi, va statistik hisobot uchun butun faylni Python'da aylanish kerak bo'ldi. Ular sqlite3 ga o'tishdi — bir necha o'nlab qator kod. Lekin darhol ikkinchi, jiddiyroq muammo chiqdi: qidiruv maydonini f"SELECT * FROM users WHERE name='{qidiruv}'" bilan yozishgan edi — bu SQL injection, eng mashhur veb-zaifliklardan biri. Kimdir qidiruvga ' OR '1'='1 yozsa, butun bazani ko'rardi.

Bu darsda sqlite3 bilan ishlashni, parametrli so'rovlar bilan SQL injection dan himoyani, tranzaksiyalarni va Python obyektlarini bazaga bog'lashni o'rganamiz.

Bu darsda:

connect, execute, executemany, fetchone/fetchall
Parametrli so'rovlar — ? va nomli, SQL injection dan himoya
Row factory — ustun nomi bilan kirish
Tranzaksiyalar: commit, rollback, with con
Tur yaqinligi (type affinity) va adapter/converter
Foreign key, PRAGMA, agregatsiya
create_function, backup
Amaliy: obyektlarni saqlovchi repozitoriy

2. Nazariya — chuqur tushuntirish

2.1. Ulanish va so'rovlar

python

import sqlite3

con = sqlite3.connect("wisar.db")          # yoki ":memory:" — xotirada
con.execute("CREATE TABLE kitob(id INTEGER PRIMARY KEY, nom TEXT NOT NULL, narx INTEGER)")
con.execute("INSERT INTO kitob(nom, narx) VALUES(?, ?)", ("Sarob", 38000))
con.commit()
for qator in con.execute("SELECT nom, narx FROM kitob"):
    print(qator)                           # (('Sarob', 38000))
con.close()

Metod	Nima qiladi
`sqlite3.connect(yol)`	Ulanish (fayl yoki `:memory:`)
`con.execute(sql, params)`	So'rov bajaradi, `Cursor` qaytaradi
`con.executemany(sql, ketma)`	Ko'p qatorni bir marta
`con.executescript(sql)`	Bir nechta bayonot (parametrsiz)
`cur.fetchone()` / `fetchall()` / `fetchmany(n)`	Natijani olish
`con.commit()` / `con.rollback()`	Tranzaksiyani tasdiqlash/bekor qilish

Cursor — iterator: for qator in con.execute(...).

2.2. Parametrli so'rovlar — SQL injection dan himoya

Hech qachon so'rovga qiymatni satr birlashtirish/f-string bilan qo'ymang:

python

con.execute(f"SELECT * FROM kitob WHERE nom='{qidiruv}'")   # ❌ SQL INJECTION
con.execute("SELECT * FROM kitob WHERE nom=?", (qidiruv,))   # ✅ parametrli

Parametrli so'rovda qiymat SQL kodi sifatida emas, ma'lumot sifatida uzatiladi — hujumkor SQL yoza olmaydi.

Uslub	Sintaksis	Parametr
Pozitsion	`?`	kortej: `("Sarob", 38000)`
Nomli	`:nom`	lug'at: `{"nom": "Sarob"}`

Jadval va ustun nomlari ni parametr qilib bo'lmaydi (faqat qiymatlar). Ularni oq ro'yxatdan tekshirib qo'ying.

2.3. `Row` factory

Sukut bo'yicha qator — kortej (qator[0]). Row factory ustun nomi bilan kirishni beradi:

python

con.row_factory = sqlite3.Row
r = con.execute("SELECT * FROM kitob WHERE id=?", (1,)).fetchone()
r["nom"]        # ustun nomi bilan
r[0]            # indeks bilan ham
r.keys()        # ustun nomlari

2.4. Tranzaksiyalar

SQLite har o'zgartirishni tranzaksiyaga o'raydi. commit — saqlaydi, rollback — bekor qiladi. Eng toza usul — with con:

python

with con:                              # blok muvaffaqiyatli tugasa commit
    con.execute("INSERT ...")          # istisno bo'lsa — rollback
    con.execute("UPDATE ...")

with con ulanishni yopmaydi — faqat tranzaksiyani boshqaradi. IntegrityError (masalan NOT NULL, UNIQUE, foreign key buzilishi) butun blokni bekor qiladi.

Cursor atributi	Ma'nosi
`cur.lastrowid`	Oxirgi `INSERT` ning `id` si
`cur.rowcount`	O'zgargan qatorlar soni (`UPDATE`/`DELETE`)

2.5. Tur yaqinligi (type affinity)

SQLite turlari qat'iy emas — u "tur yaqinligi" (affinity) ishlatadi. Ustun TEXT bo'lsa, unga yozilgan son str ga aylanadi; ustun INTEGER bo'lsa, "456" satri int ga aylanadi:

Ustun turi	Yozilgan `123` (int)	Yozilgan `"456"` (str)
`TEXT`	`'123'`	`'456'`
`INTEGER`	`123`	`456`

Bu jim ma'lumot buzilishiga olib kelishi mumkin — turlarni Python tomonda ham tekshiring.

Native turlar: NULL→None, INTEGER→int, REAL→float, TEXT→str, BLOB→bytes. Boshqa turlar (date, datetime, Decimal, JSON) — adapter/converter kerak.

2.6. Adapter va converter

Adapter: Python obyekti → SQLite turi (yozishda).
Converter: SQLite bayti → Python obyekti (o'qishda, detect_types bilan).

python

sqlite3.register_adapter(datetime.date, lambda d: d.isoformat())
sqlite3.register_converter("DATE", lambda b: datetime.date.fromisoformat(b.decode()))
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

Python 3.12 dan date/datetime uchun standart adapter/converter olib tashlandi (deprecated edi) — endi o'zingiz ro'yxatdan o'tkazasiz. Murakkab qiymatlar (dict, list) uchun ko'pincha TEXT ustunga JSON 16.2-bob saqlanadi.

2.7. Foreign key, PRAGMA, agregatsiya

SQLite'da foreign key tekshiruvi sukut bo'yicha o'chiq — har ulanishda yoqing:

python

con.execute("PRAGMA foreign_keys = ON")

Agregatsiya SQL da — Python'da aylanishdan tez:

sql

SELECT shahar, AVG(ball), COUNT(*) FROM natija GROUP BY shahar HAVING COUNT(*) > 5

Imkoniyat	Izoh
`con.create_function(nom, argc, f)`	Python funksiyasini SQL da ishlatish
`src.backup(dst)`	Onlayn zaxira nusxa
`con.autocommit` (3.12+)	Tranzaksiya rejimini boshqarish
Indeks (`CREATE INDEX`)	Qidiruvni tezlashtiradi

2.8. Resurs boshqaruvi

sqlite3.Connection context manager sifatida ulanishni yopmaydi. To'liq yopish uchun:

python

with sqlite3.connect("wisar.db") as con:
    ...
con.close()                            # aniq yoping
# yoki contextlib.closing(sqlite3.connect(...))

3. Tez ma'lumotnoma

python

import sqlite3

con = sqlite3.connect("wisar.db")
con.row_factory = sqlite3.Row
con.execute("PRAGMA foreign_keys = ON")

con.execute("INSERT INTO kitob(nom, narx) VALUES(?, ?)", (nom, narx))   # ⭐ parametrli
con.execute("SELECT * FROM kitob WHERE muallif = :m", {"m": ism})

with con:                              # tranzaksiya: commit yoki rollback
    con.executemany("INSERT INTO ...", qatorlar)

r = con.execute("SELECT * FROM kitob WHERE id=?", (1,)).fetchone()
r["nom"]
con.close()

Qoidalar

qiymat — HAR DOIM parametr (?, :nom), hech qachon f-string
jadval/ustun nomi — parametr emas, oq ro'yxat
foreign key: har ulanishda PRAGMA foreign_keys=ON
tranzaksiya: with con
date/datetime — adapter/converter (3.12+ o'zingiz)
agregatsiya — SQL da, Python'da emas
ulanishni close() bilan yoping

4. Batafsil misollar

Misol 1 — Asoslar: jadval, so'rov, parametr, Row

python

"""connect(:memory:); CREATE; INSERT executemany; SELECT parametrli (? va :nom); ORDER BY, WHERE; Row factory; lastrowid, rowcount."""

import sqlite3


def main() -> None:
    con = sqlite3.connect(":memory:")
    con.execute("""CREATE TABLE kitob(
        id INTEGER PRIMARY KEY,
        nom TEXT NOT NULL,
        narx INTEGER,
        muallif TEXT
    )""")

    print("=== 1. INSERT executemany ===")
    kitoblar = [("Oʻtkan kunlar", 45000, "Qodiriy"), ("Sarob", 38000, "Qahhor"),
                ("Mehrobdan chayon", 42000, "Qodiriy"), ("Kecha va kunduz", 40000, "Cholpon")]
    cur = con.executemany("INSERT INTO kitob(nom, narx, muallif) VALUES(?, ?, ?)", kitoblar)
    con.commit()
    print(f"  qo'shildi, jami: {con.execute('SELECT count(*) FROM kitob').fetchone()[0]}")

    print("\n=== 2. Parametrli SELECT (?) ===")
    for nom, narx in con.execute("SELECT nom, narx FROM kitob WHERE narx > ? ORDER BY narx", (40000,)):
        print(f"  {nom:20} {narx}")

    print("\n=== 3. Nomli parametr (:nom) ===")
    natija = con.execute("SELECT nom FROM kitob WHERE muallif = :m ORDER BY nom", {"m": "Qodiriy"}).fetchall()
    print(f"  Qodiriy kitoblari: {[q[0] for q in natija]}")

    print("\n=== 4. Row factory ===")
    con.row_factory = sqlite3.Row
    r = con.execute("SELECT * FROM kitob WHERE nom = ?", ("Sarob",)).fetchone()
    print(f"  ustun nomlari: {r.keys()}")
    print(f"  r['nom']={r['nom']}, r['narx']={r['narx']}, r[0]={r[0]}")
    con.row_factory = None

    print("\n=== 5. lastrowid va rowcount ===")
    cur = con.execute("INSERT INTO kitob(nom, narx, muallif) VALUES(?, ?, ?)", ("Dunyoning ishlari", 25000, "Qodiriy"))
    print(f"  yangi id (lastrowid): {cur.lastrowid}")
    cur = con.execute("UPDATE kitob SET narx = narx + 5000 WHERE muallif = ?", ("Qodiriy",))
    con.commit()
    print(f"  o'zgargan qatorlar (rowcount): {cur.rowcount}")
    print(f"  fetchmany(2): {con.execute('SELECT nom FROM kitob ORDER BY id').fetchmany(2)}")

    con.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. INSERT executemany ===
  qo'shildi, jami: 4

=== 2. Parametrli SELECT (?) ===
  Mehrobdan chayon     42000
  Oʻtkan kunlar        45000

=== 3. Nomli parametr (:nom) ===
  Qodiriy kitoblari: ['Mehrobdan chayon', 'Oʻtkan kunlar']

=== 4. Row factory ===
  ustun nomlari: ['id', 'nom', 'narx', 'muallif']
  r['nom']=Sarob, r['narx']=38000, r[0]=2

=== 5. lastrowid va rowcount ===
  yangi id (lastrowid): 5
  o'zgargan qatorlar (rowcount): 3
  fetchmany(2): [('Oʻtkan kunlar',), ('Sarob',)]

Nima ko'rsatdi: 2.1, 2.2, 2.3, 2.4-bo'limlar.

Misol 2 — SQL injection va tranzaksiyalar

python

"""SQL injection: string format vs parametrli; nom/ustunni oq ro'yxatdan; with con tranzaksiya va rollback; IntegrityError (NOT NULL, UNIQUE)."""

import sqlite3


def main() -> None:
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE foydalanuvchi(id INTEGER PRIMARY KEY, login TEXT UNIQUE, rol TEXT NOT NULL)")
    con.executemany("INSERT INTO foydalanuvchi(login, rol) VALUES(?, ?)",
                    [("aziz", "oquvchi"), ("bek", "oquvchi"), ("admin", "admin")])
    con.commit()

    print("=== 1. ⚠️ SQL injection ===")
    hujum = "' OR '1'='1"
    # XATO usul (namoyish uchun — string birlashtirish):
    xato_sql = f"SELECT login, rol FROM foydalanuvchi WHERE login = '{hujum}'"
    print(f"  string-format so'rov: {xato_sql}")
    print(f"  natija: {con.execute(xato_sql).fetchall()}  ← BARCHA foydalanuvchi ochildi!")

    print("\n=== 2. ✅ Parametrli — himoyalangan ===")
    xavfsiz = con.execute("SELECT login, rol FROM foydalanuvchi WHERE login = ?", (hujum,)).fetchall()
    print(f"  parametrli natija: {xavfsiz}  ← hech narsa (bunday login yo'q)")
    haqiqiy = con.execute("SELECT login, rol FROM foydalanuvchi WHERE login = ?", ("aziz",)).fetchall()
    print(f"  haqiqiy login bilan: {haqiqiy}")

    print("\n=== 3. Jadval/ustun nomi — oq ro'yxat ===")
    RUXSAT = {"login", "rol", "id"}
    def saralab_ol(ustun: str):
        if ustun not in RUXSAT:                       # nomni parametr qilib bo'lmaydi
            raise ValueError(f"ruxsatsiz ustun: {ustun}")
        return con.execute(f"SELECT login FROM foydalanuvchi ORDER BY {ustun}").fetchall()
    print(f"  rol bo'yicha: {[q[0] for q in saralab_ol('rol')]}")
    try:
        saralab_ol("rol; DROP TABLE foydalanuvchi")
    except ValueError as xato:
        print(f"  hujum bloklandi: {xato}")

    print("\n=== 4. Tranzaksiya va rollback ===")
    try:
        with con:                                     # muvaffaqiyatsizlikda rollback
            con.execute("INSERT INTO foydalanuvchi(login, rol) VALUES(?, ?)", ("nodira", "oquvchi"))
            con.execute("INSERT INTO foydalanuvchi(login, rol) VALUES(?, ?)", ("aziz", "oquvchi"))  # UNIQUE buzilishi
    except sqlite3.IntegrityError as xato:
        print(f"  IntegrityError: {xato}")
    soni = con.execute("SELECT count(*) FROM foydalanuvchi WHERE login='nodira'").fetchone()[0]
    print(f"  nodira qo'shildimi: {soni}  ← 0, butun blok bekor qilindi")

    print("\n=== 5. NOT NULL buzilishi ===")
    try:
        with con:
            con.execute("INSERT INTO foydalanuvchi(login, rol) VALUES(?, ?)", ("kamola", None))
    except sqlite3.IntegrityError as xato:
        print(f"  IntegrityError: {xato}")
    print(f"  jami foydalanuvchilar: {con.execute('SELECT count(*) FROM foydalanuvchi').fetchone()[0]}")

    con.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. ⚠️ SQL injection ===
  string-format so'rov: SELECT login, rol FROM foydalanuvchi WHERE login = '' OR '1'='1'
  natija: [('aziz', 'oquvchi'), ('bek', 'oquvchi'), ('admin', 'admin')]  ← BARCHA foydalanuvchi ochildi!

=== 2. ✅ Parametrli — himoyalangan ===
  parametrli natija: []  ← hech narsa (bunday login yo'q)
  haqiqiy login bilan: [('aziz', 'oquvchi')]

=== 3. Jadval/ustun nomi — oq ro'yxat ===
  rol bo'yicha: ['admin', 'aziz', 'bek']
  hujum bloklandi: ruxsatsiz ustun: rol; DROP TABLE foydalanuvchi

=== 4. Tranzaksiya va rollback ===
  IntegrityError: UNIQUE constraint failed: foydalanuvchi.login
  nodira qo'shildimi: 0  ← 0, butun blok bekor qilindi

=== 5. NOT NULL buzilishi ===
  IntegrityError: NOT NULL constraint failed: foydalanuvchi.rol
  jami foydalanuvchilar: 3

Nima ko'rsatdi: 2.2, 2.4-bo'limlar.

Misol 3 — Turlar, adapter/converter va foreign key

python

"""tur yaqinligi (affinity) surprise; register_adapter/converter (date, Decimal); JSON ustun; PRAGMA foreign_keys; agregatsiya GROUP BY; create_function."""

import json
import sqlite3
from datetime import date
from decimal import Decimal


def main() -> None:
    print("=== 1. ⚠️ Tur yaqinligi ===")
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE t(matn TEXT, son INTEGER)")
    con.execute("INSERT INTO t VALUES(?, ?)", (123, "456"))       # int matn ustunga, str son ustunga
    r = con.execute("SELECT matn, son, typeof(matn), typeof(son) FROM t").fetchone()
    print(f"  yozildi: 123 (int) → {r[0]!r} ({r[2]}), '456' (str) → {r[1]!r} ({r[3]})")
    print("  ⚠️ SQLite ustun turiga moslashtiradi — Python tomonda ham tekshiring")

    print("\n=== 2. Adapter va converter ===")
    sqlite3.register_adapter(date, lambda d: d.isoformat())
    sqlite3.register_adapter(Decimal, str)
    sqlite3.register_converter("SANA", lambda b: date.fromisoformat(b.decode()))
    sqlite3.register_converter("PUL", lambda b: Decimal(b.decode()))
    db = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
    db.execute("CREATE TABLE tolov(id INTEGER PRIMARY KEY, sana SANA, summa PUL, meta TEXT)")
    db.execute("INSERT INTO tolov(sana, summa, meta) VALUES(?, ?, ?)",
               (date(2026, 9, 17), Decimal("125000.50"), json.dumps({"kanal": "karta"})))
    r = db.execute("SELECT sana, summa, meta FROM tolov").fetchone()
    print(f"  sana: {r[0]!r} ({type(r[0]).__name__})")
    print(f"  summa: {r[1]!r} ({type(r[1]).__name__})")
    print(f"  meta (JSON matn): {json.loads(r[2])}")

    print("\n=== 3. ⚠️ Foreign key ===")
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("CREATE TABLE muallif(id INTEGER PRIMARY KEY, ism TEXT)")
    db.execute("CREATE TABLE kitob(id INTEGER PRIMARY KEY, nom TEXT, muallif_id INTEGER REFERENCES muallif(id))")
    db.execute("INSERT INTO muallif VALUES(1, 'Qodiriy')")
    db.execute("INSERT INTO kitob VALUES(1, 'Sarob', 1)")
    try:
        db.execute("INSERT INTO kitob VALUES(2, 'Nomalum', 999)")     # mavjud emas muallif
    except sqlite3.IntegrityError as xato:
        print(f"  mavjud emas muallif: IntegrityError: {xato}")
    print("  ⚠️ PRAGMA foreign_keys=ON bo'lmasa bu tekshirilmasdi")

    print("\n=== 4. Agregatsiya (SQL da) ===")
    con.execute("CREATE TABLE natija(shahar TEXT, ball INTEGER)")
    con.executemany("INSERT INTO natija VALUES(?, ?)",
                    [("Toshkent", 90), ("Toshkent", 80), ("Samarqand", 70), ("Samarqand", 85), ("Buxoro", 60)])
    for shahar, ortacha, soni in con.execute(
            "SELECT shahar, AVG(ball), COUNT(*) FROM natija GROUP BY shahar HAVING COUNT(*) >= 2 ORDER BY shahar"):
        print(f"  {shahar:12} o'rtacha={ortacha:.1f} ({soni} ta)")

    print("\n=== 5. create_function ===")
    con.create_function("belgi", 1, lambda b: "a'lo" if b >= 85 else "yaxshi" if b >= 70 else "o'rta")
    for shahar, ball, belgi in con.execute("SELECT shahar, ball, belgi(ball) FROM natija ORDER BY ball DESC LIMIT 3"):
        print(f"  {shahar:12} {ball} → {belgi}")

    con.close()
    db.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. ⚠️ Tur yaqinligi ===
  yozildi: 123 (int) → '123' (text), '456' (str) → 456 (integer)
  ⚠️ SQLite ustun turiga moslashtiradi — Python tomonda ham tekshiring

=== 2. Adapter va converter ===
  sana: datetime.date(2026, 9, 17) (date)
  summa: Decimal('125000.5') (Decimal)
  meta (JSON matn): {'kanal': 'karta'}

=== 3. ⚠️ Foreign key ===
  ⚠️ PRAGMA foreign_keys=ON bo'lmasa bu tekshirilmasdi

=== 4. Agregatsiya (SQL da) ===
  Samarqand    o'rtacha=77.5 (2 ta)
  Toshkent     o'rtacha=85.0 (2 ta)

=== 5. create_function ===
  Toshkent     90 → a'lo
  Samarqand    85 → a'lo
  Toshkent     80 → yaxshi

Nima ko'rsatdi: 2.5, 2.6, 2.7-bo'limlar.

Misol 4 — Amaliy: obyektlarni saqlovchi repozitoriy

Ilovamiz vazifalarni (task) saqlaydi. Har vazifa — dataclass. Repozitoriy sinfi bazani boshqaradi: sxemani yaratadi, obyektni saqlaydi va o'qiydi (turlarni tiklab), parametrli so'rovlar bilan qidiradi, tranzaksiyada ko'p amalni bajaradi va statistika chiqaradi. Bu — sqlite3 ni real ilovada to'g'ri qatlamlash namunasi (23-qismdagi ORM g'oyasining oddiy ko'rinishi).

python

"""dataclass obyektlarini sqlite3 da saqlash: sxema, parametrli CRUD, Row → obyekt, tranzaksiya, indeks, agregatsiya; contextlib.closing bilan resurs."""

import sqlite3
from contextlib import closing
from dataclasses import dataclass
from datetime import date
from enum import Enum


class Holat(str, Enum):
    YANGI = "yangi"
    JARAYONDA = "jarayonda"
    TUGADI = "tugadi"


@dataclass
class Vazifa:
    id: int | None
    sarlavha: str
    holat: Holat
    muddat: date
    ball: int


class VazifaRepozitoriy:
    def __init__(self, con: sqlite3.Connection) -> None:
        self.con = con
        con.row_factory = sqlite3.Row
        con.execute("PRAGMA foreign_keys = ON")
        con.executescript("""
            CREATE TABLE IF NOT EXISTS vazifa(
                id INTEGER PRIMARY KEY,
                sarlavha TEXT NOT NULL,
                holat TEXT NOT NULL CHECK(holat IN ('yangi','jarayonda','tugadi')),
                muddat TEXT NOT NULL,
                ball INTEGER NOT NULL DEFAULT 0
            );
            CREATE INDEX IF NOT EXISTS idx_holat ON vazifa(holat);
        """)

    def qosh(self, v: Vazifa) -> int:
        cur = self.con.execute(
            "INSERT INTO vazifa(sarlavha, holat, muddat, ball) VALUES(?, ?, ?, ?)",
            (v.sarlavha, v.holat.value, v.muddat.isoformat(), v.ball))
        return cur.lastrowid

    def _dan(self, r: sqlite3.Row) -> Vazifa:
        return Vazifa(r["id"], r["sarlavha"], Holat(r["holat"]), date.fromisoformat(r["muddat"]), r["ball"])

    def ol(self, id: int) -> Vazifa | None:
        r = self.con.execute("SELECT * FROM vazifa WHERE id = ?", (id,)).fetchone()
        return self._dan(r) if r else None

    def holat_boyicha(self, holat: Holat) -> list[Vazifa]:
        qatorlar = self.con.execute("SELECT * FROM vazifa WHERE holat = ? ORDER BY muddat", (holat.value,))
        return [self._dan(r) for r in qatorlar]

    def holatni_ozgartir(self, id: int, holat: Holat) -> bool:
        cur = self.con.execute("UPDATE vazifa SET holat = ? WHERE id = ?", (holat.value, id))
        return cur.rowcount > 0

    def statistika(self) -> dict[str, int]:
        qatorlar = self.con.execute("SELECT holat, COUNT(*) AS soni FROM vazifa GROUP BY holat")
        return {r["holat"]: r["soni"] for r in qatorlar}


def main() -> None:
    with closing(sqlite3.connect(":memory:")) as con:
        repo = VazifaRepozitoriy(con)

        print("=== 1. Qo'shish (tranzaksiyada) ===")
        vazifalar = [
            Vazifa(None, "16-qismni yozish", Holat.JARAYONDA, date(2026, 9, 20), 50),
            Vazifa(None, "Testlarni o'tkazish", Holat.YANGI, date(2026, 9, 22), 30),
            Vazifa(None, "Hujjatlarni yangilash", Holat.YANGI, date(2026, 9, 25), 20),
            Vazifa(None, "16.6 darsni tekshirish", Holat.TUGADI, date(2026, 9, 18), 40),
        ]
        with con:
            idlar = [repo.qosh(v) for v in vazifalar]
        print(f"  qo'shilgan id lar: {idlar}")

        print("\n=== 2. ID bo'yicha o'qish (turlar tiklandi) ===")
        v = repo.ol(1)
        print(f"  {v}")
        print(f"  holat turi: {type(v.holat).__name__}, muddat turi: {type(v.muddat).__name__}")

        print("\n=== 3. Holat bo'yicha qidirish ===")
        for v in repo.holat_boyicha(Holat.YANGI):
            print(f"  [{v.holat.value}] {v.sarlavha} (muddat: {v.muddat})")

        print("\n=== 4. Holatni o'zgartirish ===")
        print(f"  o'zgartirildi: {repo.holatni_ozgartir(2, Holat.JARAYONDA)}")
        print(f"  yo'q id: {repo.holatni_ozgartir(999, Holat.TUGADI)}")

        print("\n=== 5. Statistika (agregatsiya) ===")
        for holat, soni in sorted(repo.statistika().items()):
            print(f"  {holat:12} {soni} ta")

        print("\n=== 6. CHECK cheklovi ===")
        try:
            with con:
                con.execute("INSERT INTO vazifa(sarlavha, holat, muddat) VALUES(?, ?, ?)",
                            ("Xato holat", "notogri", "2026-09-30"))
        except sqlite3.IntegrityError as xato:
            print(f"  CHECK buzilishi: IntegrityError: {str(xato).split(':')[0]}")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. Qo'shish (tranzaksiyada) ===
  qo'shilgan id lar: [1, 2, 3, 4]

=== 2. ID bo'yicha o'qish (turlar tiklandi) ===
  Vazifa(id=1, sarlavha='16-qismni yozish', holat=<Holat.JARAYONDA: 'jarayonda'>, muddat=datetime.date(2026, 9, 20), ball=50)
  holat turi: Holat, muddat turi: date

=== 3. Holat bo'yicha qidirish ===
  [yangi] Testlarni o'tkazish (muddat: 2026-09-22)
  [yangi] Hujjatlarni yangilash (muddat: 2026-09-25)

=== 4. Holatni o'zgartirish ===
  o'zgartirildi: True
  yo'q id: False

=== 5. Statistika (agregatsiya) ===
  jarayonda    2 ta
  tugadi       1 ta
  yangi        1 ta

=== 6. CHECK cheklovi ===
  CHECK buzilishi: IntegrityError: CHECK constraint failed

Nima ko'rsatdi: 2.1–2.8-bo'limlar.

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

Noto'g'ri fikr	To'g'risi
"So'rovga qiymatni f-string bilan qo'ysa bo'ladi"	SQL injection — har doim parametr (`?`)
"Jadval nomini ham `?` bilan berish mumkin"	Faqat qiymatlar; nom — oq ro'yxat
"SQLite turlari qat'iy"	Tur yaqinligi — `123` `TEXT` ustunda `'123'` bo'ladi
"Foreign key avtomatik ishlaydi"	`PRAGMA foreign_keys=ON` kerak
"`with con` ulanishni yopadi"	Faqat tranzaksiya; `close()` alohida
"`date` avtomatik saqlanadi"	3.12+ da adapter/converter kerak
"Agregatsiyani Python'da qilish yaxshi"	SQL `GROUP BY` tezroq
"`commit` shart emas"	O'zgarish saqlanmaydi (autocommit'siz)

6. Keng tarqalgan xatolar va yechimlari

1. SQL injection

python

con.execute(f"SELECT * FROM t WHERE nom='{x}'")     # ❌
con.execute("SELECT * FROM t WHERE nom=?", (x,))    # ✅

2. commit ni unutish

python

con.execute("INSERT ...")                           # ❌ dastur yopilsa yo'qoladi
with con: con.execute("INSERT ...")                 # ✅

3. Foreign key yoqmaslik

python

sqlite3.connect("db")                               # ❌ FK tekshirilmaydi
con.execute("PRAGMA foreign_keys = ON")             # ✅ har ulanishda

4. date ni to'g'ridan-to'g'ri

python

con.execute("INSERT INTO t VALUES(?)", (date.today(),))   # ❌ 3.12+ xato
sqlite3.register_adapter(date, lambda d: d.isoformat())   # ✅

5. Ulanishni yopmaslik

python

con = sqlite3.connect("db"); ...                    # ❌ ochiq qoladi
with closing(sqlite3.connect("db")) as con: ...     # ✅

6. Parametrni bitta qiymat sifatida

python

con.execute("... WHERE id=?", 5)                    # ❌ ketma-ketlik kerak
con.execute("... WHERE id=?", (5,))                 # ✅ kortej

7. IN (?) ni bitta parametr deb o'ylash

python

con.execute("... WHERE id IN (?)", (ids,))          # ❌
qm = ",".join("?" * len(ids))                       # ✅
con.execute(f"... WHERE id IN ({qm})", ids)         # nom emas — qiymatlar

8. Katta natijani fetchall bilan

python

qatorlar = con.execute("SELECT * FROM katta").fetchall()   # ❌ xotira
for qator in con.execute("SELECT * FROM katta"): ...       # ✅ iterator

7. Integratsiya — bu bilim qayerda kerak bo'ladi

16.2-dars (o'tilgan): JSON — murakkab maydonlarni TEXT ustunga saqlash
16.3-dars (o'tilgan): CSV ni bazaga yuklash
16.8-dars: katta natijalarni oqim bilan o'qish
20-qism: FastAPI — baza bilan ishlash, Depends
23-qism: bazalar va ORM — SQLAlchemy, Django ORM, migratsiyalar
29-qism: miqyoslash — indekslar, so'rov optimizatsiyasi

8. Eng yaxshi amaliyotlar

Qiymatlar — har doim parametr (?/:nom); jadval/ustun nomlari — oq ro'yxat.
Tranzaksiyalar uchun with con; ulanishni close() bilan yoping.
Foreign key kerak bo'lsa — har ulanishda PRAGMA foreign_keys = ON.
Sana/vaqt/Decimal uchun adapter/converter; murakkab maydon — JSON TEXT.
Agregatsiya va filtrni SQL'ga qoldiring, Python'da aylanmang.
Ko'p qidiriladigan ustunga indeks.
Row factory bilan ustun nomlaridan foydalaning.
Ma'lumot qatlamini repozitoriy sinfiga ajrating (biznes mantiq'dan alohida).

9. Amaliy topshiriq

Vazifa 1: Natijani bashorat qiling

python

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(a TEXT, b INTEGER)")
con.execute("INSERT INTO t VALUES(?, ?)", ("x", 10))
con.execute("INSERT INTO t VALUES(?, ?)", (5, "20"))
1.  print(con.execute("SELECT typeof(a) FROM t WHERE b=10").fetchone()[0])
2.  print(con.execute("SELECT a FROM t WHERE b=20").fetchone())
3.  print(con.execute("SELECT typeof(b) FROM t WHERE a='5'").fetchone()[0])
4.  print(con.execute("SELECT COUNT(*) FROM t").fetchone()[0])
5.  cur = con.execute("INSERT INTO t VALUES('y', 30)"); print(cur.lastrowid)
6.  cur = con.execute("UPDATE t SET b=0 WHERE a='x'"); print(cur.rowcount)
7.  print(con.execute("SELECT SUM(b) FROM t").fetchone()[0])
8.  print(con.execute("SELECT a FROM t WHERE a IN ('x','y') ORDER BY a").fetchall())
9.  print(con.execute("SELECT ? + ?", (2, 3)).fetchone()[0])
10. try:
        con.execute("SELECT * FROM t WHERE a=?", ("x", "y"))
    except sqlite3.ProgrammingError:
        print("xato")
11. print(con.execute("SELECT MAX(b), MIN(b) FROM t").fetchone())
12. con.row_factory = sqlite3.Row
    r = con.execute("SELECT a AS nom FROM t LIMIT 1").fetchone(); print(r["nom"])

Javoblar

text
('5',) — 5 (int) TEXT ustunga '5' bo'lib yozildi, b ustuni 20
integer — "20" (str) INTEGER ustunga 20 bo'lib yozildi
2
3
1
50 (10→0, 20, 30 → 0+20+30)
[('x',), ('y',)] — (5-savolda 'y' qo'shilgan)
5
xato — 1 ta ? ga 2 parametr
(30, 0)
x — Row factory ustun nomi bilan kirish beradi

Vazifa 2: Xatolarni tuzating

python

1.  def qidir(con, ism):
        return con.execute(f"SELECT * FROM users WHERE name='{ism}'").fetchall()

2.  def saqla(con, kitob):
        con.execute("INSERT INTO kitob(nom) VALUES(?)", (kitob,))
        # commit yo'q

3.  def id_lar_boyicha(con, idlar):
        return con.execute("SELECT * FROM t WHERE id IN (?)", (idlar,)).fetchall()

4.  def sana_saqla(con, hodisa, kun):
        con.execute("INSERT INTO log(hodisa, sana) VALUES(?, ?)", (hodisa, kun))  # kun: date

5.  def saralab(con, ustun):
        return con.execute(f"SELECT * FROM t ORDER BY {ustun}").fetchall()  # ustun foydalanuvchidan

Javoblar

python

1.  def qidir(con, ism):
        return con.execute("SELECT * FROM users WHERE name = ?", (ism,)).fetchall()

2.  def saqla(con, kitob):
        with con:
            con.execute("INSERT INTO kitob(nom) VALUES(?)", (kitob,))

3.  def id_lar_boyicha(con, idlar):
        qm = ",".join("?" * len(idlar))
        return con.execute(f"SELECT * FROM t WHERE id IN ({qm})", tuple(idlar)).fetchall()

4.  # bir marta ilova boshida:
    sqlite3.register_adapter(date, lambda d: d.isoformat())
    def sana_saqla(con, hodisa, kun):
        con.execute("INSERT INTO log(hodisa, sana) VALUES(?, ?)", (hodisa, kun.isoformat()))

5.  RUXSAT = {"id", "nom", "sana"}
    def saralab(con, ustun):
        if ustun not in RUXSAT:
            raise ValueError(f"ruxsatsiz ustun: {ustun}")
        return con.execute(f"SELECT * FROM t ORDER BY {ustun}").fetchall()

Vazifa 3: Mini ORM

Model bazaviy sinf: dataclass maydonlaridan CREATE TABLE yaratsin
save(), get(id), filter(**shartlar) (parametrli), delete(id)
Tur moslashuvi: int→INTEGER, str→TEXT, date→adapter, bool→INTEGER
Vazifa misolida sinang; filter(holat="yangi", ball__gt=20) kabi

Vazifa 4: CSV → SQLite import

CSV 16.3-bob ni o'qib jadvalga yuklang; sxemani sarlavhadan yarating
Turlarni taxmin qiling (hammasi int bo'lsa INTEGER, va h.k.)
executemany va bitta tranzaksiya bilan tez yuklang; 100 000 qatorda vaqtni o'lchang
Dublikatlarni INSERT OR IGNORE va UNIQUE bilan o'tkazib yuboring

Vazifa 5: To'liq matn qidiruv

FTS5 virtual jadval (CREATE VIRTUAL TABLE ... USING fts5) yarating
Hujjatlarni indekslang va MATCH bilan qidiring
O'zbekcha matn va reglashtirilmagan qidiruvni sinang
Oddiy LIKE qidiruvi bilan tezlikni solishtiring

Vazifa 6: Migratsiyalar

schema_version jadvali va ketma-ket migratsiya funksiyalari
Har migratsiya user_version PRAGMA ni oshirsin
Migratsiyani tranzaksiyada bajaring — yarim qolmasin
Yangi ustun qo'shish va ma'lumotni ko'chirish misolini yozing

Vazifa 7: O'ylash

SQLite dunyodagi eng ko'p joylashtirilgan ma'lumotlar bazasi — trillion nusxadan ortiq. U server emas, faqat bitta fayl; "haqiqiy" bazalar (PostgreSQL, MySQL) bilan solishtirganda "o'yinchoq" ko'rinadi, lekin aksincha, u ko'p holatda fopen() ga muqobil sifatida ishlatiladi. Nima uchun serversiz, bitta faylli baza bu qadar keng tarqaldi, va qachon SQLite yetarli, qachon "haqiqiy" server kerak?

Javob

Qisqa javob: SQLite keng tarqaldi, chunki u ma'lumotlar bazasi emas, kutubxona — server o'rnatish, sozlash, boshqarish shart emas, u ilova ichida ishlaydi va bazani bitta ko'chma faylda saqlaydi. SQLite mualliflari uni PostgreSQL ga emas, fopen() ga raqib deb ataydi: u tuzilgan ma'lumotni fayldan yaxshiroq saqlash usuli. Server bazalar esa ko'p mijoz bir vaqtda yozadigan, tarmoq orqali ulanadigan, katta miqyosli tizimlar uchun kerak.

1. Nega keng tarqaldi

Sabab	Tafsilot
Serversiz	O'rnatish, sozlash, boshqarish yo'q
Bitta fayl	Nusxa ko'chirish, ko'chirish oson
Ilova ichida	Tarmoq kechikishi yo'q, tez
Ishonchli	Yillar sinovidan o'tgan, keng test qoplami
Bepul, ochiq	Public domain
Universal	Telefon, brauzer, IoT, ish stoli

2. fopen() ga muqobil

SQLite mualliflari falsafasi: ilova ma'lumotni JSON/XML/maxsus faylda saqlagandan ko'ra SQLite'da saqlashi yaxshi — chunki so'rov, tranzaksiya, yaxlitlik, qisman yangilash bepul keladi. Bu darsning boshidagi muammo — aynan shu.

3. Qachon SQLite yetarli

Holat	Sabab
Ish stoli/mobil ilova	Bir foydalanuvchi, mahalliy ma'lumot
Ilova sozlamasi, kesh	Tuzilgan, so'rovli
O'rtacha veb-sayt (ko'p o'qish)	Yozish kam bo'lsa yetarli
Test va prototip	Tez, sozlashsiz
Ma'lumot fayli formati	`.sqlite` — almashinuv formati

4. Qachon server kerak

Holat	Sabab
Ko'p bir vaqtda yozuvchi	SQLite yozishda butun bazani qulflaydi
Tarmoq orqali ko'p mijoz	SQLite mahalliy fayl
Juda katta ma'lumot/yuklama	Miqyoslash, replikatsiya
Murakkab ruxsatlar, foydalanuvchilar	Server autentifikatsiyasi
Yuqori mavjudlik (HA)	Klaster, failover

5. Muhandislik saboqlari

To'g'ri vosita — vazifaga mos: ko'p ilova uchun SQLite haddan ziyod emas, aynan yetarli
"Oddiy" yechim ko'pincha eng ishonchlisi
Server bazaga o'tish — o'sish talab qilganda, oldindan emas
SQL, tranzaksiya, sxema ko'nikmalari ikkalasida ham bir xil — SQLite'da o'rgangan bilim PostgreSQL'ga o'tadi (23-qism)

6. Xulosa

SQLite serversizligi va soddaligi tufayli keng tarqaldi
U fopen() ga muqobil — fayldan yaxshiroq saqlash
Bir yozuvchi, mahalliy ma'lumot — SQLite; ko'p yozuvchi, tarmoq, miqyos — server
SQL bilim ikkalasiga umumiy

Nimani mustahkamlaydi: 2.1–2.8-bo'limlar.

Xulosa

Bu darsda Python bilan birga keladigan sqlite3 bazasini o'rgandik.

Eng muhim uch fikr:

Qiymatlar — har doim parametrli so'rov orqali. con.execute("... WHERE nom=?", (qiymat,)) — hech qachon f-string yoki satr birlashtirish. Bu SQL injection dan yagona ishonchli himoya: parametr qiymati SQL kodi emas, ma'lumot sifatida uzatiladi. Jadval va ustun nomlari ni parametr qilib bo'lmaydi — ularni oq ro'yxatdan tekshiring.
Tranzaksiyalar va turlar. O'zgarishlar commit bilan saqlanadi; eng toza usul — with con (muvaffaqiyatda commit, istisnoda rollback), lekin u ulanishni yopmaydi. SQLite turlari qat'iy emas — "tur yaqinligi" tufayli 123 TEXT ustunda '123' bo'ladi, shuning uchun turlarni Python tomonda ham tekshiring. date/datetime uchun (3.12+) adapter/converter, murakkab maydonlar uchun JSON TEXT.
Baza — fayldan kuchli va ilovada qatlamlash. Foreign key har ulanishda PRAGMA foreign_keys=ON bilan yoqiladi; agregatsiya (GROUP BY) va filtr SQL'da, Python'da emas; ko'p qidiriladigan ustunga indeks. Ma'lumot qatlamini repozitoriy sinfiga ajrating — bu 23-qismdagi ORM ga o'tishning tabiiy ko'prigi.

Keyingi darsda — 16-qismning yakuniy darsi: gigabaytlab kattalikdagi fayllarni butun xotiraga sig'dirmasdan, oqim bilan o'qish va qayta ishlash. Generatorlar, iterparse, JSON Lines va baza — hammasini birlashtirib, katta ma'lumot bilan ishlashning umumiy naqshlarini ko'ramiz.

Ulashish:Telegram'da

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!

16.7-dars: sqlite3

1. Kirish va motivatsiya

2. Nazariya — chuqur tushuntirish

2.1. Ulanish va so'rovlar

2.2. Parametrli so'rovlar — SQL injection dan himoya

2.3. Row factory

2.4. Tranzaksiyalar

2.5. Tur yaqinligi (type affinity)

2.6. Adapter va converter

2.7. Foreign key, PRAGMA, agregatsiya

2.8. Resurs boshqaruvi

3. Tez ma'lumotnoma

4. Batafsil misollar

Misol 1 — Asoslar: jadval, so'rov, parametr, Row

Misol 2 — SQL injection va tranzaksiyalar

Misol 3 — Turlar, adapter/converter va foreign key

Misol 4 — Amaliy: obyektlarni saqlovchi repozitoriy

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

6. Keng tarqalgan xatolar va yechimlari

7. Integratsiya — bu bilim qayerda kerak bo'ladi

8. Eng yaxshi amaliyotlar

9. Amaliy topshiriq

Xulosa

Izohlar (0)

16.7-dars: `sqlite3`

2.3. `Row` factory