import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
import os

engine = create_engine('sqlite:///:memory:', echo=True)

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(64), unique=True)

    posts = relationship(
        "Post",
        back_populates='author',
        cascade="all, delete, delete-orphan"
    )


class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    body = Column(String(120))

    user_id = Column(Integer, ForeignKey('users.id'))

    author = relationship("User", back_populates = "posts")


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine, autoflush=False)
session = Session()

j = User(username='john')
s = User(username='susan')
p1 = Post(body='first', author=j)
p2 = Post(body='second', author=j)
p3 = Post(body='third', author=s)
session.add_all([
    j, s, p1, p2, p3
])
session.commit()
os.system('clear')
print()

print('session.query(Post).filter_by(author=j).all()')
print('----------------------------------------------------------------')
print(session.query(Post).filter_by(author=j).all())
print()

print('================================================================')
print('================================================================')
print('================================================================')
print()
print()


print('session.query(Post).filter_by(author=j).all()')
print('----------------------------------------------------------------')
print(session.query(Post).filter_by(author=j).all())
print()

print('session.query(Post).with_parent(j).all()')
print('----------------------------------------------------------------')
print(session.query(Post).with_parent(j).all())
print()

print('session.query(Post).with_parent(j, property="posts").all()')
print('----------------------------------------------------------------')
print(session.query(Post).with_parent(j, property='posts').all())
print()

print('session.query(Post).with_parent(j, property=User.posts).all()')
print('----------------------------------------------------------------')
print(session.query(Post).with_parent(j, property=User.posts).all())
print()

print('j.posts')
print('----------------------------------------------------------------')
print(j.posts)