create_and_seed_db.py

from sqlalchemy.orm import scoped_session, sessionmaker, relationship

from db_declaration import Base, User, Post, Category, Tag
from db import engine

Base.metadata.bind = engine
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
session = scoped_session(Session)


def create_db():
    Base.metadata.create_all()


def seed_db():
    tags = []

    for category_name in ['category1', 'category2']:
        category = Category(
            name=category_name,
            slug=f'slug_for_{category_name}',
            description=f'description_for_{category_name}'
        )
        session.add(category)

    for tag_name in ['tag1', 'tag2', 'tag3']:
        tag = Tag(
            name=tag_name,
            slug=f'slug_for_{tag_name}',
            description=f'description_for_{tag_name}'
        )
        tags.append(tag)
        session.add(tag)

    for user_name in ['Bob', 'Sam']:
        user = User(
            login=user_name,
            name=user_name,
            password=f'pass for {user_name}',
            email=f'{user_name}@me',
        )
        session.add(user)

    post = Post(
        title='Some post',
        description='Some description',
        body='Some body',
        creator=user,
        category=category,
    )

    post.tags = tags[1:]
    session.add(post)

    session.commit()


# looks like it worth to use `alembic` or something like that
if __name__ == '__main__':
    create_db()
    seed_db()

db.py

import os
from sqlalchemy import create_engine

basedir = os.path.abspath(os.path.dirname(__file__))
SQLALCHEMY_DATABASE_URI = "sqlite:///" + \
    os.path.join(basedir, "blog.db")

engine = create_engine(SQLALCHEMY_DATABASE_URI)

db_declaration.py

import hashlib
import datetime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (
    Table,
    Column,
    ForeignKey,
    Integer,
    String,
    Float,
    Boolean,
    DateTime,
)
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm.collections import attribute_mapped_collection

Base = declarative_base()


def generate_password_hash(password):
    m = hashlib.md5()
    m.update(password.encode('utf-8'))
    m.update(m.hexdigest().encode('utf-8'))
    return m.hexdigest()


posts_tags_association = Table(
    "posts_tags",
    Base.metadata,
    Column("post_id", Integer, ForeignKey("posts.id")),
    Column("tag_id", Integer, ForeignKey("tags.id")),
)


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200))
    login = Column(String(200))
    password_hash = Column(String(128))
    about = Column(String(), unique=True, index=True)
    email = Column(String(50), unique=True, index=True)
    reg_date = Column(DateTime, default=datetime.datetime.now)
    posts = relationship("Post", back_populates="creator")

    def __repr__(self):
        return f"User(id={self.id}, email={self.email})"

    def __init__(self, name, login, password, email):
        self.name = name
        self.login = login
        self.password_hash = generate_password_hash(password)
        self.password = password
        self.email = email

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return generate_password_hash(password) == self.password_hash


class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(100))
    description = Column(String(1000))
    body = Column(String)

    def __repr__(self):
        return f"Post(id={self.id})"

    creator = relationship("User", back_populates="posts")
    creactor_id = Column(Integer, ForeignKey("users.id"))

    category = relationship("Category", back_populates="posts")
    category_id = Column(Integer, ForeignKey("categories.id"))

    tags = relationship(
        "Tag", secondary=posts_tags_association, back_populates="posts")


class Category(Base):
    __tablename__ = "categories"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    slug = Column(String(100))
    description = Column(String)
    posts = relationship("Post", back_populates="category")

    def __repr__(self):
        return f"Category(id={self.id})"

    # Unfortunately can't fix next
    # "sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'categories.parent_id' could not find table 'catogories' with which to generate a foreign key to target column 'id'"
    # # https://stackoverflow.com/questions/4896104/creating-a-tree-from-self-referential-tables-in-sqlalchemy
    # parent_id = Column(Integer, ForeignKey("catogories.id"))
    # children = relationship(
    #     "Category",
    #     cascade="all",
    #     backref=backref("parent", remote_side="categories.parent_id"),
    #     collection_class=attribute_mapped_collection("name"),
    # )

    # def __init__(self, name, parent=None):
    #     self.name = name
    #     self.parent = parent

    # def append(self, nodename):
    #     self.children[nodename] = Category(nodename, parent=self)


class Tag(Base):
    __tablename__ = "tags"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    slug = Column(String(100))
    description = Column(String)

    def __repr__(self):
        return f"Category(id={self.id})"

    posts = relationship(
        "Post", secondary=posts_tags_association, back_populates="tags"
    )

Resulted.sql

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "posts_tags" (
	"post_id"	INTEGER,
	"tag_id"	INTEGER,
	FOREIGN KEY("post_id") REFERENCES "posts"("id"),
	FOREIGN KEY("tag_id") REFERENCES "tags"("id")
);
CREATE TABLE IF NOT EXISTS "posts" (
	"id"	INTEGER NOT NULL,
	"title"	VARCHAR(100),
	"description"	VARCHAR(1000),
	"body"	VARCHAR,
	"creactor_id"	INTEGER,
	"category_id"	INTEGER,
	FOREIGN KEY("category_id") REFERENCES "categories"("id"),
	PRIMARY KEY("id"),
	FOREIGN KEY("creactor_id") REFERENCES "users"("id")
);
CREATE TABLE IF NOT EXISTS "tags" (
	"id"	INTEGER NOT NULL,
	"name"	VARCHAR(100),
	"slug"	VARCHAR(100),
	"description"	VARCHAR,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "categories" (
	"id"	INTEGER NOT NULL,
	"name"	VARCHAR(100),
	"slug"	VARCHAR(100),
	"description"	VARCHAR,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"	INTEGER NOT NULL,
	"name"	VARCHAR(200),
	"login"	VARCHAR(200),
	"password_hash"	VARCHAR(128),
	"about"	VARCHAR,
	"email"	VARCHAR(50),
	"reg_date"	DATETIME,
	PRIMARY KEY("id")
);
INSERT INTO "posts_tags" VALUES (1,2);
INSERT INTO "posts_tags" VALUES (1,3);
INSERT INTO "posts" VALUES (1,'Some post','Some description','Some body',2,2);
INSERT INTO "tags" VALUES (1,'tag1','slug_for_tag1','description_for_tag1');
INSERT INTO "tags" VALUES (2,'tag2','slug_for_tag2','description_for_tag2');
INSERT INTO "tags" VALUES (3,'tag3','slug_for_tag3','description_for_tag3');
INSERT INTO "categories" VALUES (1,'category1','slug_for_category1','description_for_category1');
INSERT INTO "categories" VALUES (2,'category2','slug_for_category2','description_for_category2');
INSERT INTO "users" VALUES (1,'Bob','Bob','864cfa80858abacd6313cce4a88560c6',NULL,'Bob@me','2020-01-29 14:37:03.513169');
INSERT INTO "users" VALUES (2,'Sam','Sam','bcc95d33ffdb5314a196d08889a580d5',NULL,'Sam@me','2020-01-29 14:37:03.514121');
CREATE UNIQUE INDEX IF NOT EXISTS "ix_users_email" ON "users" (
	"email"
);
CREATE UNIQUE INDEX IF NOT EXISTS "ix_users_about" ON "users" (
	"about"
);
COMMIT;