MySQL - 파이썬으로 다루는 MySQL

 

데이터베이스 입문 수업을 듣고 중요한 내용을 정리했습니다.
개인 공부 후 자료를 남기기 위한 목적이므로 내용 상에 오류가 있을 수 있습니다.

 

파이썬으로 다루는 MySQL

파이썬 코드로 MySQL을 다루는 연습을 하고자 한다.

 

pymysql 설치

pymysql은 Python에서 mysql을 사용할 수 있는 가장 쉬운 라이브러리이다.

pymysql 라이브리러 이외에도 MySQLdb, 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()의 함수를 사용하여 서버로부터 가져온

    data를 코드에서 활용

6. 삽입, 갱신, 삭제 등의 DML(Data Manipulation Language) 문장을 실행하는 경우, INSERT/UPDATE/DELETE 후

    Connection 객체의 commit() 함수를 사용하여 data를 확정

7. Connection 객체의 close() 함수를 사용하여 DB 연결을 닫음

 

• mysql 핸들링 Python 코드 예시

import pymysql

 

pymysql 모듈을 import 한다.

 

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

print(db)

 

```

결과 :

<pymysql.connections.Connection at 0x1035568b0> (접속 성공)

 

```

 

pymysql.connect() 함수를 사용하여 MySQL에 연결한다.

connect() 함수의 파라미터로 호스트명, 포트, 로그인, 암호(본인 mysql pw), 접속할 DB 등을 지정한다.

 

*참고(주요 파라미터)

- host : 접속할 mysql server 주소

- port : 접속할 mysql server의 포트번호

- user : mysql ID

- passwd : mysql ID의 암호(본인 암호)

- db : 접속할 데이터베이스

- charset = 'utf8' : mysql에서 select해서 data를 가져올 때, 한글이 깨질 수 있으므로 연결 설정에 넣어주는 세팅값

 

cursor = db.cursor()

print(cursor)

 

```

결과 :

<pymysql.cursors.Cursor object at 0x103556190>

 

```

 

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

cursor.execute(sql)

 

SQL 실행 코드이다. 

Cursor 객체의 execute() 함수를 사용하여 INSERT, UPDATE, DELETE 문장을 DB 서버에 보낸다.

 

db.commit()

 

삽입, 갱신, 삭제 등이 모두 끝났으면, Connection 객체의 commit() 함수를 사용하여 data를 Commit(data 변경 승인) 한다.

 

db.close()

 

database의 접속을 끊는 코드이다.

즉, Connection 객체의 close() 함수를 사용해서 DB의 연결을 닫는다.

 

• 전체 코드(예시)

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

# 2. 접속하기
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', 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()

 

```

결과 :

 

 

```

 

 

테이블 생성(삽입)

import pymysql

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

cursor = db.cursor()

sql = '''
        INSERT INTO product VALUES
        ('215673140', '스위트바니 여름신상 롱원피스', 23000, 6900, 70, 'F');
'''
cursor.execute(sql)

db.commit()
db.close()

 

```

결과 :

```

 

import pymysql

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

cursor = db.cursor()

for index in range(10):
    product_code = 215673140 + index + 1
    sql = """INSERT INTO product VALUES(
    '""" + str(product_code) + """', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F'); """
    print(sql)
    cursor.execute(sql)

db.commit()
db.close()

 

```

결과 :

```

 

데이터 삽입(INSERT)

 

Cursor Object 가져오기 : cursor = db.cursor()

SQL 실행하기 : cursor.execute(SQL)

실행 mysql 서버에 data 변경(삽입) 확정 반영하기 : db.commit()

 

 

데이터 검색(조회)

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', db='ecommerce', charset='utf8')
cursor = db.cursor()

sql = "SELECT * FROM product"
cursor.execute(sql)

result = cursor.fetchall()
for record in result:
    print(record)
db.close()

 

```

결과 :

('215673140', '스위트바니 여름신상 롱원피스', 23000, 6900, 70, 'F')

('215673141', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673142', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673143', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673144', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673145', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673146', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673147', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673148', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673149', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673150', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

 

```

 

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', 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()

# fetchmany() 함수의 인자로 조회할 데이터의 개수를 지정할 수 있다.(ex. fetchmany(size = 10))

 

```

결과 :

('215673140', '스위트바니 여름신상 롱원피스', 23000, 6900, 70, 'F')

('215673141', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

('215673142', '스위트바니 여름신상5900원~롱원피스티셔츠/긴팔/반팔', 23000, 6900, 70, 'F')

 

```

 

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', db='ecommerce', charset='utf8')
cursor = db.cursor()

sql = "SELECT * FROM product"
cursor.execute(sql)

result = cursor.fetchone()
print(result)
db.close()

 

```

결과 :

('215673140', '스위트바니 여름신상 롱원피스', 23000, 6900, 70, 'F')

```

 

데이터 조회(SELECT)

 

Cursor Object 가져오기 : cursor = db.cursor()

SQL 실행하기 : cursor.execute(SQL)

mysql 서버로부터 data 가져오기 : fetch() 함수 사용

• fetchall() 함수 : table의 모든 검색 결과(all the rows)를 가져옴
• fetchmany() 함수 : table의 복수의 검색 결과(several rows)를 가져옴, 인자로 size(개수)를 넣어줌
• fetchone() 함수 : table의 최초 검색 결과(the next row)를 가져옴 

 

 

데이터 수정

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', db='ecommerce', charset='utf8')
cursor = db.cursor()

SQL = """
UPDATE product SET 
    TITLE='달리샵린넨원피스 뷔스티에 썸머 가디건 코디전', 
    ORI_PRICE=33000, 
    DISCOUNT_PRICE=9900, 
    DISCOUNT_PERCENT=70 
    WHERE PRODUCT_CODE='215673141'
"""

cursor.execute(SQL)
db.commit()
db.close()

 

```

결과 :

```

 

데이터 수정(UPDATE)

 

Cursor Object 가져오기 : cursor = db.cursor()

SQL 실행하기 : cursor.execute(SQL)

실행 mysql 서버에 data 변경(수정) 확정 반영하기 : db.commit()

 

 

데이터 삭제

import pymysql
db = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql pw', db='ecommerce', charset='utf8')
cursor = db.cursor()

SQL = """DELETE FROM product WHERE PRODUCT_CODE='215673142'"""
cursor.execute(SQL)

db.commit()
db.close()

 

```

결과 :

```

 

데이터 삭제(DELETE)

 

Cursor Object 가져오기 : cursor = db.cursor()

SQL 실행하기 : cursor.execute(SQL)

실행 mysql 서버에 data 변경(삭제) 확정 반영하기 : db.commit()