SQLite: қайта жазылған файлдың орнына тарих, жабыстырудың орнына `?`

Не үшін керек

Он бірінші сабақ шолуға дискіні берді, он екіншісі — CSV-дегі кестені. Екеуінде де файл қайтадан жазылатын: кешегі сандар жоғалатын, ал «тарихты» қолмен жалғап, бір жолдың екі рет түспеуін қадағалауға тура келер еді.

Дерекқор дәл сол үшін ойлап табылған. Оған жол қосады; ескілері қала береді. Жолдың кілті бар, әрі кілт бойынша жазбаның бұрын болғаны көрінеді. Ал таңдау — «қаңтардағы доллар күндер бойынша» — шартты цикл емес, сұраныс.

Ештеңе орнатудың қажеті жоқ: sqlite3 Python-мен бірге келеді, ал бүкіл дерекқор — бағдарламаның қасындағы бір файл.

Бірден тұтас

baza.py файлы. Іске қосу: ортадан python baza.py.

Міндеттісі — алғашқы үш блок: кесте, қайта іске қосу және сұраныс. Төртінші мен бесіншісі екі тұзақты, алтыншысы дерекқор жолын dataclass-пен байланыстырады.

"""21-сабақ: SQLite — тарих қайта жазылмайтындай етіп қайда салу керек.

Шолу осы уақытқа дейін деректі файлға салып, оны әр жолы қайта жазатын: кешегі
сандар жоғалатын. Дерекқор олай істемейді — оған жол қосады, ал ескілері қала
береді. Әрі ол нөл рет орнатылады: sqlite3 Python-мен бірге келеді.
"""

import sqlite3
from dataclasses import dataclass
from pathlib import Path

HERE = Path(__file__).parent
FILE = HERE / "svodka.db"
FILE.unlink(missing_ok=True)          # оқу файлын таза беттен бастаймыз


@dataclass
class Rate:
    """Тарихтың бір жолы: күн, валюта, бір бірлікке бағам."""

    day: str
    code: str
    value: float


print("== кесте және алғашқы жазба")
conn = sqlite3.connect(FILE)
conn.execute("""
    CREATE TABLE IF NOT EXISTS rates (
        day   TEXT NOT NULL,
        code  TEXT NOT NULL,
        value REAL NOT NULL,
        PRIMARY KEY (day, code)
    )
""")
first = [
    Rate("2026-01-15", "USD", 510.43),
    Rate("2026-01-15", "EUR", 594.86),
    Rate("2026-01-16", "USD", 511.02),
]
conn.executemany("INSERT INTO rates (day, code, value) VALUES (?, ?, ?)",
                 [(r.day, r.code, r.value) for r in first])
conn.commit()
print("дерекқордағы жол:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== екінші іске қосу тарихты бұзбайды")
again = [
    Rate("2026-01-15", "USD", 510.43),    # сол күн — бұрыннан бар
    Rate("2026-01-16", "EUR", 596.10),    # жаңа күн — қосылады
]
conn.executemany("""
    INSERT INTO rates (day, code, value) VALUES (?, ?, ?)
    ON CONFLICT (day, code) DO UPDATE SET value = excluded.value
""", [(r.day, r.code, r.value) for r in again])
conn.commit()
print("қайталаудан кейінгі жол:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== циклдің орнына сұраныс")
conn.row_factory = sqlite3.Row
for row in conn.execute("SELECT day, value FROM rates WHERE code = ? ORDER BY day", ("USD",)):
    print(f"{row['day']}: {row['value']:.2f}")
print("доллардың орташа бағамы:",
      round(conn.execute("SELECT avg(value) FROM rates WHERE code = ?", ("USD",)).fetchone()[0], 2))

print()
print("== мәнді неге сұрақ белгісімен қояды")
sneaky = "USD' OR '1'='1"
glued = conn.execute(f"SELECT count(*) FROM rates WHERE code = '{sneaky}'").fetchone()[0]
safe = conn.execute("SELECT count(*) FROM rates WHERE code = ?", (sneaky,)).fetchone()[0]
print("жабыстырумен табылды:", glued, "— бүкіл кесте")
print("? арқылы табылды:      ", safe, "— мұндай валюта жоқ")

print()
print("== commit-сіз ештеңе сақталмады")
conn.execute("INSERT INTO rates (day, code, value) VALUES (?, ?, ?)", ("2026-01-17", "USD", 512.00))
print("осы қосылымда:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()                           # commit демей жаптық

conn = sqlite3.connect(FILE)
conn.row_factory = sqlite3.Row
print("қайта ашқаннан кейін:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])

print()
print("== дерекқордың жолы жазбаға айналады")
row = conn.execute("SELECT day, code, value FROM rates ORDER BY day, code LIMIT 1").fetchone()
rate = Rate(row["day"], row["code"], row["value"])
print(rate)
print("бұл", type(rate).__name__, "— онымен кәдімгі объект сияқты жұмыс істейді:", rate.code, rate.value)

conn.close()
FILE.unlink()

Шығатыны:

== кесте және алғашқы жазба
дерекқордағы жол: 3

== екінші іске қосу тарихты бұзбайды
қайталаудан кейінгі жол: 4

== циклдің орнына сұраныс
2026-01-15: 510.43
2026-01-16: 511.02
доллардың орташа бағамы: 510.73

== мәнді неге сұрақ белгісімен қояды
жабыстырумен табылды: 4 — бүкіл кесте
? арқылы табылды:       0 — мұндай валюта жоқ

== commit-сіз ештеңе сақталмады
осы қосылымда: 5
қайта ашқаннан кейін: 4

== дерекқордың жолы жазбаға айналады
Rate(day='2026-01-15', code='EUR', value=594.86)
бұл Rate — онымен кәдімгі объект сияқты жұмыс істейді: EUR 594.86

Талдау

Кесте — не сақтайтыныңыз туралы келісім

CREATE TABLE IF NOT EXISTS rates (
    day   TEXT NOT NULL,
    code  TEXT NOT NULL,
    value REAL NOT NULL,
    PRIMARY KEY (day, code)
)

Үш баған және бір шешім: кілт — «күн мен валюта» қосағы. Ол бір күні бір валютаның дәл бір бағамы болатынын айтады, әрі дерекқор оны өзі қадағалайды.

IF NOT EXISTS іске қосуды қайталанатын етеді: бағдарламаны күн сайын жүргізуге болады, кесте бір рет жасалады.

Күн ISO пішімінде мәтін болып жатыр — 2026-01-15. SQLite-та күнге арналған бөлек тип жоқ, әрі бұл — он төртінші сабақтағы сол жағдай: ISO жол ретінде күндер сияқты сұрыпталады, сондықтан ORDER BY day дұрыс жұмыс істейді.

Елестетіп көріңіз. Қойма кітабы. Оған жолдар қосып жазады, бетті қайта көшірмейді; әрі онда бір тауар бойынша бір күнді екі рет жазуға болмайды.

Екінші іске қосу тарихты бұзбайды

дерекқордағы жол: 3
қайталаудан кейінгі жол: 4

Екінші ретте бағдарлама екі жазба әкелді: бірі бұрыннан таныс, бірі жаңа. Дерекқорда бес емес, төрт жол болды — таныс жазба орнында жаңарды:

INSERT INTO rates (day, code, value) VALUES (?, ?, ?)
ON CONFLICT (day, code) DO UPDATE SET value = excluded.value

Мұны upsert дейді: қою, ал мұндай кілт бұрыннан болса — жаңарту. Мұндағы excluded — сіз әкелген жол; дерекқор ескі мән мен жаңасын осылай ажыратады.

Кілтсіз әрі ON CONFLICT-сіз қайта іске қосу тарихты екі есе қылар еді, әрі бұл кезде ешқандай қате болмас еді — жай ғана қаңтардың орташасы екі еселенген дерекпен саналар еді. Курста үнсіз қате дауыстап айтылғаннан қымбат болатын екінші жағдай.

Циклдің орнына сұраныс

conn.execute("SELECT day, value FROM rates WHERE code = ? ORDER BY day", ("USD",))

Бұрын «тек доллар, күннің өсуі бойынша» деген таңдау if бар цикл мен сұрыптау болатын. Енді бұл — сұраныстар тілінде жазылған бір жол, әрі оны дерекқор істейді: ол соған жазылған.

conn.row_factory = sqlite3.Row кортеждерді аттары бар жолдарға ауыстырады: row[0] емес, row["day"]. Бұл — он екінші сабақтағы csv.reader мен DictReader-дің айырмасы, әрі себебі де сол: сұраныс өзгерсе, баған нөмірі үнсіз жылжиды.

Санауды да дерекқор біледі: avg, count, min, max, sum. Толығырақ — келесі сабақта, ол түгелдей сұраныстар туралы.

? — ыңғайлылық үшін емес

жабыстырумен табылды: 4 — бүкіл кесте
? арқылы табылды:      0 — мұндай валюта жоқ

Міне, бір ережені өмір бойы есте сақтауға тұрарлық өлшеу. Сұраныс мәтініне жабыстырылған USD' OR '1'='1 деген мән тырнақшаны жауып, әрқашан ақиқат шартты қосып жазды — әрі сұраныс бір валютаның орнына бүкіл кестені қайтарды.

Мұны SQL-инъекция дейді, әрі онымен экрандаумен күреспейді: мәнді жай ғана жабыстырмайды. ? — дерекқор мәнді сұранысты талдап болғаннан кейін қоятын орын; ол сұраныс мәтініне зиян келтіре алмайды, өйткені сұраныс ол кезде дайын.

Ереже қысқа: сұраныста мән болмайды, тек сұрақ белгілері болады. Кесте мен бағанның атын олай да қоюға болмайды — оларды кірістіруден емес, өз тізімінен алады.

commit — «сақтау» дегеннің өзі

осы қосылымда: 5
қайта ашқаннан кейін: 4

Жол қойылған жерде көрініп тұрды да, файлды қайта ашқанда жоғалды. Өйткені INSERT транзакция ашты, ал commit()-сіз close() оны кері қайтарды.

Транзакция — «бәрі не ештеңе»: бірнеше өзгеріс не файлға бірге түседі, не мүлде түспейді. Шолу үшін бұл — он бірінші сабақтағы файлда болмаған қорғаныс: бағдарламаны ортасынан үзуге болады, дерекқор бүтін қалады.

with sqlite3.connect(...) as conn commit жасайтынын, бірақ қосылымды жаппайтынын білген де пайдалы — файлдарға арналған with-тен айырмашылығы осы. Файлды анық жабады.

REAL — ал төртінші сабақтағы Decimal қайда кетті

Төртінші сабақ тіке айтты: ақшаны float-пен санамайды, өйткені 0.1 + 0.2 0.3-ке тең емес. Ал мұнда бағам REAL бағанында, яғни сол float-та жатыр. Қайшылық жоқ, бірақ шекараны атау керек, әйтпесе ереже жойылғандай көрінеді.

Decimal туралы ереже — біреу біреуге қарыз соманы айтады: чектегі баға, шоттағы қалдық, есептелген салық. Онда бір жүздіктегі қате — ақшадағы қате, әрі ол мың жолды қосқанда жинақталады.

Валюта бағамы, инфляция, температура — бұл өлшемдер. Олардың дәлдігі кірісте-ақ өз шамасында (банк екі таңба береді), оларды сирек қосады, ал орташалары бәрібір жуық. Мұндай қатар үшін float адал.

Ал дерекқорға дәл ақша жататын болса, SQLite-та Decimal типі жоқ, әрі шешімді алдын ала қабылдайды — екеуінің бірін:

• тиынның бүтін саны (INTEGER): 1 234,56 ₸ 123456 болып сақталады, әрі бүкіл арифметика бүтінмен жүреді;
• мәтін (TEXT): Decimal жол болып қойылады да, Decimal(row["value"]) арқылы алынады.

Екеуі де — келісім, әрі оны кестенің қасына жазып қояды. Үнсіз «float-қа салайық, кейін көреміз» дегені — жарты жылдан кейін есеп бір тиынға сәйкес келмей, себебін ешкім есіне түсіре алмайтын сол жағдай.

dataclass — аттары бар жазба

@dataclass
class Rate:
    day: str
    code: str
    value: float

@dataclass рутинадан құтқарады: __init__-ті де, салыстыруды да, басып шығаруды да өзі жазады. Дерекқордан жол келеді, жолдан Rate жиналады, әрі әрі қарай бағдарламада кортеж емес, өрістері бар объект жүреді — екінші орында не тұрғанын есте ұстаудың қажеті жоқ.

Ол өзін өзі басып та шығарады — Rate(day='2026-01-15', code='EUR', value=594.86) — әрі бұл әшекей емес: жөндеу кезінде айнымалыда не жатқаны көрініп тұрады.

Әрі class сөзі туралы адал айтқанда. Объектілік модель — әдістері бар кластар, мұрагерлік, оның артындағының бәрі — бұл курсқа кірмейді: шолуға ол керек емес, әрі курс тілде бұдан басқа ештеңе жоқ сияқты көрсетудің орнына мұны тіке айтады.

Бірақ class сізге үш рет кездесті, әрі әр жолы бір ғана қарапайым рөлде — атау ретінде. Оныншы сабақта class BadRow(ValueError) өз қатеңізге ат берді. Жиырмасыншыда класспен оқу сервері сипатталған — оны сіз жазған жоқсыз, оқыдыңыз. Мұнда @dataclass жазбаға ат береді: Rate — бұл «күн, валюта, бағам», ал Rate("2026-01-15", "USD", 510.43) — сондай бір жиынтық, дана. Өрісі сіз жариялағанша, әдістерін жазған жоқсыз әрі бүгін жазбайсыз да.

dataclass-ты саналы пайдалану үшін осы жеткілікті. Кластар туралы қалғанының бәрі — бөлек әңгіме, әрі ол мұнда емес.

Сабақ картасы

Өз сөзіңізбен айтыңыз

Қарамай, дауыстап немесе қағазға жауап беріңіз. Жауаптары — сабақтың соңында.

1. ON CONFLICT (day, code) DO UPDATE не істейді, әрі онсыз не болар еді?
2. Мәндерді неге сұраныс мәтініне жабыстырмай, ? арқылы қояды?
3. Қосылым commit-сіз жабылса, жазбаға не болады?

Жаттығу

Тапсырмаға дейінгі үш қысқа қадам: болжау, толықтыру, жөндеу. Жауаптары — сабақтың соңында, бірақ алдымен өзіңіз жауап беріңіз.

1. Болжаңыз. Бұл бағдарлама не басып шығарады?

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT PRIMARY KEY, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
conn.execute("""
    INSERT INTO t VALUES (?, ?)
    ON CONFLICT (code) DO UPDATE SET value = excluded.value
""", ("USD", 511.0))
print(conn.execute("SELECT count(*), max(value) FROM t").fetchone())

2. Бос орынды толтырыңыз. ... орнына сұранысқа мән қойылатын нәрсені жазыңыз.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
code = "USD"
print(conn.execute("SELECT value FROM t WHERE code = ...", (code,)).fetchall())

3. Жөндеңіз. Бағдарлама бағамды жазады, бірақ файлды келесі ашқанда ол жерде жоқ. Түсіп қалған жолды табыңыз.

import sqlite3
from pathlib import Path

FILE = Path(__file__).parent / "kurs.db"
FILE.unlink(missing_ok=True)

conn = sqlite3.connect(FILE)
conn.execute("CREATE TABLE rates (code TEXT, value REAL)")
conn.execute("INSERT INTO rates VALUES (?, ?)", ("USD", 510.43))
conn.close()

conn = sqlite3.connect(FILE)
print("файлдағы жол:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()
FILE.unlink()

Тапсырма

Міндетті. Қайта іске қосу бұзбайтын бағамдар тарихын жинаңыз. Берілгені:

rows = [
    Rate("2026-01-15", "USD", 510.43),
    Rate("2026-01-15", "EUR", 594.86),
    Rate("2026-01-16", "USD", 511.02),
    Rate("2026-01-16", "EUR", 596.10),
]

dataclass Rate (күн, валюта, бағам) және күн мен валютадан құралған кілті бар rates кестесін жасаңыз. save(records) функциясын жазыңыз: жаңа жазбалар қосылады, таныстары жаңарады. Оны қатарынан екі рет шақырып, әр шақырудан кейінгі жол санын басып шығарыңыз. Содан кейін долларды күндер бойынша таңдап, орташа бағамын сұраныспен санаңыз да, күні бойынша соңғы жазбаны алып, оны қайта Rate-ке айналдырыңыз.

Күтілетін шығу:

бірінші іске қосудан кейін: 4
екінші іске қосудан кейін: 4
доллар күндер бойынша:
  2026-01-15: 510.43
  2026-01-16: 511.02
доллардың орташа бағамы: 510.73
соңғы жазба: Rate(day='2026-01-16', code='EUR', value=596.1)

Дайын болғаны: шығу жол-жолмен сәйкес келеді; екінші іске қосу бірде-бір жол қоспады; бірде-бір мән сұраныс мәтініне жабыстырылмаған; орташаны Python емес, дерекқор санады; жұмыстан кейін дерекқор файлы жиналған.

Өз деректеріңізде. Өткен сабақтардың кез келгенінен өз қатарыңызды алыңыз — шығындар, бағалар, өлшемдер — да, оны күннен құралған кілті бар дерекқорға салыңыз. Бағдарламаны үш рет іске қосып, жол саны күн санымен бірдей екеніне көз жеткізіңіз.

Қалауыңызша.

• «Қашан алынды» деген баған қосып, оны қою кезінде толтырыңыз.
• Сол кілті бар жолды кәдімгі INSERT-пен қойып көріңіз де, қатені оқыңыз.
• Дерекқор файлын кез келген SQLite көрсеткішінде ашып, кестеңізді көзбен тауып көріңіз.

Жобада бұл қайда тұрады

Шолуда жад пайда болады. Әр күннің бағамы дерекқорға жатады, қайта іске қосу ештеңені екі еселемейді, ал «доллар ай ішінде қалай өзгерді» деген сұрақ барлық файлды қатарынан оқуды талап етуін қояды.

Қарыздар. Біз SELECT-ті ең қарапайым түрінде жаздық: келесі сабақ түгелдей сұраныстар туралы — топтау, екі кестені біріктіру және бірде-бір цикл жазбай тілім алу. Әрі дерекқорымыз әзірге бәріне біреу; кесте бірнешеу болғанда нені немен байланыстыру керегін шешуге тура келеді.

Жауаптар

(1, 511.0)

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (code TEXT, value REAL)")
conn.execute("INSERT INTO t VALUES (?, ?)", ("USD", 510.43))
code = "USD"
print(conn.execute("SELECT value FROM t WHERE code = ?", (code,)).fetchall())

[(510.43,)]

import sqlite3
from pathlib import Path

FILE = Path(__file__).parent / "kurs.db"
FILE.unlink(missing_ok=True)

conn = sqlite3.connect(FILE)
conn.execute("CREATE TABLE rates (code TEXT, value REAL)")
conn.execute("INSERT INTO rates VALUES (?, ?)", ("USD", 510.43))
conn.commit()
conn.close()

conn = sqlite3.connect(FILE)
print("файлдағы жол:", conn.execute("SELECT count(*) FROM rates").fetchone()[0])
conn.close()
FILE.unlink()

файлдағы жол: 1

Дереккөздер

• Python: sqlite3 модулі
• Python: dataclasses
• SQLite: UPSERT
• SQLite: дерек типтері