0014.6 pymysql, mysql python wrapper

• pymysql doc
• 설치: pip install pymysql

"""
pymysql 모듈 실습
"""

import pymysql.cursors
from pprint import pprint
from faker import Faker
import random

faker = Faker(["ko_KR"])

con = pymysql.connect(
    host="localhost",
    user="root",
    password="oz-password",
    db="classicmodels",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)

### 1. SELECT


def get_customers(limit: int = 10):
    cur = con.cursor()
    cur.execute("SELECT * FROM customers")
    cur.close()
    return cur.fetchall()


### 2. INSERT


def create_customers(number: int = 10):
    cur = con.cursor()
    values = []

    for _ in range(10):
        number = random.randint(0, 999999)
        name = f"'{faker.name()}'"
        first_name = f"'{faker.first_name()}'"
        last_name = f"'{faker.last_name()}'"
        phone = f"'{faker.phone_number()}'"
        address = f"'{faker.address()}'"
        city = f"'{faker.city()}'"
        country = f"'{faker.country()}'"

        values.append(
            ", ".join((str(number), name, first_name, last_name, phone, address, city, country))
        )

    sql = (
        "INSERT INTO customers(customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, city, country) VALUES ("
        + "),\n(".join(values)
        + ")"
    )
    cur.execute(sql)
    con.commit()  # 이거 안해주면 DB 저장 안함
    cur.close()


### 3. UPDATE


def update_customer(where: str, to: str):
    cur = con.cursor()
    update_name = to
    contact_last_name = to

    sql = f"""UPDATE customers 
    SET customerName='{update_name}', 
        contactLastName='{contact_last_name}' 
    WHERE {where}"""

    cur.execute(sql)
    con.commit()
    cur.close()


### DELETE

def delete_customer(where: str):
    cur = con.cursor()
    sql = f"DELETE FROM customers WHERE {where}"
    cur.execute(sql)
    con.commit()
    cur.close()

create_customers(5)
pprint(get_customers())

update_customer("MOD(customerNumber, 2) = 0", "💀")
pprint(get_customers())

delete_customer("customerNumber > 10000")
pprint(get_customers())