데이터베이스 예제코드 (MySQL+AWS) - pymysql 로 실습 환경 만들기

3. pymysql 로 실습 환경 만들기

  • pymysql 로 SQL 사용하기 - read_sql() 메서드 익히기
import pymysql
import pandas as pd
host_name = "0.0.0.0"
username = "root"
password = "korea123"
database_name = "sakila"
  • db = pymysql.connect() 내의 인자는 다음 두 방식으로 사용해도 무방합니다.
    • host='서버주소', port=3306, user='아이디', passwd='암호', db='데이터베이스이름', charset='utf8'
    • '서버주소', 3306, '아이디', '암호', '데이터베이스이름', 'utf8'
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)
SQL = "SHOW TABLES"
  • read_sql(쿼리, 연결된 db connection 객체)
df = pd.read_sql(SQL, db)
df
Out[17]:
Tables_in_student_mgmt
0 students
SQL = "SELECT * FROM students"
df = pd.read_sql(SQL, db)
df
Out[19]:
id name gender birth english math korean
0 1 dave man 1983-07-16 90 80 71
1 2 minsun woman 1982-10-16 30 88 60
2 3 david man 1982-12-10 78 77 30
3 4 jade man 1979-11-01 45 66 20
4 5 jane man 1990-11-12 65 32 90
5 6 wage woman 1982-01-13 76 30 80
6 7 tina woman 1982-12-03 87 62 71
cursor = db.cursor()
sql = """

DROP DATABASE IF EXISTS student_mgmt;
CREATE DATABASE student_mgmt DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

USE student_mgmt;
DROP TABLE IF EXISTS students;
CREATE TABLE students (
  id TINYINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  gender ENUM('man','woman') NOT NULL,
  birth DATE NOT NULL,
  english TINYINT NOT NULL,
  math TINYINT NOT NULL,
  korean TINYINT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('dave', 'man', '1983-07-16', 90, 80, 71);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('minsun', 'woman', '1982-10-16', 30, 88, 60);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('david', 'man', '1982-12-10', 78, 77, 30);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('jade', 'man', '1979-11-1', 45, 66, 20);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('jane', 'man', '1990-11-12', 65, 32, 90);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('wage', 'woman', '1982-1-13', 76, 30, 80);
INSERT INTO students (name, gender, birth, english, math, korean) VALUES ('tina', 'woman', '1982-12-3', 87, 62, 71);
"""
print(sql)
cursor.execute(sql)
db.commit()

테이블에 데이터 한번에 입력하기

  • mysql CLI로 CSV 파일 LOAD 하기 (현업에서는 mysql CLI로 많이 사용합니다.)
mysql> LOAD DATA INFILE 'CSV 데이터 파일' INTO TABLE student_mgmt.students (name, gender, birth, english, math, korean);
  • Mysql Workbench
    • Go to Schemas -> Find student_mgmt database (만약 없으면, Schemas 메뉴의 refresh 버튼) -> Go to Tables -> Go to students -> Table Data Import Wizard -> student.csv File 선택 -> Source Column / Dest Column 설정 -> Import