[업무자동화] (1) DB화 (DB만들기)

1. 마리아 DB 설치
2. HeidiSQL 실행
3. 세션, database, table 생성
4. python 에서 pymysql 라이브러리 설치 (pip install pymysql)
5. python 에서 다음 코드로 마리아 DB 연결

import pymysql
conn = None 
cur = None 
sql ="" 
conn = pymysql.connect(host="xxx", user='root', password='xxx', db='pythondb', charset='utf8')
cur = conn.cursor() 
cur.execute("select * from xxx") 
row = cur.fetchall() 
print(row[0])

 

■ 마리아 DB

1. database 생성

CREATE DATABASE database_name
CHARACTER SET = 'utf8'

  (1-1) database 확인

SHOW DATABASES

  (1-2) database 선택

USE database_name

  (1-3) database 삭제

DROP DATABASE database_name

 

2. table 생성

CREATE TABLE table_name

USE database_name
CREATE TABLE table_name(
	column_1_name INT NOT NULL AUTO_INCREMENT,
    column_2_name VARCHAR(100) NOT NULL,
    column_3_name VARCHAR(40) NOT NULL,
    column_4_name DATE,
    PRIMARY KEY(column_1_name)
)
COLLATE = 'utf8_general_ci'
ENGINE = 

  (2-1) table 확인

SHOW CREATE TABLE 'database_name', 'table_name'

  (2-2) table 열 추가

ALTER TABLE table_name ADD COLUMN(
	add_column_name_1 DATETIME DEFAULT current_timestamp(),
    add_column_name_2 POINT NOT DEFAULT ST_GeomFromText('POINT(0 0)'),
    add_column_name_3 TEXT CHARSET utf8 DEFAULT
    )

UPDATE table_name SET add_column_name_3=NULL WHERE id=3

  (2-3) table 열 삭제

ALTER TABLE table_name DROP COLUMN [IF EXISTS] column_name

  (2-4) table constraint 추가

CREATE TABLE table_name(
	column_name_1 INT PRIMARY KEY AUTO_INCREMENT,
    column_name_2 VARCHAR(100),
    column_name_3 VARCHAR(100),
    column_name_4 INT,
    column_name_5 VARCHAR(100),
    column_name_6 INT
)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
	CHECK((column_name_4 + column_name6)=0)    

  (2-5) table constraint 제거

ALTER TABLE table_name
DROP CONSTRAINT constraint_name

CREATE TABLE table_name(
	column_name_1 INT,
    column_name_2 INT,
    column_name_3 INT,
    CONSTRAINT CHECK(column_name_1 > column_name_2),
    CONSTRAINT constraint_name_1 CHECK(column_name_1 = column_name_3)
)

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = table_name    

  (2-6) table 열 유형 수정

ALTER TABLE table_name MODIFY column_name_1 bigint unsigned

  (2-7) table 열의 이름과 유형을 변경

ALTER TABLE table_name CHANGE column_name_before column_name_after bigint unsigned auto_increment

  (2-8) table 열 삭제, 열 추가, 열 이름과 유형 변경

ALTER TABLE table_name DROP column_name_1, ADD column_name_new INT, CHANGE column_name_before column_name_after INT

  (2-9) table 삭제

DROP TABLE table_name
SHOW TABLES

(2-10) table description 확인

DESC table_name

 

3. table insert

INSERT INTO table_name VALUES('column_1_value_1', 'column_2_value_1', 'column_3_value_1')
INSERT INTO table_name(column_2, column_3) VALUES('column_2_value_1', 'column_3_value_1')

  (3-1) 특정 데이터 값 변경

UPDATE table_name SET column_name_1 = 'modification_1' WHERE column_name_2 = 'condition_1'

  (3-2) 데이터 삭제

DELETE FROM table_name WHERE column_name_1 = 'condition_1'

4. 엑셀 파일을 insert

  - "도구" - "CSV 파일 가져오기" 에서

  - 옵션 : "입력 파일에 로컬 형식의 숫자를 포함" 만 선택

  - 중복 행 처리 : "INSERT(오류 발생 가능)" 선택

  - 필드 종결자 => ,

  - 필드를 감싸는 구분자 => "

  - 필드를 벗어나는 구분자 => "

  - 줄 종결자 => \r\n

  - 열 : "모두" 선택

 

5. 엑셀에서 마리아 DB 핸들링

import pymysql
import pandas as pd
from pandas import DataFrame as df

conn = None
cur = None
sql = ''
conn = pymysql.connect(host='', user='root', password='', db='', charset='utf8')
cur = conn.cursor()
row = cur.fetchall()

sql_table_index = "DESC appendix_table"
cur.execute(sql_table_index)
table_index = cur.fetchall()

sql_contents = "select * from pythondb.appendix_table where page = 'pl_status' and year_num = '2021' "
cur.execute(sql_contents)
contents = cur.fetchall()

df_temp = df(contents)
df_temp.columns = [table_index[0][0], table_index[1][0], table_index[2][0], table_index[3][0], table_index[4][0], table_index[5][0], table_index[6][0]]

  (5-1) dataframe pivot

pivot_temp = pd.pivot_table(df_temp, index = 'item', columns = ('year_num', 'period'), values = 'item_value', aggfunc = 'sum')                           

  (5-2) index 순서 변경

pivot_temp = pivot_temp.reindex(['direct premium written','net premium earned','underwriting profit','net investment profit','non operating profit','pre-tax profit','income taxes','net profit','annualized eps' ])

  (5-3) 변화율(delta ratio) 열 추가

pivot_temp['delta'] = pivot_temp[('2021', '1q')] / pivot_temp[('2020', '1q')] - 1