Python-da verilənlər bazası: sqlite3

Standart `sqlite3` modulu ilə işləmək: qoşulma və kursor, `CREATE`/`INSERT`/`SELECT`, SQL inyeksiyasından qoruyan parametrlər, `executemany`, `row_factory`, tranzaksiyalar və `with`, kiçik kitabxana tətbiqi və SQLAlchemy-yə ilk baxış.

  • İrəli
  • 22 dəq
  • 12 sual
  • 66 dərsdən 37-cisi

Bu dərsdə öyrənəcəksən

  • Verilənlər bazasına qoşulmaq, cədvəl yaratmaq, sətir əlavə etmək və sorğu nəticələrini oxumaq
  • Qiymətləri yalnız parametrlərlə ötürmək və SQL inyeksiyasının necə işlədiyini izah etmək
  • executemany, fetchone/fetchall və sqlite3.Row ilə səmərəli işləmək
  • Əlaqəli dəyişiklikləri with conn: tranzaksiyasında birləşdirmək

Məktəb kitabxanasının siyahısı JSON faylındadır. Kitab axtarmaq üçün bütün faylı oxuyursan, bir kitabı dəyişmək üçün hamısını yenidən yazırsan, yazma yarıda kəsilsə, fayl korlanır. Verilənlər bazası bu problemləri həll edir, SQLite isə ən sadəsidir: server tələb etmir, bütün baza bir fayldır, Python-a isə sqlite3 modulu kimi daxildir. SQLite telefonlarda, brauzerlərdə və minlərlə proqramda işləyir. SQL kursunda sorğuları əllə yazmışdın, indi onları Python proqramı icra edəcək.

Qoşulma və kursorTərif

Qoşulma (sqlite3.connect(...)) bir verilənlər bazası ilə açıq əlaqədir: tranzaksiyaları idarə edir (commit, rollback) və bağlanmalıdır. Kursor (conn.cursor()) sorğunu icra edir və nəticənin sətirlərini bir-bir verir. conn.execute(...) kursoru özü yaradıb qaytaran qısa yoldur.

Qoşulma və ilk cədvəl

sqlite3.connect('library.db') faylı açır (yoxdursa, yaradır), sqlite3.connect(':memory:') isə yaddaşda müvəqqəti baza yaradır — qoşulma bağlananda yox olur və nümunələr üçün idealdır. SQL mətnini execute icra edir. Dəyişikliklər conn.commit() çağırılana qədər təsdiqlənmir. INTEGER PRIMARY KEY sütunu yeni sətrin nömrəsini özü verir, cur.lastrowid isə onu göstərir.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''
    CREATE TABLE books (
        id     INTEGER PRIMARY KEY,
        title  TEXT NOT NULL,
        author TEXT NOT NULL,
        year   INTEGER,
        copies INTEGER DEFAULT 1
    )
''')
cur.execute("INSERT INTO books (title, author, year) VALUES ('Ali and Nino', 'Kurban Said', 1937)")
print(cur.lastrowid, cur.rowcount)
conn.commit()

cur.execute('SELECT id, title, year, copies FROM books')
print(cur.fetchall())
conn.close()
Gözlənilən nəticə
1 1
[(1, 'Ali and Nino', 1937, 1)]
lastrowid 1-dir (yeni kitabın id-si), rowcount isə dəyişən sətirlərin sayıdır. copies sütunu verilmədiyi üçün DEFAULT 1 işlədi. Nəticə kortejlər siyahısıdır.
  1. Qoşul

    conn = sqlite3.connect('library.db') — fayl yoxdursa, yaradılır.

  2. Parametrlərlə icra et

    conn.execute('... WHERE id = ?', (book_id,)) — qiymətlər yalnız parametr kimi.

  3. Oxu

    fetchone(), fetchall() və ya kursor üzərindən dövr.

  4. Təsdiqlə

    conn.commit() və ya with conn: bloku.

  5. Bağla

    İş bitəndə conn.close().

PythonSQLite
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB
Tarixləri ISO mətni kimi saxla ('2026-10-03'): onlar düzgün sıralanır və SQL-in tarix funksiyaları ilə işləyir.

Davamını oxumaq üçün pulsuz qeydiyyatdan keç

Dərsin qalan bölmələri, test və qeydlər Educora hesabı ilə açılır. Qeydiyyat bir dəqiqə çəkir; irəliləyişin bütün cihazlarında saxlanılır.

Sətir birləşdirmə yox, parametrlər

İstifadəçinin daxil etdiyi qiyməti SQL mətninə f-sətirlə yapışdırmaq ən təhlükəli səhvdir. Qiymətdə apostrof varsa, o, sətri bağlayır və qalan hissə SQL kodu kimi oxunur — buna SQL inyeksiyası deyilir. Parametrli sorğuda isə mətndə yalnız ? yer tutucuları olur, qiymətlər ayrıca ötürülür və heç vaxt kod kimi oxunmur.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE users (name TEXT, password TEXT)')
conn.executemany('INSERT INTO users VALUES (?, ?)', [('aysel', 'x7!kL'), ('murad', 'p@ss9')])


def login_unsafe(name, password):
    sql = f"SELECT name FROM users WHERE name = '{name}' AND password = '{password}'"
    return conn.execute(sql).fetchall()


def login_safe(name, password):
    sql = 'SELECT name FROM users WHERE name = ? AND password = ?'
    return conn.execute(sql, (name, password)).fetchall()


attack = "' OR '1'='1"
print(login_unsafe('aysel', 'wrong'))
print(login_unsafe('aysel', attack))
print(login_safe('aysel', attack))
print(login_safe('aysel', 'x7!kL'))
Gözlənilən nəticə
[]
[('aysel',), ('murad',)]
[]
[('aysel',)]
Parollar burada nümunəni qısa saxlamaq üçün açıq saxlanılır; real tətbiqlər yalnız parolların heşini saxlayır. Hücum sətri ilə təhlükəsiz olmayan sorğu bütün istifadəçiləri qaytardı.

Nümunə: inyeksiya addım-addım

Məsələ

password = "' OR '1'='1" olanda login_unsafe hansı SQL-i icra edir və niyə hamı daxil olur?

Həllini göstərHəllini gizlət

Həll

1) Yerinə qoyandan sonra: WHERE name = 'aysel' AND password = '' OR '1'='1'.
2) AND OR-dan əvvəl hesablanır: (name = 'aysel' AND password = '') OR '1'='1'.
3) '1'='1' hər sətir üçün doğrudur, deməli şərt bütün sətirlər üçün doğrudur və sorğu bütün istifadəçiləri qaytarır.
4) ? ilə sətir ' OR '1'='1 parol kimi müqayisə olunur, belə parol yoxdur — nəticə [].

İki yer tutucu üslubu var: ? qiymətləri kortejdən sıra ilə götürür, :name isə lüğətdən ada görə. Diqqət: bir qiymətli kortej vergüllə yazılır — (author,). Vergülsüz (author) sadəcə mötərizədəki sətirdir və Python onun hər hərfini ayrıca parametr sayır. Yer tutucular yalnız qiymətlər üçündür; cədvəl və sütun adlarını parametrlə ötürmək olmaz.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (title TEXT, author TEXT, year INTEGER)')
conn.executemany('INSERT INTO books VALUES (:title, :author, :year)', [
    {'title': 'Animal Farm', 'author': 'George Orwell', 'year': 1945},
    {'title': 'Nineteen Eighty-Four', 'author': 'George Orwell', 'year': 1949},
    {'title': 'Robinson Crusoe', 'author': 'Daniel Defoe', 'year': 1719},
])
author = 'George Orwell'
print(conn.execute('SELECT title FROM books WHERE author = ? ORDER BY year', (author,)).fetchall())
print(conn.execute('SELECT title FROM books WHERE year > :after', {'after': 1900}).fetchall())

try:
    conn.execute('SELECT title FROM books WHERE author = ?', (author))
except sqlite3.ProgrammingError as e:
    print('ProgrammingError:', e)
Gözlənilən nəticə
[('Animal Farm',), ('Nineteen Eighty-Four',)]
[('Animal Farm',), ('Nineteen Eighty-Four',)]
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 13 supplied.
'George Orwell' 13 simvoldur — vergülsüz kortej 13 parametr kimi ötürüldü. executemany lüğətlər siyahısı ilə :title, :author, :year adlarını doldurdu.

Nəticələri oxumaq: fetch metodları və row_factory

Kursor iterasiya oluna bilər: for row in conn.execute(...) sətirləri bir-bir verir və böyük nəticələrdə yaddaşa qənaət edir. fetchone() növbəti sətri və ya sətir qalmayanda None, fetchall() bütün qalan sətirləri siyahı kimi qaytarır. executemany eyni əmri siyahının hər elementi üçün təkrarlayır — minlərlə sətir əlavə etməyin ən sürətli yolu. Susmaya görə sətir kortejdir; conn.row_factory = sqlite3.Row təyin etsən, sütunlara adla müraciət etmək olar: row['title'], dict(row).

Python
import sqlite3

books = [
    ('Ali and Nino', 'Kurban Said', 1937, 3),
    ('The Little Prince', 'Antoine de Saint-Exupéry', 1943, 5),
    ('Robinson Crusoe', 'Daniel Defoe', 1719, 2),
    ('Animal Farm', 'George Orwell', 1945, 4),
    ('Nineteen Eighty-Four', 'George Orwell', 1949, 0),
]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER, copies INTEGER)')
cur = conn.executemany('INSERT INTO books (title, author, year, copies) VALUES (?, ?, ?, ?)', books)
print('inserted:', cur.rowcount)

conn.row_factory = sqlite3.Row
for row in conn.execute('SELECT id, title, year FROM books WHERE year < ? ORDER BY year', (1940,)):
    print(row['id'], row['title'], row['year'])

row = conn.execute('''SELECT author, COUNT(*) AS n, SUM(copies) AS total FROM books
                      GROUP BY author ORDER BY n DESC, author LIMIT 1''').fetchone()
print(dict(row), row.keys())
print(conn.execute('SELECT title FROM books WHERE copies = 99').fetchone())
Gözlənilən nəticə
inserted: 5
3 Robinson Crusoe 1719
1 Ali and Nino 1937
{'author': 'George Orwell', 'n': 2, 'total': 4} ['author', 'n', 'total']
None
row_factory qoşulmaya təyin olunur və sonrakı sorğulara təsir edir. Uyğun sətir olmayanda fetchone() None qaytarır — bunu həmişə yoxla.
Metod / atributNə edir
conn.execute(sql, params)bir əmri icra edir, kursor qaytarır
conn.executemany(sql, seq)əmri ardıcıllığın hər elementi üçün təkrarlayır
conn.executescript(text); ilə ayrılmış bir neçə əmri icra edir (parametrsiz)
fetchone(), fetchall(), fetchmany(n)bir sətir / hamısı / n sətir
cur.lastrowid, cur.rowcountson əlavə olunan sətrin id-si / dəyişən sətirlərin sayı
conn.commit(), conn.rollback(), conn.close()təsdiqlə / ləğv et / bağla

Tranzaksiyalar və with

TranzaksiyaTərif

Ya hamısı, ya heç biri qaydası ilə icra olunan dəyişikliklər qrupu. COMMIT hamısını birdən təsdiqləyir, ROLLBACK hamısını ləğv edir, sanki heç nə olmayıb. Pul köçürmə klassik nümunədir: bir hesabdan çıxmaq və digərinə əlavə etmək birlikdə baş verməlidir.

Susmaya görə sqlite3 modulu INSERT, UPDATE və DELETE-dən əvvəl tranzaksiyanı özü açır, onu isə sən commit() ilə bağlamalısan. Ən rahat yol with conn: blokudur: blok xətasız bitərsə, COMMIT, istisna baş verərsə, ROLLBACK olur. Amma qoşulma açıq qalır — onu close() ilə və ya contextlib.closing ilə bağla (bax: «Kontekst menecerləri və with operatoru»). Ətraflı nəzəriyyə SQL kursunun «İndekslər, görünüşlər və tranzaksiyalar» dərsindədir.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('''CREATE TABLE accounts (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL CONSTRAINT non_negative CHECK (balance >= 0))''')
with conn:
    conn.executemany('INSERT INTO accounts VALUES (?, ?)', [('Aysel', 100), ('Murad', 50)])


def transfer(src, dst, amount):
    try:
        with conn:    # one transaction: COMMIT on success, ROLLBACK on an error
            conn.execute('UPDATE accounts SET balance = balance + ? WHERE name = ?', (amount, dst))
            conn.execute('UPDATE accounts SET balance = balance - ? WHERE name = ?', (amount, src))
        print(f'{src} -> {dst}: {amount} OK')
    except sqlite3.IntegrityError as e:
        print(f'{src} -> {dst}: {amount} cancelled ({e})')


transfer('Aysel', 'Murad', 30)
transfer('Murad', 'Aysel', 500)
print(conn.execute('SELECT * FROM accounts ORDER BY name').fetchall())
conn.close()
Gözlənilən nəticə
Aysel -> Murad: 30 OK
Murad -> Aysel: 500 cancelled (CHECK constraint failed: non_negative)
[('Aysel', 70), ('Murad', 80)]
İkinci köçürmədə Aysel 500 aldı, sonra Muradın balansı mənfi olacaqdı və CHECK məhdudiyyəti IntegrityError verdi. with ROLLBACK etdi, ona görə Ayselin artıq yazılmış 500-ü də ləğv olundu.

Mini tətbiq: kitabxana

Bütün hissələri birləşdirək. connect faylı açır və cədvəlləri CREATE TABLE IF NOT EXISTS ilə hazırlayır, borrow isə kitabın qalığını yoxlayır, onu azaldır və borcu bir tranzaksiyada yazır. Sonda qoşulma bağlanır və yenidən açılır: məlumat faylda qalıb.

Python
import sqlite3


def connect(path):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT UNIQUE NOT NULL,
                                          copies INTEGER NOT NULL CHECK (copies >= 0));
        CREATE TABLE IF NOT EXISTS loans (book_id INTEGER REFERENCES books (id), reader TEXT NOT NULL);
    ''')
    return conn


def borrow(conn, title, reader):
    with conn:
        book = conn.execute('SELECT id, copies FROM books WHERE title = ?', (title,)).fetchone()
        if book is None or book['copies'] == 0:
            return f'{reader}: {title!r} is not available'
        conn.execute('UPDATE books SET copies = copies - 1 WHERE id = ?', (book['id'],))
        conn.execute('INSERT INTO loans VALUES (?, ?)', (book['id'], reader))
    return f'{reader} borrowed {title!r}'


conn = connect('library.db')
with conn:
    conn.executemany('INSERT INTO books (title, copies) VALUES (?, ?)',
                     [('Ali and Nino', 1), ('The Little Prince', 2)])
for reader, title in [('Aysel', 'Ali and Nino'), ('Murad', 'Ali and Nino'), ('Leyla', 'Dune')]:
    print(borrow(conn, title, reader))
conn.close()

conn = connect('library.db')     # a new connection: the data is still in the file
sql = '''SELECT b.title, b.copies, COUNT(l.reader) AS loans
         FROM books b LEFT JOIN loans l ON l.book_id = b.id GROUP BY b.id ORDER BY b.title'''
for row in conn.execute(sql):
    print(dict(row))
conn.close()
Gözlənilən nəticə
Aysel borrowed 'Ali and Nino'
Murad: 'Ali and Nino' is not available
Leyla: 'Dune' is not available
{'title': 'Ali and Nino', 'copies': 0, 'loans': 1}
{'title': 'The Little Prince', 'copies': 2, 'loans': 0}
LEFT JOIN borcu olmayan kitabları da göstərir (loans 0). Brauzerdə library.db hər işə salmada boş qovluqda yenidən yaranır.

Layihə böyüdükcə SQL sətirləri, sütun adlarının əl ilə köçürülməsi və sətirlərin obyektlərə çevrilməsi yorucu olur. SQLAlchemy eyni işi Python sinifləri ilə görür və eyni kod SQLite-dan PostgreSQL-ə keçəndə də işləyir. Müqayisə et (ətraflı «SQLAlchemy: ORM ilə verilənlər bazası» dərsində):

sqlite3

rows = conn.execute(
    'SELECT title, year FROM books WHERE author = ? ORDER BY year',
    ('George Orwell',),
).fetchall()

SQLAlchemy

stmt = (select(Book.title, Book.year)
        .where(Book.author == 'George Orwell')
        .order_by(Book.year))
rows = session.execute(stmt).all()
Hər ikisi parametrli SQL göndərir; ORM-də Book cədvəli təsvir edən sinifdir, session isə qoşulma və tranzaksiyanı idarə edir.

Tapşırıq

products siyahısını bir executemany çağırışı ilə cədvələ əlavə et. Sonra qiyməti 10-dan az olan və anbarda olan (stock > 0) məhsulların adını və qiymətini ən ucuzdan başlayaraq seç və hər sətri name: price şəklində çap et. Hədd qiymətini (10) parametr kimi ötür.

Tapşırıq · Python
import sqlite3

products = [('pen', 1.5, 120), ('notebook', 3.2, 0), ('backpack', 45.0, 7),
            ('ruler', 0.8, 60), ('calculator', 18.9, 12)]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE products (name TEXT, price REAL, stock INTEGER)')
# 1) insert all products with one executemany call
# 2) select name and price of the products cheaper than 10 that are in stock, cheapest first
# 3) print every row as "name: price"
Gözlənilən nəticə
ruler: 0.8
pen: 1.5

Tapşırıq

sell(name, qty) bir tranzaksiyada sales cədvəlinə satış sətri əlavə etsin və stock-da qalığı azaltsın. Qalıq 0-dan aşağı düşəcəksə (CHECK xətası), heç nə dəyişməməli və funksiya False qaytarmalıdır; uğurda — True.

Tapşırıq · Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE stock (name TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0))')
conn.execute('CREATE TABLE sales (name TEXT, qty INTEGER)')
conn.executemany('INSERT INTO stock VALUES (?, ?)', [('pen', 5), ('ruler', 2)])
conn.commit()


def sell(name, qty):
    # in ONE transaction: add a row to sales and reduce the stock;
    # if the stock would drop below 0, nothing may change and the function returns False
    return False


print(sell('pen', 3), sell('ruler', 5), sell('pen', 2))
print(conn.execute('SELECT * FROM stock ORDER BY name').fetchall())
print(conn.execute('SELECT * FROM sales').fetchall())
Gözlənilən nəticə
True False True
[('pen', 0), ('ruler', 2)]
[('pen', 3), ('pen', 2)]

Yadda saxla

  • sqlite3.connect(path) faylı, ':memory:' isə müvəqqəti bazanı açır; conn.execute kursor qaytarır.
  • Qiymətləri həmişə ? və ya :name parametrləri ilə ötür; bir qiymətli kortej: (x,).
  • fetchone() (və ya None), fetchall(), kursorun özü üzərindən dövr; sqlite3.Row sütunlara adla müraciət verir.
  • executemany çox sətri bir çağırışla əlavə edir.
  • with conn: uğurda COMMIT, xətada ROLLBACK edir, amma qoşulmanı bağlamır; commit()-siz close() dəyişiklikləri itirir.
Dərsdə səhv və ya qeyri-dəqiqlik gördün?

Özünü yoxla

12 sual. Hər düzgün cavab XP qazandırır.

Sual 1 / 12

Sual 1 / 12. sqlite3.connect(':memory:') nə yaradır?