SQLAlchemy, ORM

1. ORM

• ORM (Object Relational Mapping)이란? : 객체-관계 맵핑
  

• 객체 지향 프로그래밍 언어를 사용하여 호환되지 않는 type 시스템 간에 데이터들을 변환하기 위한 프로그래밍 기술

• 메모리에서의 클래스(객체 지향 프로그래밍)와 관계형 데이터베이스의 테이블은 서로 일치하지 않으므로 ORM을 통해 해결

왜 ORM을 사용해야하는가?

• 객체 모델과 관계형 데이터베이스 간의 불일치
  = 테이블 구조가 변경될 때 ORM 모델만 수정하면 됨 → 클래스 내부 프로퍼티만 수정
• ORM은 단순한 반복적인 데이터베이스 쿼리를 다루는 것으로부터 프로그래머를 자유롭게 한다.
• 데이터베이스를 비즈니스 객체에 자동으로 매핑 한다.
• 프로퍼티의 값을 변경하는 것만으로 데이터베이스의 값을 바꿀 수 있다.
• 프로그래머들은 비즈니스 문제에 더 많은 관심을 가지고 데이터 저장은 줄인다.
• 매핑 프로세스는 데이터베이스에 도달하기 전에 데이터 검증 및 보안을 지원할 수 있다.
• ORM은 데이터베이스 위에 캐싱 계층을 제공할 수 있다.
• 단점 : 처리 오버 헤드가 잠재적으로 증가

SQL쿼리 vs ORM

출처: Elice

ORM in Python

• ORM을 사용하면 개발자가 SQL 대신 python 코드로 생성, 읽기, 수정 및 삭제 할 수 있다.
• 개발자는 SQL문을 쓰거나 프로시저를 작성하는 대신에 데이터베이스를 사용하여 편한 프로그래밍 언어를 사용할 수 있다.
• ORM을 사용하여 이론적으로 다양한 관계형 데이터베이스 간에 응용 프로그램을 바꿀 수 있다.
• 그렇지만 연습으로는, 실제로 사용되는 것과 동일한 데이터베이스를 로컬에서 개발하는 것이 가장 좋다.
  

2. SQLAlchemy

SQLAlchemy란

• 파이썬 ORM 라이브러리 -> 파이썬 코드에서 데이터베이스와 연결하기 위해 사용할 수 있는 라이브러리
• SQLAlchemy은 잘 알려진 데이터베이스 툴킷이며 ORM 구현은 파이썬으로 작성되었다.
• SQLAlchemy는 SQL문을 작성할 필요없이 데이터베이스에 독립적인 코드를 작성하고 실행하기 위한 일반화 된 인터페이스를 제공한다.

SQLAlchemy ORM 사용법

SELECT

session.query(Model).all() # SELECT * FROM model
session.query(Model.id, Model.name, Model.age).all() # SELECT id, name, age FROM model
session.query(Model).first() # SELECT * FROM model LIMIT 1

from sqlalchemy import func # count 함수
session.query(func.count(Model.id))
# SELECT COUNT(id) FROM model

# AS 
session.query(Model.id.label('model_id')).all()
# SELECT id AS model_id FROM model

WHERE

• filter를 사용한다.

session.query(Model).filter(Model.name == 'lowell').all() 
# SELECT * FROM model WHERE name = 'lowell'

session.query(Model).filter(Model.name == 'lowell', Model.age == 20).all() 
# SELECT * FROM model WHERE name = 'lowell' AND age = 20

from sqlalchemy import or_ # OR 연산자
session.query(Model).filter(or_(Model.mame == 'lowell', Model.age == 20)).all()
# SELECT * FORM model WHERE name = 'lowell' OR age = 20

INSERT

user = Model(name='lowell', age=20)
session.add(user)
session.commit() # session.rollback()

# INSERT INTO model(name, age) VALUES ('lowell', 20)
# COMMIT

UPDATE

• 방법 1

user = session.query(Model).filter(Model.name == 'lowell').first()
user.age += 1
session.commit()

# select 후 update 함

• 방법 2

user = session.query(Model).filter(Model.name == 'lowell').update({'age': User.age + 1});
session.commit()

# select를 하지 않는 update 방법
# UPDATE model SET age = age + 1 WHERE name = 'lowell'

DELETE

user = session.query(Model).filter(Model.name == 'lowell').first()
session.delete(user)
session.commit()

ORDER BY

session.query(Model).filter(Model.name == 'lowell').order_by(Model.created_at)
# SELECT * FROM model WHERE name = 'lowell' ORDER BY created_at

session.query(Model).filter(Model.name == 'lowell').order_by(Model.created_at.desc(), Model.status) 
# SELECT * FROM model WHERE name = 'lowell' ORDER BY created_at DESC, status

JOIN

• INNER JOIN

session.query(Model1, Model2).filter(Model1.id == Model2.id).all() 
# SELECT * FROM model1 JOIN model2 ON model1.id = model2.id

session.query(Model1).join(Model2, Model1.id == Model2.id).all()
# SELECT * FROM model1 JOIN model2 ON model1.id = model2.id

• OUTER JOIN

session.query(Model1). \
    outerjoin(Model2, Model1.id == Model2.id).\
    all()
    
# SELECT * FROM model1 LEFT JOIN model2 ON model1.id = model2.id

• 여러개 조인을 하기 이해선 그냥 이어 붙이면 된다.

session.query(Model1.name, Model2.student_id, Model3.account).\
	outerjoin(Model2, Model1.id == Model2.id).\
	outerjoin(Model3, Model1.id == Model3.id).\
	all()
	
# SELECT model1.name, model2.student_id , model3.account 
# FROM model1
# LEFT JOIN model2 ON model1.id = model2.id
# LEFT JOIN model3 ON model1.id = model3.id

• SELF JOIN: 하나의 테이블을 조인하는 것
  aliased 를 사용

model2 = aliased(Model)

self.session.query(Model).\
    join(model2, model2.id == Model.id).\
    all()
    
# SELECT model.* FROM model JOIN model model2 ON model2.id = model.id;

GROUP BY

session.query(Model).group_by(Model.id).all()

# SELECT * FROM model GROUP BY id

SUBQUERY

from sqlalchemy import subquery

stmt = query.session(Model2).filter(Model2.grade == 'A').subquery() 
# SELECT id, grade FROM model2 WHERE grade = 'A'

session.query(Model1, stmt.c.id, stmt.c.grade).\
	outerjoin(stmt, stmt.c.id = Model1.id)
	
# SELECT model1.*, model2.id, model2.grade
# FROM mode1l LEFT JOIN (SELECT id, grade FROM model2 WHERE grade = 'A') model2 
# ON model1.id = model2.id

그 외 팁

CASE 문

from sqlalchemy import case
	
session.query(
	case(
        	[
            	(Model.age >= 20, 'adult'),
                (Model.age >= 10, 'teenager')
        	], 
        	else_='not adult, not teenager'
    	)
    ).\
    filter(Model.sex='female').\
    all()

# SELECT CASE WHEN age >= 20 THEN 'adult' WHEN age >= 10 THEN 'teenager' ELSE 'not adult, not teenager' FROM model WHERE sex = 'female';

last_row_id 얻기

user = Model(name='lowell', age=20)
session.add(user)
session.flush() # DB connection 일어남

id = user.id # auto_encrement로 생성된 id

session.commit()

검색 (LIKE)

results = session.query(Model).\
	filter(Model.name.like('김%')).all() # 성이 김씨인 사람 찾음
    
# 응용 

keyword = kwargs.get('keyword', '')
search = True 

if keyword:
    search = Model.name.like(f'{keyword}%')
    
results = session.query(Model).\
	filter(search).all() # 검색어가 있으면 검색, 없으면 모두 가져옴

IN

session.query(Model).filter(Model.name.in_(('lowell', 'yejin'))).all() 
# SELECT * FROM model WHERE name IN ('lowell', 'yejin');

NOT IN

session.query(Model).filter(~Model.name.in_(('lowell', 'yejin'))).all() 
# SELECT * FROM model WHERE name NOT IN ('lowell', 'yejin');

COMMIT, ROLLBACK

session.commit() # commit
session.rollback() # rollback

DATE 계산하기 (DATE_ADD)

today = datetime.datetime.now().strftime('%Y-%m-%d')
session.query(Model).filter(today >= func.ADDDATE(Model.created_at, 30)).all()
# SELECT * FROM model WHERE NOW() >= DATE_ADD(created_at, INTERVAL 30 DAY);

https://lowelllll.github.io/til/2019/04/19/TIL-flask-sqlalchemy-orm/