데이터베이스 (MySQL) - pymysql 모듈 이해 및 실습

6. pymysql 모듈 이해 및 실습

6.1 pymysql 라이브러리 소개 및 설치

  • mysql을 python에서 사용할 수 있는 라이브러리 (pymysql 라이브러리 이외에도 MySQLdb(Mysql-pytion), MySQL connector 등 다양한 라이브러리 존재)
  • 이 중에서 설치가 가장 쉬운 라이브러리
  • 설치

    • pip install PyMySQL
  • 일반적인 mysql 핸들링 코드 작성 순서

    1. PyMySql 모듈 import
    2. pymysql.connect() 메소드를 사용하여 MySQL에 연결
      • 호스트명, 포트, 로그인, 암호, 접속할 DB 등을 파라미터로 지정
    3. MySQL 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴
    4. Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 전송
    5. SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 서버로부터 가져온 데이타를 코드에서 활용
    6. 삽입, 갱신, 삭제 등의 DML(Data Manipulation Language) 문장을 실행하는 경우, INSERT/UPDATE/DELETE 후 Connection 객체의 commit() 메서드를 사용하여 데이타를 확정
    7. Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫음
  • PyMySql 모듈 import
import pymysql
  • pymysql.connect() 메소드를 사용하여 MySQL에 연결
    • 호스트명, 포트, 로그인, 암호, 접속할 DB 등을 파라미터로 지정
    • 주요 파라미터
      • host : 접속할 mysql server 주소
      • port : 접속할 mysql server 의 포트 번호
      • user : mysql ID
      • passwd : mysql ID의 암호
      • db : 접속할 데이터베이스
      • charset='utf8' : mysql에서 select하여 데이타를 가져올 때 한글이 깨질 수 있으므로 연결 설정에 넣어줌
실습8
1. pymysql 설치
2. mysql workbench 또는 mysql 터미널로 dave 데이터베이스 만들기
3. 다음 테이블 생성 코드까지 실행한 후, mysql workbench 또는 mysql 터미널로 posts_test 테이블 구조 확인하기
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='student_mgmt', charset='utf8')
db
  1. MySQL 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴
  2. Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 전송
  • 테이블 생성
    • Cursor Object 가져오기: cursor = db.cursor()
    • SQL 실행하기: cursor.execute(SQL)
    • 실행 mysql 서버에 확정 반영하기: db.commit()
cursor = db.cursor()
cursor
  • cursor 는 control structure of database 입니다. (연결된 객체로 보셔도 좋습니다.)
sql = """
    CREATE TABLE korea (
           id INT UNSIGNED NOT NULL AUTO_INCREMENT,
           name VARCHAR(20) NOT NULL,
           model_num VARCHAR(10) NOT NULL,
           model_type VARCHAR(10) NOT NULL,
           PRIMARY KEY(id)
    );
    """
  • SQL 실행 (Cursor 객체의 execute() 메서드를 사용하여 INSERT, UPDATE 혹은 DELETE 문장을 DB 서버에 보냄)
cursor.execute(sql)
cursor.execute("SHOW TABLES")
  • 삽입, 갱신, 삭제 등이 모두 끝났으면 Connection 객체의 commit() 메서드를 사용하여 데이타를 Commit
db.commit()
  • Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫음
db.close()
import pymysql

# 접속
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')

# 커서 가져오기
cursor = db.cursor()

# SQL 문 만들기
sql = '''
            CREATE TABLE korea2 (
                   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                   name VARCHAR(20) NOT NULL,
                   model_num VARCHAR(10) NOT NULL,
                   model_type VARCHAR(10) NOT NULL,
                   PRIMARY KEY(id)
            );
        '''

# 실행하기
cursor.execute(sql)

# DB에 Complete 하기
db.commit()

# DB 연결 닫기
db.close()

좀더 깔끔하게 작성한다면, 다음과 같이 작성합니다.

import pymysql

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
    with db.cursor() as cursor:
        sql = '''
            CREATE TABLE korea (
                   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                   name VARCHAR(20) NOT NULL,
                   model_num VARCHAR(10) NOT NULL,
                   model_type VARCHAR(10) NOT NULL,
                   PRIMARY KEY(id)
            );
        '''
        cursor.execute(sql)
        db.commit()
finally:
    db.close()
line by line 프로그래밍 코드

만약 로컬 환경에서 연결이 안될 경우 - bitnami 설정

  • Go phpadmin -> use mysql
GRANT ALL PRIVILEGES ON *.* to 'root'@'%' identified by "암호"
import pymysql
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = '''
            CREATE TABLE cpu_info (
                   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                   name VARCHAR(20) NOT NULL,
                   model_num VARCHAR(10) NOT NULL,
                   model_type VARCHAR(10) NOT NULL,
                   PRIMARY KEY(id)
            )
'''
cursor.execute(sql)
db.commit()
db.close()
# 잘못만들어졌으면,  DROP DATABASE [IF EXISTS] dbname; 로 테이블 삭제 후 재생성
  • 데이터 삽입(INSERT)
    • Cursor Object 가져오기: cursor = db.cursor()
    • SQL 실행하기: cursor.execute(SQL)
    • 실행 mysql 서버에 확정 반영하기: db.commit()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
    cursor = db.cursor()
    for num in range(10, 20):
        sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(" + str(num) + ", 'i5', '7700', 'Kaby Lake')"
        print(sql)
        cursor.execute(sql)

    db.commit()
    print(cursor.lastrowid)
finally:
    db.close()
  • 데이터 조회(SELECT)
    • Cursor Object 가져오기: cursor = db.cursor()
    • SQL 실행하기: cursor.execute(SQL)
    • mysql 서버로부터 데이터 가져오기: fetch 메서드 사용
      • fetchall(): Fetch all the rows
      • fetchmany(size=None): Fetch several rows
      • fetchone(): Fetch the next row
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
    cursor = db.cursor()
    sql = "SELECT * FROM cpu_info"
    cursor.execute(sql)
    result = cursor.fetchall()
    for row_data in result:
        print(row_data[0])
        print(row_data[1])
        print(row_data[2])
        print(row_data[3])        
finally:
    db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
result = cursor.fetchall()
result
type(result)
for data in result:
    print(data[0])
    print(data[1])
    print(data[2])
    print(data[3])
    
result = cursor.fetchone()
print(result)
for row_data in result:
    print(row_data[0], row_data[1], row_data[2], row_data[3])

db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
    with db.cursor() as cursor:
        sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
        cursor.execute(sql)
        
        while result:
            result = cursor.fetchone()
            print(result)        
finally:
    db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
while result:
    result = cursor.fetchone()
    print(result)     
db.close()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
    with db.cursor() as cursor:
        sql = "SELECT * FROM cpu_info"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row_data in result:
            print(row_data)
finally:
    db.close()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
result = cursor.fetchall()
for row_data in result:
    print(row_data)
db.close()
  • 데이터 수정(UPDATE)
    • Cursor Object 가져오기: cursor = db.cursor()
    • SQL 실행하기: cursor.execute(SQL)
    • 실행 mysql 서버에 확정 반영하기: db.commit()
import pymysql
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
    with db.cursor() as cursor:
        sql = "UPDATE cpu_info SET model_type='%s' WHERE name = 'i7'" % '카비레이크'
        cursor.execute(sql)
        db.commit()
        print(cursor.rowcount)
finally:
    db.close()
  • 데이터 삭제(DELETE)
    • Cursor Object 가져오기: cursor = db.cursor()
    • SQL 실행하기: cursor.execute(SQL)
    • 실행 mysql 서버에 확정 반영하기: db.commit()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
hit_count = 20
try:
    with db.cursor() as cursor:
        sql = "DELETE FROM cpu_info WHERE name = '%s'" % 'i7'
        cursor.execute(sql)
        db.commit()
        print(cursor.rowcount)
finally:
    db.close()
연습문제
* 위 pymysql 샘플코드를 참고해서 다음과 깉이 작업하기

웹페이지(http://www.enuri.com/list.jsp?cate=070701) 에서 1위 ~ 10위까지 데이터 입력하기

- id는 자동 증가
- name 은 코어 이름
- model_num 은 코어 모델 번호
- model_type 은 코드명 (카비레이크, 스카이레이크 로 작성)

- 예: 코어 i7 7700 카비레이크
-> name 은 i7
-> model_num 은 7700
-> model_type 은 카비레이크
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
    cursor = db.cursor()
    for num in range(10, 20):
        sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(" + str(num) + ", 'i5', '7700', 'Kaby Lake')"
        print(sql)
        cursor.execute(sql)

    db.commit()
    print(cursor.lastrowid)
finally:
    db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(1, 'i5', '7700', 'Kaby Lake')"
cursor.execute(sql)
db.commit()
db.close()

SQL 활용

로우(Row)수 세기: COUNT

mysql> SELECT COUNT(*) FROM cpu_info;
+----------+
| COUNT(*) |
+----------+
| 9        |
+----------+

각 name 별 로우(Row) 수

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
    with db.cursor() as cursor:
        sql = "SELECT name, COUNT(*) FROM cpu_info GROUP BY name"
        cursor.execute(sql)
        result = cursor.fetchall()
        for row_data in result:
            print(row_data)
finally:
    db.close()
  • name의 모든 레코드들을 한데 묶기 위해 GROUP BY 절을 사용해야 함

각 name, model_num 별 로우(Row) 수

  • SELECT name, model_num, COUNT(*) FROM cpu_info GROUP BY name, model_num

AS 와 쓴 경우

SELECT COUNT(*) AS N_STUDENT FROM STUDENT;

HAVING

  • WHERE 절에서는 집계함수를 사용 할 수 없다.
  • HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
  • HAVING절은 GROUP BY절과 함께 사용
SELECT ID_LEC COUNT(*) AS N_REG FROM LECTURES GROUP BY ID_LEC HAVING COUNT(*) >= 5;

다른 테이블과 함께 검색

SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno

복합 검색

SELECT job, SUM(sal) 
  FROM emp  
 WHERE job != 'SALES'      -- 판매원은 제외
 GROUP BY job              -- 업무별로 Group By 
HAVING SUM(sal) > 5000     -- 전체 월급이 5000을 초과하는
 ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬


JOB                  급여합계
------------------ ----------
MANAGER                  8275
ANALYST                  6000
SALESMAN                 5600
SELECT b.dname, COUNT(a.empno) 
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
 GROUP BY b.dname
HAVING COUNT(a.empno) > 5;


DNAME          사원수
------------ -------
SALES              6