๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Python/ETC

[Python] ํŒŒ์ด์ฌ MySQL์—ฐ๋™, DB ์—‘์…€ ๋ฐ์ดํ„ฐ ์—…๋กœ๋“œ ๋‹ค์šด๋กœ๋“œ :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2019. 1. 6.

MySQL ์—ฐ๋™

python์„ ์ด์šฉํ•ด์„œ MySQL ์—ฐ๋™์„ ํ•ด๋ณด์ž.
๋จผ์ € pip๋‚˜ PyCharm ์ธํ„ฐํ”„๋ฆฌํ„ฐ ์„ค์ •์œผ๋กœ pymysql ๋ชจ๋“ˆ์„ ์„ค์น˜ํ•ด์ค€๋‹ค.
pip install PyMySQL
 

 

 

์—‘์…€ํŒŒ์ผ๋„ ๋‹ค๋ฃจ์–ด์•ผํ•˜๊ธฐ ๋•Œ๋ฌธ์— openpyxl ๋ชจ๋“ˆ์ด ์—†๋‹ค๋ฉด ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ pip๋ฅผ ์ด์šฉํ•ด์„œ ์„ค์น˜ํ•ด์ค€๋‹ค.

์•„๋ž˜ ๋ชจ๋“ˆ๋“ค์„ import ํ•ด์ค€๋‹ค.

import pymysql
from openpyxl import Workbook
from openpyxl import load_workbook
 

 

 

๊ฐ„๋‹จํ•œ ํ…Œ์ŠคํŒ…์„ ์œ„ํ•ด local์— ์„ค์น˜๋œ MySQL DB์— ํ…Œ์ด๋ธ”์„ ๋งŒ๋“ค์–ด์ค€๋‹ค.

create table test(num int(11), name varchar(10));
 

 

 

ํ•„์ˆ˜์ ์ธ ๋ถ€๋ถ„์€ ์•„๋‹ˆ์ง€๋งŒ, ๊ฐ€๋…์„ฑ์„ ์œ„ํ•ด CRUDํ•จ์ˆ˜๋“ค์— ์‚ฌ์šฉํ•  ํด๋ž˜์Šค๋ฅผ ํ•˜๋‚˜ ๋งŒ๋“ค์–ด ๋†“์ž.

#DB ํ…Œ์ด๋ธ” ์นผ๋Ÿผ๋Œ€๋กœ ๋งŒ๋“  ๊ฐ์ฒด
class Test:
    def __init__(self, num, name):
        self.num = num
        self.name = name
 

 

 

 

์ด์ œ ๊ฐ CRUD๋ฅผ ํ•จ์ˆ˜๋กœ ๋งŒ๋“ค์–ด ์‚ฌ์šฉํ•˜์ž.

๋ชจ๋“  ํ•จ์ˆ˜๋“ค์€ connection Leak๋ฅผ ๋ง‰์•„์ฃผ๊ธฐ ์œ„ํ•ด try .. finally ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•˜๊ณ , with๋ฌธ์œผ๋กœ  cursor๋ฆฌ์†Œ์Šค๋ฅผ ์ž๋™์œผ๋กœ ํ•ด์ œ๋˜๋„๋ก ์ฝ”๋“œ๋ฅผ ์ž‘์„ฑํ•œ๋‹ค.

DB Select All

#์ „์ฒด Select
def select_all():
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = "select * from test"
            curs.execute(sql)
            rs = curs.fetchall()
            for row in rs:
                print(row)
    finally:
        conn.close()
 

 

DB Insert

#DB Insert
def insert_test(test_obj):
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = 'insert into test values(%s, %s)'
            curs.execute(sql, (test_obj.num, test_obj.name))
        conn.commit()
    finally:
        conn.close()
 

 

DB Delete

#num์นผ๋Ÿผ์œผ๋กœ DB Delete
def delete_test(num):
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = 'delete from test where num=%s'
            curs.execute(sql, num)
        conn.commit()
    finally:
        conn.close()
 

 

DB Delete All

#DB Delete All
def delete_all():
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = 'delete from test'
            curs.execute(sql)
        conn.commit()
    finally:
        conn.close()
 

 

DB Update

#DB Update
def update_test(test_obj):
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = 'update test set name=%s where num=%s'
            curs.execute(sql, (test_obj.name, test_obj.num))
        conn.commit()
    finally:
        conn.close()
 

 

 

 

 

 

MySQL ๋ฐ์ดํ„ฐ ์—‘์…€ํŒŒ์ผ ์“ฐ๊ธฐ

์ข…์ข… DB์˜ ๋‚ด์šฉ์„ ์—‘์…€ํŒŒ์ผ๋กœ ๋ฐ›์–ด์„œ ํ™•์ธ์„ ํ•ด์•ผํ•˜๋Š” ๊ฒฝ์šฐ๊ฐ€ ์žˆ๋‹ค.
csvํŒŒ์ผ๋กœ ๋งŒ๋“ค์–ด์„œ ๋ฐ”๋กœ ์˜ฌ๋ฆฌ๊ณ  ๋‚ด๋ ค๋ฐ›๊ณ  ํ•  ์ˆ˜ ์žˆ๋Š”๋ฐ... ํ•„์ž๊ฐ€ ์—ฌ๋Ÿฌ๋ฒˆ ํ•ด๋ณธ ๊ฒฝํ—˜์œผ๋กœ ํ•œ๊ธ€ ๋•Œ๋ฌธ์— ๋ฌธ์ž๊ฐ€ ๊นจ์ง€๊ณ , ๊ฐ„ํ˜น ๋ฐ์ดํ„ฐ ์•ˆ์— ","๊ฐ€ ๋“ค์–ด ์žˆ์œผ๋ฉด ๋‹ค๋ฅธ ํŠน์ˆ˜๋ฌธ์ž๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ตฌ๋ถ„์„ ํ•ด์•ผ ์ •ํ™•ํ•˜๊ฒŒ ์ •์ œ๋œ ๋ฐ์ดํ„ฐ๊ฐ€ ๋–จ์–ด์ง„๋‹ค.
์–ด์ฐจํ”ผ csvํŒŒ์ผ๋„ ์—‘์…€ํŒŒ์ผ๋กœ ๋ณ€ํ™˜ํ•ด์•ผํ• ๊ฑฐ ์†๋„์ ์ธ ์ธก๋ฉด์—์„œ ๋งŽ์ด ์ฐจ์ด๊ฐ€ ์•ˆ๋‚œ๋‹ค๋ฉด Python์„ ์ด์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ด๋™์‹œํ‚ค๋Š”๊ฒŒ ํŽธํ•˜๋‹ค.
 
insert_testํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ 1000๊ฐœ์ •๋„ ๋งŒ๋“ค์–ด ๋†“๊ณ  ์—‘์…€ํŒŒ์ผ๋กœ ๋–จ์–ด์ง„๊ฒƒ์„ ํ™•์ธํ•ด๋ณด์ž.
#์ „์ฒด Select ํ•˜์—ฌ ์—‘์…€ํŒŒ์ผ ์“ฐ๊ธฐ
def select_all_to_excel():
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = "select * from test"
            curs.execute(sql)
            rs = curs.fetchall()

            wb = Workbook()
            ws = wb.active

            #์ฒซํ–‰ ์ž…๋ ฅ
            ws.append(('๋ฒˆํ˜ธ','์ด๋ฆ„'))

            #DB ๋ชจ๋“  ๋ฐ์ดํ„ฐ ์—‘์…€๋กœ
            for row in rs:
                ws.append(row)

            wb.save('/Users/Jamong/Desktop/์ˆซ์ž.xlsx')
    finally:
        conn.close()
        wb.close()


if __name__ == "__main__":
    #๋ฐ์ดํ„ฐ 1000๊ฐœ์ •๋„ ๋„ฃ๊ธฐ
    for i in range(1,1000):
        test = Test(i, str(i) + '์ด๋ฆ„')
        insert_test(test)
    
    #DB -> ์—‘์…€ํŒŒ์ผ
    select_all_to_excel()
 

 

 

-๊ฒฐ๊ณผ

์ •ํ™•์ด ์—‘์…€ํŒŒ์ผ๋กœ ์ด์ƒ์—†์ด ๋งŒ๋“ค์–ด์ง„ ๊ฒƒ์„ ํ™•์ธํ•œ๋‹ค.

 

 

 

 

 

์—‘์…€ํŒŒ์ผ MySQL DB Insert

์ด๋ฒˆ์—๋Š” ๋ฐ˜๋Œ€๋กœ ์—‘์…€ํŒŒ์ผ์˜ ๋ฐ์ดํ„ฐ๋ฅผ MySQL DB๋กœ Insertํ• ๊ฑฐ๋‹ค.
์—‘์…€ํŒŒ์ผ์„ ์—ด์–ด 30๊ฐœ์˜ ๋ฐ์ดํ„ฐ๋งŒ ๋‚จ๊ธฐ๊ณ  ํŒŒ์ผ์„ ์ €์žฅํ•œ๋‹ค.

 

 
 
์œ„์—์„œ ์ž‘์„ฑํ•œ ํ•จ์ˆ˜๋กœ ํ˜„์žฌ DB์— ๋“ค์–ด์žˆ๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ๋ชจ๋‘ ์‚ญ์ œํ•œ ํ›„
์—‘์…€ ํŒŒ์ผ์„ DB๋กœ Insertํ•ด๋ณด์ž.
iter๋ฅผ ์‚ฌ์šฉํ•œ ์ด์œ ๋Š” ์—‘์…€ํŒŒ์ผ์˜ ์ฒซ๋ฒˆ์งธ ํ–‰์„ ๋นผ๊ณ  ๋ฐ˜๋ณต๋ฌธ์„ ๋Œ๋ฆด๋ ค๊ณ  ์‚ฌ์šฉํ–ˆ๋‹ค.
#์—‘์…€ํŒŒ์ผ DB Insert
def insert_excel_to_db():
    conn = pymysql.connect(host='localhost', user='root', password='๋น„๋ฐ€๋ฒˆํ˜ธ', db='python', charset='utf8')
    try:
        with conn.cursor() as curs:
            sql = 'insert into test values(%s, %s)'

            wb = load_workbook('/Users/Jamong/Desktop/์ˆซ์ž.xlsx',data_only=True)
            ws = wb['Sheet']

            iter_rows = iter(ws.rows)
            next(iter_rows)
            for row in iter_rows:
                curs.execute(sql, (row[0].value, row[1].value))
            conn.commit()
    finally:
        conn.close()
        wb.close()


if __name__ == "__main__":
    delete_all()
    insert_excel_to_db()
    select_all()
 
 
-๊ฒฐ๊ณผ

 

select_allํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ํ˜„์žฌ  DB์˜ ๋ชฉ๋ก์„ Selectํ•ด๋ณด๋‹ˆ ์ž‘์„ฑํ•œ ์—‘์…€ ๋ฐ์ดํ„ฐ๊ฐ€ ์ด์ƒ์—†์ด Insert๋œ๊ฒƒ์„ ํ™•์ธํ–ˆ๋‹ค.
 

 

๋Œ“๊ธ€