Python kursi/Bazalar va ORM5/12-dars16 daqiqa

Mundarija (22)

23.5-dars: PostgreSQL bilan ishlash

23-QISM — BAZALAR VA ORM · 5-dars

1. Kirish va motivatsiya

SQLite — o'rganish va kichik loyihalar uchun ajoyib (bir fayl, sozlamasiz). Lekin ishlab chiqarishda eng mashhur relyatsion baza — PostgreSQL. U kuchli, ishonchli, boy xususiyatli (JSONB, ARRAY, to'liq matn qidiruv, kengaytmalar). Ko'p jiddiy loyiha (Instagram, Reddit) PostgreSQL ishlatadi. Bu darsda PostgreSQL bilan ishlashni o'rganamiz.

PostgreSQL — "eng ilg'or ochiq kod baza": SQL standartini to'liq qo'llaydi, murakkab turlar (JSONB, ARRAY, UUID), tranzaksiyalar 23.4-bob, indekslar 23.3-bob, va katta ma'lumot bilan ishlaydi. SQLite'dan farqi — server (alohida jarayon), ko'p foydalanuvchi, yuqori yuklama. Python'da psycopg (yoki asyncpg) bilan ulaniladi.

Real vaziyat. Bir loyiha SQLite bilan boshladi (prototip) — bir fayl, oson. Lekin foydalanuvchi ko'paydi: bir vaqtda ko'p yozish (SQLite bloklaydi), katta ma'lumot (sekin). Jamoa PostgreSQL'ga o'tdi: ko'p ulanish, tez, ishonchli. Kod deyarli o'zgarmadi (DB-API bir xil) — faqat ulanish (psycopg.connect). SQLite — prototip; PostgreSQL — ishlab chiqarish.

Bu darsda PostgreSQL'ni — ulanish, xususiyatlar va SQLite'dan farqlarini o'rganamiz.

Bu darsda:

PostgreSQL nima (ishlab chiqarish bazasi)
SQLite vs PostgreSQL
psycopg bilan ulanish
DB-API (bir xil interfeys)
PostgreSQL turlari (SERIAL, JSONB, ARRAY)
RETURNING, UPSERT
Ulanish havzasi (pool)
Amaliy: PostgreSQL naqshlari

ℹ Misollarda sqlite3 bilan o'tkaziladigan (PostgreSQL'da ham ishlovchi) xususiyatlar sinaladi.

2. Nazariya — chuqur tushuntirish

2.1. PostgreSQL nima

PostgreSQL — kuchli, ochiq kod relyatsion baza (server):

Xususiyat	PostgreSQL
Turi	Server (alohida jarayon)
Foydalanuvchi	Ko'p (parallel)
SQL	To'liq standart
Turlar	Boy (JSONB, ARRAY, UUID)
Ishlatish	Ishlab chiqarish

PostgreSQL — server bazasi (SQLite fayl, PostgreSQL server): alohida jarayon, ko'p foydalanuvchi, tarmoq orqali ulanish. U boy xususiyatli (JSONB, to'liq matn qidiruv, kengaytmalar) va ishonchli (ACID, 23.4). Ishlab chiqarishda standart tanlov.

2.2. SQLite vs PostgreSQL

Xususiyat	SQLite	PostgreSQL
Tuzilma	Bir fayl	Server
Sozlash	Yo'q	Kerak (o'rnatish)
Ko'p yozish	Bloklaydi	Parallel
Turlar	Kam	Boy (JSONB, ARRAY)
Hajm	Kichik-o'rta	Katta
Ishlatish	Prototip, mobil	Ishlab chiqarish

SQLite — bir fayl, sozlamasiz, kichik loyiha (prototip, mobil, test). PostgreSQL — server, ko'p foydalanuvchi, katta ma'lumot, ishlab chiqarish. SQLite — o'rganish/boshlash; PostgreSQL — jiddiy loyiha. Kod (SQL, DB-API) deyarli bir xil — o'tish oson.

2.3. `psycopg` bilan ulanish

Python'da PostgreSQL — psycopg (v3) bilan:

python

import psycopg

conn = psycopg.connect(
    "postgresql://foydalanuvchi:parol@localhost:5432/wisar_db"
)
cur = conn.cursor()
cur.execute("SELECT * FROM kurs WHERE narx > %s", (100,))
conn.commit()
conn.close()

psycopg.connect(ulanish_satri) — PostgreSQL'ga ulanish (postgresql://user:parol@host:port/baza). psycopg — Python'ning eng mashhur PostgreSQL drayveri (v3 zamonaviy, async ham). Parametr %s (SQLite'da ?), qolgani bir xil.

2.4. DB-API (bir xil interfeys)

Python DB-API — barcha baza uchun bir xil interfeys:

python

# sqlite3 va psycopg — bir xil naqsh:
conn = drayver.connect(...)
cur = conn.cursor()
cur.execute("SELECT ...", (parametr,))
cur.fetchall()
conn.commit()
conn.close()

DB-API (PEP 249) — Python'ning baza standarti: connect, cursor, execute, fetch, commit. Barcha baza (sqlite3, psycopg, MySQL) shu interfeysni qo'llaydi — kod deyarli bir xil (faqat connect va parametr belgisi farq). Bu 22.5 dagi WSGI kabi standart — bazani almashtirish oson.

2.5. PostgreSQL turlari

PostgreSQL boy turlarga ega:

Tur	Ma'no
`SERIAL`	Avto-oshuvchi son (id)
`JSONB`	JSON (indekslanadigan)
`ARRAY`	Massiv (`INTEGER[]`)
`UUID`	Noyob identifikator
`TIMESTAMP`	Sana-vaqt
`TEXT`	Cheksiz matn

sql

CREATE TABLE kurs (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    teglar TEXT[],              -- massiv
    sozlama JSONB               -- JSON
);

PostgreSQL — SQLite'dan boy turlar: SERIAL (avto-id), JSONB (indekslanadigan JSON), ARRAY (massiv), UUID. Bu strukturlangan va yarim-strukturlangan ma'lumotni birga saqlash imkonini beradi (JSONB — NoSQL kabi, relyatsion ichida).

2.6. `RETURNING`, `UPSERT`

PostgreSQL (va zamonaviy SQLite) kuchli SQL:

sql

-- RETURNING (qo'shilgan/o'zgartirilgan satrni qaytarish):
INSERT INTO kurs (nom, narx) VALUES ('Python', 100) RETURNING id;

-- UPSERT (bor bo'lsa yangila, yo'q bo'lsa qo'sh):
INSERT INTO hisob (id, ball) VALUES (1, 5)
ON CONFLICT (id) DO UPDATE SET ball = ball + excluded.ball;

RETURNING — INSERT/UPDATE/DELETE dan keyin satrni (masalan yangi id) qaytaradi (alohida SELECT kerak emas). UPSERT (ON CONFLICT) — "bor bo'lsa yangila, yo'q bo'lsa qo'sh" (bitta so'rov). Bu 20.9 CRUD'ni soddalashtiradi.

2.7. Ulanish havzasi (pool)

Ko'p ulanish uchun havza (connection pool):

python

from psycopg_pool import ConnectionPool

pool = ConnectionPool("postgresql://...")
with pool.connection() as conn:
    conn.execute("SELECT ...")

Ulanish havzasi — tayyor ulanishlar to'plami (qayta ishlatiladi). Har so'rovda yangi ulanish yaratish (qimmat) o'rniga havzadan olinadi. Bu yuqori yuklamada muhim (ming so'rov — chekli ulanish). Bu 20.8 dagi baza ulanish boshqaruvining ishlab chiqarish tomoni.

2.8. PostgreSQL — ishlab chiqarish tanlovi

Sabab	Tafsilot
Ishonchli	ACID, yetuk (30 yil)
Kuchli	JSONB, to'liq matn, kengaytma
Miqyosli	Katta ma'lumot, ko'p ulanish
Ochiq kod	Bepul, katta jamoa
Standart	Ko'p ilova, hosting

PostgreSQL — ishlab chiqarishda standart tanlov (22.4 "vazifaga mos"): ishonchli (ACID), kuchli (boy turlar), miqyosli (katta ma'lumot). SQLite — prototip/kichik; PostgreSQL — jiddiy ishlab chiqarish. Django/FastAPI ilovalar odatda PostgreSQL bilan (21.16, 20.8).

3. Tez ma'lumotnoma

python

# psycopg (v3) ulanish:
import psycopg
conn = psycopg.connect("postgresql://user:parol@localhost:5432/baza")
cur = conn.cursor()
cur.execute("SELECT * FROM kurs WHERE narx > %s", (100,))   # %s (SQLite'da ?)
conn.commit(); conn.close()

# ulanish havzasi:
from psycopg_pool import ConnectionPool
pool = ConnectionPool("postgresql://...")
with pool.connection() as conn: ...

sql

-- PostgreSQL turlari:
CREATE TABLE kurs (id SERIAL PRIMARY KEY, teglar TEXT[], sozlama JSONB);

-- RETURNING / UPSERT:
INSERT INTO kurs (nom) VALUES ('Python') RETURNING id;
INSERT INTO hisob (id, ball) VALUES (1, 5) ON CONFLICT (id) DO UPDATE SET ball = ball + excluded.ball;

4. Batafsil misollar

Misollarda sqlite3 bilan o'tkaziladigan xususiyatlar (PostgreSQL'da ham ishlaydi) sinaladi.

Misol 1 — DB-API (bir xil interfeys)

python

"""DB-API standart (connect, cursor, execute, fetch, commit); psycopg va sqlite3 bir xil naqsh."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    # DB-API naqshi — psycopg'da ham bir xil (faqat connect va %s farq)
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    print("=== 1. connect + cursor ===")
    print(f"  connection: {type(conn).__name__}, cursor: {type(cur).__name__}")

    print("\n=== 2. execute (DDL, DML) ===")
    cur.execute("CREATE TABLE kurs (id INTEGER PRIMARY KEY, nom TEXT, narx INTEGER)")
    cur.execute("INSERT INTO kurs (nom, narx) VALUES (?, ?)", ("Python", 100))
    conn.commit()
    print("  jadval yaratildi, satr qo'shildi")

    print("\n=== 3. execute (parametr) + fetch ===")
    cur.execute("SELECT nom, narx FROM kurs WHERE narx > ?", (50,))
    r = cur.fetchone()
    print(f"  natija: {r['nom']}, {r['narx']}")

    print("\n=== 4. DB-API — bir xil naqsh ===")
    print("  sqlite3: ? parametr; psycopg: %s parametr")
    print("  connect → cursor → execute → fetch → commit (bir xil)")
    print("  ⭐ DB-API — barcha baza uchun bir xil interfeys (PEP 249)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. connect + cursor ===
  connection: Connection, cursor: Cursor

=== 2. execute (DDL, DML) ===
  jadval yaratildi, satr qo'shildi

=== 3. execute (parametr) + fetch ===
  natija: Python, 100

=== 4. DB-API — bir xil naqsh ===
  sqlite3: ? parametr; psycopg: %s parametr
  connect → cursor → execute → fetch → commit (bir xil)
  ⭐ DB-API — barcha baza uchun bir xil interfeys (PEP 249)

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

Misol 2 — `RETURNING`

python

"""RETURNING — qo'shilgan/o'zgartirilgan satrni qaytarish; yangi id; alohida SELECT kerak emas."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("CREATE TABLE kurs (id INTEGER PRIMARY KEY AUTOINCREMENT, nom TEXT, narx INTEGER)")

    print("=== 1. INSERT ... RETURNING id ===")
    r = cur.execute("INSERT INTO kurs (nom, narx) VALUES (?, ?) RETURNING id", ("Python", 100)).fetchone()
    print(f"  yangi id: {r['id']} (alohida SELECT kerak emas)")

    print("\n=== 2. RETURNING bir necha ustun ===")
    r = cur.execute("INSERT INTO kurs (nom, narx) VALUES (?, ?) RETURNING id, nom, narx", ("Go", 120)).fetchone()
    print(f"  qaytarildi: {dict(r)}")

    print("\n=== 3. UPDATE ... RETURNING ===")
    r = cur.execute("UPDATE kurs SET narx = narx + 10 WHERE nom = 'Python' RETURNING nom, narx").fetchone()
    print(f"  yangilangan: {r['nom']} → {r['narx']}")

    print("\n=== 4. DELETE ... RETURNING ===")
    r = cur.execute("DELETE FROM kurs WHERE nom = 'Go' RETURNING nom").fetchone()
    print(f"  o'chirilgan: {r['nom']}")
    print("  ⭐ RETURNING — o'zgartirilgan satrni darrov qaytarish (PostgreSQL)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. INSERT ... RETURNING id ===
  yangi id: 1 (alohida SELECT kerak emas)

=== 2. RETURNING bir necha ustun ===
  qaytarildi: {'id': 2, 'nom': 'Go', 'narx': 120}

=== 3. UPDATE ... RETURNING ===
  yangilangan: Python → 110

=== 4. DELETE ... RETURNING ===
  o'chirilgan: Go
  ⭐ RETURNING — o'zgartirilgan satrni darrov qaytarish (PostgreSQL)

Nima ko'rsatdi: 2.6-bo'lim.

Misol 3 — `UPSERT` va JSON

python

"""UPSERT (ON CONFLICT — bor yangila, yo'q qo'sh); JSON saqlash va o'qish (PostgreSQL JSONB kabi)."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    print("=== 1. UPSERT — birinchi (INSERT) ===")
    cur.execute("CREATE TABLE hisob (id INTEGER PRIMARY KEY, ball INTEGER)")
    cur.execute("INSERT INTO hisob (id, ball) VALUES (1, 10)")
    print(f"  ball: {cur.execute('SELECT ball FROM hisob WHERE id=1').fetchone()['ball']}")

    print("\n=== 2. UPSERT — takror (ON CONFLICT UPDATE) ===")
    cur.execute(
        "INSERT INTO hisob (id, ball) VALUES (1, 5) "
        "ON CONFLICT (id) DO UPDATE SET ball = ball + excluded.ball"
    )
    print(f"  ball (10+5): {cur.execute('SELECT ball FROM hisob WHERE id=1').fetchone()['ball']}")

    print("\n=== 3. JSON saqlash ===")
    cur.execute("CREATE TABLE profil (id INTEGER PRIMARY KEY, data TEXT)")
    cur.execute("INSERT INTO profil (data) VALUES (?)", ('{"ism": "Ali", "yosh": 25, "til": ["uz", "en"]}',))
    print("  JSON ma'lumot saqlandi")

    print("\n=== 4. JSON o'qish (json_extract) ===")
    r = cur.execute(
        "SELECT json_extract(data, '$.ism') ism, json_extract(data, '$.yosh') yosh FROM profil"
    ).fetchone()
    print(f"  ism: {r['ism']}, yosh: {r['yosh']}")
    print("  ⭐ UPSERT (bor yangila/yo'q qo'sh); JSON (PostgreSQL JSONB kabi)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. UPSERT — birinchi (INSERT) ===
  ball: 10

=== 2. UPSERT — takror (ON CONFLICT UPDATE) ===
  ball (10+5): 15

=== 3. JSON saqlash ===
  JSON ma'lumot saqlandi

=== 4. JSON o'qish (json_extract) ===
  ism: Ali, yosh: 25
  ⭐ UPSERT (bor yangila/yo'q qo'sh); JSON (PostgreSQL JSONB kabi)

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

Misol 4 — Amaliy: SQLite'dan PostgreSQL'ga

SQLite bilan yozilgan kod PostgreSQL'ga qanday o'tishini ko'rsatami: DB-API bir xil, faqat ulanish va ba'zi sintaksis farq. Bu — real migratsiya (SQLite → PostgreSQL) namunasi.

python

"""SQLite → PostgreSQL o'tish: DB-API bir xil kod; farqlar (connect, %s vs ?, SERIAL vs AUTOINCREMENT)."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    print("=== 1. Bir xil DB-API kod ===")
    cur.execute("CREATE TABLE kurs (id INTEGER PRIMARY KEY AUTOINCREMENT, nom TEXT, narx INTEGER)")
    cur.executemany("INSERT INTO kurs (nom, narx) VALUES (?, ?)", [("Python", 100), ("Go", 120)])
    conn.commit()
    print(f"  kurslar: {[r['nom'] for r in cur.execute('SELECT nom FROM kurs')]}")

    print("\n=== 2. So'rovlar bir xil (SQL standart) ===")
    natija = cur.execute("SELECT nom, narx FROM kurs WHERE narx > ? ORDER BY narx", (50,)).fetchall()
    print(f"  filtr: {[(r['nom'], r['narx']) for r in natija]}")

    print("\n=== 3. Farqlar (SQLite → PostgreSQL) ===")
    farqlar = [
        ("Ulanish", "sqlite3.connect('fayl.db')", "psycopg.connect('postgresql://...')"),
        ("Parametr", "? (savol belgisi)", "%s (foiz s)"),
        ("Avto id", "INTEGER PRIMARY KEY AUTOINCREMENT", "SERIAL PRIMARY KEY"),
        ("JSON", "TEXT + json_extract", "JSONB (indekslanadigan)"),
    ]
    for nom, sqlite_v, pg_v in farqlar:
        print(f"    {nom}: {sqlite_v}  →  {pg_v}")

    print("\n=== 4. Migratsiya oson ===")
    print("  DB-API bir xil — asosiy o'zgarish: connect + parametr belgisi")
    print("  ⭐ SQLite → PostgreSQL — kod deyarli bir xil (DB-API standart)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. Bir xil DB-API kod ===
  kurslar: ['Python', 'Go']

=== 2. So'rovlar bir xil (SQL standart) ===
  filtr: [('Python', 100), ('Go', 120)]

=== 3. Farqlar (SQLite → PostgreSQL) ===
    Ulanish: sqlite3.connect('fayl.db')  →  psycopg.connect('postgresql://...')
    Parametr: ? (savol belgisi)  →  %s (foiz s)
    Avto id: INTEGER PRIMARY KEY AUTOINCREMENT  →  SERIAL PRIMARY KEY
    JSON: TEXT + json_extract  →  JSONB (indekslanadigan)

=== 4. Migratsiya oson ===
  DB-API bir xil — asosiy o'zgarish: connect + parametr belgisi
  ⭐ SQLite → PostgreSQL — kod deyarli bir xil (DB-API standart)

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
"SQLite ishlab chiqarish uchun"	Prototip; PostgreSQL — ishlab chiqarish
"PostgreSQL murakkab"	O'rnatish kerak, lekin kuchli
"Kod butunlay o'zgaradi"	DB-API bir xil (kam farq)
"PostgreSQL faqat SQL"	JSONB (NoSQL kabi)
"Parametr `?`"	psycopg: `%s`
"Har so'rovda yangi ulanish"	Havza (pool)
"SQLite ko'p yozishga"	Bloklaydi (PostgreSQL parallel)
"PostgreSQL sekin"	Katta ma'lumotda tez

6. Keng tarqalgan xatolar va yechimlari

1. Ishlab chiqarishda SQLite (ko'p yozish)

python

# minglab parallel yozish — SQLite bloklaydi     # ⚠️
# PostgreSQL (parallel)                            # ✅

2. psycopg'da ? parametr

python

cur.execute("... WHERE id = ?", (id,))            # ❌ psycopg %s
cur.execute("... WHERE id = %s", (id,))           # ✅

3. Ulanishni yopmaslik

python

conn = psycopg.connect(...); ...                  # ⚠️ yopilmaydi
with psycopg.connect(...) as conn: ...            # ✅

4. Har so'rovda yangi ulanish (yuqori yuklama)

python

# har so'rov: psycopg.connect(...)                 # ⚠️ qimmat
# ulanish havzasi (pool)                            # ✅

5. Parolni kodda

python

psycopg.connect("...parol...")                    # ⚠️ git'da
psycopg.connect(os.environ["DATABASE_URL"])       # ✅ .env

6. SERIAL o'rniga AUTOINCREMENT (PostgreSQL)

sql

id INTEGER PRIMARY KEY AUTOINCREMENT              -- SQLite
id SERIAL PRIMARY KEY                             -- ✅ PostgreSQL

7. JSON'ni TEXT sifatida (PostgreSQL)

sql

sozlama TEXT                                      -- ⚠️ qidiruv sekin
sozlama JSONB                                     -- ✅ indekslanadigan

7. Integratsiya — bu bilim qayerda kerak bo'ladi

23.1–23.4 (o'tilgan): SQL, JOIN, indeks, tranzaksiya (PostgreSQL'da ham)
20.8-dars (o'tilgan): baza ulanish — psycopg
21.16-dars (o'tilgan): Django deploy — PostgreSQL
23.6/23.7-dars: SQLAlchemy — PostgreSQL bilan
28-qism: infratuzilma — PostgreSQL sozlash

8. Eng yaxshi amaliyotlar

Ishlab chiqarishda PostgreSQL (SQLite — prototip).
psycopg (v3) bilan ulaning.
DB-API standartiga tayaning (kod ko'chiriladi).
Ulanish havzasi (pool) — yuqori yuklamada.
Parolni .envda (git'da emas).
PostgreSQL turlarini ishlating (JSONB, ARRAY, SERIAL).
RETURNING/UPSERT bilan SQL'ni soddalashtiring.
with bilan ulanishni yoping.

9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

python

1.  # PostgreSQL nima?
2.  # SQLite vs PostgreSQL?
3.  # psycopg nima?
4.  # DB-API nima?
5.  # psycopg parametr belgisi?
6.  # SERIAL nima?
7.  # JSONB nima?
8.  # RETURNING nima?
9.  # UPSERT nima?
10. # ulanish havzasi nima?
11. # PostgreSQL qachon?
12. # kod ko'chiriladimi?

Javoblar

Kuchli ishlab chiqarish relyatsion baza (server)
SQLite — fayl/prototip; PostgreSQL — server/ishlab chiqarish
Python PostgreSQL drayveri
Python baza standart interfeysi (PEP 249)
%s
Avto-oshuvchi id
Indekslanadigan JSON
O'zgartirilgan satrni qaytarish
Bor yangila, yo'q qo'sh
Tayyor ulanishlar to'plami
Ishlab chiqarish (katta, ko'p ulanish)
Ha (DB-API bir xil, kam farq)

Vazifa 2: Xatolarni tuzating

python

1.  # ishlab chiqarishda SQLite (ko'p yozish)    # PostgreSQL

2.  cur.execute("... WHERE id = ?", (id,))       # psycopg %s

3.  conn = psycopg.connect(...); ...             # with (yopish)

4.  psycopg.connect("...parol...")               # .env

5.  id INTEGER PRIMARY KEY AUTOINCREMENT         # SERIAL (PG)

Javoblar

python

1.  # PostgreSQL (parallel yozish)

2.  cur.execute("... WHERE id = %s", (id,))

3.  with psycopg.connect(...) as conn: ...

4.  psycopg.connect(os.environ["DATABASE_URL"])

5.  id SERIAL PRIMARY KEY

Vazifa 3: DB-API kod

DB-API bilan (sqlite3):

connect, cursor, execute
Parametr, fetch
commit, close
psycopg farqlarini hujjatlash

Vazifa 4: RETURNING/UPSERT

Zamonaviy SQL:

INSERT ... RETURNING
UPSERT (ON CONFLICT)
UPDATE ... RETURNING
Har birini test

Vazifa 5: JSON ma'lumot

JSON bilan:

JSON saqlash
json_extract o'qish
PostgreSQL JSONB bilan taqqoslash
Test

Vazifa 6: SQLite → PostgreSQL

Migratsiya rejasi:

SQLite kod
PostgreSQL farqlari (connect, %s, SERIAL)
Kod ko'chirish
Farqlarni hujjatlash

Vazifa 7: O'ylash

Python DB-API (PEP 249) — barcha baza (sqlite3, psycopg, MySQL) uchun bir xil interfeys: connect, cursor, execute, fetch, commit. Bu tufayli SQLite'dan PostgreSQL'ga o'tish oson (kod deyarli bir xil). Bu 22.5 dagi WSGI/ASGI kabi standart interfeys. "Standart interfeys" (DB-API) bazani almashtiriladigan qilishning afzalligi nima, va nima uchun bu 22.5 dagi WSGI standartiga o'xshaydi?

Javob

Qisqa javob: DB-API — barcha baza bir xil interfeys (connect, cursor, execute, fetch). Bu tufayli ilova bazani almashtirib oladi (SQLite → PostgreSQL) — kod deyarli o'zgarmaydi (faqat connect va parametr belgisi). Afzalligi: moslashuvchanlik (baza almashtiriladi), o'rganish bir marta (bir interfeys, ko'p baza), portativlik (kod bazaga bog'liq emas). Bu 22.5 dagi WSGI kabi standart interfeys: WSGI freymvork↔server, DB-API ilova↔baza. Standart interfeys ikki tomonni ajratadi — biri ikkinchisiga bog'liq emas (interfeys orqali). Bu komponentlarni mustaqil va almashtiriladigan qiladi.

1. DB-API — bir xil interfeys

Baza	Interfeys
sqlite3	connect, cursor, execute
psycopg	connect, cursor, execute
MySQL	connect, cursor, execute

2. Almashtiriladiganlik

Ilova DB-API'ga tayansa, bazani almashtiradi (SQLite → PostgreSQL) — kod bir xil (faqat connect farq).

3. WSGI'ga o'xshashlik

Standart	Ajratadi
DB-API	Ilova ↔ baza
WSGI/ASGI	Freymvork ↔ server (22.5)
SQL	Ilova ↔ baza tili

Ikkalasi ham standart interfeys — ikki tomonni ajratadi (mustaqil, almashtiriladigan).

4. Standart interfeys kuchi

Standart komponentlarni mustaqil qiladi: baza ilovani bilmaydi, ilova bazani (interfeys orqali). Almashtirish, o'rganish, portativlik oson.

5. Muhandislik saboqlari

DB-API — bir xil interfeys (baza almashtiriladi)
Standart — komponentlarni ajratadi (WSGI kabi)
Almashtiriladiganlik, portativlik
Interfeysga tayaning (aniq bazaga emas)

6. Xulosa

DB-API — bir xil baza interfeysi
SQLite → PostgreSQL oson (kod bir xil)
WSGI kabi standart (ajratadi)
Standart interfeys — mustaqil komponentlar

Nimani mustahkamlaydi: 2.1–2.8-bo'limlar.

Xulosa

Bu darsda PostgreSQL bilan ishlashni o'rgandik.

Eng muhim uch fikr:

PostgreSQL — ishlab chiqarish bazasi. PostgreSQL — kuchli, ochiq kod server bazasi (SQLite fayl, PostgreSQL server): alohida jarayon, ko'p foydalanuvchi, tarmoq orqali ulanish. SQLite — bir fayl, sozlamasiz, kichik loyiha (prototip, mobil, test); PostgreSQL — server, ko'p yozish (parallel), katta ma'lumot, ishlab chiqarish. Boy xususiyatli: JSONB, ARRAY, to'liq matn qidiruv, kengaytmalar. Jiddiy loyiha (Django/FastAPI ilova) odatda PostgreSQL bilan.
psycopg va DB-API. Python'da PostgreSQL — psycopg (v3) drayveri bilan (psycopg.connect("postgresql://user:parol@host:port/baza")). DB-API (PEP 249) — barcha baza uchun bir xil interfeys: connect, cursor, execute, fetch, commit. Bu tufayli SQLite → PostgreSQL o'tish oson (kod deyarli bir xil, faqat connect va parametr belgisi: SQLite ?, psycopg %s). Ulanish havzasi (pool) — yuqori yuklamada (tayyor ulanishlar).
Kuchli SQL va standart. PostgreSQL boy turlar (SERIAL — avto-id, JSONB — indekslanadigan JSON, ARRAY, UUID) va kuchli SQL: RETURNING (o'zgartirilgan satrni darrov qaytarish), UPSERT (ON CONFLICT — bor yangila/yo'q qo'sh). PostgreSQL — ishlab chiqarishda standart tanlov (ishonchli ACID, kuchli, miqyosli). DB-API — WSGI 22.5-bob kabi standart interfeys: ilova va bazani ajratadi (baza almashtiriladigan, kod portativ).

Keyingi darsda SQLAlchemy Core ni o'rganamiz: Python'da SQL'ni obyekt sifatida yozish — Table, select(), va SQL'ning Python ifodalari (ORM'dan pastroq, xom SQL'dan yuqoriroq).

Ulashish:Telegram'da

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!

23.5-dars: PostgreSQL bilan ishlash

1. Kirish va motivatsiya

2. Nazariya — chuqur tushuntirish

2.1. PostgreSQL nima

2.2. SQLite vs PostgreSQL

2.3. psycopg bilan ulanish

2.4. DB-API (bir xil interfeys)

2.5. PostgreSQL turlari

2.6. RETURNING, UPSERT

2.7. Ulanish havzasi (pool)

2.8. PostgreSQL — ishlab chiqarish tanlovi

3. Tez ma'lumotnoma

4. Batafsil misollar

Misol 1 — DB-API (bir xil interfeys)

Misol 2 — RETURNING

Misol 3 — UPSERT va JSON

Misol 4 — Amaliy: SQLite'dan PostgreSQL'ga

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)

2.3. `psycopg` bilan ulanish

2.6. `RETURNING`, `UPSERT`

Misol 2 — `RETURNING`

Misol 3 — `UPSERT` va JSON