6 장 Python에서 MySQL데이터의 접근

6.1 Mysql 사용자를 추가

MySQL 관리자로 로그인
```
$ mysql -u root -p
Enter password:
```

로컬에서 접속 가능한 사용자 추가

mysql> CREATE user '사용자'@'localhost' IDENTIFIED BY '사용자_비밀번호';

DB 권한 부여하기

mysql> GRANT ALL PRIVILEGES ON *.* to '사용자'@'localhost'; # 모든 DB에 접근 가능 *.*
mysql> GRANT ALL PRIVILEGES ON db_name.* to '사용자'@'localhost'; # 특정 DB에만 접근 가능

mysql> FLUSH PRIVILEGES; # 새로운 세팅 적용

사용자 계정 삭제

mysql> DROP USER '사용자'@'localhost';

6.2 Python에서 MySQL 접속하기

6.2.1 Python의 MySql모듈 설치

Python의 MySql모듈은 여러가지가 있음
- PyMySQL
- mysql-connector-python ( >= MySQL 8.0) or mysql.connector (< MySQL 8.0)
여기서는 PyMySQL를 이용하여 접근하는 방법을 예시함
```
$ pip install PyMySQL
```

6.2.2 Python에서 MySQL 접속 절차

PyMySql 모듈을 import
MySQL 연결: pymysql.connect()
- 호스트명, 로그인, 암호, 접속할 DB 등 지정
DB커서 객체생성: Connection 객체로부터 cursor() 호출
SQL문 실행
- Cursor객체의 메소드를 사용하여 SQL을 DB서버에 전송 & Fetch
  1. SQL을 DB서버에 전송: execute()
  2. Fetch: fetchall(), fetchone(), fetchmany(n)
- Connection 객체의 commit(): 삽입, 갱신, 삭제 (INSERT/UPDATE/DELETE)
DB 연결 종료: Connection객체의 close()

6.2.3 Python에서 MySQL 접속 예시

# STEP 1
import pymysql

# STEP 2: MySQL Connection 연결
con = pymysql.connect(host='localhost', user='mysql_user_id', password='_password_',
                       db='access_db', charset='utf8') # 한글처리 (charset = 'utf8')
 
# STEP 3: Connection 으로부터 Cursor 생성
cur = con.cursor()
 
# STEP 4: SQL문 실행 및 Fetch
sql = "SELECT player, birth FROM baseball"
cur.execute(sql)
 
# 데이타 Fetch
rows = cur.fetchall()
print(rows)     # 전체 rows

# STEP 5: DB 연결 종료
con.close()

6.2.4 Python에서 MySQL 실습

Mysql 접속

import pymysql
import pandas as pd

con = pymysql.connect(host='localhost', user='mysql_user_id', password='_password_',
                      db='access_db', charset='utf8', # 한글처리 (charset = 'utf8')
                      autocommit=True, # 결과 DB 반영 (Insert or update)
                      cursorclass=pymysql.cursors.DictCursor # DB조회시 컬럼명을 동시에 보여줌
                     )
cur = con.cursor()

특정 테이블 불러오기

sql = "SELECT * FROM customers" # customers 테이블 전체를 불러옴
cur.execute(sql)
rows = cur.fetchall()
con.close() # DB 연결 종료
print(rows)

[{'회원번호': 'CID_0001', '법인유무': 1, '생년월일': 1981, '성별': 1, '직업': '14', '결혼유무': 'N', '회원등급': '다이아몬드', '가입일': '19970709', '2012구매건수': 87, '2012구매금액': 5106270, 'addr1': '경기', 'addr2': '오산시'}, {'회원번호': 'CID_0002', '법인유무': 1, '생년월일': 1984, '성별': 2, '직업': '0', '결혼유무': 'N', '회원등급': '일반', '가입일': '19981207', '2012구매건수': 5, '2012구매금액': 206970, 'addr1': '대전', 'addr2': '유성구'} ...

데이터프레임형태로 변환
```
customers = pd.DataFrame(rows)

customers
```
Python으로 불러온 테이블을 데이터프레임으로 출력

데이터 전처리

idx = customers[(customers['성별'] != 1) & (customers['성별'] != 2)].index
customers.drop(idx, inplace=True) # 성별이 1 또는 2가 아닌 행 제거

customers.loc[customers['성별'] == 1, '성별'] = '남성' # 1 ==> 남성
customers.loc[customers['성별'] == 2, '성별'] = '여성' # 2 ==> 여성

데이터 시각화

여성들이 남성들에 비해 ‘2012구매금액’ 대비 ’2012구매건수’가 높은 경향을 보임
```
import plotly.express as px
fig = px.histogram(customers, x="2012구매금액", y="2012구매건수", color="성별", marginal="rug")
fig.show()
```
2012구매금액’ 대비 ’2012구매건수’의 히스토그램