SQLite3로 데이터 베이스를 다루는 연습-3-

안녕하세요?

이번 포스팅에서는 SQLite를 이용해서, 데이터 베이스에 데이터를 집어 넣는데, 문제는 파이썬을 이용해서 DataFrame형식으로 된 데이터를 데이터베이스 파일에 집어넣는 연습을 하고자 합니다. 왜 이런 연습이 필요한가 하면, 가만히 보니까, 데이터를 분석하고 하려면 사실상 DataFrame형태로 가지고 오는 수 밖에 없는데, 그러면 이 데이터 역시 데이터 베이스에 집어 넣을 수도 있어야 합니다.

이번 실습에서는 이전에 만들어 둔 적이 있는 kospi.db에 있는 stocktest라는 테이블을 사용해 보도록 합니다. 일단 확인차원에서 열어 보기는 했습니다만, 여전히 아무런 데이터도 없는 것을 볼 수 있습니다.

일단 본격적인 작업을 하기 전에 conda install pandas라고 아나콘다 프롬프트에서 입력해 주도록 합니다. 실수로 베이스로 깔려 있는 가상환경이 아니라, 작업을 진행하고자 하는 가상환경인 PyQtTest에 설치해 주도록 합니다.

이제 jupyter qtconsole이라고 입력을 해서 위 스크린샷처럼 Jupyter QtConsole을 가지고 오도록 하며, 위 스크린샷처럼 먼저 pandas를 import하도록 합니다. 그리고 나서 pandas에 있는 Series와 DataFrame을 가지고 오도록 하며, 3번째 줄에서 했는 것 처럼 raw_data를 지정해 주도록 합니다. 그리고 마지막으로 4번째 줄에서 처럼 df=DataFrame(raw_data) 라는 명령어로 데이터프레임 객체로 만들어 주도록 합니다.

작업이 제대로 되었는지 알아보기 위해서 한번 df라고 입력을 해본결과, 제대로 column의 이름이 나오고 있으며, 값이 입력이 된 것을 확인할 수 있습니다. 물론 이 상태에서는 메모리상에서만 존재하고 있기 때문에, 어떤 파일에 들어가 있는 상태가 아니기는 합니다. 그래서 이제 데이터 베이스의 파일에 넣는 작업이 필요합니다.

먼저 sqlite3 모듈을 import해 오도록 하며, 다음으로 데이터 베이스의 파일이 있는 kospi.db의 경로와 이어주는 변수인 con을 정의해 주도록 합니다.

그리고 그냥 df.to_sql(테이블 이름, con) 이라고 입력을 해서 DataFrame객체를 데이터베이스 파일에 집어 넣으려고 했으나,  위 스크린샷처럼 에러가 발생하는 것을 볼 수 있었습니다.

무슨 에러인가 해서 에러로그를 자세히 보니까, ValueError이라고 하면서 제가 지정한 테이블의 이름은 stocktest라는 것이 이미 존재하고 있다는 것을 확인할 수 있었습니다. 그래서 아무래도 새로운 테이블을 생성하면서 데이터 베이스의 파일에 DataFrame객체의 데이터를 모두 저장한다는 것을 알 수 있었습니다.

그래서 이번에는 df.to_sql('새로운 테이블의 이름',con) 이라고 입력을 해서, 데이터 베이스의 파일에 데이터를 집어 넣도록 합니다. 여기서 주목해야 할 것은, pandas에서는 to_sql()이라는 메소드가 있어서 SQL구문을 사용하지 않더라도 데이터 베이스 파일에 데이터를 저장하는 것이 가능합니다.

거기다가 추가로, to_sql()메서드를 사용하는 중에 너무 데이터의 row가 있어서 패킷의 제한으로 인한 오류가 발생할 수 있습니다. 그래서 이런 경우를 위해서는 한번에 저장이 될 데이터의 갯수를 1000으로 지정해 주면 됩니다.

df.to_sql('새로 생성될 테이블의 이름',con,chunksize=1000)

여기서 언급을 하고 있는 chunksize라는 것이 바로 저장이 될 row의 갯수를 지정해 주는 역할을 하는 것 입니다.

그런데 데이터 베이스의 파일을 열어 보았을 때, 아무런 데이터도 저장이 되지 않은 것을 볼 수 있었습니다.

그래서 지난번에 언급했던 commit()메소드를 추가하는 것을 잊어버려서 생긴 에러라는 생각이 들어서, con.commit()이라는 구절을 입력해서 마무리를 지어 보도록 합니다.

그런데 그러고도 다시 열어보니, 아무런 데이터가 없다는 느낌이 들기는 들었습니다만.......

알고보니 stocktest2라는 제가 설정해둔 임의의 테이블을 열어야만 제대로 데이터가 나오는 것을 확인할 수 있었습니다.

 이번에는 반대로 데이터 베이스에 저장되어 있는 데이터를 읽어와서 DataFrame의 객체로 만들어 주는 실습을 해 보고자 합니다. 일단 Jupyter QtConsole을 한번 exit()라고 입력해서 종료한 다음, 다시 시작해서 작업을 이어가는데, 먼저 pandas와 pandas의 하위 모델, 그리고 sqlite3 모듈까지 import하도록 합니다.

일단 위 스크린샷처럼 df=pd.read_sql("SELECT * From 테이블명", con, index_col=None) 이라고 입력을 해서, 데이터 베이스 파일에서 데이터를 읽어와서 DataFrame, 객체로 만들어 오도록 합니다. 여기서 pandas에서 제공하는 read_sql() 이라는 메소드로 데이터 베이스 파일을 읽어 오기는 오는데, 여기서는 위 스크린샷처럼 SELECT를 위한 SQL구문을 사용해야 합니다.

그리고 이어서 index_col=None이라는 인자는 인덱스를 지정하는데, None이면 column을 정하지 않고 자동으로 0으로 시작하는 정수값이 인덱스로 할당이 됩니다.

그래서 이번에는 index_col의 값을 지정해 둔 상태에서 한번 알아보기 위해서 다시 Jupyter QtConsole을 실행해서 다시 셋팅을 하도록 합니다. 이렇게 하는 이유는 아까처럼 pandas의 to_sql()메소드를 사용해서 만들어진 데이터베이스의 테이블이라면, 관련된 내용에서 이미 index가 있기 때문입니다.

그래서 이번에는 index_col='index' 라고 지정을 해두자, 위 스크린샷처럼, 이미 index가 있는 경우에는 index가 인덱스로 지정이 되어서 따로 지정된 column이 없이 제대로 된 형태로 나오는 것을 볼 수 있습니다. 위에서 None으로 두었을 경우에는 제가 지정하지도 않은 index라는 column이 추가된 것을 볼 수 있었는데, index_col='index' 라고 지정을 해야, to_sql()메소드로 만들어진 데이터를 제대로 읽어올 수 있습니다. 이렇게 하는 것으로 일단 Jupyter QtConsole에서 하는 것은 끝났고, 다음 포스팅 부터는 이제 증권사 Open API를 이용해서 직접 주식 데이터를 가지고 오는 것을 올려 보겠습니다.