01.Mysql 기초 - pymysql 다루기

🌈 파이썬으로 mysql 다루기

🔥 pymysql 이란?

🔥 pymysql로 데이터 생성(Create)

🔥 pymysql로 데이터 읽기(Read)

🔥 pymysql로 데이터 수정(Update)

🔥 pymysql로 데이터 삭제(Delete)

1. pymysql 이란?

• python에서 sql문법을 사용해 데이터베이스를 다루는 방법은 pymysql 라이브러리 사용함
• pymysql 라이브러리 설치 : pip3 install pymysql
• pymysql이 설치되었다면, python에서 데이터베이스에 접속할 수 있음
• cursor 객체를 사용해 mysql을 다룸

1) 데이터베이스 접속

• host : 서버명 입력(내 PC는 localhost 또는 127.0.0.1)
• port : 포트명 입력(내 PC 포트명은 3306, mysql Workbench 접속화면에서 볼 수 있음)
• user : 아이디명(내 PC는 root)
• password : mysql의 해당 user의 비밀번호
• db : 연결할 데이터베이스
• charset : 한글 깨지지 않도록 설정하는 옵션

✍🏻 python

# 터미널에서 pip3 install pymysql
# 1단계 : import
import pymysql 
# 2단계 : connect(mysql 접속)
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='내 비밀번호 입력', db='ecommerce', charset='utf8')
# 3단계 : cusor : 커서 생성
cursor = db.cursor()

2) 데이터베이스 내 테이블 생성

• 데이터베이스 연결 후 커서까지 생성하였으면, sql문으로 테이블 생성 가능
• sql문을 mysql에서 실행시키기 위해, cursor 객체의 esecute() 메서드로 명령
• 테이블 생성 명령 후, 이를 반영(확정)하기 위해 commit()을 해주어야 함
  • [db연결 객체].commit() ⇢ 🔍 db.commit()
• 생성이 모두 완료되었다면, close() 메서드로 접속 해제
  • [db연결 객체].close() ⇢ 🔍 db.close()

✍🏻 python

# 1단계 : import
import pymysql
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='내 비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(테이블 생성 sql문 작성)
sql = """ 
    CREATE TABLE product(
        PRODUCT_CODE VARCHAR(20) NOT NULL,
        TITLE VARCHAR(200) NOT NULL,
        ORI_PRICE INT,
        DISCOUNT_PRICE INT,
        DISCOUNT_PERCENT INT,
        DELIVERY VARCHAR(2),
        PRIMARY KEY(PRODUCT_CODE)
);
"""
# 5단계 : sql 실행 명령(테이블 생성)
cursor.execute(sql) # cusor 객체의 execute() 메서드를 사용하여 CRUD 문장을 데이터베이스 서버로 보냄
# 6단계 : 실행 결과 확정 선언
db.commit() # CRUD가 완료되었으면, commit()메서드를 사용하여 데이터를 commit
# 7단계 : DB연결 해제
db.close()

2. pymysql로 데이터 생성(Create)

1) 데이터 삽입 방법

• import ⇢ db접속 ⇢ curosor 생성 ⇢ sql문 작성 ⇢ sql문 실행 ⇢ 실행 결과 확정(commit) ⇢ 연결 해제
• 데이터 삽입 방법은 sql문과 동일
• 방법1 : INSERT INTO [테이블명] VALUES(value1, value2, value3...)
  • 🔍 INSERT INTO model_info VALUES(1, 'i7', '7700', 'Kaby Lake');
• 방법2 : INSERT INTO [테이블명] (field1, field2, field3...) VALUES (value1, value2, value3...)
  • 🔍 INSERT INTO model_info (name, model_num, model_type) VALUES('i7', '9700', 'ice Lake');

✍🏻 python - 데이터 삽입 기본

# 1단계 : import
import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 create)
sql = """
    INSERT INTO product VALUES('215673140', '스위트바니 여름신상5900원-롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F');
"""
# 5단계 : sql문 실행(데이터 create 실행)
cursor.execute(sql)
# 6단계 : 실행 결과 확정
db.commit()
# 7단계 : 연결 해제
db.close()

✍🏻 python - for문으로 여러 데이터 삽입

# 1단계 : import
import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 create)
for index in range(10): # 4-1단계 : for문으로 데이터 여러게 삽입
    product_code = 215673140 + index + 1
    sql = """
        INSERT INTO product VALUES(
        '"""+ str(product_code) +"""', '스위트바니 여름신상5900원-롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F');"""
    cursor.execute(sql) # 5단계 : sql문 실행(데이터 create 실행)
# 6단계 : 실행 결과 확정
db.commit()
# 7단계 : 연결 해제
db.close()

3. pymysql로 데이터 읽기(Read)

• import ⇢ db접속 ⇢ curosor 생성 ⇢ sql문 작성 ⇢ sql문 실행 ⇢ fetch 메서드 ⇢ 실행결과 콘솔 출력 ⇢ DB commit ⇢ 연결 해제
• fetch의 원리는 튜플의 원리와 같음
• 테이블의 각 레이블을 튜플안에 튜플로 담고, value들은 그 튜플안에 요소로 넣음
• fetch 메서드를 이용하여 sql문으로 SELECT한 결과를 콘솔창으로 가져올 수 있음
  • fetchall() : 모든 레이블(row)를 가져옴(fetch)
  • fetchmany(size=number) : size만큼 레이블(row) 가져옴(fetch)
  • fetchone() : 해당하는 첫번째 레이블만 가져옴(fetch)

1) fetchall() : 모든 레이블(row)를 fetch

✍🏻 python

# 1단계 : import
import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 read)
sql = "SELECT * FROM product"
# 5단계 : sql문 실행(데이터 read)
cursor.execute(sql)
# 6단계 : fetch 메서드(조회결과 콘솔창에서 보기 위함)
result = cursor.fetchall() # fetchall() : 전부 가져오기
# 7단계 : 실행 결과 콘솔창에서 출력
for data in result:
    print(data)
# 8단계 : commit 및 연결 해제
db.commit() # 커밋은 반복할 필요 없음
db.close()

2) fetchmany(size=num) : size만큼 레이블(row) fetch

✍🏻 python

# 1단계 : import
import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 read)
sql = "SELECT * FROM product"
# 5단계 : sql문 실행(데이터 read)
cursor.execute(sql)
# 6단계 : fetch 메서드(조회결과 콘솔창에서 보기 위함)
result = cursor.fetchmany(size=5) # fetmany(size=?) : size만큼 레이블(row)가져오기
# 7단계 : 실행 결과 콘솔창에서 출력
for data in result:
    print(data)
# 8단계 : commit 및 연결 해제
db.commit()
db.close()

3) fetchone() : SELECT된 데이터 중 첫번째 레이블(🔥1개만 갖고오기 때문에 for문 필요없음)

✍🏻 python

# 1단계 : import
import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 read)
sql = "SELECT * FROM product"
# 5단계 : sql문 실행(데이터 read)
cursor.execute(sql)
# 6단계 : fetch 메서드(조회결과 콘솔창에서 보기 위함)
result = cursor.fetchone() # fetchone() : 1개 fetch
# 7단계 : 콘솔창에 출력
print(result)
# 8단계 : commit 및 연결 해제
db.commit()
db.close()

4. pymysql로 데이터 수정(Update)

• import ⇢ db접속 ⇢ curosor 생성 ⇢ sql문 작성 ⇢ sql문 실행 ⇢ DB commit ⇢ 연결 해제
• UPDATA [테이블명] SET [필드명] = '바꾸고자하는 값' WHERE [필드명] = 값;
• "WHERE [필드명] = 값"에 해당하는 레이블을 "[필드명] = '바꾸고자하는 값'"로 수정하겠다!

✍🏻 python

import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='비밀번호', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 수정) : PRODUCT_CODE가 '215673141'인 레이블 중 4가지 필드 수정(DELIVERY 제외)
sql = """
UPDATE product SET
    TITLE='달리샵린넨원피스 뷔스티에 썸머 가디건 코디전',
    ORI_PRICE=33000,
    DISCOUNT_PRICE=9900,
    DISCOUNT_PERCENT=70
    WHERE PRODUCT_CODE = '215673141'
"""
# 5단계 : sql문 실행(데이터 수정)
cursor.execute(sql)
# 6단계 : commit 및 연결 해제
db.commit()
db.close()

5. pymysql로 데이터 삭제(Delete)

• import ⇢ db접속 ⇢ curosor 생성 ⇢ sql문 작성 ⇢ sql문 실행 ⇢ DB commit ⇢ 연결 해제
• DELETE FROM [테이블명] WHERE [특정 컬럼] = '값';
• "WHERE [특정 컬럼] = '값'" 레이블을 삭제하겠다!

✍🏻 python

import pymysql 
# 2단계 : connect : mysql 접속
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='jewon119sql', db='ecommerce', charset='utf8') 
# 3단계 : cusor : 커서 생성
cursor = db.cursor() 
# 4단계 : sql문 작성(데이터 삭제) : PRODUCT_CODE가 '215673142'인 레이블 삭제
sql = "DELETE FROM product WHERE PRODUCT_CODE = '215673142'"
# 5단계 : sql문 실행(데이터 삭제 명령)
cursor.execute(sql)
# 6단계 : commit 및 연결 해제
db.commit()
db.close()