- Самый лучший способ сохранять данные пользователя, на данный момент, - это базы данных. В отличаи от файлов, которые тоже могут сохранять данные вашей программы, базы данных имеют множество преимуществ, а именно:
- Разрешения - вы можете задать разрешения, по которым пользователи базы могут взаимодействовать с данными
- Структурированность - данные хранятся не абы как, а в конкретных местах и определенным образом, и база данных дает этому гарантии
- Легкость работы над данными - чтобы получить какие-то записи из БД, не нужно считывать оттуда все данные. БД дает удобные инструменты для сортировки, фильтровки и обновления данных
- Защита данных - БД не так уж легко взломать и получить доступ к данным.
- В реляционных БД данные хранятся в таблицах. Чтобы однозначно определить данные в таблице, есть такое понятие как первичный ключ. По сути это уникальное ненулевое значение какой-то записи в таблице, например email, уникальный идентификатор, uuid.
- Внешним ключем является ссылка из поля одной таблицы на первичный ключ другой таблицы.
-
Для работы с БД, в Python есть встроенный модуль
sqlite3, для работы с бд нужно создать подключение и получить курсор:import sqlite3 connection = sqlite3.connect("example.db") # создаем соединение cursor = connection.cursor() # получаем курсор
курсор - это объект, через который мы будем делать операции с БД, касающиеся данных. Соединение - более общая концепция, дающее контроль над всем взаимодействием с БД, а не только над данными.
-
Создание таблиц происходит с помощью команды
CREATE TABLE table_name(...);:# создаем таблицу со столбцами id, email, name cursor.execute(""" CREATE TABLE users (id, email, name); """) connection.commit() # сохраняем
Однажды создав таблицу, мы не можем ее создать второй раз. Потому после запуска данных строк кода, их стоит закомментить, чтобы не получать ошибку.
-
Добавление записей делается с помощью команды
INSERT INTO table_name VALUES (...);:# добавляем записи пользователей в таблицу cursor.execute( """INSERT INTO users VALUES (1, "edefe", "alex");""" ) cursor.execute( """INSERT INTO users VALUES (2, "edefe1", "vova");""" ) cursor.execute( """INSERT INTO users VALUES (3, "edefe2", "bob");""" ) cursor.execute( """INSERT INTO users VALUES (5, "edefe3", "alice");""" ) # сохраняем изменения connection.commit()
-
Получение данных делается при помощи команды
SELECT ... FROM table_name;:# запрос на получение всех данных cursor.execute(""" SELECT * FROM users; """) # получение запрашиваемых данных data = cursor.fetchall()
Или можно получать только определенные колонки из таблицы:
# запрос на получение имен cursor.execute(""" SELECT name FROM users; """) # получение запрашиваемых данных data = cursor.fetchall()
-
Можно фильтровать получаемые данные при помощи конструкции
WHERE ...;:# запрос на получение всех данных о пользователе с именем bob cursor.execute(""" SELECT * FROM users WHERE name="bob"; """) # получение запрашиваемых данных data = cursor.fetchall()
-
Обновление данных происходит с помощью команды
UPDATE table_name SET column_name=value;, чаще всего нужно еще добавлять условие, т.е. указать, какие именно записи нужно обновить при помощиWHERE:# запрос на обновление email у пользователей с именем bob cursor.execute(""" UPDATE users SET email="qqq" WHERE name="bob"; """) # сохранение изменений connection.commit()
-
Удаление данных происходит через команду
DELETE FROM table_name WHERE ...;, если не написатьWHEREв конце и не указать условия, то будут удалены все данные из таблицы:# запрос удаления пользователя с именем bob cursor.execute(""" DELETE FROM users WHERE name="bob"; """) # сохранение изменений connection.commit()
-
Посмотреть о SQLite подробнее можно тут
-
PostgreSQL(рус док: тут, англ док: тут) является одной из наиболее популярных систем управления базами данных. В отличаи от SQLite - это не файловая база данных, а серверная, значит что для своего функционирования создает отдельный сервер, к которому могут подключаться различные приложения или пользователи со своими разрешениями. Как установить почитать тут
-
Основные команды остаются прежними:
CREATE TABLE table_name(params);-- в постгрес необходимо конкретно указывать тип столбца. Весь список тут.INSERT INTO table_name(params) VALUES (params), (params) ...;-- можно убрать params, но тогда номер вставляемого значения должен соответствовать номеру столбца и вставляемых значений должно быть не меньше количества столбцов.SELECT params FROM table_name WHERE column_name=value;-- получение значений из таблицы, у которых column_name = value.UPDATE table_name SET column_name=value WHERE column_name=value1;-- изменение значения column_name везде, где column_name = value1.DELETE FROM table_name WHERE column_name=value;-- удаление всех записей таблицы, у которых column_name = value.
-
Создание таблиц. Всегда необходимо указывать определенный тип, кроме того, можно указывать определенные ограничения при помощи команды
CHECK. Создадим таблицу пользователей:- Подключим расширение для uuid:
CREATE EXTENSION "uuid-ossp";- Создадим таблицу пользователей с автоматической генерацией uuid:
CREATE TABLE users( uuid uuid DEFAULT uuid_generate_v4() PRIMARY KEY, -- uuid - является первичным ключом name CHARACTER VARYING(256), -- задали имя с макс длиной 256 age SMALLINT CHECK(age > 18 AND age < 100) -- подобрали тип данных для возраста и сделали проверку на то, чтобы возраст был больше 18 и меньше 100 );
В столбцы с ограничениями попросту нельзя занести значения, которые не соответствуют ограничениям. 3. Кроме uuid, как в предыдущем примере, можно использовать просто уникальное числовое значение, например 1,2,3,4... В postgres для этого также есть инструменты, которые вместо нас будут искать доступные свободные индексы и генерировать их. Перепишем предыдущий пример:
CREATE TABLE users_test( id SERIAL PRIMARY KEY, -- uuid - является первичным ключом name CHARACTER VARYING(256), -- задали имя с макс длиной 256 age SMALLINT CHECK(age > 18 AND age < 100) -- подобрали тип данных для возраста и сделали проверку на то, чтобы возраст был больше 18 и меньше 100 );
SERIAL- специальный тип, который автоматически генерирует целочисленное значение, которое в таблице еще не занято. Однако дальше мы будем использовать первый вариант данной таблицы. 4. Создадим еще одну таблицу, которая нам понадобится в будущем:CREATE TABLE projects( id SERIAL PRIMARY KEY, name VARCHAR (124), description TEXT );
-
Для внесения данных в таблицу используется конструкция
INSERT:INSERT INTO users(name, age) VALUES ('Bob', 23), ('Alice', 34), ('Karl', 45);
Если попытаться внести пользователя с возрастом 120, будет ошибка, так как сработает ограничение, которое мы поставили. Добавим также данных во вторую таблицу:
INSERT INTO projects(name, description) VALUES ('shop', 'Create an online shop'), ('bot', 'Create a telegram bot for buying products from online shop'), ('sql', 'Create some sql tasks');
-
SELECTслужит для получения данных из таблицы:
-- получение всех пользователей старше 30
SELECT name, age FROM users WHERE age > 30;Так же можно сортировать данные запроса при помощи конструкции ORDER BY:
-- сортировка по возрасту: от самого молодого до самого старого
SELECT name, age FROM users ORDER BY age;
-- сортировка по возрасту: от самого старого до самого молодого
SELECT name, age FROM users ORDER BY age DESC;Или получать определенное количество записей, при помощи конструкции LIMIT:
-- получить 2 первые записи из выборки
SELECT name, age FROM users ORDER BY age DESC LIMIT 2;Кроме того иногда полезными являются подзапросы:
-- получение всех пользователей, которые старше среднего
SELECT name, age FROM users WHERE age > (SELECT AVG(age) FROM users);
-- получение всех пользователей, которые старше чем разница между возрастом самого старого и самого молодого пользователя
SELECT name, age FROM users
WHERE age > (
SELECT MAX(age) - MIN(age) FROM users);-
Соединение таблиц.
- Создадим 3ю таблицу, на основе двух других:
CREATE TABLE participants ( user_uuid uuid REFERENCES users(uuid), project_id SERIAL REFERENCES projects(id), project_role CHARACTER VARYING(64) );
Эта таблица-связка между таблицами projects и users. Связывается она с ними при помощи внешнего ключа. Внешние ключи объявляются при помощи ключевого слова
REFERENCESи указанием таблицы и поля, на которое идет ссылка. Почитать тут, дока тут- Заполним таблицу на основе таблицы users:
INSERT INTO participants SELECT uuid, 1, 'developer' -- можно делать так. берем uuid прямо налету и сразу вставляем в participants FROM users WHERE name='Bob'; INSERT INTO participants SELECT uuid, 2, 'developer' FROM users WHERE name='Bob'; INSERT INTO participants SELECT uuid, 3, 'developer' FROM users WHERE name='Bob'; INSERT INTO participants SELECT uuid, 2, 'team-lead' FROM users WHERE name='Alice'; INSERT INTO participants SELECT uuid, 3, 'project manager' FROM users WHERE name='Karl';
- Теперь сделаем запросы во все 3 таблицы сразу, соединяя их:
-- получить названия проектов, и роль, в которых есть Bob SELECT projects.name, project_role FROM users JOIN participants ON users.uuid = participants.user_uuid JOIN projects ON participants.project_id = projects.id WHERE users.name = 'Bob'; --(extra) получить проекты, на которых средний возраст всех участников ниже, чем средний возраст в компании WITH project_age AS ( SELECT projects.name as project_name, AVG(users.age) as user_age FROM users JOIN participants ON users.uuid = participants.user_uuid JOIN projects ON participants.project_id = projects.id GROUP BY project_name ) SELECT project_name, user_age FROM project_age WHERE user_age < (SELECT AVG(age) FROM users);
Таблицы соединяются по внешнему ключу при помощи конструкции JOIN ... ON ..., где JOIN связывает названия таблиц, а ON связывает конкретные ключи. О соединении таблиц почитать тут.(extra) Последний запрос с
WITHназывается называется общим табличным выражением(CTE). Почитать можно очень интересно можно тут, дока тут -
Для того чтобы не повторять один и тот же код запросов множество раз, есть такое понятие как представление. В postgres оно создается командой CREATE VIEW (дока тут), по сути это просто удобное название вашего запроса, а не отдельная таблица, однако часто это помогает делать код чище и приятнее, пример:
-- создаем представление CREATE VIEW users_projects AS ( SELECT users.uuid as user_uuid, users.name as user_name, users.age as user_age, projects.id as project_id, projects.name as project_name, description, project_role FROM users JOIN participants ON users.uuid = participants.user_uuid JOIN projects ON projects.id = participants.project_id );
Однажды создав представление в БД, оно там будет, пока вы его не удалите. Потому обратиться к нему можно в любой момент:
-- получить все данные из представления SELECT * FROM users_projects; -- получить user_name, project_name для пользователей которые старше 30 SELECT user_name, project_name FROM users_projects WHERE user_age > 30;