230221_DB복습

안녕하세요. 보랏입니다. 

 

오늘은 어제에 이어서 Linux, DB적재를 배우고

python을 통해 크롤링한 데이터를 얻었습니다.

 

바로 복습 시작하겠습니다. 

 

1. Linux - PuTTY

sudo systemctl status mariadb #active running 표시

• Host Name : centOS에 ip addr을 입력하여 나온 가상  ip주소를 입력하여 open을 하면 리눅스서버와 연결됩니다.
• Saved Sessions : 지속해서 연결하는 ip주소를 사용하기 위해서는 Saved Sessions에 추가하여 Save를 해주시면 됩니다. 

2. MySQL한글  설정

리눅스 서버에서 DB를 실행할 때 처음에 아무 설정도 안하면 한글은 깨져서 보일 수 있습니다. 

그렇기 때문에 PuTTY를 통해 리눅스에 'utf-8'을 설정해야합니다.

 

그 방법에 대해서 알아보도록 하겠습니다. 

cd /etc/my.cnf.d/ # my.cnf.d 폴더 이동

vim mysql-clients.cnf # mysql-.clients.cnf 파일 vim으로 열기

[mysql]
default-character-set = utf-8
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
default-character-set = utf-8  #저장 후 닫기

vim server.cnf # server.cnf파일 vim으로 열기

[server]
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = set names utf8:

vim client.cnf # client.cnf 파일 vim으로 열기

[client]
default-character-set=utf8

#재가동 및 상태 확인
systemctl restart mariadb
systemctl status mariadb

• 한글을 설정하기 위해서는 Mysql, server, client에 모두 utf-8을 설정해주셔야 합니다. 
• Mysql폴더에 접속하여 vim으로 mysql-clients.cnf를 열어서 위에 있는 괄호에 문구를 추가해주시면 됩니다. 
• server파일과 client파일에 접속하여 똑같이 위의 문구를 추가해주시고 저장한 다음 재가동 해주시고 상태를 확인하시면 초록색으로 running(active)표시가 나타납니다. 

 

3. 리눅스 권한 확인

• 리눅스는 파일별로 권한에 따라 읽기(r), 쓰기(w), 실행(x)을 가지고 있습니다. 그리고 이러한 상태에 따라 숫자로 불러서 부르게 됩니다. 
• ls -al 명령을 사용하여 파일, 디렉토리 명령을 확인할 수 있으며 권한은 총 4부분으로 나눌 수 있습니다.

     - ex ) d rwx rw_ rw_

     - (-) : 파일, (d) : 디렉토리

     - rwx : 사용자 권한

     - rw_ : 그룹 권한

     - rw_ : 다른  사용자(others) 권한

• r = 2^2, w = 2^1, x = 2^0 으로 만약 rwxrw_rw_ = 766, r__r__r__ = 444입니다. 
• 그리고 이에 대한 권한변경을 시행하려면 chmod [번호][파일명]을 적어주시면 해당 번호에 맞는 권한으로 변경되게 됩니다. 

4. 크롤링 데이터 DB적재

• 한국거래소(KRX) 홈페이지에 정보데이터시스템을 크롤링하여 DB에 적재하는 작업을 하였습니다. KRX홈페이지는 post구조의 홈페이지로 request구문을 활용하여 크롤링을 진행하였습니다.
• 데이터 적재 전에 미리 SQL에 테이블(KRX)을 생성하였고, 파이썬에서 데이터 적재 시 컬럼 수 만큼 %s를 써주고서 r.json의 value 값을 찾아 데이터를 적재합니다. 이 때 con.commit이 반드시 필요합니다. 

import requests
import pymysql

#DB연결 
try:
    con = pymysql.connect(host='192.168.56.101', 
                          user='test', password='123', 
                          charset='utf8', db='test')
    cur = con.cursor()
except Exception as e:
    print ("error ->", e)

#크롤링 및 DB적재
krx_url = "http://data.krx.co.kr/comm/bldAttendant/getJsonData.cmd"
payload = {"bld"  : "dbms/MDC/STAT/standard/MDCSTAT01901",
            "locale"  : "ko_KR",
            "mktId"  : "ALL",
            "share"  : "1",
            "csvxls_isNo"  : "false",}
r = requests.post(krx_url, data=payload)

rt = r.json()

sql = "INSERT INTO KRX VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

for data in rt['OutBlock_1']:
    try:
        cur.execute(sql, list(data.values()))
    except:
        pass
        
con.commit()

 

• 이렇게 적재된 DB를 활용하여 SQL에서 코스피와 코스닥을 검색하는 쿼리를 작성 후에 이를 다시 파이썬으로 가져와 실행하고 padnas를 통해 표로 만들었습니다. 

import pandas as pd

krx = pd.read_sql_query("""SELECT *
FROM KRX
WHERE MKT_TP_NM IN ('KOSPI', 'KOSDAQ')""",con)

krx

 

5. loc, iloc,set_index

• .loc : 데이터프레임의 행이나 컬럼에 컬럼명으로 접근하는 방법
• .iloc : 데이터프레임의 행이나 컬럼에 인덱스값으로 접근하는 방법
• .set_index(컬럼명) : 해당 컬럼명으로 인덱스 변경

6. 네이버 증권 크롤링 및 DB적재

• 네이버증권 홈페이지에 데이터를 크롤링하여 DB에 넣는 작업을 하였습니다. 네이버 증권 홈페이지는 get방식의 홈페이지입니다. 
• 시작하기 전에 똑같이 SQL에서 미리 테이블을 생성하였고, 이번에는 파생변수, 컬럼순서를 변경하여 데이터를 적재하였습니다. 

import requests
import pymysql
import numpy as np

# DB서버 연결
try:
    con = pymysql.connect(host='192.168.56.101', 
                          user='test', password='123', 
                          charset='utf8', db='test')
    cur = con.cursor()
except Exception as e:
    print ("error ->", e)
    
# 크롤링 함수 생성
def get_stock(code, page):
    naver_url = "https://finance.naver.com/item/sise_day.naver?code={}&page={}"
    head = {'User-Agent': "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/110.0.0.0 Safari/537.36"}
    r= requests.get(naver_url.format(code,page),headers=head)
    return pd.read_html(r.text)[0].dropna()
    
#변수생성, 컬럼 순서 변경, 1~20페이지 데이터 적재
sql = "INSERT INTO samsung VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"

for i in range(1,20) : 
    data =  get_stock("005930", i)
    data[['종가', '전일비', '시가', '고가', '저가', '거래량']] = \
    data[['종가', '전일비', '시가', '고가', '저가', '거래량']].astype(int)
    data['거래금액'] = samsung['종가'] * samsung['거래량']
    data['종목'] = "005930"
    data = data[['종목','날짜', '종가', '전일비', '시가', '고가', '저가', '거래량', '거래금액']]
    # 파생변수
    # 컬럼순서 
    for idx, row in data.iterrows():
        try:
            cur.execute(sql, list(row.values))
        except Exception as e:            
            print(e)
            break
            
 con.commit()

 

8 . 기타용어

    1) POC(Proof of Concept) : 개념 실증

• 새로운 프로젝트가 실제로 실현 가능성이 있는지 효과와 효용, 기술적인 관점에서 검증하는 과정
• 프로토 타입 및 배포 : 시제품 만들기 
• 검증 : 관계자, 피험자 등으로부터 사용 및 피드백 수렴
• 실현 여부판단 : 실현가능성 판단

    2) HASH테이블

• key, value로 데이터를 저장하는 자료구조로 빠르게 데이터를 검색할 수 있는 자료구조
• 각각의 key값에 해시함수를 적용해 고유한 index생성, 이를 활용하여 값을 저장 검색 

 

이렇게 오늘 복습도 끝났습니다.

이제 이틀 뒤면 던파 신캐릭터인 뮤즈가 출시되는데, 

공부하느라 시간이 없어 제대로 즐길 시간도 없을 거 같네요..ㅠ(신캐 키우기 힘든...)

뮤즈 출시기념으로 일러스트 하나 남기고 들어가보도록 하겠습니다.

 

감사합니다.