PyMySQL

pymysql 기본 사용 방법

 

1. 모듈 import

import pymysql

 

2. mySQL 연결

db = pymysql.connect(host='localhost', 
		     port=3306, user='유저명', 
                     passwd='비밀번호', 
                     db='데이터베이스 이름', 
                     charset='utf8')

 

3. MySQL 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져옴

ecommerce = db.cursor()

 

4. Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 전송 가능

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

ecommerce.execute(sql)

 

5. 실행 mysql 서버에 확정 반영

db.commit()

 

6. DB 연결 종료

db.close()

 

---

데이터 추가 : INSERT

import pymysql

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

ecommerce = db.cursor()

sql = """
    INSERT INTO product2 VALUES(
        '215630', '원피스', 23000, 6900, 80, 'F');
"""

ecommerce.execute(sql)
db.commit()
db.close()

 

 

데이터 조회: SELECT

mysql 서버로부터 데이터 가져오기: fetch 메서드

• fetchall(): 모든 행을 Fetch
• fetchmany(size=None): size 갯수만큼 행을 Fetch 
• fetchone(): 한 행만 Fetch

import pymysql

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

ecommerce = db.cursor()

sql = "SELECT * FROM product2"
ecommerce.execute(sql)

result = ecommerce.fetchall()

for record in result:
    print(record)

db.close()

output
('215630', '원피스', 23000, 6900, 80, 'F')

 

 

데이터 수정: UPDATE

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='rlaqufgml!23', db='ecommerce', charset='utf8')
ecommerce = db.cursor()

sql = """
UPDATE product2 SET
    TITLE = '반바지',
    ORI_PRICE = 29999,
    DISCOUNT_PRICE = 9020,
    DISCOUNT_PERCENT = 70
    WHERE PRODUCT_CODE = '215630';
"""

ecommerce.execute(sql)
db.commit()
db.close()

 

데이터 삭제: DELETE

import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', passwd='rlaqufgml!23', db='ecommerce', charset='utf8')
ecommerce = db.cursor()

# 삭제
sql = "DELETE FROM product2 WHERE PRODUCT_CODE = '215630'"
ecommerce.execute(sql)

db.commit()

# 조회
sql = "SELECT * FROM product2"
ecommerce.execute(sql)
result = ecommerce.fetchall()

for record in result:
    print(record)
db.close()