SQLAlchemy ORM(postgresql 연결) | 파이썬에서 ORM 사용하기, SELECT, JOIN, CAST, UNION, LABEL 등 자주 쓰는 문법 정리

 

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.name

A, 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 등이 있는데 이는 각 테이블의 데이터 양을 고려하여 가장 적합한 방법을 사용하는 것이 좋다.

https://doh-an.tistory.com/30

SQL JOIN 성능 비교 글 : https://wakestand.tistory.com/511

 

---

참고 링크