database 구축

• 데이터 베이스 구축은 database.py파일에 실시함
• SQLAlchemy 사용하여 ORM으로 구축
• sqlite사용

1. pip install sqlalchemy

2. database.py에 아래 내용 추가

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker



#프로젝트 루트디렉터리에 저장
SQLALCEMY_DATABASE_URL = "sqlite:///./myapi.db"

#커넥션 풀(db접속하는 객체를 일정 갯수만큼 만들어 놓고 사용하는것) 생성
engine = create_engine(
    SQLALCEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

#데이터베이스에 접근하기 위한 클래스
SessionLocal = sessionmaker(
    autocommit=False, #데이터변경시 commit을 통해서만 저장 가능
    autoflush=False,
    bind=engine
)

#모델 구성할때 Base를 통해 구성
Base = declarative_base()

 

3. models.py에 (질문, 답변 모델 생성)

from sqlalchemy import Column, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import relationship
from database import Base


#질문 모델 생성
class Question(Base):
    __tablename__ = "question" #모델에 의해 관리되는 테이블의 이름

    id = Column(Integer, primary_key=True) #
    subject = Column(String, nullable=False)
    content = Column(Text, nullable=False)
    create_date = Column(DateTime, nullable=False)

#답변 모델 생성
class Answer(Base):
    __tablename__ = "answer"

    id = Column(Integer, primary_key=True)
    content = Column(Text, nullable=True)
    created_date = Column(DateTime, nullable=True)
    question_id = Column(Integer, ForeignKey('question.id')) #fk설정
    question = relationship("Question", backref="answers") #역참조

 

4. 모델 초기화

• pip install alembic
• alembic init migrations > migrations폴더와  alembic.ini파일 생성되는지 확인
• alembic.ini, migrations/env.py 내용 수정

# 1. alembic.ini
# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = migrations

<............생략.............>

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = sqlite:///./myapi.db #이부분 수정


[post_write_hooks]
# post_write_hooks defines scripts or Python functions that are run
# on newly generated revision scripts.  See the documentation for further
# detail and examples



# 2. env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
import models // 추가

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = models.Base.metadata #이부분 수정

 

5. db생성

• alembic revision --autogenerate: 생성
• alembic upgrade head: 반영
• sqlite gui확인