Flask restx를 사용하여 API를 구축할 일이 있어 ORM을 사용하여 데이터베이스와 연결하였다.
데이터베이스 연결 및 ORM을 사용할 수 있도록 제공되는 파이썬 라이브러리로는 SQLAlchemy가 있다.
SQLAlchemy를 사용하면 쿼리를 코드에서 실행할 수 있으며 ORM을 통해 데이터베이스와 연결 후 작업이 가능하다.
먼저 데이터베이스와 연결하는 작업이 필요하다.
가장 먼저 sqlalchemy.create_engine 함수를 이용하여 데이터베이스와 연결해 주는 엔진을 만들어야 한다.
나는 postgresql을 사용하고 있으며 "postgresql://db id:db pw@ip:port/db 이름"을 사용하여 엔진을 생성해주었다.
데이터베이스와의 소통을 관리해주는 세션을 로드하여 사용하여야 한다.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
db = create_engine(
"postgresql://db id:db pw@ip:port/db 이름"
)
base = declarative_base()
Session = sessionmaker(db)
session = Session()
base.metadata.create_all(db)위의 코드는 데이터베이스의 엔진 및 세션을 로드한 코드이다.
다음으로는 테이블을 정의 해야한다.
ORM 방식으로 데이터베이스의 테이블을 선언할 수 있다.
from database.postrgesql import session, base
from sqlalchemy import Column, String, Boolean, Integer, Float, DateTime, Sequence
class TableName(base):
__tablename__ = '<table name>'
a = Column(String(10), primary_key=True) # string type, primary key 지정
b = Column(Integer) # Int type
c = Column(Boolean) # Bool type
d = Column(Float) # float type
e = Column(DateTime) # datetime type
f = Column(Integer, Sequence('table name_id_seq')) # serial type엔진과 세션을 생성하면서 같이 생성한 base에서 DDL을 기본적으로 제공하고 있기 때문에 이를 사용하여 데이터베이스와 연결된 테이블 클래스를 만들 수 있다.
a부터 e까지 자주 쓰이는 str, int, float, bool, datetime 타입의 컬럼을 선언하였다. string 같은 경우는 길이까지 지정하여 선언할 수 있다.
그리고 f는 시리얼 타입으로 데이터베이스에서 튜플이 추가될 때 마다 자동으로 숫자가 +1 되는 변수형이다. sqlalchemy는 serial 타입을 지원하지 않기 때문에 Sequence를 사용하여 해당 컬럼이 시리얼 타입임을 명시할 수 있다.
SELECT
테이블 선언까지 완료하면 나는 flask와 연결하여 swagger를 사용하는 것이 목적이었기에 POST, GET, PUT, DELETE에 해당하는 쿼리를 작성하고 데이터베이스에 커밋을 해 주면 된다.
가장 먼저 데이터베이스의 정보를 가져오는 GET을 사용하기 위해 Select와 같은 역할을 하는 쿼리문을 생성할 수 있다.
# GET 함수
def get_table_name(name):
table_name = session.query(TableName).filter(TableName.name == name)
data = {테이블 내용}
return data위의 쿼리는 SQL에서
SELECT TN.name FROM TableName TN WHERE TN.name = name이와같이 쓸 수 있다.
아래는 새로운 정보를 추가할 수 있는 POST 함수이다.
def add_table_name(data):
name = TableName('추가할 테이블 튜플 내용)
session.add(name)
session.commit()
return 'Success'추가할 내용을 name으로 생성 후 session에 add, commit을 해 주어야 쿼리문이 데이터베이스에 적용된다.
다음은 특정 정보를 지울 수 있는 DELETE 함수이다.
def delete_table_name(name):
session.query(TableName).filter(TableName.name == name).delete(synchronize_session=False)
session.commit()
return 'Success'qeury.delete 후 commit을 해 주어야 쿼리문이 데이터베이스에 적용된다.
마지막으로 특정 컬럼 내용을 수정할 수 있는 PUT 함수이다.
def update_table_name(name, data):
session.query(TableName).filter(TableName.name == name).update({테이블 내용}, synchronize_session = False)
session.commit()
return 'Success'마찬가지로 업데이트 역시 query.update를 한 후 commit을 해야만 데이터베이스에 쿼리문이 적용된다.
CAST, UNION, LABEL
다음은 만든 SQL 쿼리문이다.
select a.name, cast(b.name as text) dl from A a, B b
where a.name = b.name
union
select a.name, cast(c.name1 as text) || ',' || cast(c.name2 as text) dl from A a, C c
where a.name = c.nameA, B, C 테이블에서 각각 필요한 컬럼만 뽑아내서 하나의 테이블로 만들고 싶었던 발버둥이었다.
처음엔 join을 쓸까 했지만 C 테이블의 컬럼이 B 테이블의 컬럼과 형태가 달랐기에 각각 필요한 컬럼을 select한 후 union을 사용하여 합쳤다.
이를 sqlalchemy로 변경한 쿼리문은 아래와 같다.
from sqlalchemy import String, func
A+B = session.query(A.name, func.cast(B.name, String).label('NAME')).filter(A.name==B.name)
A+C = session.query(A.name, func.concat(func.cast(C.name1, String), ',', func.cast(C.name2, String))).filter(A.name==C.name)
A+B+C = A+B.union_all(A+C)
A+B+C_list = A+B+C.all()SQL에서 cast는 sqlalchemy에서 sqlalchemy의 func을 이용하여 cast를 사용할 수 있다.
또, SQL에서의 concat이 postgresql에서는 사용할 수 없어 || 연산자를 사용하여 문자열들을 병합하였다. 이는 sqlalchemy에서 func의 concat을 사용하여 문자열 병합을 할 수 있다.
컬럼을 가지고 오면서 컬럼명을 변경하기 위해서는 SQL에서는 별 다른 쿼리문 추가 없이 변경할 컬럼명을 붙여주기만 하면 되었지만 sqlalchemy에서는 (컬럼).label('변경할 컬럼명')을 사용해야 한다.
이렇게 가져온 두 테이블을 병합하기 위해 사용한 union은 sqlalchemy에서는 <기존 테이블.union_all(합칠 테이블)>과 같은 형태로 union을 사용한다.
마지막으로 합친 테이블의 내용을 가지고 오기 위해 .all()을 사용하였다.
JOIN
SQL에서 조인을 사용하기 위해서는 중첩 where를 사용하는 등 쿼리문이 정말 많이 길어지는데
sqlalchemy에서는 굉장히 간단하게 join을 할 수 있다.
# A = 기존 테이블
# B = join 할 테이블
Name = A.qeury.join(A.b).filter(B.name==session.get('name')).all()위는 서로 다른 두개의 테이블(A,B)를 join하는 쿼리문이다.
join에는 inner join, outer join 등이 있는데 이는 각 테이블의 데이터 양을 고려하여 가장 적합한 방법을 사용하는 것이 좋다.
SQL JOIN 성능 비교 글 : https://wakestand.tistory.com/511
