Mysql 02 pymysql

pymysql 모듈 이해 및 실습

pymysql 라이브러리 소개 및 설치

• mysql을 python에서 사용할 수 있는 라이브러리

• 설치

  • 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하여 데이타를 가져올 때 한글이 깨질 수 있으므로 연결 설정에 넣어줌

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')

db

<pymysql.connections.Connection at 0x1e867f37f10>

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

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

ecommerce = db.cursor()

ecommerce

<pymysql.cursors.Cursor at 0x1cb9c5f65b0>

• cursor 는 control structure of database

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)
    );
"""

• SQL 실행 (Cursor 객체의 execute() 메서드를 사용하여 INSERT, UPDATE 혹은 DELETE 문장을 DB 서버에 보냄)

ecommerce.execute(sql)

0

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

db.commit()

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

db.close()

패턴으로 익히는 pymysql

# 1. 라이브러리 가져오기
import pymysql

# 2. 접속하기
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')

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

# 4. SQL 구문 만들기 (CRUD 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)

# 6. DB에 Complete 하기
db.commit()

# 7. DB 연결 닫기
db.close()

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

i= 1498699891
sql = """
INSERT INTO product VALUES(
"{}", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
""".format(i)
print(sql)

INSERT INTO product VALUES(
"1498699891", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");

import pymysql

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

cursor = db.cursor()
product_code = 1498699891
for index in range(10):
    sql = """INSERT INTO product VALUES(
    "{}", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");""".format(product_code)
    product_code += 1
    print (sql)
    cursor.execute(sql)

db.commit()
db.close()

INSERT INTO product VALUES(
    "1498699891", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699892", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699893", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699894", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699895", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699896", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699897", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699898", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699899", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");
INSERT INTO product VALUES(
    "1498699900", "(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지", 22600, 6800, 69, "F");

• 데이터 조회(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

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM product"
cursor.execute(sql)
result = cursor.fetchone()
#print(record)
print(result)
db.close()

('1498699891', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM product"
cursor.execute(sql)
result = cursor.fetchall()
#result = cursor.fetchmany(size=3)
for record in result:
    print(record)

db.close()

('1498699892', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699893', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699894', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699895', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699896', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699897', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699898', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699899', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699900', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM product"
cursor.execute(sql)
result = cursor.fetchmany(size=3)
for record in result:
    print(record)

db.close()

('1498699891', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699892', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')
('1498699893', '(12%쿠폰)1+1 장당 2995원/쿨론/반팔티/등산복/바지', 22600, 6800, 69, 'F')

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

체이스컬트 여름 팬츠/상의 마무리 SALE
33,000원
9,900원
70

UPDATE product SET
title = '체이스컬트 여름 팬츠/상의 마무리 SALE',
ori_price = 33000,
discount_price = 9900,
discount_price =70
WHERE PRODUCT_CODE='1498699891'

# 1. 라이브러리 가져오기
import pymysql

# 2. 접속하기
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')

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

# 4. SQL 구문 만들기
SQL = """
UPDATE product SET 
    title = '체이스컬트 여름 팬츠/상의 마무리 SALE', 
    ori_price = 33000, 
    discount_price = 9900, 
    discount_price =70 
    WHERE PRODUCT_CODE='1498699891'
"""

# 5. SQL 구문 실행하기
cursor.execute(SQL)

# 6. commit 하기
db.commit()

# 7. close 하기
db.close()

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

DELETE FROM product WHERE PRODUCT_CODE='1498699891'

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='1234', db='ecommerce', charset='utf8')

cursor = db.cursor()

SQL = """DELETE FROM product WHERE PRODUCT_CODE='1498699891'"""

cursor.execute(SQL)

db.commit()

db.close()