내가 노는 놀이터

현재 Jupyter Notebook (Python) 과 HeidiSQL (MariaDB)사용 중입니다.

--------------------------------------------------------------< 준비 >---------------------------------------------------------------------

1. pymysql 설치

https://sosweetstrawberry.tistory.com/130

--------------------------------------------------------------< 사용 >---------------------------------------------------------------------

1. 기본 구조

#기본 구조
import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root',password='ezen',db='mydb',charset='utf8') #연결부 선언
curs = conn.cursor()   #전달체 cursor 선언 ( SQL문을 실행 시키면 그 데이터가 cursor에 담김 )

n = curs.execute('SELECT * FROM emp')    # excute 실행문 ( 결과가 반영된 행 수를 return )

rows = curs.fetchall()     #curs가 가지고 있는 데이터 뽑아내기( 튜플 형태 ) #행이 하나면 fetchone
print(rows)

curs.close()
conn.close()

((11, 'blake', datetime.date(2022, 8, 18), 3500),
 (12, 'join', datetime.date(2022, 8, 18), 3000),
 (14, 'Kim', datetime.date(2011, 4, 19), 9000),
 (15, 'lee', datetime.date(2000, 6, 6), 5000))

Tip. conn 객체를 만드는 함수를 사용하면 더 편안

def get_conn():
    conn = pymysql.connect(host='127.0.0.1',user='root',password='ezen',db='mydb',charset='utf8')
    return conn

2. sql문에 튜플형태로 전달인자 전달하기

curs.execute(sql,(int(u_sal),int(u_num))) -> 전달인자를 튜플 형태로 ((int(u_sal),int(u_num)) 주면 차례대로 %s에 들어감

Update,Delete, Insert문을 사용할떄는 마지막에 conn.commit()을 해야 저장됨

sql="UPDATE emp set sal = %s where empno = %s "
u_num,u_sal = (11,5000)

with get_conn() as conn:
    with conn.cursor() as curs:
        n = curs.execute(sql,(int(u_sal),int(u_num))) # (int(u_sal),int(u_num)) -> 튜플타입으로 한번에 전달(%s에 차례로 들어감)
        print('수정성공') if n>0 else print('수정실패')
    conn.commit()

3. with절 ( 자동 close )

with get_conn() as conn:
    with conn.cursor() as curs:
        n = curs.execute(sql,(int(u_num)))
    conn.commit()

4. 가져온 데이터를 보기 쉽게 표로 보여주기 ( pandas )

import pymysql
import pandas as pd

def emp_list():
    conn=get_conn()
    curs = conn.cursor(pymysql.cursors.DictCursor)  #딕셔너리 형태로 가져옴
    curs.execute("SELECT empno,ename,hiredate,sal FROM emp")
    rows = curs.fetchall()
    
    df = pd.DataFrame(rows)
    display(df)   #표로 보여주기
    
    curs.close()
    conn.close()
    
emp_list()

< CRUD >

import pymysql

def get_conn():
    conn = pymysql.connect(host='127.0.0.1',user='root',password='ezen',db='mydb',charset='utf8')
    return conn
   
class EmpVo:
    def __init__(self,empno,ename,hiredate,sal):
        self.empno = empno
        self.ename = ename
        self.hiredate = hiredate
        self.sal = sal

class EmpDAO:
    
    def show_all():   #모든 행 가져와서 보여주기
        sql="SELECT * FROM emp" 
        with get_conn() as conn:
            with conn.cursor() as curs:
                curs.execute(sql)
                rows = curs.fetchall()
                for i in rows:
                    print(i)
                    
    def add():      #Insert
        sql="INSERT INTO emp VALUES( %s,%s,%s,%s ) "
        info = input('번호, 이름, 날짜, 금액')
        EmpVo = info.split(" ")

        with get_conn() as conn:
            with conn.cursor() as curs:
                n = curs.execute(sql,(EmpVo))
                print('추가성공') if n>0 else print('추가실패')
            conn.commit()
            
    def find_by_empno():    #검색
        num = input('검색할 사번을 입력하세요')
        sql="SELECT * FROM emp WHERE empno = %s" 
        with get_conn() as conn:
            with conn.cursor() as curs:
                curs.execute(sql,(num,))
                rows = curs.fetchone()    #fetchone->하나의 행만 가져온다
                print(rows)
    
    def find_by_ename():     #검색
        ename = input('검색할 이름을 입력하세요.')
        sql="SELECT * FROM emp WHERE ename = %s" 
        with get_conn() as conn:
            with conn.cursor() as curs:
                curs.execute(sql,(ename,))
                rows = curs.fetchone()
                print(rows)
        
    def update_by_ename():    #Update
        sql="UPDATE emp set sal = %s where ename = %s " 
        info = input('이름, 금액')
        u_name,u_sal = info.split(" ")

        with get_conn() as conn:
            with conn.cursor() as curs:
                n = curs.execute(sql,(int(u_sal),u_name))
                print('수정성공') if n>0 else print('수정실패')
            conn.commit()
            
    def delete():              #Delete
        sql="DELETE FROM emp WHERE empno = %s " 
        u_empno = input('삭제 할 사번을 입력하세요.')

        with get_conn() as conn:
            with conn.cursor() as curs:
                n = curs.execute(sql,u_empno)
                print('삭제성공') if n>0 else print('삭제실패')
            conn.commit()