๐ŸŒˆ PyMySQL ์ด๋ž‘ ์นœํ•ด์ง€๊ธฐ

yeeun leeยท2020๋…„ 5์›” 31์ผ
1

flask

๋ชฉ๋ก ๋ณด๊ธฐ
3/5

pymysql์„ ์จ์„œ request์— ๋‹ด๊ธด ๋ฐ์ดํ„ฐ๋ฅผ db์— ์ €์žฅํ•˜๊ฑฐ๋‚˜, request์— ๋Œ€ํ•œ response๋ฅผ ๋ณด๋‚ด๋Š” ๋ฐฉ๋ฒ•์„ ๊ณต๋ถ€ ๋ชฉ์ ์œผ๋กœ ์ •๋ฆฌํ–ˆ๋‹ค.

์ž”์žฌ๋ฏธ์ฝ”๋”ฉ์— ๋งค์šฐ ์ •๋ฆฌ๊ฐ€ ์ž˜ ๋˜์–ด์žˆ์–ด ๊ณ ๋Œ€๋กœ ๊ฐ€์ ธ์˜จ ์  ์ฐธ๊ณ !

์—ฌ๊ธฐ๋„ ์ข‹์€ ๊ฒƒ ๊ฐ™๋‹ค.
https://www.tutorialspoint.com/python_data_access/python_mysql_select_data.htm

mysql์„ python์—์„œ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋Š” ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ๋Š”

  • pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ
  • MySQLdb(Mysql-pytion)
  • MySQL connector ๋“ฑ ๋‹ค์–‘ํ•œ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์กด์žฌ

pymysql

  1. pymysql.connect() ๋ฉ”์†Œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ MySQL์— ์—ฐ๊ฒฐ. ํ˜ธ์ŠคํŠธ๋ช…, ํฌํŠธ, ๋กœ๊ทธ์ธ, ์•”ํ˜ธ, ์ ‘์†ํ•  DB ๋“ฑ์„ ํŒŒ๋ผ๋ฏธํ„ฐ๋กœ ์ง€์ •

  2. MySQL ์ ‘์†์ด ์„ฑ๊ณตํ•˜๋ฉด, ์œ„์—์„œ ๋งŒ๋“  Connection ๊ฐ์ฒด๋กœ๋ถ€ํ„ฐ cursor() ๋ฉ”์„œ๋“œ๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ Cursor ๊ฐ์ฒด๋ฅผ ๊ฐ€์ ธ์˜ด
    - Cursor: SQL ๊ตฌ๋ฌธ์„ ์‹คํ–‰ํ•˜๊ธฐ ์œ„ํ•ด์„œ ๋งŒ๋“œ๋Š” ๊ฐ์ฒด (Executes a SQL statement.)

์ฐธ๊ณ ๋งํฌ: https://www.tutorialspoint.com/python_data_access/python_mysql_cursor_object.htm

The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database.

Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.

You can create Cursor object using the cursor() method of the Connection object/class.

-> cursor ๋Š” control structure of database (์—ฐ๊ฒฐ๋œ ๊ฐ์ฒด)

  1. SQL ์ฟผ๋ฆฌ์˜ ๊ฒฝ์šฐ Cursor ๊ฐ์ฒด์˜ fetchall(), fetchone(), fetchmany() ๋“ฑ์˜ ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์„œ๋ฒ„๋กœ๋ถ€ํ„ฐ ๊ฐ€์ ธ์˜จ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ”๋“œ์—์„œ ํ™œ์šฉ

  2. ์‚ฝ์ž…, ๊ฐฑ์‹ , ์‚ญ์ œ ๋“ฑ์˜ DML(Data Manipulation Language) ๋ฌธ์žฅ์„ ์‹คํ–‰ํ•˜๋Š” ๊ฒฝ์šฐ, INSERT/UPDATE/DELETE ํ›„ Connection ๊ฐ์ฒด์˜ commit() ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํƒ€๋ฅผ ํ™•์ •

  3. Connection ๊ฐ์ฒด์˜ close() ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ DB ์—ฐ๊ฒฐ์„ ๋‹ซ์Œ


insert data(Create)

import pymysql

# ์ ‘์†
# ๋น„๋ฐ€๋ฒˆํ˜ธ๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ๋ณดํ†ต configํŒŒ์ผ์—์„œ key๊ฐ’์œผ๋กœ ๋ถ€๋ฅธ๋‹ค.
# ํ•ด๋‹น ์ฝ”๋“œ๋Š” ์˜ˆ์‹œ์ด๊ธฐ ๋•Œ๋ฌธ์— ์ง์ ‘ ์ž…๋ ฅ๋˜์–ด ์žˆ๋Š” ํ˜•ํƒœ์ธ ์ ์„ ์ฐธ๊ณ !
db = pymysql.connect(
                    host='127.0.0.1', 
                    port=3306, 
                    user='root', 
                    passwd='your_password', 
                    db='your_dbname', 
                    charset='utf8'
                    )

# Cursor Object ๊ฐ€์ ธ์˜ค๊ธฐ
cursor = db.cursor()

# SQL ๋ฌธ ๋งŒ๋“ค๊ธฐ
sql = '''
            CREATE TABLE your_table (
                   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                   name VARCHAR(20) NOT NULL,
                   model_num VARCHAR(10) NOT NULL,
                   model_type VARCHAR(10) NOT NULL,
                   PRIMARY KEY(id)
            );
        '''

# SQL ์‹คํ–‰ํ•˜๊ธฐ
cursor.execute(sql)

# ์‹คํ–‰ mysql ์„œ๋ฒ„์— ํ™•์ • ๋ฐ˜์˜ํ•˜๊ธฐ
db.commit()

# DB ์—ฐ๊ฒฐ ๋‹ซ๊ธฐ
db.close()

get data (Read)

db = pymysql.connect(
                    host='127.0.0.1', 
                    port=3306, 
                    user='root', 
                    passwd='your_password', 
                    db='your_dbname', 
                    charset='utf8'
                    )

try:
    with db.cursor() as cursor:
        sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
        cursor.execute(sql)
        
        while result:
            result = cursor.fetchone()
            print(result)        
finally:
    db.close()

update

db = pymysql.connect(
                    host='127.0.0.1', 
                    port=3306, 
                    user='root', 
                    passwd='your_password', 
                    db='your_dbname', 
                    charset='utf8'
                    )
try:
    with db.cursor() as cursor:
        sql = "UPDATE cpu_info SET model_type='%s' WHERE name = 'i7'" % '์นด๋น„๋ ˆ์ดํฌ'
        cursor.execute(sql)
        db.commit()
        print(cursor.rowcount)
finally:
    db.close()

delete

db = pymysql.connect(
                    host='127.0.0.1', 
                    port=3306, 
                    user='root', 
                    passwd='your_password', 
                    db='your_dbname', 
                    charset='utf8'
                    )
hit_count = 20
try:
    with db.cursor() as cursor:
        sql = "DELETE FROM cpu_info WHERE name = '%s'" % 'i7'
        cursor.execute(sql)
        db.commit()
        print(cursor.rowcount)
finally:
    db.close()
profile
์ด์‚ฌ๊ฐ„ ๋ธ”๋กœ๊ทธ: yenilee.github.io

1๊ฐœ์˜ ๋Œ“๊ธ€

comment-user-thumbnail
2020๋…„ 11์›” 2์ผ

๊ทธ๊ฑฐ ์•Œ์•„์š”? ์ €๋Š” ๋งค์ผ ์ด ๊ณณ์—์„œ ๋ฐฐ์šฐ๊ณ  ์žˆ๋‹ค๋Š” ๊ฒƒ์„ ใ…‹ใ…‹ ์ง„์งœ ํŒŒ์ด์ฌ ํ”Œ๋ผ์Šคํฌ ํŒŒ์ด๋งˆ์ด์—์Šคํ์—˜ ๋ชฐ๋ผ์„œ ๊ฒ€์ƒ‰ํ•˜๋ฉด ๋งจ๋‚  ์ด ๋ธ”๋กœ๊ทธ๋กœ ๋“ค์–ด์˜ด ใ…‹ใ…‹ใ…‹ใ…‹ใ…‹ใ…‹ใ…‹ ์ •๋ง๊ฐ์‚ฌํ•ด์š” ์˜ˆ์€๋‹˜

๋‹ต๊ธ€ ๋‹ฌ๊ธฐ