[TIL]21.07.20 SQLalchemy

Seung Joo·2021년 7월 20일

TIL

목록 보기

27/31

1. 테이블 생성

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy import declarative_base, Session, relationship


# Base 생성
Base = declarative_base()
# 연결할 데이터 베이스로 엔진객체 생성
engine = create_engine("sqlite:///test.db")

class Student(Base):
    __tablename__ = "Student"
    # 테이블명을 정의하지 않으면 소문자로 class명이 테이블명이 됨
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String, name='Name', nullable=False)
    age = Column(Integer, name='Age'
    
    # relationship 정의
    score_rel = relationship('Score', backref='std_rel')
    
    def __repr__(self):
        return f"Student {self.id}: {self.name}"
    # repr 미정의시 쿼리요청에 User 객체를 반환
    # repr 정의시 __repr__문을 쿼리요청에 를 반환

class Score(Base):
    __tablename__ = 'Score'
    id = Column(Integer, primary_key=True)
    korean = Column(Integer)
    english = Column(Integer)
    math = Column(Integer)
    student_id = Column(Integer, ForeignKey('Student.id'))
    
    def __repr__(self):
        return f"Score {self.id}: student_id: {self.student_id}"
        
Base.metadata.drop_all(engine) # 테이블 데이터 모두 삭제
Base.metadata.create_all(engine) # 테이블 데이터 생성

📌Nullable = False 일 때 SQL의 NOT NULL 과 같은 뜻이다.
📌 PrimaryKey는 nullable을 설정해주지 않아도 NOT NULL이다.

✅relationship 정의

backref 파라미터 사용으로 한쪽에서만 다 대 일 관계 정의가능
Student table에서는 Student.score_rel
Score table에서는 Score.std_rel

2. 데이터 삽입

여러가지 방법이 있지만 가장 간단한 add를 사용했다.

# 작업할 세션 생성
session = Session(bind=engine)

# 학생 클래스 생성
brian = Student(name='brian', age=17)
dean = Student(name='dean', age=18)
alex = Student(name='alex', age=17)
# id를 지정하지 않아도 자동으로 저장순서의 id를 생성해줌

# 학생 데이터를 session에 저장
# session.add(brian) add는 하나씩 저장
session.add_all([brian, dean, alex])
# 세션에 저장된 내용 데이터 베이스에 적용
session.commit()

# 항상 작업 완료 후 세션 닫기
session.close()

✅Id를 지정하지 않아도 자동으로 Auto_increment 해준다.

📌dictionary 형태의 데이터 생성 및 삽입

mendez = {'name' : 'mendez', 'age' : 18}

session.add(Student(**mendez))
session.commit()
session.close()

2-1. ForeignKey 적용하여 Score 삽입

# 함수를 통해서 학생 이름을 통해 ForeignKey를 불러온다.
def create_score(kor_score, eng_score, math_score, name, session=session):
    std_id = session.query(Student).filter_by(name=name).one()
    std_id = std_id.id
    scores = Score(korean=kor_score, english=eng_score, math=math_score, student_id=std_id)
    session.add(scores)
    



# print(std_mendez)
score_1 = create_score(80, 90, 100, 'brian')
score_2 = create_score(70, 60, 50, 'alex')
score_3 = create_score(90, 100, 40, 'dean')
score_4 = create_score(100, 100, 95, 'mendez')

session.commit()
session.close()

잘 저장된 것을 볼 수 있다.

3. query 사용하여 읽어오기

# 전체 불러오기
stmt = session.query(Student).all()
for x in stmt:
    print(x.id, x.name, x.age)
    
# >>>
# 1 brian 17
# 2 dean 18
# 3 alex 17
# 4 mendez 18

# filter, filter_by의 사용
# SQL의 WHERE로 생각하면 됨

stmt = session.query(Student).filter(Student.name=='brian').one()
stmt2 = session.query(Student).filter_by(name='brian').one()
print('stmt :', stmt)
print('stmt2 :', stmt2)

# >>>
# stmt : Student 1: brian
# stmt2 : Student 1: brian

# join 사용
stmt3 = session.query(Student, Score).join(Score.std_rel)
stmt3_ = session.query(Student, Score).join(Score.std_rel).all()
print(stmt3)
print(stmt3_)
for x in stmt3:
    print(x.Score.english)
# >>>
# stmt3 : SELECT "Student"."Name" AS "Student_Name", "Student"."Age" AS "Student_Age",
# "Student".id AS "Student_id", "Score".id AS "Score_id", "Score".korean AS "Score_korean",
# "Score".english AS "Score_english", "Score".math AS "Score_math",
# "Score".student_id AS "Score_student_id"
# FROM "Score" JOIN "Student" ON "Student".id = "Score".student_id

# stmt3_ : [(Student 1: brian, Score 1: student_id: 1),
# (Student 3: alex, Score 2: student_id: 3),
# (Student 2: dean, Score 3: student_id: 2), 
# (Student 4: mendez, Score 4: student_id: 4)]

# brian : english : 90
# alex : english : 60
# dean : english : 100
# mendez : english : 100

이외에 order_by등 다양한 함수가 존재하므로 SQLAlchemy Query API docs참조

4. 데이터 업데이트

# 데이터 베이스에서 해당하는 위치의 데이터를 가져와서 이름 수정
def update_student_name(student_id, new_name, session=session):
    student = session.query(Student).filter_by(id = student_id).first()
    student.name = new_name
    session.commit()

update_student_name(1, 'mayer')

잘 변경된 것을 확인할 수 있다.

5. 데이터 삭제

# 데이터 베이스에서 해당하는 위치의 데이터를 가져와서 삭제
def delete_student(student_id, session=session):
    del_std = session.query(Student).filter_by(id=student_id).one()
    session.delete(del_std)
    session.commit()

delete_student(3)