[python3] Flask-SQLAlchemy 사용 시 json 으로 데이터 가공하기

웹크롤링 할게 많을거 같아 항상 쓰던 자바대신 python을 이용했다.

이전에는 django를 이용했는데 사용하면서 느낀점은 잘되어있지만 커스텀하게 쓰기엔 조금 무거운 감이 있었다.

알아야할 것도 많기도 하고.

그래서 이번에는 보다 가벼운 flask를 적용하기로 하고, 거기에 ORM 방식으로 데이터접근하면 좋을거 같아 찾다가 발견한 것이 SQLAlchemy 이다.

SQLAlchemy를 사용하는 방법은 인터넷에 이미 충분히 있으니 여기서는 json으로 변환하여 리턴하는 것에만 집중했다.

json으로 변환하려는 이유는 데이터를 주고받는데 json형태가 가장 편하고, 자바스크립트에서도 데이터를 접근하거나 조작하는데 유용하기 때문이다. 그리고 ajax를 이용해 json 형태로 데이터를 주고받을수도 있기 때문에 여러모로 장점이 있다.

SQLAlchemy 를 이용하면 데이터형식이 list로 반환된다.

# 아래 문법은 like절로 검색하는 것

result = stock.query.filter(stock.name.like('%'+search_text+'%')).all()

print(result)

print(type(result))

결과

검색어:  삼성

[<stock 000810>, <stock 001360>, <stock 005930>, <stock 006400>, <stock 006660>, <stock 009150>, <stock 010140>, <stock 016360>, <stock 018260>, <stock 028050>, <stock 028260>, <stock 029780>, <stock 032830>, <stock 068290>, <stock 207940>, <stock 291230>, <stock 309930>]

<class 'list'>

결과에 대한 type을 보면 list형태로 되어있음을 확인할 수 있다. 처음 말한바와 같이 json형태로 변환을 시켜주기 위해선 다양한 방법이 있는데, 구글검색에서 sqlalchemy result json 으로 검색했을때 아래 사이트가 맨 상단에 차지했다. 그외에도 여러개를 찾아본 결과 비슷한 패턴을 발견할 수 있었는데 다음과 같이 encoder 클래스나 def를 두는 것이었다.

import decimal, datetime

def alchemyencoder(obj):

    """JSON encoder function for SQLAlchemy special classes."""

    if isinstance(obj, datetime.date):

        return obj.isoformat()

    elif isinstance(obj, decimal.Decimal):

        return float(obj)

def example():

    res = conn.execute(select([accounts]))

    # use special handler for dates and decimals

    return json.dumps([dict(r) for r in res], default=alchemyencoder)

일단 똑같이 따라해봤는데 잘 안되었다. 그리고 encoder라는 것을 이용한다는것이 썩 맘에들진 않았다.

그래서 좀더 찾아보니 pandas를 이용해 변환한 다음, json으로 변환하는 것이 있었다. 

방법은 다음과 같다.

import pandas as df

queryset = stock.query.filter(stock.name.like('%'+search_text+'%'))

# 위와 달라진게 있다면 끝에 .all() 이 없다

df = pd.read_sql(queryset.statement, queryset.session.bind)

print(json.loads(df.to_json()))

json으로는 잘 변환되었는데 문제는 또 있었다. 

DBMS를 자주 다뤄본 사람들은 List 형태 안에 오브잭트 단위로 들어있는것에 익숙할 것이다.

그런데 위의 결과는 컬럼별로 List를 만들어서 담는 형태였다.

{  

   'code':{  

      '0':'000810',

      '1':'001360',

      '2':'005930',

      ...

   },

   'name':{  

      '0':'삼성화재해상보험',

      '1':'삼성제약',

      '2':'삼성전자',

      ...

   }

}

그러나 내가 원하는 바는 다음형태다

[

{'code':'000810', 'name': '삼성화재해상보험'},

{'code':'001360', 'name': '삼성제약'},

...

]

그래서 좀더 살펴보니 옵션으로 orient='records' 를 넣으면 원하는 형태로 json이 구성된다.

import pandas as df

queryset = stock.query.filter(stock.name.like('%'+search_text+'%'))

# 위와 달라진게 있다면 끝에 .all() 이 없다

df = pd.read_sql(queryset.statement, queryset.session.bind)

# 옵션으로 orient 를 준다

print(json.loads(df.to_json(orient='records')))

[  

   {  

      'code':'000810',

      'name':'삼성화재해상보험'

   },

   {  

      'code':'001360',

      'name':'삼성제약'

   },

   ...

]

~~~

참조:

https://beomi.github.io/2017/10/21/SQLAlchemy-Query-to-Pandas-DataFrame/

https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json

http://codeandlife.com/2014/12/07/sqlalchemy-results-to-json-the-easy-way/