flask 웹개발 기초 정리 - 3 (feat. SQLAlchemy)

SQLAlchemy 계속

ORM Query

전 게시글에서 다뤘듯이 클래스명으로 쿼리에 접근하거나 세션으로 쿼리를 접근하든, 아래의 쿼리 실행문은 동일한 결과를 도출한다. 

Class.query.filter...
db_session.query(Class).filter...

 

filter 를 줄 때 변수명 앞에 : 을 사용하여 아래처럼 문자열 SQL 조건문을 작성해줄 수 있다.

User.query.filter("id < :val").params(val=10)

:변수명 형태로 사용하여 params 메서드에 인자로 받고 값을 대입해줄 수 있다.

filter 와 filter_by

둘의 쓰임새는 조건문을 처리해주는 것이다. 다만 filter 메서드는 인자로 Column 객체나 표현식을 받아서 처리해줘야 하지만 filter_by 메서드는 인자 **kwargs 로 Column의 정보를 받아와서 처리해준다.

q1 = session.query(Class).filter_by(name = 'Kim')
q2 = session.query(Class).filter(Class.name == 'Kim')

 그러므로 아래처럼 동적으로 Column 명이 바뀌게 되면 filter_by를 사용할 수 없게 된다.

col = 'name'

q1 = session.query(Class).filter_by(col = 'Kim') # X
q2 = session.query(Class).filter(getattr(Class, col) === 'Kim') # O

 

조건식을 직접 주지 않고 아래와 같이 이미 구현된 내용을 조회할 수 있습니다.

Class.query.all() # 모든 정보
Class.query.one() # 하나
Class.query.first() # 가장 처음 정보
Class.query.count() # 레코드 수

 

 

SQLAlchemy Foreign key and Relationship + Join

from sqlalchemy import Column, ForeignKey
from sqlalchemy.orm import relationship, backref

class Album(Base):
    __tablename__='album'
    
    albumid = Column(Integer, primary_key=True)
    
    
class Song(Base):
    __tablename__ = 'song'
    
    songno = Column(String, primary_key=True )
    title = Column(String)
    genre = Column(String)
    albumid = Column(Integer, ForeignKey('album.albumid'), nullable=False)
    likecnt = Column(Integer)
    album = relationship('Album', backref=backref('album'))

노래와 앨범의 class 를 만들고자 할 때 앨범과 노래 사이의 관계는 앨범 하나에 노래가 여러개 있을 수 있고 노래는 앨범 하나에만 속하게 되는 다대일 관계이다. 관계를 표현하고자 할 때 ORM 에선 relationship 으로 표기해줘야 한다. 다쪽이든 일쪽이든 어느쪽에서든 정의해줘도 상관없으나 보통 다쪽에다가 걸어주면 굳이 리스트로 만들 필요가 없어지므로 위처럼 song 안에 album relationship 이 존재하는 것이다. relationship으로 만들어준 변수를 사용하여 해당 테이블의 정보에 접근할 수 있게 된다.

backref

양방향 연결을 위해 추가해줘야 하는 인자이다. 기존에는 관계를 맺고 있는 클래스 간 모두 back_populates 를 서로의 클래스에 지정해줘야 하지만, backref 를 사용하면 관계의 한쪽에만 적용시켜도 된다. 관계된 매퍼 클래스 속성의 문자열 이름을 가리키며 매퍼 클래스의 생성과 함께 자동 생성된다. 

 

이렇게 지정해준 관계성은 나중에 Song 정보를 읽어오거나 사용해줄 때 마지막에 관계로 넣어주었던 album 을 통해 Album 클래스의 내용에 접근할 수 있게 된다. 즉 Join 이 되는 것이다. 하지만 터미널을 살펴보면 매 결과마다 Join 처리가 되어 비효율적으로 접근횟수가 많아지게 된다. 

 

subqueryload and joinedload

위에서 소개한 가장 기본적인 Join 방식은 비효율적인 조회 횟수가 발생함을 알 수 있다. 이를 개선하기 위해 사용되는 개념들이다.

from sqlalchemy.orm import subqueryload, joinedload

 

@app.route('/presql')
def presql():
    ret = Song.query.options(subqueryload(Song.album)).filter(Song.likecnt < 20).all()
    # ret = Song.query.options(joinedload(Song.album)).filter(Song.likecnt < 20).all()

    return str(ret)

간단하게 처리한 위의 결과값은 Join 의 결과값과 동일하다. 달라진 점은 options 속성에 subqueryload 안에 relationship 값을 지정해준 것이다.

subqueryload 와 joinedload 의 차이점은 무엇을 베이스로(서브쿼리냐 조인이냐) 삼느냐의 차이이며 결과값은 동일하나 미리 관계를 알려줘서 조회 횟수를 줄여줄 수 있다.

joinedload

연관된 여러 테이블을 한번의 쿼리로 가져오기 위한 옵션

subqueryload

서브쿼리를 불러올 때 한번에 연계하여 불러오는 옵션

 

References exists / create - 1:N

노래와 앨범 사이의 관계는 앨범이 존재해야만 노래가 생길 수 있던 일대다 관계였다. 그럼 새노래를 추가하기 위해선 무슨 테이블에 먼저 데이터가 들어가야 할까? 당연히 앨범 정보가 들어간 다음 노래 정보가 들어가야 할 것이다. 

a1 = Album.query.filter(Album.albumid == '123').one()
song1 = Song(songno=56677, title='노래이름3')
song1.album = a1

db_session.add(song1)
db_session.commit()

위 코드는 이미 존재하는 앨범에 새로운 노래를 넣어주고 있다. 그럼 존재하지 않는 앨범에 새로운 노래를 넣어주려면?

a1 = Album(albumid = '78777', title='앨범이름')
song1 = Song(songno=31, title='노래이름')
song1.album = a1

db_session.add(song1)
db_session.commit()

이렇게 직접 Album 클래스로 생성해주고 Song 테이블에 넣어주면 된다. 결론적으로 아래와 같이 filter 해준 내용의 count 를 읽어와서 결과값이 없으면 0으로 처리해주고 새로운 앨범을 생성해준다. 있으면 그대로 결과값을 읽어오는 로직을 추가해줘서 마무리 한다.

a1 = Album.query.filter(Album.albumid == '3533')

if a1.count() == 0:
    a1 = Album(albumid='A_88', title='앨범명')
else:
	a1 = a1.one()
    
song1 = Song(songno=56677, title='111커리111')
song1.album = a1

db_session.add(song1)
db_session.commit()

 

M:N

기본적으로 관계형 데이터베이스에서 다대다 관계를 표현할 수 없다. 그래서 중간에 교차 테이블을 두어 일대다 형태로 만들어주는데 피치 못할 사정으로 다대다 관계를 만든다면 아래처럼 지정이 가능하다. __table_args__ 는 __tablename__과 같이 테이블에 관련된 설정들을 담을 수 있는 특별한 속성이다.

class SongArtist(Base):
    __tablename__ = 'SongArtist'
    
    songno = Column(String, ForeignKey('Song.songno'), nullable=False)
    artistid = Column(String, ForeignKey('Artist.artistid'))
    atype = Column(Integer)
    
    artist = relationship('Artist')
    
    __table_args__ = (PrimaryKeyConstraint('songno', 'artistid', 'atype'), {})

 

PrimaryKeyConstraint

다대다 관계를 받는 맵핑 테이블이 SongArtist 이므로 해당 클래스는 각각 Song 과 Artist 에서 가져온 PK를 FK, PK로 만든 복합키를 사용하게 된다. 그때 사용하게 되는 속성 중 하나이다.

 

---

SQL Injection

악의적인 사용자가 SQL문을 임의로 주입하고 실행되게 하여 DB가 비정상적으로 동작하도록 조작하는 행위를 의미한다. 공격이 비교적 쉽고 큰 피해를 줄 수 있다고 한다.

논리적 방식

Or 1 = 1 -- 구문 같이 무조건 참으로 만들어주는 구문을 SQL문 중간에 삽입하여 로그인이 무조건 되게끔 처리해줄 수 있다. -- 은 주석으로 만드는 부분으로 패스워드 앞에 들어가게 되면 무용지물로 로그인이 될 수밖에 없을 것이다.

select * from Users where id = 'XXX' <<or 1=1 -->> and pw='...';

그외에도 Union 을 통한 쿼리문 합성으로 개인 정보를 확인할 수 있게 처리하는 등 다양한 방법이 존재한다고 한다. 하지만 SQLAlchemy 를 사용하면 이러한 위험성은 줄어든다고 한다.

 

File upload

위의 SQL Injection 은 파일 업로드 등에서도 충분히 일어날 수 있다. 그래서 플라스크측에서 만들어진 secure_filename 함수를 통해 파일을 업로드를 안전히 구현할 수 있다.

 

from werkzeug.utils import secure_filename

upfile = request.files['file']
filename = secure_filename(upfile.filename)

우선 request의 files 객체에서 업로드된 파일을 읽어올 수 있다(이때 form 태그 안에 name 값으로 담아준 게 바로 읽어올 수 있는 키가 됨). 이때 요청 메서드는 POST 여야 한다. 일전에 배웠듯이 POST의 경우 HTTP Body 에 담긴 정보를 Form Data로 담아주기 때문에 request.form.get() 으로 가져온다.

html 템플릿을 만들기는 귀찮아서 일단은 플라스크로 업로드하는 간단한 코드를 올린다.

@app.route('/viewupload')
def viewupload():
    return '<div> \
                <form action="http://localhost:5000/upload" method="POST" enctype="multipart/form-data">\
                    <input name="file" type="file"/> \
                    <input type="submit" value="업로드" /> \
                </form>\
            </div>'


@app.route('/upload', methods=['POST'])
def upload():
    upfile = request.files['file']

    filename = secure_filename(str(upfile))
    path = os.path.join("./helloflask/static/upfiles/"+ filename)
    
    upfile.save(path)
    return jsonify({"path": path})

한가지 주의할 점은 secure_filename의 인자에는 문자열이 들어가줘야 한다. 위처럼 적당히 바꿔줘도 정상작동함을 확인했다. 그리고 받아준 upfile은 FileStorage 클래스의 일부라 save 를 사용하여 지정해준 경로(여기선 path)에 넣어주면 된다. 반환값으론 jsonify() 를 사용하여 경로만 보이게 설정하였다.

잘 저장된다

def rename(path):
    while True:
        if os.path.isfile(path):
            idx = path.rindex('.')
            if idx == -1:
                path += '_1'
            else:
                underbaridx = path.rindex('_')
                num = path[underbaridx:idx]
                if type(int(num)) is int:
                    path = path[:underbaridx] + num + path[idx:]
                else:
                    path[:underbaridx] + '1' + path[idx:]
        else:
            return path

추가적으로 중복 파일을 올리게 되어 저장할 때 이름이 중복된다면 위와 같은 코드를 쓰면 될거다 아마... 파일명_숫자.확장자 의 규칙으로 대애강 만들어봤다.

이렇게 저장해준 파일을 DB측에 저장해주면 된다.

 

File Download

경로상에 존재하는 파일을 보내줄 때 사용하는 함수는 아래와 같다. as_attachment 를 True로 해주면 브라우저딴에서 바로 다운로드가 가능해진다.

send_file(path, as_attachment=True)

 

@app.route('/save')
def save():
    path = os.path.abspath('./helloflask/static/upfiles/FileStorage_.jpg_image_jpeg')
    return send_file(path, as_attachment=True)

path의 경우는 상대경로뿐만 아니라 내 경우처럼 절대경로도 가능하므로 편한 방식으로 하자.