[MySQL] (20200827) TIL

TIL - 인프런 강의를 듣고 공부한 내용을 정리(강의 링크)

• 공부한 내용
  1. pymysql과 pandas
  2. 외래키(Foreign Key)

1. pymysql과 pandas

• pandas란 : pandas란 python을 위한 데이터 분석 툴이다. pymysql과 함께 pandas를 이용하면 더 손쉽게 sql실행 결과를 얻을 수 있다.
• pandas 설치

pip install pandas

• pandas.read_sql(쿼리, 연결된 db connection객체) : SQL구문을 읽는다. (공식 문서)
• pandas.DataFrame.to_csv('파일명', sep='길이 1의 구분자') : 객체를 csv파일로 생성해줌. (공식 문서)

# 예시

import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
username = 'root'
password = '설정한 비밀번호'
database_name = 'student_mgmt'

db = pymysql.connect(
    host=host_name,
    port=host_port,
    user=username,
    passwd=password,
    db=database_name,
    charset='utf8'
)

SQL = "SELECT * FROM students"
df = pd.read_sql(SQL, db)
print(df) # 1
df.to_csv('student.csv', sep=',') # 2 

• 위 예시 코드의 #1부분이 실행되면 SQL구문이 실행된 결과가 다음과 같이 보여짐.

  

• #2부분이 실행되면, 코드를 실행한 위치에 to_csv()의 첫 번째 파라미터로 넘겨준 파일이름으로 csv파일이 생성됨. 구분자는 두 번째 파라미터로 지정해준 기호가 된다. 참고로 index=False로 주면, 아래 두 번째 이미지의 하이라이트 부분의 index번호가 부여되지 않는다.(이는 db에 있는 데이터와는 별개로 pandas에 의해 부여되는 index번호임)

  

  

2. 외래키(Foreign Key) (혹은 외부키)

• 외래키(Foreign Key) : 한 테이블의 필드(Attribute) 중 다른 테이블의 행(Row)를 식별할 수 있는 키. (위키백과)

• 외래키를 만드는 이유 : 두 테이블 사이에 관계를 선언하여, 데이터의 무결성을 보장. 두 테이블 간의 관계에서 데이터의 정확성을 보장하는 제약 조건을 넣는 것임.

  • 현업에서는 꼭 필요한 경우에만 사용하는 경우가 많음. 그 이유는 비즈니스 로직이 다양하기 때문에, 제약을 걸어 놓을 경우, 예외 적인 비즈니스 로직 처리가 어렵기 때문이다.
  • 무결성 : 연산(삽입, 삭제, 갱신 등)후에도 DB에 저장된 데이터가 정해진 제약 조건을 항상 만족해야 함.
    

    

• 예시 SQL구문

  • sqlDB에 userTbl, buyTbl 두 테이블을 생성

  • buyTbl userID 컬럼은 userTbl의 userID를 참조(외래키로 사용)한다.

    FOREIGN KEY (userID) REFERENCES userTbl(userID)

    • (★) INSERT구문을 이용해 buyTbl에 새로운 데이터를 추가할 때 추가하는 userID값이 userTbl의 userID값 중에 존재하는 지 확인해야한다. 그렇지 않으면 ERROR가 발생함. 또한 반대로, userTbl의 userID값들 중, buyTbl의 userID값들 중에서 참조하고 있는 값이 있다면 , 해당 데이터를 DELETE구문을 이용하여 삭제 하는 것이 불가능하다.

    • 아래 구문에서, userTbl의 userID값 중에 'STJ'라는 값은 존재하지 않으므로, 에러가 발생한다.

      INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2)

    • 아래 구문 또한 에러가 발생한다. userTbl의 userID중 하나인 'BBK'는 buyTbl에서 참조하고 있기 때문이다.

      DELETE FROM userTbl WHERE userID = 'BBK';

DROP DATABASE IF EXISTS sqlDB;
CREATE DATABASE sqlDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
USE sqlDB;
DROP TABLE IF EXISTS userTbl;
CREATE TABLE userTbl (
    userID CHAR(8) NOT NULL PRIMARY KEY,
    name  VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    addr  CHAR(2) NOT NULL,
    mobile1 CHAR(3),
    mobile2 CHAR(8),
    height SMALLINT,
    mDate  DATE
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS buyTbl;
CREATE TABLE buyTbl (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID CHAR(8) NOT NULL,
    prodName CHAR(4),
    groupName CHAR(4),
    price  INT NOT NULL,
    amount  SMALLINT NOT NULL,
    FOREIGN KEY (userID) REFERENCES userTbl(userID)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2013-12-12');
INSERT INTO userTbl VALUES('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
INSERT INTO userTbl VALUES('YJS', '윤종신', 1960, '경남', NULL, NULL, 170, '2005-5-5');
INSERT INTO userTbl VALUES('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
INSERT INTO userTbl VALUES('JKW', '조관우', 1965, '경기', '016', '99999999', 172, '2010-10-10');
INSERT INTO userTbl VALUES('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');

INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('SSK', '책', '서적', 15, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '책', '서적', 15, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '책', '서적', 15, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);