IlmHamroh
Python kursi/Bazalar va ORM6/12-dars15 daqiqa
Mundarija (22)

23.6-dars: SQLAlchemy Core

23-QISM — BAZALAR VA ORM · 6-dars

1. Kirish va motivatsiya

Xom SQL (matn satri) kuchli, lekin xatolarga moyil: sintaksis xatosi faqat ishga tushirganda ko'rinadi, SQL injection xavfi, va turli baza (SQLite, PostgreSQL) uchun har xil sintaksis. SQLAlchemy — Python'ning eng mashhur baza kutubxonasi — bu muammolarni hal qiladi. Uning ikki qismi bor: Core (SQL'ni Python obyektlari bilan) va ORM 23.7-bob. Bu darsda Core'ni o'rganamiz.

SQLAlchemy Core — SQL'ni Python ifodalari sifatida yozish: select(kurs).where(kurs.c.narx > 100) (matn emas). Bu xom SQL'dan yuqoriroq (xavfsiz, tekshiriladigan) va ORM'dan pastroq (jadval-markazli, obyekt emas). Turli bazaga mos SQL yaratadi (SQLite, PostgreSQL — bir kod). Django ORM 21.9-bob SQLAlchemy'ga o'xshaydi, lekin SQLAlchemy mustaqil (freymvorksiz).

Real vaziyat. Bir jamoa xom SQL yozardi — satr birlashtirish (injection xavfi), sintaksis xatolari ishga tushirganda topilardi, PostgreSQL'ga o'tganda SQL o'zgartirish kerak edi. SQLAlchemy Core'ga o'tdi: SQL Python obyektlari (select, where) — xavfsiz (parametr avtomatik), tekshiriladigan (IDE yordami), portativ (baza almashsa kod bir xil). Kod ishonchli va ko'chiriladigan bo'ldi.

Bu darsda SQLAlchemy Core'ni — Table, select, SQL ifodalarini o'rganamiz.

Bu darsda:

  • SQLAlchemy Core nima
  • Engine va ulanish
  • Table (jadval ta'rifi)
  • select() (so'rov obyekti)
  • where, order_by, agregat
  • insert, update, delete
  • Core vs xom SQL vs ORM
  • Amaliy: Core bilan CRUD

ℹ Misollarda SQLAlchemy Core (SQLite) bilan sinaladi.

2. Nazariya — chuqur tushuntirish

2.1. SQLAlchemy Core nima

SQLAlchemy Core — SQL'ni Python obyektlari bilan yozish:

python
# xom SQL:
conn.execute("SELECT nom FROM kurs WHERE narx > 100")

# SQLAlchemy Core:
select(kurs.c.nom).where(kurs.c.narx > 100)

SQLAlchemy Core — SQL'ni Python ifodalari sifatida quradi (matn emas). U xom SQL'dan yuqoriroq (xavfsiz, tekshiriladigan) va ORM'dan pastroq (jadval-markazli). Turli bazaga (SQLite, PostgreSQL) mos SQL yaratadi — portativ.

2.2. Engine va ulanish

Engine — bazaga ulanish manbai:

python
from sqlalchemy import create_engine

engine = create_engine("sqlite:///wisar.db")
# yoki PostgreSQL: create_engine("postgresql://user:parol@host/baza")

with engine.connect() as conn:          # o'qish
    conn.execute(select(...))

with engine.begin() as conn:            # yozish (tranzaksiya)
    conn.execute(insert(...))

create_engine(ulanish_satri) — baza manbai (ulanish havzasini boshqaradi). engine.connect() — ulanish; engine.begin() — tranzaksiya (avtomatik commit/rollback, 23.4). Ulanish satri bazani belgilaydi (sqlite://, postgresql://) — kod bir xil.

2.3. Table (jadval ta'rifi)

Table — jadval sxemasini Python'da:

python
from sqlalchemy import Table, Column, Integer, String, MetaData

metadata = MetaData()
kurs = Table(
    "kurs", metadata,
    Column("id", Integer, primary_key=True),
    Column("nom", String),
    Column("narx", Integer),
)
metadata.create_all(engine)             # jadval yaratish

Table(nom, metadata, Column(...)) — jadval ta'rifi (21.3 Django model, 23.1 CREATE TABLE ga o'xshaydi). Column — ustun (nom + tur). metadata.create_all — jadvallarni yaratadi. kurs.c.nom — ustunga havola (so'rovlarda).

2.4. select() (so'rov obyekti)

select() — SELECT so'rovini quradi:

python
from sqlalchemy import select

select(kurs)                            # SELECT *
select(kurs.c.nom, kurs.c.narx)         # tanlangan ustun
select(kurs).where(kurs.c.narx > 100)   # filtr

select(ustunlar) — SELECT so'rov obyekti. kurs.c.ustun — ustun (c = columns). Bu 21.9 dagi Django Kurs.objects ga o'xshaydi, lekin jadval-markazli (obyekt emas). So'rov obyekt — bajarilguncha SQL emas (dangasa, 21.9).

2.5. where, order_by, agregat

So'rovni boyitish:

python
from sqlalchemy import func

select(kurs).where(kurs.c.narx > 100)                    # filtr
select(kurs).where(kurs.c.til == "uz", kurs.c.narx < 200)  # VA
select(kurs).order_by(kurs.c.narx.desc())                # saralash
select(func.count(), func.avg(kurs.c.narx))              # agregat
select(kurs.c.til, func.count()).group_by(kurs.c.til)    # guruh

.where() (filtr), .order_by() (saralash — .desc()), func.count()/func.avg() (agregat), .group_by() (guruh). Bu 23.1 SQL va 21.9 ORM'ga mos. Python operatorlari (>, ==) SQL shartiga aylanadi. Zanjir bilan quriladi.

2.6. insert, update, delete

O'zgartirish so'rovlari:

python
from sqlalchemy import insert, update, delete

conn.execute(insert(kurs), {"nom": "Python", "narx": 100})       # qo'shish
conn.execute(insert(kurs), [{...}, {...}])                        # ko'p
conn.execute(update(kurs).where(kurs.c.id == 1).values(narx=150))  # yangila
conn.execute(delete(kurs).where(kurs.c.narx > 200))              # o'chir

insert(jadval) (qo'shish — dict yoki ro'yxat), update(jadval).where(...).values(...) (yangila), delete(jadval).where(...) (o'chir). Barcha o'zgarish engine.begin() (tranzaksiya) ichida. Parametrlar avtomatik (injection himoya, 23.1).

2.7. Core vs xom SQL vs ORM

Daraja Nima
Xom SQL Matn satri ("SELECT ...")
SQLAlchemy Core SQL obyekti (select(...))
SQLAlchemy ORM Python obyekt (session.query)

Uch daraja: xom SQL (matn — kuchli, lekin xavfli), Core (SQL obyekt — xavfsiz, portativ, jadval-markazli), ORM (Python obyekt — 23.7, model-markazli). Core — o'rta: SQL nazorati + Python xavfsizligi. Murakkab so'rov (hisobot) uchun Core, oddiy CRUD uchun ORM.

2.8. Core afzalliklari

Afzallik Tafsilot
Xavfsiz Parametr avtomatik (injection yo'q)
Portativ Baza almashsa kod bir xil
Tekshiriladigan IDE, tur belgilari
SQL nazorati To'liq SQL kuchi

SQLAlchemy Core — xom SQL'ning kuchi + Python'ning xavfsizligi: parametr avtomatik (injection yo'q), turli bazaga mos (portativ), IDE yordami (tekshiriladigan). Murakkab so'rovlar (JOIN, agregat, hisobot) uchun ideal. ORM ostida ham Core ishlaydi.

3. Tez ma'lumotnoma

python
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy import select, insert, update, delete, func

engine = create_engine("sqlite:///wisar.db")
metadata = MetaData()
kurs = Table("kurs", metadata,
    Column("id", Integer, primary_key=True),
    Column("nom", String), Column("narx", Integer))
metadata.create_all(engine)

# yozish (tranzaksiya):
with engine.begin() as conn:
    conn.execute(insert(kurs), {"nom": "Python", "narx": 100})
    conn.execute(update(kurs).where(kurs.c.id == 1).values(narx=150))

# o'qish:
with engine.connect() as conn:
    r = conn.execute(select(kurs).where(kurs.c.narx > 100).order_by(kurs.c.narx))
    for row in r: ...

Core komponentlari

create_engine — ulanish · Table/Column — jadval · select/insert/update/delete
kurs.c.ustun — ustun · where/order_by/func — so'rov

4. Batafsil misollar

Misollarda SQLAlchemy Core (SQLite) bilan sinaladi.

Misol 1 — Engine, Table, insert

python
"""create_engine (ulanish); Table/Column (jadval); metadata.create_all; insert (qo'shish)."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, insert, select


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    metadata = MetaData()
    kurs = Table(
        "kurs", metadata,
        Column("id", Integer, primary_key=True),
        Column("nom", String),
        Column("narx", Integer),
    )

    print("=== 1. Jadval ta'rifi (Table) ===")
    metadata.create_all(engine)
    print(f"  jadval: {kurs.name}, ustunlar: {[c.name for c in kurs.columns]}")

    print("\n=== 2. insert (bitta) ===")
    with engine.begin() as conn:
        conn.execute(insert(kurs), {"nom": "Python", "narx": 100})
    print("  Python qo'shildi")

    print("\n=== 3. insert (ko'p) ===")
    with engine.begin() as conn:
        conn.execute(insert(kurs), [{"nom": "Go", "narx": 120}, {"nom": "Rust", "narx": 150}])
    print("  Go, Rust qo'shildi")

    print("\n=== 4. Tekshirish (select) ===")
    with engine.connect() as conn:
        nomlar = [row.nom for row in conn.execute(select(kurs.c.nom).order_by(kurs.c.id))]
    print(f"  kurslar: {nomlar}")
    print("  ⭐ create_engine, Table/Column, insert — Core asoslari")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. Jadval ta'rifi (Table) ===
  jadval: kurs, ustunlar: ['id', 'nom', 'narx']

=== 2. insert (bitta) ===
  Python qo'shildi

=== 3. insert (ko'p) ===
  Go, Rust qo'shildi

=== 4. Tekshirish (select) ===
  kurslar: ['Python', 'Go', 'Rust']
  ⭐ create_engine, Table/Column, insert — Core asoslari

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

Misol 2 — select (where, order_by)

python
"""select (so'rov obyekti); where (filtr); order_by (saralash); kurs.c.ustun; Python operatorlari."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, insert, select


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    metadata = MetaData()
    kurs = Table(
        "kurs", metadata,
        Column("id", Integer, primary_key=True),
        Column("nom", String), Column("narx", Integer), Column("til", String),
    )
    metadata.create_all(engine)
    with engine.begin() as conn:
        conn.execute(insert(kurs), [
            {"nom": "Python", "narx": 100, "til": "uz"},
            {"nom": "JavaScript", "narx": 80, "til": "uz"},
            {"nom": "Go", "narx": 120, "til": "en"},
            {"nom": "Rust", "narx": 150, "til": "en"},
        ])

    with engine.connect() as conn:
        print("=== 1. select + where (filtr) ===")
        r = conn.execute(select(kurs.c.nom, kurs.c.narx).where(kurs.c.narx >= 120)).fetchall()
        print(f"  narx>=120: {[(row.nom, row.narx) for row in r]}")

        print("\n=== 2. where (VA — bir necha shart) ===")
        r = conn.execute(select(kurs.c.nom).where(kurs.c.til == "uz", kurs.c.narx < 100)).fetchall()
        print(f"  uz va narx<100: {[row.nom for row in r]}")

        print("\n=== 3. order_by (saralash) ===")
        r = conn.execute(select(kurs.c.nom).order_by(kurs.c.narx.desc())).fetchall()
        print(f"  narx kamayuvchi: {[row.nom for row in r]}")

        print("\n=== 4. Zanjir (where + order_by + limit) ===")
        r = conn.execute(select(kurs.c.nom).where(kurs.c.til == "en").order_by(kurs.c.narx).limit(1)).fetchall()
        print(f"  en, eng arzon: {[row.nom for row in r]}")
    print("  ⭐ select(...).where(...).order_by(...) — SQL obyekt zanjiri")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. select + where (filtr) ===
  narx>=120: [('Go', 120), ('Rust', 150)]

=== 2. where (VA — bir necha shart) ===
  uz va narx<100: ['JavaScript']

=== 3. order_by (saralash) ===
  narx kamayuvchi: ['Rust', 'Go', 'Python', 'JavaScript']

=== 4. Zanjir (where + order_by + limit) ===
  en, eng arzon: ['Go']
  ⭐ select(...).where(...).order_by(...) — SQL obyekt zanjiri

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

Misol 3 — Agregat va update/delete

python
"""func (count, avg, sum); group_by (guruh); update/delete (o'zgartirish); tranzaksiya."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, delete, func, insert, select, update


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    metadata = MetaData()
    kurs = Table(
        "kurs", metadata,
        Column("id", Integer, primary_key=True),
        Column("nom", String), Column("narx", Integer), Column("til", String),
    )
    metadata.create_all(engine)
    with engine.begin() as conn:
        conn.execute(insert(kurs), [
            {"nom": "Python", "narx": 100, "til": "uz"},
            {"nom": "Go", "narx": 120, "til": "en"},
            {"nom": "Rust", "narx": 150, "til": "en"},
            {"nom": "Java", "narx": 90, "til": "uz"},
        ])

    with engine.connect() as conn:
        print("=== 1. Agregat (count, avg, max) ===")
        r = conn.execute(select(func.count(), func.avg(kurs.c.narx), func.max(kurs.c.narx))).fetchone()
        print(f"  soni: {r[0]}, o'rtacha: {round(r[1], 1)}, max: {r[2]}")

        print("\n=== 2. group_by (til bo'yicha) ===")
        r = conn.execute(select(kurs.c.til, func.count()).group_by(kurs.c.til).order_by(kurs.c.til)).fetchall()
        print(f"  {[(row[0], row[1]) for row in r]}")

    print("\n=== 3. update (uz kurslarga +10) ===")
    with engine.begin() as conn:
        conn.execute(update(kurs).where(kurs.c.til == "uz").values(narx=kurs.c.narx + 10))
    with engine.connect() as conn:
        r = conn.execute(select(kurs.c.nom, kurs.c.narx).where(kurs.c.til == "uz").order_by(kurs.c.nom)).fetchall()
        print(f"  yangilangan: {[(row.nom, row.narx) for row in r]}")

    print("\n=== 4. delete (qimmat kurslar) ===")
    with engine.begin() as conn:
        conn.execute(delete(kurs).where(kurs.c.narx >= 150))
    with engine.connect() as conn:
        nomlar = [row.nom for row in conn.execute(select(kurs.c.nom).order_by(kurs.c.id))]
    print(f"  qolgan: {nomlar}")
    print("  ⭐ func (agregat), group_by, update/delete — Core so'rovlari")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. Agregat (count, avg, max) ===
  soni: 4, o'rtacha: 115.0, max: 150

=== 2. group_by (til bo'yicha) ===
  [('en', 2), ('uz', 2)]

=== 3. update (uz kurslarga +10) ===
  yangilangan: [('Java', 100), ('Python', 110)]

=== 4. delete (qimmat kurslar) ===
  qolgan: ['Python', 'Go', 'Java']
  ⭐ func (agregat), group_by, update/delete — Core so'rovlari

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

Misol 4 — Amaliy: Core bilan to'liq CRUD

SQLAlchemy Core bilan to'liq CRUD (yaratish, o'qish, yangila, o'chir) — xavfsiz, portativ. Bu — real Core ilovasining namunasi.

python
"""to'liq CRUD Core bilan: insert, select (filtr/agregat), update, delete; xavfsiz (parametr avto)."""

import warnings
warnings.filterwarnings("ignore")

from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine, delete, func, insert, select, update


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    metadata = MetaData()
    kurs = Table(
        "kurs", metadata,
        Column("id", Integer, primary_key=True),
        Column("nom", String), Column("narx", Integer),
    )
    metadata.create_all(engine)

    def kurslar():
        with engine.connect() as conn:
            return [(row.id, row.nom, row.narx) for row in conn.execute(select(kurs).order_by(kurs.c.id))]

    print("=== 1. Create (insert) ===")
    with engine.begin() as conn:
        conn.execute(insert(kurs), [{"nom": "Python", "narx": 100}, {"nom": "Go", "narx": 120}])
    print(f"  {[(k[1], k[2]) for k in kurslar()]}")

    print("\n=== 2. Read (select filtr) ===")
    with engine.connect() as conn:
        r = conn.execute(select(kurs.c.nom).where(kurs.c.narx > 100)).fetchall()
    print(f"  narx>100: {[row.nom for row in r]}")

    print("\n=== 3. Update ===")
    with engine.begin() as conn:
        conn.execute(update(kurs).where(kurs.c.nom == "Python").values(narx=150))
    print(f"  {[(k[1], k[2]) for k in kurslar()]}")

    print("\n=== 4. Delete va hisobot ===")
    with engine.begin() as conn:
        conn.execute(delete(kurs).where(kurs.c.narx >= 150))
    with engine.connect() as conn:
        soni = conn.execute(select(func.count()).select_from(kurs)).scalar()
    print(f"  qolgan: {[k[1] for k in kurslar()]}, jami: {soni}")
    print("  ⭐ Core CRUD — xavfsiz (parametr avto), portativ (baza almashsa bir xil)")


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text
=== 1. Create (insert) ===
  [('Python', 100), ('Go', 120)]

=== 2. Read (select filtr) ===
  narx>100: ['Go']

=== 3. Update ===
  [('Python', 150), ('Go', 120)]

=== 4. Delete va hisobot ===
  qolgan: ['Go'], jami: 1
  ⭐ Core CRUD — xavfsiz (parametr avto), portativ (baza almashsa bir xil)

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
"Core — ORM bilan bir xil" Core jadval-markazli, ORM obyekt
"Xom SQL yozish kerak" Core (SQL obyekt)
"Core SQL emas" SQL yaratadi (yuqori daraja)
"select darrov bajariladi" Obyekt (bajarilganda)
"Parametr qo'lda" Avtomatik (injection yo'q)
"Baza almashsa qayta yozish" Portativ (bir kod)
"engine.connect yozish uchun" engine.begin (tranzaksiya)
"Core keraksiz (ORM bor)" Murakkab so'rov uchun

6. Keng tarqalgan xatolar va yechimlari

1. Yozishda connect (tranzaksiyasiz)

python
with engine.connect() as conn: conn.execute(insert(...))   # ⚠️ commit yo'q
with engine.begin() as conn: conn.execute(insert(...))     # ✅ tranzaksiya

2. kurs.c o'rniga kurs.ustun

python
select(kurs.nom)                              # ❌
select(kurs.c.nom)                            # ✅ (c = columns)

3. metadata.create_all unutish

python
kurs = Table(...)                             # ⚠️ jadval yaratilmaydi
metadata.create_all(engine)                   # ✅

4. Xom SQL bilan aralashtirish

python
conn.execute("SELECT ...")                    # xom (ba'zan kerak)
conn.execute(select(kurs)...)                 # ✅ Core (xavfsiz)

5. whereda and/or (Python)

python
select(kurs).where(kurs.c.a > 1 and kurs.c.b < 2)   # ❌ Python and
select(kurs).where(kurs.c.a > 1, kurs.c.b < 2)      # ✅ (VA)

6. Natijani fetchallsiz ishlatish

python
r = conn.execute(select(...))                 # Result obyekt
for row in r: ...                             # ✅ (yoki .fetchall())

7. Tableni har so'rovda qayta ta'rifla

python
# har funksiyada Table(...)                    # ⚠️ takror
# bir marta (metadata)                          # ✅

7. Integratsiya — bu bilim qayerda kerak bo'ladi

  • 23.1–23.4 (o'tilgan): SQL — Core asosi
  • 23.5-dars (o'tilgan): PostgreSQL — Core bilan
  • 23.7-dars: SQLAlchemy ORM — Core ustida
  • 21.9-dars (o'tilgan): Django ORM — taqqoslash
  • 20.8-dars (o'tilgan): FastAPI baza — SQLAlchemy

8. Eng yaxshi amaliyotlar

  1. Murakkab so'rov uchun Core (xom SQL emas).

  2. engine.begin() — yozish (tranzaksiya).

  3. kurs.c.ustun bilan ustunga havola.

  4. Parametr avtomatik (injection himoya).

  5. Portativ kod (baza almashsa bir xil).

  6. func bilan agregat (count, avg).

  7. Tableni bir marta ta'rifla (metadata).

  8. Oddiy CRUD — ORM 23.7-bob, murakkab — Core.

9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

python
1.  # SQLAlchemy Core nima?
2.  # Engine nima?
3.  # Table nima?
4.  # select nima?
5.  # kurs.c nima?
6.  # engine.connect vs begin?
7.  # where nima?
8.  # func nima?
9.  # Core vs ORM?
10. # parametr avtomatikmi?
11. # portativ nima?
12. # metadata.create_all nima?
Javoblar
  1. SQL'ni Python obyektlari bilan yozish
  2. Bazaga ulanish manbai
  3. Jadval ta'rifi (Column bilan)
  4. SELECT so'rov obyekti
  5. Ustunlar (c = columns)
  6. connect — o'qish, begin — tranzaksiya
  7. Filtr (WHERE)
  8. Agregat funksiyalari (count, avg)
  9. Core jadval-markazli, ORM obyekt-markazli
  10. Ha (injection himoya)
  11. Baza almashsa kod bir xil
  12. Jadvallarni yaratadi

Vazifa 2: Xatolarni tuzating

python
1.  with engine.connect(): conn.execute(insert(...))   # begin

2.  select(kurs.nom)                          # c

3.  kurs = Table(...)                         # create_all

4.  where(kurs.c.a > 1 and kurs.c.b < 2)      # VA

5.  # har funksiyada Table(...)               # bir marta
Javoblar
python
1.  with engine.begin() as conn: conn.execute(insert(...))

2.  select(kurs.c.nom)

3.  metadata.create_all(engine)

4.  where(kurs.c.a > 1, kurs.c.b < 2)

5.  # Table bir marta (metadata)

Vazifa 3: Core CRUD

To'liq CRUD:

  1. Table, Engine
  2. insert, select, update, delete
  3. where, order_by
  4. Test

Vazifa 4: So'rov zanjiri

Murakkab select:

  1. where (bir necha)
  2. order_by, limit
  3. func (agregat)
  4. group_by

Vazifa 5: Xavfsizlik

Parametr:

  1. Foydalanuvchi kiritgan qiymat
  2. where bilan (avtomatik parametr)
  3. Injection urinish (himoyalangan)
  4. Test

Vazifa 6: Portativlik

Baza almashtirish:

  1. SQLite bilan kod
  2. PostgreSQL ulanish (o'zgarish)
  3. So'rov bir xil
  4. Portativlikni ko'rsatish

Vazifa 7: O'ylash

SQLAlchemy Core SQL'ni Python obyektlari (select(kurs).where(...)) sifatida yozadi — matn satri emas. Bu xato manbaini kamaytiradi: sintaksis xatosi darrov (matn emas, obyekt), parametr avtomatik (injection yo'q), IDE yordami (tur belgilari). "Matn o'rniga tuzilgan obyekt" (structured over string) tamoyili nima uchun xavfsizroq va tekshiriladigan, va bu 20.8 dagi SQL injection himoyasi bilan qanday bog'liq?

Javob

Qisqa javob: SQL'ni matn (satr) o'rniga tuzilgan obyekt (select, where) sifatida yozish xavfsizroq, chunki: sintaksis xatosi darrov (obyekt yaratganda, matn emas), parametr avtomatik ajratiladi (qiymat SQL matniga aralashmaydi — injection yo'q, 20.8), IDE yordami (obyekt tekshiriladi, matn emas). "Matn vs tuzilgan obyekt" — matn tekshirib bo'lmaydi (faqat ishga tushirganda), obyekt tuzilishi bilan to'g'ri (IDE, tur). Bu 20.8 dagi SQL injection himoyasi bilan bog'liq: parametr avtomatik (kod va ma'lumot ajratiladi — matn birlashtirish yo'q). Tuzilgan obyekt — kod va ma'lumotni ajratiladigan qiladi.

1. Matn vs tuzilgan obyekt

Matn (xom SQL) Obyekt (Core)
"SELECT ... " + nom select(...).where(...)
Injection xavfi Parametr avtomatik
Sintaksis ishga tushirganda Obyekt yaratganda
IDE yordami yo'q Tur, tekshiruv

2. Nega obyekt xavfsizroq

Obyektda qiymat (nom) parametr sifatida (avtomatik ajratiladi) — SQL matniga aralashmaydi. Matnda birlashtirish (+) — qiymat kodga aylanish xavfi (injection, 20.8).

3. SQL injection bilan bog'lik

20.8: "kod va ma'lumotni ajrat" — injection'ning yechimi. Core buni avtomatik qiladi (parametr). Matn birlashtirish — ajratish buziladi.

4. "Tuzilgan obyekt" boshqa joyda

Matn Tuzilgan obyekt
SQL satr select (Core)
HTML satr Template (escape)
Yo'l satr Path obyekt
JSON satr dict + json

5. Muhandislik saboqlari

  1. Matn o'rniga tuzilgan obyekt (xavfsizroq)
  2. Parametr avtomatik (injection yo'q)
  3. Obyekt tekshiriladi (matn emas)
  4. Kod va ma'lumotni ajrat (20.8)

6. Xulosa

  1. Core — SQL obyekt (matn emas)
  2. Parametr avtomatik (injection himoya)
  3. Tekshiriladigan (IDE, tur)
  4. "Tuzilgan obyekt" — xavfsiz, ajratilgan

Nimani mustahkamlaydi: 2.1–2.8-bo'limlar.

Xulosa

Bu darsda SQLAlchemy Core'ni o'rgandik.

Eng muhim uch fikr:

  1. Core — SQL Python obyektlari bilan. SQLAlchemy Core — SQL'ni Python ifodalari (select(kurs).where(kurs.c.narx > 100)) sifatida quradi (matn emas). create_engine(ulanish_satri) — baza manbai (sqlite://, postgresql://); engine.connect() — o'qish, engine.begin() — tranzaksiya (avtomatik commit/rollback). Table(nom, metadata, Column(...)) — jadval ta'rifi (23.1 CREATE TABLE, 21.3 model ga o'xshaydi), metadata.create_all — jadvallarni yaratadi.

  2. select va so'rov zanjiri. select(ustunlar) — SELECT obyekti; kurs.c.ustun — ustun (c = columns). .where() (filtr — Python operatorlari >, == SQL shartiga aylanadi), .order_by() (saralash — .desc()), func.count()/func.avg() (agregat), .group_by() (guruh). insert/update/delete — o'zgarish (engine.begin() ichida). Bu 23.1 SQL va 21.9 ORM'ga mos, so'rov obyekt (bajarilganda SQL).

  3. Xavfsizlik va portativlik. Core — xom SQL'ning kuchi + Python'ning xavfsizligi: parametr avtomatik (SQL injection yo'q, 20.8 — kod va ma'lumot ajratiladi), turli bazaga mos (portativ — baza almashsa kod bir xil), IDE yordami (tekshiriladigan). Uch daraja: xom SQL (matn, xavfli), Core (SQL obyekt — jadval-markazli), ORM (Python obyekt — 23.7, model-markazli). Murakkab so'rov (JOIN, agregat, hisobot) uchun Core; oddiy CRUD uchun ORM.

Keyingi darsda SQLAlchemy ORM ni o'rganamiz: SQL'ni Python obyektlari (model klasslari) bilan — Django ORM 21.3-bob kabi, lekin mustaqil (freymvorksiz).

Ulashish:Telegram'da

Izohlar (0)

Izoh yozish uchun kiring.

  • Hozircha izoh yo'q. Birinchi bo'ling!
23.6-dars: SQLAlchemy Core — IlmHamroh