앞서 작성한 SQLAlchemy 시작하기 – Part 1에서 이어지는 번역이다.
(여기서 뭔가 모자란 부분이나 틀린게 있으면 틀린게 맞으므로 언제든 지적해주시고, 애매한 표현은 원본 문서를 봐주시면 감사하겠습니다. 원본 문서는 SQLAlchemy Tutorial. 한글로 된 sqlalchemy 튜토리얼 있으면 알려주세요!)
리스트와 Scalars 반환하기
Query
객체의 all()
, one()
, first()
메소드는 즉시 SQL을 호출하고 non-iterator 값을 반환한다. all()
은 리스트를 반환한다.
query = session.query(User).filter(User.name.like('%air')). order_by(User.id)
query.all()
# [<User('haruair', 'Edward Kim', '1234')>, <User('wendy','Wendy Williams', 'foobar')>]
first()
는 첫째를 리밋으로 설정해 scalar로 가져온다.
query.first()
# <User('haruair', 'Edward Kim', '1234')>
one()
은 모든 행을 참조해 식별자를 값으로 가지고 있지 않거나 여러 행이 동일한 값을 가지고 있는 경우 에러를 만든다.
from sqlalchemy.orm.exc import MultipleResultsFound
try:
user = query.one()
except MultipleResultsFound, e:
print e
from sqlalchemy.orm.exc import NoResultFound
try:
user = query.filter(User.id == 99).one()
except NoResultFound, e:
print e
문자로 된 SQL 사용하기
문자열을 Query
와 함께 유연하게 쓸 수 있다. 대부분 메소드는 문자열을 수용한다. 예를 들면 filter()
와 order_by()
에서 쓸 수 있다.
for user in session.query(User).\
filter("id<224").\
order_by("id").all():
print user.name
연결된 파라미터에서는 콜론을 이용한, 더 세세한 문자열 기반의 SQL를 사용할 수 있다. 값을 사용할 때 param()
메소드를 이용한다.
session.query(User).filter("id<:value and name=:name").\
params(value=1234, name='fred').order_by(User.id).one()
문자열 기반의 일반적인 쿼리를 사용하고 싶다면 from_statement()
를 쓴다. 대신 컬럼들은 매퍼에서 선언된 것과 동일하게 써야한다.
session.query(User).from_statement(
"SELECT * FROM users WHERE name=:name").\
params(name='haruair').all()
또한 from_statement()
아래와 같은 문자열 SQL 방식으로도 쓸 수 있다.
session.query("id", "name", "thenumber12").\
from_statement("SELECT id, name, 12 as "
"thenumber12 FROM users WHERE name=:name").\
params(name='haruair').all()
문자열 SQL의 장단점
Query
로 생성해서 쓰는건 sqlalchemy의 이점인데 그렇게 쓰지 않으면 당연히 안좋아지는 부분이 있다. 직접 쓰면 특정하게 자기가 필요한 결과물을 쉽게 만들어낼 수 있겠지만 Query
는 더이상 SQL구조에서 아무 의미 없어지고 새로운 문맥으로 접근할 수 있도록 변환하는 능력이 상실된다.
예를 들면 User
객체를 선택하고 name
컬럼으로 정렬하는데 name이란 문자열을 쓸 수 있다.
q = session.query(User.id, User.name)
q.order_by("name").all()
지금은 문제 없다. Query
를 쓰기 전에 뭔가 멋진 방식을 사용해야 할 때가 있다. 예를 들면 아래처럼 from_self()
같은 고급 메소드를 사용해, 사용자 이름의 길이가 다른 경우를 비교할 때가 있다.
from sqlalchemy import func
ua = aliased(User)
q = q.from_self(User.id, User.name, ua.name).\
filter(User.name < ua.name).\
filter(func.length(ua.name) != func.length(User.name))
Query
는 서브쿼리에서 불러온 것처럼 나타나는데 User
는 내부와 외부 양쪽에서 불러오게 된다. 이제 Query
에게 name으로 정렬하라고 명령하면 어느 name을 기준으로 정렬할지 코드로는 예측할 수 없게 된다. 이 경우에는 바깥과 상관없이 aliased된 User
를 기준으로 정렬된다.
q.order_by("name").all()
# [(3, u'fred', u'haruair'), (4, u'haruair', u'mary'), (2, u'mary', u'wendy'), (3, u'fred', u'wendy'), (4, u'haruair', u'wendy')]
User.name
또는 ua.name
같이 SQL 요소를 직접 쓰면 Query
가 알 수 있을 만큼 충분한 정보를 제공하기 때문에 어떤 name을 기준으로 정렬해야할지 명확하게 판단하게 된다. 그래서 아래 두가지와 같은 차이를 볼 수 있다.
q.order_by(ua.name).all()
# [(3, u'fred', u'haruair'), (4, u'haruair', u'mary'), (2, u'mary', u'wendy'), (3, u'fred', u'wendy'), (4, u'haruair', u'wendy')]
q.order_by(User.name).all()
# [(3, u'fred', u'wendy'), (3, u'fred', u'haruair'), (4, u'haruair', u'wendy'), (4, u'haruair', u'mary'), (2, u'mary', u'wendy')]
숫자세기
Query
는 count()
라는 숫자를 세는 편리한 메소드를 포함한다.
session.query(User).filter(User.name.like('haru%')).count()
count()
는 몇개의 행이 반환될지 알려준다. 위 코드로 생성되는 SQL을 살펴보면, SQLAlchemy는 항상 어떤 쿼리가 오더라도 거기서 행의 수를 센다. SELECT count(*) FROM table
하면 단순해지지만 최근 버전의 SQLAlchemy는 정확한 SQL로 명시적으로 판단할 수 있는 경우 추측해서 처리하지 않는다.
숫자를 세야 할 필요가 있는 경우에는 func.count()
로 명시적으로 작성하면 된다.
from sqlalchemy import func
session.query(func.count(User.name), User.name).group_by(User.name).all()
# [(1, u'fred'), (1, u'haruair'), (1, u'mary'), (1, u'wendy')]
SELECT count(*) FROM table
만 하고 싶으면
session.query(func.count('*')).select_from(User).scalar()
User의 primary key를 사용하면 select_from 없이 사용할 수 있다.
session.query(func.count(User.id)).scalar()
관계(relationship) 만들기
이제 User
와 관계된, 두번째 테 이블을 만들 것이다. 계정당 여러개 이메일 주소를 저장할 수 있게 만들 것이다. users 테이블과 연결되는, 일대다 테이블이므로 테이블 이름을 addresses라고 정하고 전에 작성했던 것처럼 Declarative로 address
클래스를 작성한다.
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", backref=backref('addresses', order_by=id))
def __init__(self, email_address):
self.email_address = email_address
def __repr__(self):
return "<Address('%s')>" % self.email_address
위 클래스는 ForeignKey
를 어떻게 만드는지 보여준다. Column
에 직접 넣은 지시자는 이 컬럼의 내용이 대상된 컬럼을 따르도록 만든다. 이 점이 관계 데이터베이스의 주요 특징 중 하나인데 풀과 같은 역할을 해, 연결되지 않은 테이블 사이를 잘 붙여준다. 위에서 작성한 ForeignKey
는 addresses.user_id
컬럼이 users.id
컬럼을 따르도록 만든다.
두번째 지시자인 relationship()
은 ORM에게 Address
클래스 자체가 User
클래스에 연결되어 있다는 사실을 Address.user
속성을 이용해 알 수 있게 해준다. relationship()