SQLAlchemy with DDL

SQL명령어 중, DML 위주의 ORM 사용이 주류를 이루는 것 같았다. (아니면, 댓글 부탁해요.)
스프링에서도, 노드에서도...

python flask의 ORM인 SQLAlchemy로 SQL DDL로 테이블 생성하는 것과 동일하게 코드를 작성해 보았습니다.

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(
    DB_USER, DB_PASS, DB_SERVER, DB_PORT, DB_NAME
)

• app.py

from flask import Flask
from config import SQLALCHEMY_DATABASE_URI, SECRET_KEY
from models import db, migrate


app = Flask(__name__)

app.config['SECRET_KEY'] = SECRET_KEY
app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db.init_app(app)
migrate.init_app(app, db)


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

• models.py

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from sqlalchemy import text
from sqlalchemy.sql import func

db = SQLAlchemy()
migrate = Migrate()

class Lecture(db.Model):
    __tablename__ = 'lecture'
    __table_args__ = (
        db.UniqueConstraint('name', 'activated'),
    )

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    teacher_id = db.Column(db.BigInteger, nullable=False, index=True)
    name = db.Column(db.String(70), nullable=False)
    description = db.Column(db.Text, nullable=False)
    price = db.Column(db.Integer, nullable=False)
    activated = db.Column(db.Boolean, server_default='0')
    created_at = db.Column(db.DateTime(timezone=True), nullable=False, server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

    def __init__(self, teacher_id, lecture_category_id, name, description, price):
        self.teacher_id = teacher_id
        self.name = name
        self.description = description
        self.price = price

class Student(db.Model):
    __tablename__ = 'student'
    __table_args__ = (
        db.UniqueConstraint('email', 'activated'),
    )

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    email = db.Column(db.String(255), nullable=False)
    nickname = db.Column(db.String(100), nullable=False)
    activated = db.Column(db.Boolean, server_default='1')

    def __init__(self, email, nickname):
        self.email = email
        self.nickname = nickname

class Teacher(db.Model):
    __tablename__ = 'teacher'

    id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
    name = db.Column(db.String(70), nullable=False, index=True)

    def __init__(self, name):
        self.name = name

$ flask db init
$ flask db migrate
$ flask db upgrade

flask db 명령어 뒤에 --directory=$PWD/migrations/ 옵션으로 migrations 생성 디렉토리를 변경할 수 있다.

---

• ForeignKey

Index: app/models.py
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/app/models.py b/app/models.py
--- a/app/models.py	(revision f3fc6cb6b305199efa04e72c8639a7d9447552fb)
+++ b/app/models.py	(date 1679878426723)
@@ -14,8 +14,8 @@
     )
 
     id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
-    lecture_id = db.Column(db.BigInteger, nullable=False)
-    student_id = db.Column(db.BigInteger, nullable=False, index=True)
+    lecture_id = db.Column(db.BigInteger, db.ForeignKey('lecture.id'), nullable=False)
+    student_id = db.Column(db.BigInteger, db.ForeignKey('student.id'), nullable=False, index=True)
     created_at = db.Column(db.DateTime(timezone=True), nullable=False, server_default=func.now())
 
     def __init__(self, lecture_id, student_id):

db.ForeignKey('lecture.id'), db.ForeignKey('student.id')로 인해 아래와 같은 constraint가 create table DDL에 생겼다.

...
    constraint enroll_ibfk_1
        foreign key (lecture_id) references lecture (id),
    constraint enroll_ibfk_2
        foreign key (student_id) references student (id)
...