Создание базы данных для сайта на Flask

Разработка базы данных для сайта ‒ нетривиальная задача. У каждого она своя. Приводимые далее набор таблиц и их структура могут не подходить для вашего проекта.

Для создания базы данных и таблиц будет использоваться отдельный python-скрипт. После того, как БД будет создана, он больше не понадобится.

Чтобы отделить файлы проекта, которые не относятся к "рабочим" файлам сайта, создадим отдельный каталог tools, а в нем ‒ файл schema.sql, в котором опишем схемы создаваемых таблиц.

Согласно нормализации под каждый тип (можно сказать класс, разновидность) сущностей должна быть своя таблица. Одна такая сущность для нас очевидна ‒ это статья. Поэтому у нас будет таблица, в которой каждая статья ‒ это запись, состоящая из таких ячеек как тело статьи, ее заголовок, адрес и др.

Вторая, неочевидная на первый взгляд, таблица, которая нам понадобится, ‒ это таблица разделов сайта. Она будет нужна в том числе для создания меню (сайдбара).

Раздел сайта не относится ни к какой статье. Наоборот, разные статьи могут принадлежать одному разделу. Поэтому в таблице статей у каждой будет привязка к своему разделу. Этот внешний ключ для таблицы статей будет основным ключом (id) в таблице разделов.

Однако, чтобы у нас была возможность сортировать разделы, не изменяя их id и тем самым не внося путаницу в таблицу статей, предусмотрим в таблице разделов отдельный столбец для указания порядкового номера раздела.

Таким образом, мы приходим к таблице разделов, в которой имеются три столбца: id, название, порядковый номер раздела в меню (не путать с порядковыми номерами статей в разделе).

CREATE TABLE sections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    pos INTEGER NOT NULL DEFAULT 0,
);

Столбцы таблицы для статей: собственное внутреннее id, адрес, заголовок, описание, тело статьи, id раздела, позиция статьи в разделе.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL,
    h1 TEXT NOT NULL,
    short_desc TEXT,
    body TEXT,
    theme INTEGER NOT NULL,
    pos_in_theme INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (theme) REFERENCES sections(id)
);

Также неплохо бы сохранять дату создания статьи. Мы можем внести еще один столбец в таблицу статей, но если подумать на перспективу, то следует сохранять и даты, когда статья исправлялась. Статья может правиться множество раз. Если мы будем постоянно изменять ячейку даты, то потеряем информацию о времени создания статьи и предыдущих правках.

Следует исходить из того, нужно ли сохранять все даты правки статьи. Если нет, то в таблице статей может быть два столбца: время создания и время последней правки. Если надо сохранять все даты, то приходим к мысли о создании третьей таблицы, где будут храниться все даты изменения статей. У каждой даты будет привязка к id статьи, которая правилась. Если понадобятся все даты правки одной статьи, мы всегда сможем получить их с помощью запроса. При сортировке сможем увидеть дату создания статьи или дату последней правки.

CREATE TABLE dates (
    dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    content INTEGER NOT NULL,
    FOREIGN KEY (content) REFERENCES articles(id)
);

Далее в проекте, мы будем выполнять запись в эту таблицу, но если вам нужно выводить данные из нее на веб-странице, например, отображать дату последней правки, вам придется реализовывать это самостоятельно. Также эта таблица может использоваться для sitemap.xml, если в нем указывается дата последней модификации страницы.

Для разделов можно использовать отдельную программу-скрипт, которая позволяет их так или иначе править. Мы же сейчас не будем усложнять разработку и просто добавим записи в таблицу разделов:

INSERT INTO sections (name, pos) VALUES ('Растения', 5);
INSERT INTO sections (name, pos) VALUES ('Животные', 7);
INSERT INTO sections (name, pos) VALUES ('Эволюция', 20);

Скрипт работы с разделами сайта в текстовом режиме есть в готовом шаблоне. В нем кроме правки разделов также реализовано обновление меню сайта. Поэтому скрипт применим только к полностью разработанному flask-приложению.

Теперь напишем небольшой скрипт на Питоне, который создаст базу данных и выполнит команды из файла schema.sql. Назовем его init_db.py и расположим в каталоге tools. Однако база будет создана на уровень выше (../), там, где находится файл admin.py.

import sqlite3

connection = sqlite3.connect('../database.db')

with open('schema.sql') as f:
    connection.executescript(f.read())

connection.commit()
connection.close()

Этот скрипт надо запустить на выполнение отдельно, как самостоятельную программу.

Теперь из flask-приложения admin.py будем подключаться к созданной базе данных.

Здесь, чтобы разделы брались из базы данных, потом отображались в форме, соединимся с БД, запросим темы и отсортируем их по их позициям.

import sqlite3

...

db_name = 'database.db'

con = sqlite3.connect(db_name)
sections = con.execute('SELECT id, name FROM sections \
                        ORDER BY pos').fetchall()
con.close()

Если вывести значение sections, оно будет выглядеть так:

[(1, 'Растения'), (2, 'Животные'), (3, 'Эволюция')]

Это список, состоящий из кортежей. В данном случае его сортировка лишняя. Но если потом в таблицу будут добавлены новые разделы с позиционными номерами, идущими не по порядку, сортировка понадобится.

Передадим список в шаблон:

return render_template('create.html', sections=sections)

Если мы точно знаем, сколько у нас тем, можем извлекать элементы из sections без цикла и заполнять ими список выбора:

<select name="section" id="section" style="font-size:18px;">
   <option value="{{ sections[0][0] }}">{{ sections[0][1] }}</option>
   <option value="{{ sections[1][0] }}">{{ sections[1][1] }}</option>
   <option value="{{ sections[2][0] }}">{{ sections[2][1] }}</option>
</select>

Однако в Jinja есть возможность использовать цикл, которым следует воспользоваться, так как разделов может быть много.

<select name="section" id="section" style="font-size:18px;">
  {% for s in sections %}
     <option value="{{ s[0] }}">{{ s[1] }}</option>
  {% endfor %}
</select>