파이썬 SQLite3를 이용한 실습

이번 글에서는 기본적인 쿼리문을 활용하여 SQLite3를 이용한 DB생성부터 데이터 조회까지의 내용에 대해 실습을 할 수 있는 예제를 정리합니다. 기본적인 쿼리문과 파이썬 명령어를 익혔다면 여러분의 상상력을 동원하여 다양한 예제에 활용해 볼 수 있습니다.

 

기본적인 DB생성부터 조회까지 쿼리문 및 파이썬 명령어를 다루는 것에 도움이 될 수 있는 글입니다. 간략하게 소개하고 있기 때문에 실제 예습을 통해 활용해보실 것을 추천합니다.

 

SQlite3를 이용한 파이썬 DB 활용하기(DB생성, 저장 방법)

 

 

SQlite3 파이썬 DB 데이터 조회하기

 

✔ 파이썬(python) SQLite3 예제 준비

우선 실습을 진행하기에 앞서 하나의 상황을 준비할 필요가 있습니다. DB는 프로젝트에 적용되어 서비스를 목적으로 하는 경우가 대다수이기에 상황에 따라 활용도는 달라질 수 있기 때문입니다.

 

- SQLite3 예제 실습 조건 세우기

1. DB를 형성할 data를 고려
2. DB 생성하기
3. Table 및 column 생성하기 (자동증가값(autoincrement) 설정)
4. data를 DB에 저장하고 확인하기
5. 특정 조건에 맞는 data를 조회하고 출력하기

위 과정을 거쳐 몇가지의 상황에 적용해본다면 파이썬을 이용한 프로젝트를 진행하는 과정에서 DB에 대한 걱정은 접어둘 수 있으리라 생각합니다. 

 

인터넷에서 가장 흔히 찾아볼 수 있는 웹크롤러(webcrawler, webscraping) 또는 빅데이터(big data)를 활용하는데 DB는 상당히 중요한 부분이기 때문에 DB에 대한 청사진을 그려보는 것은 앞으로 있을 여러분의 프로젝트에 분명 도움이 될 수 있을 것입니다.

 

✔ SQLite3 예제 실습 조건 세우기

 

1. DB를 형성할 data를 고려해볼까요.

개인적으로 로또(lotto)에 많은 관심을 가지고 있습니다. 인터넷을 검색해보면 코딩을 통해 로또에 접근하는 방법에 대해 언급하는 사람들을 많이 볼 수 있습니다. 한 걸음더 나아가 머신러닝(machine learning)에 접목하여 인공지능 Ai를 이용한 사례도 볼 수 있습니다.

 

여기서는 실습을 위한 예제이기 때문에 로또 분석에 대한 언급은 넘어가도록 하겠습니다. 로또 당첨번호를 DB에 담아 활용하는 상황에 대한 내용을 다루도록 할 것입니다. 만약, 로또에 관련된 어플을 제작할 생각이 있는 경우 서버를 지원하는 mysql과 같은 DB로 서비스를 연동해도 좋을 것 같네요.

 

•  로또(lotto) 당첨번호 DB 관리를 목적으로 합니다.

 

2.  DB 생성하기

개발자 혹은 사용자가 필요로 하는 DB상황을 가정했습니다. 지극히 개인적인 취향인 로또 당첨번호에 접근하여 DB를 형성하게 됩니다. 우선 SQlite DB를 만들어 볼까요.

 

# DB파일만 우선 만들도록 합니다.

import sqlite3

con = sqlite3.connect('lotto_numbers.db')

 

굳이 설명이 필요없을 만큼 간단하게 '.db'파일을 생성할 수 있습니다. sqlite3 라이브러리를 가져와서 db에 연결하는 명령어를 통해 생성할 수 있습니다. 이미 db파일이 있다면 con이라는 변수를 통해서 연결되게 됩니다.

 

준비된 명령어를 실행해 보도록 합니다. 저는 anaconda의 jupyter notebook을 이용해 진행했습니다. 위 코드를 진행하면 'lotto_numbers.db'라는 파일이 스크립트가 있는 폴더에 생성된 것을 확인할 수 있습니다.

 

3. Table 및 column 생성하기, 자동증가값(autoincrement) 설정하기

이번에는 위에서 생성한 db에 table을 만들고, 데이터들이 잘 자리잡을 수 있도록 column을 생성하도록 하겠습니다. 여기서 데이터가 저장되면 고유값으로 번호가 붙을 수 있는 자동증가값을 지정하다록 합니다.

 

autoincrement라고 부르는 자동증가값은 중복되는 값이 배정되지 않기 때문에 db의 데이터를 관리하는데 도움이 될 수 있습니다. 

 

예를 들어, 3개의 데이터를 db에 저장하게 되는 과정에서 순차적으로 데이터를 입력하게 됩니다. 이때, autoincrement를 설정한 column에는 입력되는 데이터에 맞춰 1부터 2, 3, 4, ... 순으로 순차적으로 입력되게 됩니다. 편리하겠죠.

 

• 실습에 진행될 table명, column명을 선정하도록 합니다.
  • table 명 : lotto_information
  • column 명
    • ids (autoincrement)
    • 회차
    • 추첨일
    • 1구
    • 2구
    • 3구
    • 4구
    • 5구
    • 6구
    • 보너스
• 더 많은 정보를 담을 수 있겠지만 가장 기본이 되는 정보만을 활용하도록 하겠습니다.

 

# lotto_numbers.db에 con으로 연결한 후

cur = con.cursor()
sql = 'CREATE TABEl lottos (
                            ids integer primary key autoincrement,
                            "회차" text,
                            "추첨일" text,
                            "1구" text,
                            "2구" text,
                            "3구" text,
                            "4구" text,
                            "5구" text,
                            "6구" text,
                            "보너스" text
                            )'
cur.execute(sql)

#DB 테이블명 확인하는 방법
sql = 'SELECT * FROM sqlite_master WHERE type="table";'
cur.execute(sql)
result = cur.fetchone()
print(result)

* 코드를 보기좋게 정렬하는 과정에서 엔터를 자주 사용했습니다. 오류가 발생된다면 엔터없이 나열하거나 주석(''' ''')을 이용해 sql 쿼리문을 활용해 보세요.

 

-출력문

위 사진과 같이 출력이 되었다면 DB 및 Table이 잘 만들어졌다는 것을 알 수 있습니다. 더불어, DB Browser를 이용해서도 확인이 가능합니다.

 

이제 DB와 table, column이 잘 준비되었으니, 목표였던 data를 준비해보도록 하겠습니다. 동행복권 사이트를 이용해 역대 로또 당첨번호를 추출하면 되겠습니다.

 

4. data를 DB에 저장하고 확인하기

동행복권 사이트를 이용해 로또 당첨번호에 관련된 data를 수집하는 방법은 크게 두가지로 구분할 수 있습니다. 하나는 서버를 이용하는 방법이며, 나머지는 역대 당첨번호 엑셀파일을 다운로드해서 사용하는 방법입니다. 

 

각각의 방법을 사용하기 위해 파이썬에서 이용되는 라이브러리는 달라지게 됩니다. 

• 서버(api)를 이용하는 방법 : requests
• 엑셀파일을 이용하는 방법 : pandas

 

두가지의 방법에서 더 우위에 있는 방법이 따로 있는 것은 아니기 때무에 취향에 따라 선택하는 것이 좋을 것 같네요. 앞으로의 프로젝트가 서버를 주로 이용한다면 requests를 이용하는 것이 도움이 될 수 있을 것이며, 데이터 분석을 이용한다면 pandas에 익숙해지는 것도 좋을 것 같습니다.

 

결론적으로 가장 좋은 방법은 두가지 모두에 경험을 쌓는 것이기도 합니다. 데이터 분석을 위해 크롤링이 필요한 경우가 있으며, 데이터를 시각적으로 표현할때는 pandas가 도움이 되기 때문입니다. 여기서는 requests를 이용하도록 하겠습니다.

 

4.1. 동행복권 api

일반적으로 api는 서버의 과부하를 줄이고, 이용자의 편의를 제공하는 느낌이 강합니다. 찾아보면 다양한 open api 및 유료 api를 이용할 수 있습니다. 동행복권의 경우 공식적으로 알려지진 않았던 것 같은데 인터넷을 찾아보면 정보를 얻을 수 있습니다. (공식적이지 않다는 것은 제가 못찾은 것일 수도 있습니다.)

 

• 동행복권 api 활용

• url = 'https://www.dhlottery.co.kr/common.do?method=getLottoNumber&drwNo=회차번호'

위 url 주소를 이용해 정보를 얻을 수 있다고 하네요. 이용해 봐야겠죠. 서버를 이용하는 방법이지만 너무 짧은 시간에 많은 요청을 시도하면 서버과부하로 인해 차단될 수 있습니다. 적당한 간격을 유지하는 것이 좋을 것 같네요.

 

4.2. 동행복권 api를 이용해 회차정보 얻어오는 과정

웹크롤러를 만들어 본 경험이 있다면 충분히 짧은 코드를 이용해 정보를 받아올 수 있습니다. request api를 이용하는 방법은 크게 get과 post로 나뉘는데, 전자는 정보를 받아오는 쪽, 후자는 명령을 전달하는 쪽이라고 생각하면 될 것 같네요.

 

• 사용할 라이브러리
  • requests
  • BeautifulSoup
  • json
• 어떤 값이 반환되는지는 웹브라우저에 위의 url 주소를 이용해 접속해보시면 확인이 가능합니다.

사진과 같은 값들이 반환되는 것을 확인할 수 있는데, json파일 형식으로 처리하게 되면 파이썬의 사전형처럼 '키:값'대응으로 찾을 수 있습니다. 

url = 'https://www.dhlottery.co.kr/common.do?method=getLottoNumber&drwNo=931'
response = requests.get(url)
print(response)
html = response.text
soup = BeautifulSoup(html, 'html.parser')
print(soup)

931회차 로또 당첨번호 정보를 얻어오는 코드(위)와 코드를 실행한 결과(아래)입니다. 이제 for문 또는 while문을 이용해 원하는 만큼의 회차정보를 받아오면 됩니다.

 

대략 1회부터 200회까지의 정보를 얻어와 DB에 저장하도록 하겠습니다. 

for i in range(1, 201):
    url = 'https://www.dhlottery.co.kr/common.do?method=getLottoNumber&drwNo={}'.format(str(i))
    response = requests.get(url)
    html = response.text
    soup = BeautifulSoup(html, 'html.parser')
    lotto_info = json.loads(soup.text)

    #관련 정보를 담을 변수 지정
    lo_c = str(i)
    date = lotto_info['drwNoDate']
    no1 = lotto_info['drwtNo1']
    no2 = lotto_info['drwtNo2']
    no3 = lotto_info['drwtNo3']
    no4 = lotto_info['drwtNo4']
    no5 = lotto_info['drwtNo5']
    no6 = lotto_info['drwtNo6']
    bonus = lotto_info['bnusNo']

for문을 이용해 1회부터 200회까지를 조회하게 됩니다. 각각의 정보는 코드 아래에 있는 변수들에 담기게 되며, 해당되는 값들을 DB에 INSERT 하게되면 DB 저장이 완료되게 됩니다.

 

for문이 반복되는 동안 DB에 데이터를 저장할 수 있는 쿼리문을 전달하여 명령을 수행하게 되면, 우리가 이미 만들어둔 DB에 데이터가 저장되어 활용할 수 있습니다.

 #DB에 저장
    con = sqlite3.connect('lotto_numbers.db')
    cur = con.cursor()
    sql = '''INSERT INTO lottos("회차","추첨일","1구","2구","3구","4구","5구","6구","보너스") VALUES("{}","{}","{}","{}","{}","{}","{}","{}","{}")'''.format(lo_c,date,no1,no2,no3,no4,no5,no6,bonus)
    cur.execute(sql)
    con.commit()
    con.close()

sql의 경우 길게 나열했지만, 적당히 엔터를 통해 깔끔하게 코드를 수정해도 충분히 활용할 수 있습니다. DB를 Browser를 통해서 확인하면 다음과 같은 모습을 볼 수 있습니다.

편의상 column을 만들때 text로 만들었지만, 당첨번호의 경우 숫자이므로 int로 설정하는 것이 좋을지도 모르겠습니다. 만약 숫자로 조회하여 사용한다면 int()를 이용해 type을 바꿔줘야하는 불편함이 있을 수 있기 때문입니다.

 

- 이것으로 DB생성 및 data 저장이 완료되었습니다. 이제는 필요에 따라 조회를 진행하면 되겠죠.

 

5. 특정 조건에 맞는 data를 조회하고 출력하기

간략하게 데이터를 검색하는 예시를 알아보겠습니다. 음,, 로또 당첨번호니까, 몇번째 자리에 특정 번호가 있는 회차를 검색하는 방법을 알아보는 것도 좋을 것 같네요. 로또 분석에는 이전에 출현한 번호의 위치, 회차의 간격이 중요한 지표로 활용되기도 합니다.

 

• (예시) 4구 자리에 30번이 있는 회차 검색

con = sqlite3.connect('lotto_numbers.db')
cur = con.cursor()
sql = '''SELECT * FROM lottos WHERE "4구" = "30"'''
cur.execute(sql)
result = cur.fetchall()
print(result)

4번째에 30번이 출현한 회차가 한개 또는 다수일 수 있기 때문에 fetchall()을 이용하여 대상 데이터를 조회합니다. 결과는 다음과 같습니다.

조금은 알아보기 힘들게 되어있네요. for문과 print를 이용해 하나씩 출력하거나, pprint를 이용해 깔끔하게 출력하여 확인할 수 있습니다.

pprint를 이용하니 훨씬 깔끔하게 출력된 모습을 확인할 수 있습니다. 리스트와 튜플로 이루어져있기 때문에 index를 이용해 원하는 값만을 추출하여 사용할 수 있습니다. 

 

마치며.

직접 진행해보면 그리 어려운 것은 아니지만 글로 나타내려니 장황하게 표현되는 부분들이 많이 있네요. 모쪼록 재미있는 실습 혹은 예시가 될 수 있으면 좋겠습니다. 여기에 ui를 씌워 프로그램을 완성하면 로또분석 프로그램 혹은 로또 당첨번호 추출기가 탄생할 수 있을지도 모르겠다는 생각을 해봅니다.