Flask==2.1.3
Flask-SQLAlchemy==2.5.1

(venv) A:\abc\osaka\main\api>pip install -r requirements.txt

import os, json
from flask import Flask
from datetime import timedelta
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__, instance_relative_config=True)
app.config.from_mapping(
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
    ,SQLALCHEMY_TRACK_MODIFICATIONS = False
)
db = SQLAlchemy(app)

@app.route("/", methods=["GET"])
def index():
    return "Hello"

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)

// ユーザー
user {
    id: number
    name: string
    password: string
}

// ユーザが投稿した記事的なもの
post {
    id: number
    user_id: number
    title: string
    body: string
}

// postのlike(ハートやLGTM的なもの
like {
    id: number
    user_id: number
    post_id: number
    like: boolean
}

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, nullable=False, unique=True)
    password = db.Column(db.String, nullable=False)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    title = db.Column(db.String())
    body = db.Column(db.String())

sqlite> .schema
CREATE TABLE user (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        password VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (name)
);

CREATE TABLE post (
        id INTEGER NOT NULL,
        user_id INTEGER,
        title VARCHAR,
        body VARCHAR,
        PRIMARY KEY (id),
        FOREIGN KEY(user_id) REFERENCES user (id)
);

class Like(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    post_id = db.Column(db.Integer, db.ForeignKey("post.id"))
    like = db.Column(db.Boolean, default=True)

(venv) A:\abc\osaka\main\api>python
Python 3.9.4 (tags/v3.9.4:*******, **  * ****, **:**:**) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

>>> from app import *
>>> db.create_all()

>>> apple = User(name="apple", password="42")
>>> user = User(name="osaka", password="24")
>>> db.session.add(apple)
>>> db.session.add(user)
>>> db.session.commit()

>>> post_0 = Post(user_id=1, title="なぜリンゴはニュートンの前で落ちたのか", body="調べた結果分かりませんでした、今後のリンゴの活躍に期待ですね")
>>> post_1 = Post(user_id=1, title="神はなぜリンゴを知恵の実にしたのか", body="旧約聖書にそうした記述はありませんでした、今後のリンゴの活躍に期待ですね")
>>> post_2 = Post(user_id=2, title="このタイトルに1時間くらい考えた", body="一応これも考えたけどappleと違ってosakaはネタが少ないし十分なエビデンスが得られなかったからこれでいく")
>>> db.session.add(post_0)
>>> db.session.add(post_1)
>>> db.session.add(post_2)
>>> db.session.commit()

>>> like_0 = Like(user_id=1, post_id=1, like=True)
>>> like_1 = Like(user_id=1, post_id=3, like=True)
>>> like_2 = Like(user_id=2, post_id=2, like=True)
>>> like_3 = Like(user_id=2, post_id=3, like=True)
>>> db.session.add(like_0)
>>> db.session.add(like_1)
>>> db.session.add(like_2)
>>> db.session.add(like_3)
>>> db.session.commit()

>>> user = User.query.get(1)
>>> user

>>> user.name
apple

>>> users = User.query.all()
>>> users

>>> for user in users:
...     print(user.name)
...
apple
osaka

>>> user = User.query.first()
>>> user

>>> posts = Post.query.filter(Post.user_id == 1).all()
>>> posts

>>> posts = Post.query.filter(Post.body.contains("旧約聖書")).all()
>>> posts

>>> posts = Post.query.filter(Post.body.contains("旧約聖書")).filter(Post.user_id == 1).all()
>>> posts

>>> user = 1
>>> q = ""
>>> post = Post.query \
...         .filter(Post.user_id == user if user else True) \
...         .filter(Post.body.contains(q) if q else True)

>>> post = Post.query.join(Like, (Like.post_id == Post.id)) \
...     .filter(Like.like, Like.user_id == 1) \
...     .all()

>>> user = User.query.get(1)
>>> user.name = "WorldWideWeb"
>>> db.session.commit()

>>> user = User.query
>>> user.update({User.password:"BigAdminIsWatchingYou"})
>>> db.session.commit()

>>> user = User.query.filter(User.id == 1)
>>> user.update({User.password:"42"})
>>> db.session.commit()

>>> user = User.query.get(2)
>>> db.session.delete(user)
>>> db.session.commit()

>>> user = Post.query.filter(Post.user_id == 1).delete()
>>> db.session.commit()

>>> db.drop_all()