pymysql 로 실습 환경 만들기
3. pymysql 로 실습 환경 만들기¶
- pymysql 로 SQL 사용하기 - read_sql() 메서드 익히기
In [2]:
import pymysql
import pandas as pd
In [11]:
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'
In [12]:
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'
)
In [15]:
SQL = "SHOW TABLES"
- read_sql(쿼리, 연결된 db connection 객체)
In [16]:
df = pd.read_sql(SQL, db)
In [17]:
df
Out[17]:
In [19]:
SQL = "SELECT * FROM students"
df = pd.read_sql(SQL, db)
df
Out[19]:
In [ ]:
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 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