pymysql 모듈 이해 및 실습

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.

잔재미코딩 소식 공유

좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 강의 사이트와 유투브 채널을 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다

● 직운영 온라인 강의 서비스 오픈

● 잔재미코딩 유투브 오픈 [구독해보기]

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

In [ ]:

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 테이블 구조 확인하기

In [ ]:

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='student_mgmt', charset='utf8')

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

웹개발과 SQL 로 IT 와 데이터 시작하기 (무료 강의)

IT를 처음 익히는 분들을 위한, 웹과 데이터분야를 익히며 IT 기본기를 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

db

MySQL 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴
Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 전송

테이블 생성
- Cursor Object 가져오기: cursor = db.cursor()
- SQL 실행하기: cursor.execute(SQL)
- 실행 mysql 서버에 확정 반영하기: db.commit()

In [ ]:

cursor = db.cursor()

In [ ]:

cursor

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

cursor 는 control structure of database 입니다. (연결된 객체로 보셔도 좋습니다.)

In [ ]:

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 서버에 보냄)

In [ ]:

cursor.execute(sql)

In [ ]:

cursor.execute("SHOW TABLES")

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

삽입, 갱신, 삭제 등이 모두 끝났으면 Connection 객체의 commit() 메서드를 사용하여 데이타를 Commit

In [ ]:

db.commit()

Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫음

In [ ]:

db.close()

In [ ]:

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()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

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

In [ ]:

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

In [ ]:

import pymysql

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

웹개발과 SQL 로 IT 와 데이터 시작하기 (무료 강의)

IT를 처음 익히는 분들을 위한, 웹과 데이터분야를 익히며 IT 기본기를 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')

In [ ]:

cursor = db.cursor()

In [ ]:

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)
            )
'''

In [ ]:

cursor.execute(sql)

In [ ]:

db.commit()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

db.close()

In [ ]:

# 잘못만들어졌으면,  DROP DATABASE [IF EXISTS] dbname; 로 테이블 삭제 후 재생성

데이터 삽입(INSERT)
- Cursor Object 가져오기: cursor = db.cursor()
- SQL 실행하기: cursor.execute(SQL)
- 실행 mysql 서버에 확정 반영하기: db.commit()

In [ ]:

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

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

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()

In [ ]:

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')

In [ ]:

cursor = db.cursor()

In [ ]:

sql = "SELECT * FROM cpu_info WHERE name = 'i5'"

In [ ]:

cursor.execute(sql)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

In [ ]:

result = cursor.fetchall()

In [ ]:

result

In [ ]:

type(result)

In [ ]:

for data in result:
    print(data[0])
    print(data[1])
    print(data[2])
    print(data[3])

In [ ]:

result = cursor.fetchone()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

웹개발과 SQL 로 IT 와 데이터 시작하기 (무료 강의)

IT를 처음 익히는 분들을 위한, 웹과 데이터분야를 익히며 IT 기본기를 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

print(result)

In [ ]:

for row_data in result:
    print(row_data[0], row_data[1], row_data[2], row_data[3])

In [ ]:

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()

In [ ]:

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')

In [ ]:

cursor = db.cursor()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

sql = "SELECT * FROM cpu_info WHERE name = 'i5'"

In [ ]:

cursor.execute(sql)

In [ ]:

while result:
    result = cursor.fetchone()
    print(result)

In [ ]:

db.close()

In [ ]:

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()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

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

In [ ]:

cursor = db.cursor()

In [ ]:

sql = "SELECT * FROM cpu_info WHERE name = 'i5'"

In [ ]:

cursor.execute(sql)

In [ ]:

result = cursor.fetchall()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

In [ ]:

for row_data in result:
    print(row_data)

In [ ]:

db.close()

데이터 수정(UPDATE)
- Cursor Object 가져오기: cursor = db.cursor()
- SQL 실행하기: cursor.execute(SQL)
- 실행 mysql 서버에 확정 반영하기: db.commit()

In [ ]:

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()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

웹개발과 SQL 로 IT 와 데이터 시작하기 (무료 강의)

IT를 처음 익히는 분들을 위한, 웹과 데이터분야를 익히며 IT 기본기를 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

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 은 카비레이크

In [ ]:

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()

In [ ]:

db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')

In [ ]:

cursor = db.cursor()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(1, 'i5', '7700', 'Kaby Lake')"

In [ ]:

cursor.execute(sql)

In [ ]:

db.commit()

In [ ]:

db.close()

SQL 활용¶

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

로우(Row)수 세기: COUNT¶

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

각 name 별 로우(Row) 수¶

In [ ]:

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 절을 사용해야 함

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

각 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¶

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

웹개발과 SQL 로 IT 와 데이터 시작하기 (무료 강의)

IT를 처음 익히는 분들을 위한, 웹과 데이터분야를 익히며 IT 기본기를 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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

복합 검색¶

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

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