인프런 커뮤니티 질문&답변

훌륭한 강의 잘 듣고있는 코린이입니다

수업 들으면서 코드 복습도 하곤 하는데

크롤링한 데이터를 주피터 노트북을 이용하여 데이터베이스에 저장시키는건 잘 되는데, 파이참이나 vscode 터미널 등에서 실행하면 맨 처음 데이터만 프린트 문으로 보여주고 파일은 실행되지않고 있다가

pymysql.err.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

이런 에러가 뜹니다

혼자 해결해보려고 했으나 도저히 모르겠어서 질문남깁니다! 꼭 답변부탁드려요!!

import requests
from bs4 import BeautifulSoup
import pymysql

def subcategories(url, name):
    get_items(url, name, "ALL")
    
    
    res = requests.get(url)
    soup = BeautifulSoup(res.content, 'html.parser')
    
    subcategories = soup.select('div.navi.group > ul:nth-child(1) > li')
    for subcategory in subcategories :

        subcate_name = subcategory.select_one('a').get_text()
        subcate_url = "http://corners.gmarket.co.kr" + subcategory.select_one('a')['href']
        
        get_items(subcate_url, maincate_name, subcate_name)

def get_items(url, maincate_name, subcate_name):
    res = requests.get(url)
    soup = BeautifulSoup(res.content, 'html.parser')
    
    data = soup.select('div.best-list')
    for index, item in enumerate(data[1].select('li')) :
        title = item.select_one('a.itemname').get_text()
        ori_price = item.select_one('div.o-price > span')
        dis_price = item.select_one('div.s-price > strong > span')
        discount_percent = item.select_one('div.s-price em')
        
        data_dict = dict()
        
        if ori_price == None or ori_price.get_text() == "":
            ori_price = dis_price
        
        if dis_price == None :
            ori_price, dis_price = 0, 0
        else :
            dis_price = dis_price.get_text().replace('원', "").replace(',','')
            ori_price = ori_price.get_text().replace('원', "").replace(',','')
        
        if discount_percent == None or discount_percent.get_text() == '':
            discount_percent = 0
        else:
            discount_percent = discount_percent.get_text().replace('%', '')
        
        product_url = item.select_one('a')['href']
        res = requests.get(product_url)
        soup = BeautifulSoup(res.content, 'html.parser')
        
        item_code = soup.select_one('span.text__item-number').get_text().split(":")[1].strip()
        ranking = index + 1

        seller = soup.select_one('span.text__seller > a')
        if seller == None or seller.get_text() == "":
            seller = ""
        else :
            seller = seller.get_text()
        
        data_dict['maincate_name'] = maincate_name
        data_dict['subcate_name'] = subcate_name
        data_dict['ranking'] = ranking
        data_dict['item_code'] = item_code
        data_dict['seller'] = seller
        data_dict['title'] = title
        data_dict['ori_price'] = ori_price
        data_dict['dis_price'] = dis_price
        data_dict['discount_percent'] = discount_percent
        
        save_date(data_dict)

def save_date(item_info):    
    sql = f"""select count(*) from items where item_code = {item_info['item_code']};"""
    cursor.execute(sql)
    result = cursor.fetchone()
    if result[0] == 0 :
        sql = f"""
        insert into items (item_code, title, pri_price, dis_price, discount_percent, provider) VALUES(
        '{item_info['item_code']}',
        '{item_info['title']}',
        {item_info['ori_price']},
        {item_info['dis_price']},
        {item_info['discount_percent']},
        '{item_info['seller']}'
        );
        """
        sql.replace('\n', '')
        print(sql)
        cursor.execute(sql)
        
    sql = f"""
    insert into ranking (main_category, sub_category, item_ranking, item_code) VALUES(
    '{item_info['maincate_name']}',
    '{item_info['subcate_name']}',
    '{item_info['ranking']}',
    '{item_info['item_code']}'
    );
    """
    sql.replace('\n', '')
    print(sql)
    cursor.execute(sql)

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='bestproducts', charset='utf8')
cursor = db.cursor()

url = 'http://corners.gmarket.co.kr/Bestsellers'
res = requests.get(url)
soup = BeautifulSoup(res.content, 'html.parser')

categories = soup.select('div.gbest-cate > ul.by-group > li')
for category in categories :
    maincate_name = category.select_one('a').get_text()
    maincate_url = "http://corners.gmarket.co.kr" + category.select_one('a')['href']
    subcategories(maincate_url, maincate_name)