<repo_name>thomazcarvalho/csgo_data-web_scraping<file_sep>/studying_web-scraping-(p2)(getting-hltv-data).py
from bs4 import BeautifulSoup
import requests
import mysql.connector


conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password=''
)
cursor = conn.cursor()
cursor.execute('USE csgo_hltv')

c = 100
date = ''
site = 'https://www.hltv.org/results'

while True:
    c += 100
    try:
        source = requests.get(site).text
    except Exception as error:
        print(error)
    else:
        soup = BeautifulSoup(source, 'lxml')
        for sublist in soup.find_all('div', class_='results-sublist'):
            date = sublist.text[11:40].split('\n')
            date = date[0]
            print(date)
            for match in sublist.find_all('div', class_='result-con'):
                team1 = match.find(
                    'div', class_='line-align team1'
                ).text.replace(
                    '\n', ''
                )
                team2 = match.find(
                    'div', class_='line-align team2'
                ).text.replace(
                    '\n', ''
                )
                result = match.find('td', class_='result-score').text.replace(
                    '\n', ''
                )
                result = result.split(' - ')
                event = match.find('td', class_='event').text.replace('\n', '')
                rec = (
                    'default', team1,
                    team2, result[0],
                    result[1], event,
                    date
                )
                cursor.execute(
                    'INSERT INTO csgo_results_hltv '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s) ',
                    rec
                )
                conn.commit()
            site = 'https://www.hltv.org/results?offset=' + str(c)

conn.commit()
cursor.close()
conn.close()
<file_sep>/README.md
# csgo_data - web-scraping
 Colecting data from a specialized counter-strike global offensive website. The colected data is related to the matches since 2012 and in the database you will find information like: team1, team2, results, event, date and winner of the match.
<file_sep>/studying_web-scraping-(p3)(creating-winner-column).py
import mysql.connector as ms
import pandas as pd


conn = ms.connect(
    host='localhost',
    user='root',
    password=''
)
cursor = conn.cursor()

cursor.execute('USE csgo_hltv')
cursor.execute(
    'SELECT * FROM csgo_results_hltv'
)
rec = cursor.fetchall()

df = pd.DataFrame(
    rec,
    columns=('id', 'team1', 'team2',
             'result team1', 'result team2',
             'event', 'date')
)

cursor.execute(
    'ALTER TABLE csgo_results_hltv '
    'ADD COLUMN winner VARCHAR(25) NOT NULL;'
)

winner = list()
c = 0
for register in rec:
    if int(register[3]) > int(register[4]):
        winner.append(register[1])
    elif int(register[4]) > int(register[3]):
        winner.append(register[2])
    else:
        winner.append(None)
    cursor.execute(
        'UPDATE csgo_results_hltv '
        f'SET `winner` = "{winner[c]}" '
        f'WHERE `id`={c+1}'
    )
    print(c)
    c += 1

df['winner'] = winner

print(df.head())

conn.commit()
cursor.close()
conn.close()
