Учебное пособие по программированию на Python для базы данных SQLite.

Оглавление

• Установка SQLite
• Создание базы данных SQLite
• Подключение к базе данных
• Методы SQLite
  • execute
  • executemany
  • executescript
  • lastrowid
  • fetchall
  • fetchone
  • row_factory
• Проверка существования базы данных
• Подстановка значений в SQL-запрос
• Хранение изображений в базе данных
• Метаданные таблиц
• Экспорт данных
• Импорт данных
• Транзакции

SQLite

SQLite – это встроенный движок реляционной базы данных. В документации это называется автономным, бессерверным, с нулевой конфигурацией и транзакционным ядром базы данных SQL. Он очень популярен и сегодня используется сотнями миллионов копий по всему миру. Несколько языков программирования имеют встроенную поддержку SQLite, включая Python и PHP.

Установка SQLite

sudo apt install sqlite3

Запускаем модуль sqlite3 и консоль пригласит для работы с базой данных:
sqlite>
Список команд для работы с базой данных sqlite> .help

Создание базы данных SQLite

Вся база данных SQLite хранится в обычном файле. Используем команду .open dbname.db. Если база не существует — она будет создана, если существует — будет открыта.

sqlite> .open ydb.db

Python SQLite — как подключаться к базе и отправлять запросы

#!/usr/bin/python

import sqlite3
import sys

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()
    cur.execute('SELECT SQLITE_VERSION()')

    data = cur.fetchone()[0]

    print(f"SQLite version: {data}")

except sqlite3.Error as e:

    print(f"Error {e.args[0]}")
    sys.exit(1)

finally:

    if con:
        con.close()

SQLite version: 3.31.1

import sqlite3

con = None

con = sqlite3.connect('ydb.db')

cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')

data = cur.fetchone()[0]

print(f"SQLite version: {data}")

except sqlite3.Error as e:

    print(f"Error {e.args[0]}")
    sys.exit(1)

finally:

    if con:
        con.close()

#!/usr/bin/python

import sqlite3

with sqlite3.connect('ydb.db') as con:

    cur = con.cursor()
    cur.execute('SELECT SQLITE_VERSION()')

    data = cur.fetchone()[0]

    print(f"SQLite version: {data}")

Python SQLite — метод execute

import sqlite3


with sqlite3.connect('ydb.db') as con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS cars;")
    cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
    cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
    cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
    cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
    cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
    cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
    cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
    cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
    cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)")

cur.execute("DROP TABLE IF EXISTS cars;")

cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")

cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")

sqlite> .mode column
sqlite> .headers on

sqlite> select * from cars;
id          name        price
----------  ----------  ----------
1           Audi        52642
2           Mercedes    57127
3           Skoda       9000
4           Volvo       29000
5           Bentley     350000
6           Citroen     21000
7           Hummer      41400
8           Volkswagen  21600

Python SQLite — метод executemany

import sqlite3


cars = (
    (1, 'Audi', 52642),
    (2, 'Mercedes', 57127),
    (3, 'Skoda', 9000),
    (4, 'Volvo', 29000),
    (5, 'Bentley', 350000),
    (6, 'Hummer', 41400),
    (7, 'Volkswagen', 21600)
)

with sqlite3.connect('ydb.db') as con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS cars")
    cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
    cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)

cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)

cur.execute('SELECT * FROM cars WHERE name in ({0})'.format(', '.join('?' for _ in names)), names)

Python SQLite — метод executescript

import sqlite3
import sys

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()

    cur.executescript("""
        DROP TABLE IF EXISTS cars;
        CREATE TABLE cars(id INT, name TEXT, price INT);
        INSERT INTO cars VALUES(1,'Audi',52642);
        INSERT INTO cars VALUES(2,'Mercedes',57127);
        INSERT INTO cars VALUES(3,'Skoda',9000);
        INSERT INTO cars VALUES(4,'Volvo',29000);
        INSERT INTO cars VALUES(5,'Bentley',350000);
        INSERT INTO cars VALUES(6,'Citroen',21000);
        INSERT INTO cars VALUES(7,'Hummer',41400);
        INSERT INTO cars VALUES(8,'Volkswagen',21600);
        """)

    con.commit()

except sqlite3.Error as e:

    if con:
        con.rollback()

    print(f"Error {e.args[0]}")
    sys.exit(1)

finally:

    if con:
        con.close()

con.commit()

except sqlite.Error as e:

    if con:
        con.rollback()

    print(f"Error {e.args[0]}")
    sys.exit(1)

import sqlite3

with sqlite3.connect(':memory:') as con:

    cur = con.cursor()
    cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
    cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
    cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
    cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
    cur.execute("INSERT INTO friends(name) VALUES ('Robert');")

    last_row_id = cur.lastrowid

    print(f"The last Id of the inserted row is {last_row_id}")

:memory: — база данных, которая создаётся временно.

Python SQLite — получение данных с помощью fetchall

Метод fetchall извлекает все (или все оставшиеся) строки из набора результатов запроса и возвращает список кортежей.

import sqlite3


with sqlite3.connect('ydb.db') as con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cars")

    rows = cur.fetchall()

    for row in rows:
        print(f"{row[0]} {row[1]} {row[2]}")

Python SQLite — метод fetchone

Метод fetchone возвращает следующую строку полученных результатов. Возвращает один кортеж или None, когда больше нет доступных данных.

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM cars")

    while True:

        row = cur.fetchone()

        if row == None:
            break

        print(f"{row[0]} {row[1]} {row[2]}")

Python SQLite — row_factory

По умолчанию курсор возвращает данные в виде кортежа кортежей. Когда мы используем row_factory, данные передаются в виде словарей Python. Таким образом, мы можем ссылаться на данные по именам столбцов.

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    con.row_factory = sqlite3.Row

    cur = con.cursor()
    cur.execute("SELECT * FROM cars")

    rows = cur.fetchall()

    for row in rows:
        print(f"{row['id']} {row['name']} {row['price']}")

Python SQLite — как проверить, существует ли база данных

import os
import sqlite3


if not os.path.exists('ydb.db'):

    con = sqlite3.connect('ydb.db')

    with con:

        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS cars")
        cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
        cur.execute("INSERT INTO cars VALUES(1,'Audi', 52642)")
        cur.execute("INSERT INTO cars VALUES(2,'Mercedes', 57127)")
        cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
        cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
        cur.execute("INSERT INTO cars VALUES(5,'Bentley', 350000)")
        cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
        cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
        cur.execute("INSERT INTO cars VALUES(8,'Volkswagen', 21600)")

else:

    with sqlite3.connect('ydb.db') as con:

        cur = con.cursor()
        cur.execute("SELECT * FROM cars")
        rows = cur.fetchmany(2)

        print(rows)

if not os.path.exists('test.db'):

  con = sqlite3.connect('test.db')
  ...

Python SQLite — подстановка значений в SQL-запрос

import sqlite3

uId = 1
uPrice = 62300

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))

    print(f"Number of rows updated: {cur.rowcount}")

import sqlite3

uId = 4

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})

    row = cur.fetchone()
    print(f"{row[0]}, {row[1]}")

Python SQLite — хранение изображений в базе данных

Вставка изображений

Рассмотрим хранение изображений в базе данных SQLite. Конечно, сохранять изображения в базе данных нецелесообразно.  Общая практика состоит в том, чтобы хранить изображения в каталогах файловой системы и хранить ссылки на изображения в базе данных. Но мы рассмотрим этот приём для общего понимания возможностей SQLite.

sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);

Для этого примера создаем новую таблицу под названием images. Для изображений используем тип данных BLOB (англ. Binary Large Object — двоичный большой объект) — массив двоичных данных.

import sqlite3
import sys

def readImage():

    fin = None

    try:
        fin = open("sid.jpg", "rb")
        img = fin.read()
        return img

    except IOError as e:

        print(e)
        sys.exit(1)

    finally:

        if fin:
            fin.close()

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()

    data = readImage()
    binary = sqlite3.Binary(data)
    cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )

    con.commit()

except sqlite3.Error as e:

    if con:
        con.rollback()

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

Рассмотрим скрипт подробнее:

Открываем и считываем изображения из каталога в базе данных.

try:
    fin = open("sid.jpg", "rb")
    img = fin.read()
    return img

Мы считываем двоичные данные из файловой системы. У нас есть изображение в формате JPG, называемое sid.jpg.

binary = sqlite3.Binary(data)

Данные кодируются с использованием двоичного объекта SQLite.

cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )

Получение изображений

Выполним обратную операцию: получим изображение из таблицы базы данных.

import sqlite3
import sys


def writeImage(data):

    fout = None

    try:
        fout = open('sid2.jpg','wb')
        fout.write(data)

    except IOError as e:

        print(e)
        sys.exit(1)

    finally:

        if fout:
            fout.close()

con = None

try:
    con = sqlite3.connect('ydb.db')

    cur = con.cursor()
    cur.execute("SELECT data FROM images LIMIT 1")
    data = cur.fetchone()[0]

    writeImage(data)


except sqlite3.Error as e:

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

Python SQLite — метаданные таблиц

Метаданные – это информация о данных в базе данных. Метаданные в SQLite содержат информацию о таблицах и столбцах, в которых мы храним данные.

Метаданные в SQLite можно получить с помощью команды PRAGMA. Объекты SQLite могут иметь атрибуты, которые являются метаданными. Наконец, мы также можем получить конкретные метатеги, запросив таблицу sqlite_master системы SQLite.

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()

    cur.execute('PRAGMA table_info(cars)')

    data = cur.fetchall()

    for d in data:
        print(f"{d[0]} {d[1]} {d[2]}")

В этом примере мы выполняем команду PRAGMA table_info(имя_таблицы), чтобы получить некоторую информацию о метаданных нашей таблицы cars.

ur.execute('PRAGMA table_info(cars)')

Команда PRAGMA table_info(имя_таблицы) возвращает по одной строке для каждого столбца в таблице cars. Столбцы в результирующем наборе включают порядковый номер столбца, имя столбца, тип данных, может ли столбец быть нулевым или нет, и значение по умолчанию для столбца.

for d in data:
    print(f"{d[0]} {d[1]} {d[2]}")

В следующем примере выведем все строки из таблицы cars с именами их столбцов.

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute('SELECT * FROM cars')

    col_names = [cn[0] for cn in cur.description]

    rows = cur.fetchall()

    print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")

    for row in rows:
        print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")

Мы выводим содержимое таблицы cars на консоль. Теперь мы также включаем названия столбцов. Записи выровнены по именам столбцов.

col_names = [cn[0] for cn in cur.description]

Получаем имена столбцов из свойства description объекта cursor.

print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")

В этой строке выводятся названия трех столбцов таблицы cars.

for row in rows:
    print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")

Печатаем строки с помощью цикла for. Данные выровнены по именам столбцов.

id  name       price
1   Audi         62300
2   Mercedes     57127
3   Skoda         9000
4   Volvo        29000
5   Bentley     350000
6   Hummer       41400
7   Volkswagen   21600

В последнем примере перечислим все таблицы в базе данных ydb.db.

import sqlite3

con = sqlite3.connect('ydb.db')

with con:

    cur = con.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type='table'")

    rows = cur.fetchall()

    for row in rows:
        print(row[0])

Пример кода выводит на терминал все доступные таблицы в текущей базе данных.

cur.execute("SELECT name FROM sqlite_master WHERE type='table'")

Имена таблиц хранятся внутри системной таблицы sqlite_master.

cars
images

Python SQLite — экспорт данных

Создадим резервную копию таблиц нашей базы данных.

import sqlite3

cars = (
    (1, 'Audi', 52643),
    (2, 'Mercedes', 57642),
    (3, 'Skoda', 9000),
    (4, 'Volvo', 29000),
    (5, 'Bentley', 350000),
    (6, 'Hummer', 41400),
    (7, 'Volkswagen', 21600)
)

def writeData(data):

    f = open('cars.sql', 'w')

    with f:
        f.write(data)


con = sqlite3.connect(':memory:')

with con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS cars")
    cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
    cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
    cur.execute("DELETE FROM cars WHERE price < 30000")

    data = 'n'.join(con.iterdump())

    writeData(data)

В этом примере воссоздаем таблицу cars в временной базе данных. Удаляем некоторые строки из таблицы и сохраняем текущее состояние таблицы в файл cars.sql. Этот файл будет служить резервной копией текущей таблицы.

def writeData(data):

    f = open('cars.sql', 'w')

    with f:
        f.write(data)

Данные из таблицы записываются в файл.

con = sqlite3.connect(':memory:')

Создаем таблицу во временной базе данных.

cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")

Создаём таблицу cars, вставляем значения и удаляем строки, где цена меньше 30000.

data = 'n'.join(con.iterdump())

con.iterdump возвращает итератор для дампа базы данных в текстовом формате SQL. Встроенная функция join принимает итератор и объединяет все строки в итераторе, разделенные новой строкой. Эти данные записываются в файл cars.sql с помощью функции writeData.

Python SQLite — импорт данных

Выполним обратную операцию. Импортируем ранее сохранённую таблицу.

import sqlite3


def readData():

    f = open('cars.sql', 'r')

    with f:

        data = f.read()

        return data


con = sqlite3.connect(':memory:')

with con:

    cur = con.cursor()

    sql = readData()
    cur.executescript(sql)

    cur.execute("SELECT * FROM cars")

    rows = cur.fetchall()

    for row in rows:
        print(row)

Python SQLite — транзакции

Транзакция – это процесс выполнения операций с базой данных. В одной транзакции может быть несколько операций. Операции в транзакции могут быть либо выполнены целиком и полностью все, либо не выполнены вообще.

Транзакции начинаются с инструкции BEGIN TRANSACTION и завершаются инструкциями COMMIT или ROLLBACK.

SQLite поддерживает три уровня транзакций: DEFERRED, IMMEDIATE and EXCLUSIVE.

1. DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.
2. IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.
3. EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.

Модуль Python SQLite также поддерживает режим автоматической фиксации, при котором все изменения в таблицах вступают в силу немедленно.

import sqlite3
import sys

con = None

try:
    con = sqlite3.connect('ydb.db')
    
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS friends")
    cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
    cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
    cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
    cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
    cur.execute("INSERT INTO friends(name) VALUES ('Robert')")

    #con.commit()

except sqlite3.Error as e:

    if con:
        con.rollback()

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

Мы создаем таблицу friends и пытаемся заполнить ее данными. Однако, как мы увидим, данные не фиксируются.

#con.commit()

Метод фиксации con.commit() закомментирован. Если мы раскомментируем строку, данные будут записаны в таблицу.

Python SQLite autocommit

В режиме автоматической фиксации инструкция SQL выполняется немедленно.

import sqlite3
import sys

con = None

try:
    con = sqlite3.connect('ydb.db', isolation_level = None)
    
    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS friends")
    cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
    cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
    cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
    cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
    cur.execute("INSERT INTO friends(name) VALUES ('Robert')")

except sqlite3.Error as e:

    print(e)
    sys.exit(1)

finally:

    if con:
        con.close()

В этом примере мы подключаемся к базе данных в режиме автоматической фиксации.

con = sqlite3.connect('ydb.db', isolation_level = None)

Смотрите также: Руководство по стилю SQL

Сообщение Python SQLite — учебное пособие на русском языке появились сначала на Веб-студия Юсота.

Source: usota.ru python