Skip to content

Instantly share code, notes, and snippets.

@vvscode
Last active January 29, 2020 12:54
Show Gist options
  • Save vvscode/9cbcd76aa418b1cfd504979672c70a89 to your computer and use it in GitHub Desktop.
Save vvscode/9cbcd76aa418b1cfd504979672c70a89 to your computer and use it in GitHub Desktop.
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()
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)
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"
)
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment