Python_11(pymysql을 이용한 mysql 연동)

pymysql
파이썬에서 MySQL, MariaDB DBMS와 연동하는 다양한 함수를 제공하는 모듈
Python DB API 2.0 표준을 따른다.

 

설치
조건: python version 3.6 이상, mysql version 5.6 이상
설치 : pip install PyMySQL

pip install pymysql

기본 작성 절차
1. Database 연결

   connection =  pymysql.connect(host="DBMS 서버 ip", 
                                 port="port번호", 
                                 user="계정명", 
                                 password="비밀번호", 
                                 db="연결할데이터베이스이름", 
                                 charset='utf8')

port 번호 기본값: 3306
2. Connection을 이용해 Cursor 생성
Cursor: 연결된 Database에 sql문을 전송하고 select결과 조회 기능을 제공하는 객체

 cursor = connection.cursor()

3. Cusror를 이용해 SQL문 전송

cursor.execute("sql문")

4. 연결 닫기
cursor, connection 연결을 닫는다.
with문을 이용할 수 있다.

cursor.close()
connection.close()

---

실습 : 테이블 생성

create_sql = """
create table member(
  id int primary key auto_increment
  , name varchar(30) not null
  , email varchar(100) not null unique
  , tall decimal(5,2)
  , birthday date
  , created_at datetime
  )
"""

 

import pymysql
# 1. DB와 연결
conn = pymysql.connect(host = "127.0.0.1", # DBMS의 ip주소
                       port = 3306,        # port 번호
                       user = 'playdata',   # username
                       password = '1111',   # password
                       db = 'testdb'       # 연결할 database(schema) 이름
                      ) 
print(type(conn))

 

# 2. Connection 으로부터 Cursor객체를 생성
## Cursor : sql문을 DB에 전송 그 결과를 받아주는 객체 -> sql 전송 메소드를 제공
cursor = conn.cursor()
print(type(cursor))

 

# 3. sql 전송.
cursor.execute(create_sql) # return -> sql이 적용된 데이터의 개수

 

# 4. 연결 닫기 -> cursor, connection
cursor.close()
conn.close()

---

DML :

1. INSERT

 

sql = "insert into member (name, email, tall, birthday, created_at) \
values ('홍길동', 'a@a.com', 172.23, '2000-10-10', now())"

# pymysql : manual commit 상태에서 실행한다 -> commit을 직접해야 한다.
with pymysql.connect(host="127.0.0.1", port = 3306, user = 'playdata', password = '1111', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(sql)
        print("insert된 행수 : ", cnt)
        conn.commit() # insert, delete, update 처리후 commit()을 해야 적용된다.

실행 후 MySQL Workbench에서 제대로 값이 들어갔는지 확인 (이후에도 값 INSERT될 때마다 실행시켜서 확인하기)

use testdb;
show tables;
desc member;
select * from member;

 

2. Parameterized Query
- SQL 문에서 컬럼 값이 들어가는 자리에 값대신 %s placeholder를 사용한뒤 execute()에서 placeholder에 넣을 값을 tuple로 제공한다.
- query문을 쉽게 작성할 수 있는 장점이 있다.

 

sql_template = "insert into member (name, email, tall, birthday, created_at) \
values (%s, %s, %s, %s, now())"
with pymysql.connect(host="127.0.0.1", port = 3306, user = 'playdata', password = '1111', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(sql_template, ('유재석', 'b@a.com', 182.54, '2000-10-10'))
        print(cnt)
        conn.commit()

---

Parameterized Query를 이용해 여러개 행 insert
1. for문 사용

 

from datetime import date, datetime
# python : datetime.date -> sql : date
# datetime.time -> sql : time
# datetime.datetime -> sql : datetime, timestamp
datas = [
    ['name1', 'abc1@abc.com', 165, date(2000,1,12)],
    ['name2', 'def1@abc.com', 175, date(1995,12,20)],
    ['name3', 'ghi1@abc.com', 185, date(1988, 7, 21)],
]
sql_template = "insert into member (name, email, tall, birthday, created_at) values (%s, %s, %s, %s, now())"
with pymysql.connect(host="127.0.0.1", port = 3306, user = 'playdata', password = '1111', db = 'testdb') as conn:
    with conn.cursor() as cursor:
     cnt = 0
     for data in datas:
         i = cursor.execute(sql_template, data) # placeholder에 넣을 값들은 tuple, list에 넣어 제공.
         cnt += i
     print("insert된 행수", cnt)
     conn.commit()

 

executemany() 사용
- insert할 값들을 가진 리스트를 넣어 한번에 여러 행을 insert한다.

 

datas = [
    ['name1', 'abc2@abc.com', 165, date(2000,1,12)],
    ['name2', 'def2@abc.com', 175, date(1995,12,20)],
    ['name3', 'ghi2@abc.com', 185, date(1988, 7, 21)],
]
sql_template = "insert into member (name, email, tall, birthday, created_at) values (%s, %s, %s, %s, now())"
with pymysql.connect(host="127.0.0.1", port = 3306, user = 'playdata', password = '1111', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        try:
            cnt = cursor.executemany(sql_template, datas)
            print("insert 개수:", cnt)
            conn.commit()
        except Exception as e:
            print("예외발생: ", e)
            conn.rollback()