python 기초 5

✍🏻 배운점

 

ssh 키를 통한 원격 접속에 대한 이해를 할 수 있었고, mariaDB 에 sql, 파이썬에서의 sql 사용법을 익히고 그를 응용하여 한국 주식 정보와 네이버 주식 데이터를 합치는 것까지 배울 수 있었다.

 

 

📁 목차

1. ssh 로 PC 에 우분투 연결하기
2. MariaDB 세팅
3. pymysql
4. 한국 주식 정보 데이터 가져와서 DB에 저장하기
5. 결측치 제거
6. 네이버 주식 데이터 가져와서 삼성전자 주식 정보 DB에 저장하기
7. 한국 주식 정보와 네이버 주식 정보가 담긴 테이블 합치기

 

 

---

 

 

 

들어가며

 

m2 환경 세팅 기준으로 글을 작성하였으니 유의 부탁드립니다.

 

 

 

 

 

 

 

 

ssh 로 PC 에 우분투 연결하기

 

 

맥은 ssh 가 기본으로 설치되어 있다.

 

따라서, ssh 의 기초 세팅만 해주면 된다.

 

하나의 서버가 되어줄 우분투에 ssh 설정도 마치면, 간단한 입력으로 우분투에 연결할 수 있다.

 

 

ssh [user_name]@[ubuntu_ip]

 

 

맥북 터미널에 입력하면, 맥북에서도 우분투 터미널 환경에서 작업할 수 있다.

 

 

 

 

MariaDB 세팅

 

 

우분투 서버에서 mariaDB 를 다룰 것이기 때문에, 우분투에 mariaDB 를 설정하기로 한다.  

 

 

sudo apt install mariadb-server

sudo mysql_secure_installation

 

 

초기 세팅을 완료하고, mariadb 서버가 잘 실행되고 있는 지 확인할 수 있다.

 

 

sudo service mariadb status

 

 

첫 설치인 경우, 자동으로 서버에 로그인 되어 있게 된다.

 

만약, 우분투 시스템을 종료하고 싶다면

 

 

sudo shutdown -h now

 

 

를 입력하여 시스템 접속을 끊고 종료할 수 있다.

 

 

나중에 DB 접속을 다시하고 싶다면,

 

 

mysql -u root -p

 

 

를 입력하면 mariadb 에 접속할 수 있다.

 

 

CREATE USER 'user_name'@'%' IDENTIFIED BY 'pass_word';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%';

 

 

접속한 후, mariadb 의 계정을 생성하고 그 계정에 모든 권한을 부여해준다.

 

 

sudo systemctl enable mariadb

 

 

이렇게 시스템을 다시 켰을 때 자동으로 접속할 수 있도록 하는 명령어도 있다.

 

 

 

 

pymysql

 

 

파이썬에는 파이썬으로도 mysql 을 실행할 수 있도록, pymysql 이라는 모듈이 있다.

 

import pymysql # 없으면 pip install pymysql

 

이 모듈을 사용하여 우분투에 설정한 DB 에 접속할 수 있도록 한다.

 

 

try:
    con = pymysql.conect(host='ip주소', user='mariadb 계정 이름', 
    password='mariadb 비번', 
    charset='utf8', db='db 이름')
   	
    cur = con.cursor()
    
except Exception as e:
	print("error -> ", e)

 

db 계정에 대한 정보를 입력해두고, 실행하게 되면 해당 db 와 연결할 수 있다.

(자주 사용하기 때문에, dbconnect.py 의 connection 함수로 만들어 두자.)

 

 

 

cur.execute("""CREATE TABLE 'test' (
    `InvoiceNo` VARCHAR(30), 
    `StockCode` VARCHAR(30),
    `Description` VARCHAR(255)
)""")

 

그 후, cur 에게 전달하여 sql 쿼리문을 작성해 실행할 수 있다.

(그냥 mariadb 에서 바로 쿼리문을 작성해도 된다.)

 

 

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

with open("./data/dataset3.csv", "r", encoding='utf-8') as f:
    for idx, line in tqdm(enumerate(f)):
        if idx != 0:
            cur.execute(sql, [x.replace('"', "") for x in line.split(";")])

 

새로운 테이블에 어떤 형식으로 넣을 것인지 설정하고, cur 에게 전달할 수 있다.

 

con.commit()

 

을 입력하지 않으면 db 에 데이터가 들어가지 않으니, 마지막 과정에 꼭 실행해주자.

 

 

cur.fetchall()

 

 

을 사용하여 안의 데이터를 모두 꺼내올 수 있다.

 

 

 

 

한국 주식 정보 데이터 가져와서 DB에 저장하기

 

 

한국 주식 정보 데이터 시스템 홈페이지에서 주식 전종목의 기본 정보 페이지에 들어가 개발자 모드를 킨 후, 조회 버튼을 누른다. 

 

 

 

네트워크 페이지에 getJsonData.cmd 가 보인다.

 

request url 을 확인할 수 있고, method 방식이 post 라는 것을 알 수 있다.

 

 

 

그리고 payload 에 들어가면 데이터 전달 방식을 알 수 있다.

 

 

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()

 

 

rt 를 확인하면 전달받은 데이터가 dictionary 구조로 되어있는 것을 볼 수 있다.

 

 

CREATE TABLE KRX(
    ISU_CD VARCHAR(200),
    ISU_SRT_CD VARCHAR(200),
    ISU_NM VARCHAR(200),
    ISU_ABBRV VARCHAR(200), ISU_ENG_NM VARCHAR(200),
    LIST_DD VARCHAR(200),
    MKT_TP_NM VARCHAR(200), SECUGRP_NM VARCHAR(200), SECT_TP_NM VARCHAR(200), KIND_STKCERT_TP_NM VARCHAR(200), PARVAL VARCHAR(200),
    LIST_SHRS VARCHAR(200),
    PRIMARY KEY(ISU_SRT_CD)
);

 

mariadb 에 KRX 라는 테이블을 만들어준다.

 

 

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()

 

 

그 후, 파이썬을 통해 KRX 테이블에 값을 넣어준다.

 

 

 

import pandas as pd

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

# 1번 행부터 끝까지 다보는데, 특정 열만 보기
krx.loc[1:, ['ISU_D', 'ISU_NM', 'ISU_SRT_CD']]

# 0번 행부터 끝까지 다보는데, 1,2,3열 만 보기
krx.iloc[0:, [1, 2, 3]]


# 보통주만 보기
mask = krx['KIND_STKCERT_TP_NM'] == '보통주'
krx[mask]

# 몇개의 행과 열로 이루어진지 확인
krx[mask].shape

# 행 보기
krx[mask].columns

 

 

파이썬을 통해 데이터에 접근하여 확인하는 방법이다.

 

 

 

 

 

 

결측치 제거

 

 

DataFrame 형식인 경우, 데이터에 Nan 값이 들어있는 경우가 있다.

 

그 때, 사용하는 메서드가 dropna() 이다.

 

pd.read_html(r.text)[index].dropna()

 

 

결측치가 있는 경우, dropna 를 사용하면 Nan 값이 있는 데이터는 제거된다.

 

 

 

 

 

네이버 주식 데이터 가져와서 삼성전자 주식 정보 DB에 저장하기

 

 

네이버 주식은 get 방식으로 데이터를 불러 올 수 있다.

requtests 의 get 을 사용하여 데이터를 불러오고 read_html 로 만든다.

 

 

import requests
import pandas as pd

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()

 

 

여기서 head 는 항상 고정값으로, 그냥 들어가면 파이썬의 접근을 막기 때문에 항상 head 를 기억해두어야 한다.

크롬을 사용해야할 때, head 는 코드가 달라지지 않는다고 한다.

 

참고로 위 코드는 따로 naver.py 라는 파일로 만들어 진행했다.

 

 

from naver import get_stock # naver.py 의 get_stock 불러오기
import numpy as np

from dbconnect import connection # dbconnect.py 의 connection 불러오기


samsung = get_stock("005930", 2) # 2페이지 삼전 주식 가져오기

samsung.columns # df 열 값 확인

samsung.info # df 데이터 확인

 

 

삼성 전자의 주식 정보 데이터를 dataframe 으로 가져왔다.

 

 

# 데이터를 int64 로 변환
samsung[['종가', '전일비', '시가', '고가', '저가', '거래량']] = \
samsung[['종가', '전일비', '시가', '고가', '저가', '거래량']].astype(np.int64)

# 거래 금액 column 자동으로 생성(값 계산 포함)
samsung['거래금액'] = samsung['종가'] * samsung['거래량']

# 종목 column 자동으로 생성(값 포함)
samsung['종목'] = '005930'

# 기존 samsung 에서 column 추가한 데이터로 바꿈
samsung = samsung[['종목', '날짜', '종가', '전일비', '시가', '고가', '저가', '거래량', '거래금액',]]

 

새롭게 만든 거래 금액과 종목 코드를 포함한 df 로 바꾼다.

 

 

create table stock_day( 
    symbol varchar(200), 
    stock_date date, 
    close bigint,
    `before` bigint, 
    open bigint, 
    high bigint, 
    low bigint,
    vol bigint,
    money bigint,
    primary key(symbol,stock_date)
);

 

종목 -> symbol, 날짜 -> stock_date, 종가 -> close, 전일비 -> before, 시가 -> open, 고가 -> high, 저가 -> low, 거래량 -> vol, 거래금액 -> money 로 테이블을 만들고 종목과 날짜가 primary key 로 중복된 값이 없도록 저장한다.

(참고로, `before` 에 를 붙이지 않으면 구문 오류가 나서 저렇게 작성했는데, before_ 로 표현해도 해결이 된다고 한다.)

 

 

# stock_day 테이블 값 넣기
sql = "INSERT INTO stock_day VALUES (%s , %s , %s , %s , %s , %s , %s , %s , %s)"

# db 연결
cur, con = connection();

# db 에 sql 문을 통해 넣기
for idx, row in samsung.iterrows():
    try:
        cur.execute(sql,list(row.values))
    except:
        pass

con.commit()

 

stock_day 테이블에 삼성전자 주식 정보가 들어가게 된다.

 

 

 

 

한국 주식 정보와 네이버 주식 정보가 담긴 테이블 합치기

 

 

삼성 전자 주식 정보 기반으로, 한국 주식 정보와 네이버 주식 정보가 담긴 테이블을 합칠 수 있다.

 

SELECT *
FROM stock_day AS b
JOIN KRX AS c
ON c.ISU_SRT_CD = b.symbol;

 

 

이제, stock_day 와 KRX 테이블을 join 하면, 두 테이블의 종목 이름을 기준으로 데이터가 합쳐진 테이블을 볼 수 있다.