SQL 해석 순서 : FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

1. DB 계정 생성

• root에서 계정 생성 후, database 생성.

create user ohgiraffers@'%' identified by 'ohgiraffers';

create database menudb;
create database employeedb;

show databases;

grant all privileges on menudb.* to ohgiraffers@'%';
grant all privileges on employeedb.* to ohgiraffers@'%';

show grants for ohgiraffers@'%';

2. python과 연결하기

• mySQL 설치

$ pip install mysql-connector-python

# db.py
import mysql.connector

# DB 연결
connection = mysql.connector.connect(
    host="localhost", 
    user="ohgiraffers", 
    password="ohgiraffers", 
    database="menudb",
)

# 연결 확인
if connection.is_connected():
    print("MySQL에 성공적으로 연결되었습니다!")

from db import connection

######### 공통 ######### 
# cursor() : 파이썬과 데이터베이스 사이에서 쿼리 실행 및 결과 처리를 담당
cursor = connection.cursor()

######### SELECT 문 #########
cursor.execute("SELECT menu_code, menu_name, menu_price FROM tbl_menu")
result = cursor.fetchall()
for row in result:
    print(row)

######### INSERT 문 #########
# %s = 플레이스 홀더, 위치 잡기
insert_query = "INSERT INTO tbl_menu ( menu_name, menu_price, category_code, orderable_status) VALUES (%s, %s, %s, %s)"
values = ("햄버거", 5000, 4, "Y")

cursor.execute(insert_query, values)
connection.commit()
print(f"{cursor.rowcount}개의 행이 삽입되었습니다.")

######### UPDATE 문 #########
query = "UPDATE tbl_menu SET menu_name = %s, menu_price = %s WHERE menu_code = %s"
values = ("치킨버거", 6000, 23)

cursor.execute(query, values)
connection.commit()

######### DELETE 문 #########
query = "DELETE FROM tbl_menu WHERE menu_code >= %s"
values = (22,)

cursor.execute(query, values)
connection.commit()

######### 공통 #########
cursor.close()
connection.close()