[2024.02.15] 파이썬 크롤링 작업 (flask, beautifulsoup4, sql, schedule.. )

파이썬을 사용해서 이렇쿵ㅇ저렇쿵ㅇ 뭐 열심히 치다보니까 결과물이 나왔다.

사용한 패키지와 모듈

• math
• requests
• beautifulsoup4
• pymysql
• flask
• schedule
• time
• threading

flask까지 사용해서 진짜 거의 1~2년만에 내가  html을 작성하였다.

다행이도 자바스크립트는 사용안하고 열심히 해봤다 ^^
자바스크립트는 진짜 기억안난다. ............. ㅠ

main.py

import math
import requests
from bs4 import BeautifulSoup
import pymysql
from flask import Flask, request, render_template
import schedule
import time
import threading

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='1234', db='localDB', charset='utf8')
    return conn


try:
    connection = dbconnect()
    if connection:
        print("DB 접속 완료")
    else:
        print("DB 접속 실패")
except Exception as e:
    print("DB 접속 중 오류 발생 : ", str(e))


# k스타트업
def data_insert(conn):
    conn = dbconnect()
    url1 = "https://www.k-startup.go.kr/web/contents/bizpbanc-ongoing.do"
    response1 = requests.get(url1)
    html_content1 = response1.text
    soup1 = BeautifulSoup(html_content1, 'html.parser')
    cur = conn.cursor()
    if soup1:
        # custom_search_list = soup1.find_all('div', 'custom_search-wrap')
        # board_list = soup1.find_all('div', 'board_list-wrap')
        ann_cont_list = soup1.find_all('div', 'slide')
        board_list = soup1.find_all('div', class_='board_list-wrap')

        for slide in ann_cont_list:
            ann_cont = slide.find('div', 'ann_cont')
            ann_top_list = slide.find_all('div', 'ann_top')
            tit_wrap_list = ann_cont.find_all('div', 'tit_wrap')
            ul_list = ann_cont.find_all('ul')

            # 링크 받아서 제목에서 클릭하면 실행되게 해야함
            links = slide.find_all('a')
            for link in links:
                href = link.get('href')
                hrefs = href.strip("javascript:go_view(" + ");")

            for ul in ul_list:
                li_list = ul.find_all('li')
                if len(li_list) >= 3:
                    second_li = li_list[1].text.strip()
                    third_li = li_list[2].text.strip()

            for ann_top in ann_top_list:
                txt_list = ann_top.find_all('p', 'txt')
                flag_type_tags = ann_top.find_all('span')
                if len(flag_type_tags) >= 0:
                    tags = flag_type_tags[0].text.strip()
            for tit_wrap in tit_wrap_list:
                tit_list = tit_wrap.find_all('p', 'tit')

                site = 'k스타트업'
                support = flag_type_tags[0].text.strip()
                name = tit_list[0].text.strip()
                r_date = '2024-02-14'  # 사이트에서 등록일을 받아오는 태그를 조회하면 등록된 데이터가 없다고해서 임의로 지정했습니다.
                d_dates = txt_list[0].text.strip()
                re_min = li_list[0].text.strip()
                d_date= d_dates.strip("마감일자")
                agency = li_list[1].text.strip()
                view = li_list[2].text.strip()
                views = view.strip("조회")

                check_sql = f"SELECT COUNT(*) FROM localdb.crawling WHERE 링크 = '{hrefs}';"
                cur.execute(check_sql)
                result = cur.fetchone()
                count = result[0]

                if count == 0:
                    sql = f"INSERT IGNORE INTO localdb.crawling (사이트,지원분야,지원사업명,등록일,마감일,관련부처,수행기관,조회수, 링크) VALUES ( '{site}', '{support}', '{name}', '{r_date}', '{d_date}', '{re_min}', '{agency}', '{views}','{hrefs}')"
                    cur.execute(sql)
            conn.commit()


# ----------------------------------------------------------------------------
# 비즈인포
def insert2(conn):
    url2 = "https://www.bizinfo.go.kr/web/lay1/bbs/S1T122C128/AS/74/list.do?"
    response2 = requests.get(url2)
    html_content2 = response2.text
    soup2 = BeautifulSoup(html_content2, 'html.parser')

    tbody = soup2.find('tbody')
    if tbody:
        tr_list = tbody.find_all('tr')
        conn = dbconnect()
        cur = conn.cursor()
        for tr in tr_list:
            td_list = tr.find_all('td')
            values = [td.text.strip() for td in td_list]
            site = '비즈인포'
            support = values[1]
            name = values[2]
            r_date = values[6]
            d_date = values[3]
            re_min = values[4]
            agency = values[5]
            views = values[7]

            links = tr.find_all('a')
            for link in links:
                href = link.get('href')
                hrefs = href.strip("view.do?")

            check_sql = f"SELECT COUNT(*) FROM localdb.crawling WHERE 링크 = '{hrefs}';"
            cur.execute(check_sql)
            result = cur.fetchone()
            count = result[0]

            if count == 0:
                # 중복되지 않는 경우에만 저장
                sql = (f"INSERT INTO localdb.crawling (사이트,지원분야,지원사업명,등록일,마감일,관련부처,수행기관,조회수,링크) "
                       f"VALUES ( '{site}', '{support}', '{name}', '{r_date}', '{d_date}', '{re_min}', '{agency}', {views},'{hrefs}') ;")
                cur.execute(sql)
        conn.commit()

app = Flask(__name__)


@app.route('/form')
def form():
    with app.app_context():
        conn = dbconnect()
        cur = conn.cursor()
        sql = 'select * from localdb.crawling'
        cur.execute(sql)
        result = cur.fetchall()

    data_list = []
    for i in range(len(result)):
        data = {
            'number': result[i][0],
            'site': result[i][1],
            'support': result[i][2],
            'name': result[i][3],
            'r_date': result[i][4],
            'd_dates': result[i][5],
            're_min': result[i][6],
            'agency': result[i][7],
            'views': result[i][8],
            'link': result[i][9]
        }
        data_list.append(data)

    current_page = request.args.get('page', default=1, type=int)
    total_page = (len(data_list) / 12)
    total_pages = math.trunc(total_page)
    return render_template('python_crowling.html', data_list=data_list, current_page=current_page,
                           total_pages=total_pages)
def run_app():
    app.run(host="0.0.0.0", port="8080")
def main():
    conn = dbconnect()
    insert2(conn)
    data_insert(conn)
    print('데이터 저장 실행중...')

schedule.every(5).minutes.do(main)
schedule.every(1).hours.do(main)

app_thread = threading.Thread(target=run_app)
app_thread.start()

while True:
    schedule.run_pending()
    time.sleep(1)

 

되게 코드가 더러울 수 있는데 그래도 어떻게 잘 된 거 같다.
중간에 안 쓰는 것들도 많다….스케줄러를 사용하는데, 왜 스레드를 사용하냐 궁금할 수도 있는데
이게 스케줄로만 사용하면 app·run이 동시에 안된다. 뭐 이거 말고 다른 방법으로도 할 수 있겠지만….코드의 간결성을 위해 편하게 ~~

 

그리고 이제 웹화면!
py.html 으로 저장하고

이건 templates 폴더 안에 있어야 정상적으로 돌아간다. 안그러면 오류남!!!

<!DOCTYPE html>
<html>
<h1 class="h1">파이썬 크롤링 k스타트업 & 중소기업벤처 모집 공고</h1>
<head>
    <style>
        .pagination {
     display: flex;
     justify-content: center;
     align-items: center;
     margin-top: 20px;
 }


 .pagination a {
     display: inline-block;
     padding: 8px 16px;
     margin: 0 4px;
     color: #333;
     text-decoration: none;
     border: 1px solid #ccc;
     border-radius: 4px;
 }


 .pagination a.current-page {
     background-color: #333;
     color: #fff;
 }


  .h1{
  text-align: center;
  }


  table {
      width: 1900px;
      border-collapse: collapse;
      margin: 0 auto;
  }


  th,
  td {
      border: 1px solid black;
      padding: 10px;
      text-align: center;
         color: black;
      text-decoration: none;
      font-size:14px;
  }

  th {
      background-color: lightgray;
      text-align: center;
         color: black;
      text-decoration: none;
       padding: 6px;
  }
  .name{
      color: black;
      text-decoration: none;
  }

 .pagination {
 display: flex;
 justify-content: center;
 list-style: none;
 padding: 0;
 }

 .page-item {
 margin-right: 5px;
 }


 .page-link {
 display: inline-block;
 padding: 5px 10px;
 background-color: #f2f2f2;
 color: #333;
 text-decoration: none;
 border: 1px solid #ccc;
 border-radius: 3px;
 }


 .page-link:hover {
 background-color: #e0e0e0;
 }

 .page-item.active .page-link {
 background-color: #333;
 color: #fff;
 border-color: #333;
 }

 .page-item:first-child .page-link,
 .page-item:last-child .page-link {
 padding: 5px;
 background-color: #f2f2f2;
 color: #333;
 text-decoration: none;
 border: 1px solid #ccc;
 border-radius: 3px;
 }


 .page-item:first-child .page-link:hover,
 .page-item:last-child .page-link:hover {
 background-color: #e0e0e0;
 }


 .page-item:first-child .page-link span,
 .page-item:last-child .page-link span {
 font-size: 12px;
 }


    </style>
</head>

<body style="margin:0px">
<div>
   <table >
    <tr>
        <th>번호</th>
        <th>사이트</th>
        <th>지원분야</th>
        <th>지원사업명</th>
        <th>등록일</th>
        <th>마감일</th>
        <th>관련부처</th>
        <th>수행기관</th>
        <th>조회수</th>
    </tr>

{% set start_index = (current_page - 1) * 16 %}
{% set end_index = current_page * 16 %}

{% for data in data_list[start_index:end_index] %}
    <tr>
        <td>{{ data['number'] }}</td>
        <td>{{ data['site'] }}</td>
        <td>{{ data['support'] }}</td>

        {% if data['site'] == '비즈인포' %}
        <td>
            <a href="https://www.bizinfo.go.kr/web/lay1/bbs/S1T122C128/AS/74/view.do?{{ data['link'] }}" class="name">
                {{ data['name'] }}
            </a>
        </td>
        {% elif data['site'] == 'k스타트업' %}
        <td>
            <a href="https://www.k-startup.go.kr/web/contents/bizpbanc-ongoing.do?schM=view&pbancSn={{ data['link'] }}&page=1&schStr=regist&pbancEndYn=N"
               class="name">
                {{ data['name'] }}
            </a>
        </td>
        {% else %}
        <td>{{ data['name'] }}</td>
        {% endif %}

        <td>{{ data['r_date'] }}</td>
        <td>{{ data['d_dates'] }}</td>
        <td>{{ data['re_min'] }}</td>
        <td>{{ data['agency'] }}</td>
        <td>{{ data['views'] }}</td>
    </tr>
{% endfor %}

</table>

</div>

<br>

<div class="pagination">
    {% set items_per_page = 20 %}
    {% set total_items = 100 %}

    {% set prev_page = current_page - 1 %}
    {% set next_page = current_page + 1 %}

    {% if prev_page >= 1 %}
    <a href="?page={{ prev_page }}">이전</a>
    {% endif %}

    {% for page in range(1, total_pages + 1) %}
    {% if page == current_page %}
    <a href="?page={{ page }}" class="current-page">{{ page }}</a>
    {% else %}
    <a href="?page={{ page }}">{{ page }}</a>
    {% endif %}
    {% endfor %}


    {% if next_page <= total_pages %}
    <a href="?page={{ next_page }}">다음</a>
    {% endif %}

</div>

</body>
</html>

이것도 어떻게 계속 하다보니까 됬다.

혼자서 여기까지 해본건 처음이다!
앞으로도 발전가능성이 보인다.

 

결과물은 궁금하면 직접 실행

그리고 db는 로컬에서 생성한거라 궁금하면 여기로 -> https://only-jy.tistory.com/entry/20240215-DBeaver-MariaDB-%ED%99%98%EA%B2%BD-%EC%84%B8%ED%8C%85-%EB%A9%94%EB%89%B4%EC%96%BC?category=1166964

[2024.02.15] DBeaver, MariaDB 환경 세팅 메뉴얼