pymysql

: 파이썬에서 MySQL, MariaDB DBMS와 연동하는 다양한 함수를 제공하는 모듈

MySQL, MariaDB에다가 사용 가능

- 조건
python version 3.6 이상
mysql version 5.6 이상

- 설치
pip install PyMySQL
conda install -c conda-forge 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()

Parameterized Query

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

executemany() 사용

insert할 값들을 가진 리스트를 넣어 한번에 여러 행을 insert함

datas = [
    ['이름1', 'abc2@abc.com', 165, date(2000,1,12), datetime.now()],
    ['이름2', 'def2@abc.com', 175, date(1995,12,20), datetime.now()],
    ['이름1', 'ghi2@abc.com', 185, date(1988, 7, 21), datetime.now()]
]

sql_tamplate = "insert into member (name, email, tall, birthday, create_at) \
values (%s, %s, %s, %s, now())" 
with pymysql.connect(host = "127.0.0.1", port = 3306, user = '이름', password = '비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        try:
            cnt = cursor.executemany(sql_tamplate, datas)
            print("insert 갯수: ", cnt)
            conn.commit()
        except Exception as e:
            print("예외발생:", e)
            conn.rollback()

---

update/delete

코딩 절차는 insert 와 동일

update_sql = "update member set tall= tall + 10 where id=%s"
with pymysql.connect(host = "localhost", port = 3306, user = '이름', password = '비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(update_sql, [2])
        print(cnt)
        conn.commit()

update_sql = "update member set tall= tall + 10 where id=%s"
with pymysql.connect(host = "localhost", port = 3306, user = '이름', password = '비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(update_sql, (170,))
        print(cnt)
        conn.commit()

delete_sql = "delete from member where ud = %s"
with pymysql.connect(host="localhost", port= 3306, user = 'ChaewonPark', password='pcw687101', db = 'testdb')as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(delete_sql, [2])
        print(cnt)
        conn.commit()

---

select (DQL - Data Query Language)

• 조회결과 조회

  • cursor.execute("select문") 실행 후 cursor의 결과 조회 메소드(fetch메소드)를 이용해 결과를 받음

• fetch메소드

  • fetchall()
    • 조회한 모든 행을을 반환
  • fetchmany(size=개수)
    • 지정한 size개수 만큼 반환
  • fetchone()
    • 조회결과 중 첫번째 행만 반환
    • 주로 pk 동등 조건으로 조회한 경우 사용

retchall()

import pymysql
from pprint import pprint # 자료구조 (list, tuple, dict, set) 를 가독성 좋게 출력

sql = "select * from member where name like %s"
with pymysql.connect(host = 'localhost', port = 3306, user = "ChaewonPark", password = 'pcw687101', db = 'testdb') as conn:
    with conn.cursor() as cursor:
        #select를 실행한 결과(result set)은 cursor의 속성으로 저장된다
        cursor.excute(sql, ['1%'])  # %s가 하나일 경우 상수로 전달 가능
        #cursor가 가진 조회결과 조회
        result = cursor.fetchall()

또는

for row in enumerate (result, start=1):
    m_id, name, email, tall, bd, jd = row
    print(f"{idx}. id: {m_id}, 이름: {name}, email: {email}, 키 : {tall}, 생일: {db}, 가입일: {jd}")

이를 재사용 하려면...

def show_result(result):
    for row in enumerate (result, start=1):
        m_id, name, email, tall, bd, jd = row
        print(f"{idx}. id: {m_id}, 이름: {name}, email: {email}, 키 : {tall}, 생일: {db}, 가입일: {jd}")

이러한 형태로 앞에 def show_result(result):를 붙여주면 됨

ex)

tall = float(input("조회할 키:"))
sql = "select * from member where ud = %s"
with pymysql.connect(host="localhost", port= 3306, user = '이름', password='비밀번호', db = 'testdb')as conn:
    with conn.cursor() as cursor:
        cnt = cursor.execute(sql, [tall])  # 조회된 행수가 반환
        result = cursor.fetchall()  #조회결과가 없으면 빈 튜플을 반환

if cnt ==0:
    print("조회된 결과 없음")
else:
    show_result(result)

fetchone()

# pk를 이용해서 조회 할 떄 사용 (조회 결과가 한행일 떄 사용)
sql = "select * from member where id = %s"
with pymysql.connect(host="localhost", port= 3306, 
                     user = '이름', password='비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:  # 기본 cursor 사용
        cnt = cursor.cursor() as cursor:
            cnt = cursor.execute(sql, [3])
            print(cnt)
            result = cursor.fetchone()
            print(result)

sql = "select name,email from member where id > %s"
with pymysql.connect(host="localhost", port= 3306, 
                     user = '이름', password='비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:  # 기본 cursor 사용
        cnt = cursor.cursor() as cursor:
            cnt = cursor.execute(sql, [5])
            print(cnt)
            result = cursor.fetchone() #select 결과가 여러행일 경우 첫번째 행만 반환
            print(result)

fetchmany()

sql = "select name,email from member
with pymysql.connect(host="localhost", port= 3306, 
                     user = '이름', password='비밀번호', db = 'testdb') as conn:
    with conn.cursor() as cursor:  # 기본 cursor 사용
        cnt = cursor.cursor() as cursor:
            cnt = cursor.exevute(sql)
            print(cnt)
            result = cursor.fetchmany(size = 3) # size 행만큼만 조회
            pprint(result)