MariaDB 4일차 강의 / 현대오토에버 모빌리티 SW 스쿨 / 클라우드반

질문

1. 절차적 프로그래밍도 5일 시험에 나오나요?

 

1. View

1) Inline View

• 메모리를 사용하는 방식이라 조회 속도가 좋음. View
• 1) Inline View
• 메모리를 사용하는 방식이라 조회 속도가 좋음
• FROM 절에 사용된 Sub Query
  • Sub Query를 사용하는 곳
    • WHERE
    • HAVING
    • WHERE

SELECT *
FROM (SELECT * FROM tCity) A;

 

📍tStaff 테이블에서 grade가 과장 또는 부장인 사원 중에서 score가 70 이상인 데이터를 조회

-- 틀린 코드
SELECT * 
FROM tStaff
WHERE grade = '과장' OR grade = '부장' AND score >= 70;

-- 맞는 코드
SELECT * 
FROM tStaff
WHERE (grade = '과장' OR grade = '부장') AND score >= 70;

-- 맞는 코드
SELECT * 
FROM tStaff
WHERE grade in('과장', '부장') AND score >= 70;

• INLINE VIEW 를 이용한 조회

-- 인라인 뷰를 이용한 조회
SELECT *
FROM (SELECT * FROM tStaff WHERE grade in('과장', '부장')) imsi
WHERE score >= 70;

 

2) VIEW

• 개요
  • 물리적인 테이블을 근거한 논리적인 가상의 테이블
  • SELECT 구문을 메모리에 저장해두고 사용하는 것
  • SELECT 구문의 결과가 TABLE이므로 이 테이블에 이름을 붙여서 사용하는 것
• 실습을 위한 SELECT 활용 테이블 생성

-- 혹시 있을까봐 삭제
DROP TABLE DEPT_COPY;
DROP TABLE EMP_COPY;

CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPT;

CREATE TABLE EMP_COPY
AS
SELECT * FROM EMP;

 

• 뷰 생성 기본 형식

CREATE [OR REPLACE] VIEW 뷰이름 [별명 나열]
AS
SELECT 구문
[WITH CHECK OPTION]
[WITH READ ONLY];

 

• 이러면 뷰 이름으로 SELECT 구문이 메모리에 저장됨
• WITH CHECK OPTION은 SELECT 구문에 WHERE 절이 있는 경우 조건에 맞지 않는 데이터를 삽입하거나 수정할 수 없도록 합니다.
• WITH READ ONLY는 읽기 전용을 만듭니다.

 

📍[뷰 생성] EMP_COPY 테이블에서 DEPTNO가 30인 데이터의 EMPNO, ENAME, DEPTNO를 조회하는 작업을 자주 수행

• 이런 경우 자주 사용하는 SELECT 구문을 메모리에 저장하고 빠르게 수행할 목적으로 VIEW를 생성할 수 있음

CREATE OR REPLACE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;

 

• 뷰 조회, 구조 확인

SELECT * 
FROM EMP_VIEW30;

DESC EMP_VIEW30;

 

• 사용 목적
  • 실행 속도: SELECT 구문은 보조 기억 장치에 존재하지만 VIEW는 주 기억장치(메모리)에 존재
  • 테이블처럼 사용할 수 있기 때문에 쿼리의 길이가 짧아집니다.
  • 보안에 유리: 실제 테이블 구조를 알려줄 필요가 없습니다.
• READ ONLY 옵션
  • VIEW는 가상의 테이블이지만 동적으로 물리적인 테이블을 참조해서 생성됩니다.
  • 옵션없이 VIEW를 만들면 데이터의 삽입, 삭제, 갱신이 가능할 수도 있습니다.
  • 이를 방지하고자 할 때 READ ONLY 옵션을 이용

📍READ ONLY의 필요성

CREATE VIEW DEPT_VIEW1
AS
SELECT *
FROM DEPT_COPY;

-- 현재 상태 확인: 10,20,30,40 4개의 데이터 존재
SELECT * 
FROM DEPT_COPY;

 

📍VIEW의 데이터를 삽입

INSERT INTO DEPT_VIEW1 VALUES(50, '비서', '서울');

 

📍원본 테이블 확인: 50이 추가됨

• VIEW에 INSERT 해도 원본 테이블에 삽입됨
• 그래서 VIEW를 사용할 때는 읽기 전용 계정을 만들어서 계정에서 뷰를 읽기만 할 수 있도록 함

select * from DEPT_COPY;

 

 

3) 임시 테이블

• 테이블을 만들 때 CREATE TEMPORARY TABLE로 만들면 임시 테이블이 됨
• 현재 세션에서만 접근 가능하고 세션이 만료되면 테이블은 사라짐

 

4) CTE

• 중간 결과를 저장하기 위한 임시 테이블
• INLINE VIEW와 유사한 역할
• 기본 형식
  • WITH 테이블이름(컬럼이름) AS (쿼리) 형식으로 생성해서 현재 SQL에서만 사용
• VIEW는 데이터베이스가 존재하는 한 계속 존재하지만, CTE는 현재 트랜잭션 내에서만 존재함
• tStaff 테이블에서 DEPART가 영업부이고 GENDER가 남인 사원의 NAME, SALARY, SCORE를 가지는 CTE를 생성

WITH TEMP(이름, 급여, 성취도) AS
(
SELECT NAME, SALARY, SCORE FROM tStaff WHERE DEPART = '영업부' AND GENDER = '남')
SELECT * FROM TEMP WHERE 급여 >= (SELECT AVG(급여) FROM TEMP);

 

SELECT NAME 이름, SALARY 급여, SCORE 성취도
FROM tStaff
WHERE DEPART = '영업부' AND GENDER = '남' AND SALARY >= (SELECT AVG(SALARY) FROM tStaff WHERE DEPART = '영업부' AND GENDER = '남');

 

SELECT NAME 이름, SALARY 급여, SCORE 성취도
FROM (SELECT NAME, SALARY, SCORE FROM tStaff WHERE DEPART = '영업부' AND GENDER = '남') t;

SELECT NAME 이름, SALARY 급여, SCORE 성취도
FROM (SELECT NAME, SALARY, SCORE FROM tStaff WHERE DEPART = '영업부' AND GENDER = '남') t
WHERE DEPART = '영업부' AND GENDER = '남' AND SALARY >= (SELECT AVG(SALARY) FROM WHERE DEPART = '영업부' AND GENDER = '남');

 

 

5) 임시 테이블들의 차이점

• INLINE VIEW: 자신이 속한 SELECT 구문에서만 사용 가능, 어떤 자료형이든 다 사용 가능!
• CTE: 하나의 트랜잭션 내에서만 동작
• TEMPORARY TABLE: 현재 세션에서만 사용 가능한 테이블
• VIEW: 현재 데이터베이스 전체에서 사용 가능한 테이블

---

2. Python과의 연동

1) 프로그래밍 언어와 데이터베이스 연동

• 정보
  • 데이터베이스 서버 위치(IP나 도메인), 포트번호(컴퓨터에서 외부 접속 가능한 애플리케이션을 구분하기 위한 번호), 접속할 데이터베이스 이름, 유저의 아이디와 비밀번호
  • 경우에 따라서는 IP와 포트번호만으로 접속 가능한 경우도 있습니다.
  • 현재 컴퓨터는 localhost 또는 127.0.0.1로 설정합니다.
  • 프로그래밍 언어와 데이터베이스 사이에서 인터페이스 역할을 수행해 줄 드라이버

 

2) 연동 방법

• 드라이버만으로 연동 - SQL이 필수
• ORM과 프레임워크를 이용하는 방법 - SQL을 사용하지 않고 프로그래밍 언어의 함수나 클래스를 이용해서 작업을 수행

 

3) Maria DB 연동

• 필요한 정보
  • 컴퓨터의 IP: localhost
  • 포트번호: 3306
  • 데이터베이스 이름: adam
  • userID: root
  • 비밀번호: __
• 드라이버: pymysql 패키지
  • 설치: pip install pymysql 또는 python -m pip install pymysql

 

4) 접속 및 연동

• 접속

import pymysql

#접속
try:
    con = pymysql.connect(host='데이터베이스 위치', 
    					  port='포트번호', 
                          user='계정', 
                          password='비밀번호', 
                          database='데이터베이스이름', 
                          charset='인코딩방식')
    print(con)

except:
    print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

📍샘플 테이블 생성

USE scrooge;

CREATE TABLE usertbl(
	userid char(15) PRIMARY KEY,
    password varchar(20) not null,
    nickname varchar(6) not null,
    emil varchar(30) not null,
    phone varchar(11) not null
);

INSERT INTO usertbl VALUE('mangkkong123', 'Test123!', '맹꽁이', 'mangkkong@gmall.com', '01012345678');

COMMIT;

 

📍DML 수행

• 연결 객체에서 cursor()를 호출해서 SQL 실행 객체를 생성
• SQL 실행 객체.execte(실행할 SQL 문장, [마운트될 데이터를 튜플로 설정])
• 직접 값을 설정해도 되고 SQL 문장을 만들 때 ?를 사용한 후 뒤에서 튜플로 값을 대입해도 됩니다.
• 튜플로 값을 대입하는 것을 권장
• 데이터베이스에 반영하고자 할 때는 commit 메서드를 호출하고 취소할 때는 rollback 메서드를 호출하면 됩니다.

import pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='_', 
                          database='scrooge',
                          charset='utf8md4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    #삽입하는 SQL 실행
    cursor.execute("INSERT INTO usertbl values('one123', 'Test123!', '일번', 'one@gmall.com', '01012341234')")

except:
    print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

• Transaction
  • database에서 논리적인 작업단위
  • 모드
    • Manual Commit: 수동
      • 명시적 commit
      • DDL, DCL 수행
      • 정상 종료 
    • Auto Commit: SQL 실행되면 반영

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='_', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    #삽입하는 SQL 실행 - 값이 설정되어야 하는 자리에 %s를 설정하고 다음 매개변수로 값을 설정
    cursor.execute("INSERT INTO usertbl values(%s, %s, %s, %s, %s)", ('two123', 'Test123!', '이번', 'two@gmall.com', '01012121212'))
    con.commit()

except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

📍데이터를 수정: 이전에 삽입한 데이터의 two를 second로 수정

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='_', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    #삽입하는 SQL 실행 - 값이 설정되어야 하는 자리에 %s를 설정하고 다음 매개변수로 값을 설정
    cursor.execute("UPDATE usertbl SET nickname=%s where nickname=%s", ('second', 'two'))
    con.commit()
    print("작업 성공!")

except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

• DQL 수행: SELECT
  • SQL을 실행하는 방법은 동일
  • COMMIT이나 ROLLBACK을 호출할 필요가 없습니다.
  • SQL을 실행하고 SQL 실행 객체가 fetchone이나 fetchall 메서드를 호출하면 검색된 결과 데이터 1개 또는 여러 개를 tuple이나 tuple의 tuple로 리턴합니다.

#commit으로는 실패!!
import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    cursor.execute("SELECT * FROM usertbl")
    #하나의 데이터만 조회
    con.commit()
    #하나의 데이터만 조회하므로 tuple로 리턴
    print("작업 성공!")

except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

📍하나의 데이터 조회(성공)

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    cursor.execute("SELECT * FROM usertbl")
    #하나의 데이터만 조회
    result = cursor.fetchone()
    #하나의 데이터만 조회하므로 tuple로 리턴
    print(result, "\n작업 성공!")

except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

📍여러 개의 데이터 조회

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    cursor.execute("SELECT * FROM usertbl")
    #여러 개의 데이터만 조회
    result = cursor.fetchall()
    #여러 개의 데이터를 조회하므로 tuple로 리턴
    for record in result:
        print(record)
    print("작업 성공!")

except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()
        
# --결과--
# ('mangkkong123', 'Test123!', '맹꽁이', 'mangkkong@gmall.com', '01012345678')
# ('two123', 'Test123!', '이번', 'two@gmall.com', '01012121212')
# 작업 성공!

 

📍record[2] 이면 닉네임만 나오게 할 수 있음

try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='739458', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    cursor.execute("SELECT * FROM usertbl")
    #여러 개의 데이터만 조회
    result = cursor.fetchall()
    #여러 개의 데이터를 조회하므로 tuple로 리턴
    for record in result:
        print(record[2])
    print("작업 성공!")
    
# --결과--
# 맹꽁이
# 이번
# 작업 성공!

 

 

• 프로시저 실행
  • cursor 객체를 가지고 callproc 라는 메서드를 호출하면 되는데 첫번째 매개변수는 프로시저 이름이고 args라는 매개변수에 입력 매개 변수를 튜플로 대입해서 실행
  • 프로시저가 DML 문장을 실행하는 경우하면 commit과 rollback을 호출해서 트랜잭션을 적용합니다.
  • 데이터베이스 접속 도구에서 프로시저를 생성하고 실행

DELIMITER //

CREATE PROCEDURE usertbl_insert_user(
    IN p_userid   CHAR(15),
    IN p_password VARCHAR(20),
    IN p_nickname VARCHAR(6),
    IN p_emil     VARCHAR(30),
    IN p_phone    VARCHAR(11)
)
BEGIN
    INSERT INTO usertbl (userid, password, nickname, emil, phone)
    VALUES (p_userid, p_password, p_nickname, p_emil, p_phone);
END//

DELIMITER ;

 

📍프로시저 실행 코드

CALL usertbl_insert_user('three123', 'Test123!', '삼번', 'three@gmall.com', '01033333333');

 

📍파이썬에서 프로시저 실행

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)
    
    #SQL 실행 객체를 생성
    cursor = con.cursor()
    
    #usertbl_insert_user라는 프로시저를 실행
    cursor.callproc('usertbl_insert_user', ('four123', 'Test123!', '사번', 'four@gmall.com', '01044444444'))
    con.commit()
    
except:
    print("작업 실패\n", sys.exc_info())
    #print("데이터베이스 접속에 실패했습니다.")
    
finally:
    #접속 해제
    if con != None:
        con.close()

 

• BLOB 연동
  • BLOB(Binary Large Object)
    - 데이터베이스에서 비정형 데이터를 저장할 때 사용하는 데이터 타입
    - 비정형 데이터는 텍스트나 숫자와 날짜가 아닌 이미지, 오디오, 비디오, 실행 파일 등 바이너리(이진) 형태의 데이터

📍샘플 테이블을 생성

CREATE TABLE sample(
	userid CHAR(15),
    	filename VARCHAR(1000),
    	filecontent LONGBLOB);
    
SELECT * FROM sample;

 

 

📍BLOB 기록

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')

    #SQL 실행 객체를 생성
    cursor = con.cursor()
   
    #파일 이름 결정
    filename = 'choi.jpg'
    #파일 열기
    f = open(filename, 'rb')
    #파일 읽기
    photo = f.read()
    #파일 닫기
    f.close()

    cursor.execute('INSERT INTO usertbl values(%s, %s, %s, %s, %s)", ('five123', 'Test123!', '오번', 'five@gmall.com', '01012345555'))
    con.commit()
    print('삽입 성공')


except:
    #print("데이터베이스 접속에 실패했습니다.")
    print("작업 실패", sys.exc_info())

finally:
    #접속 해제
    if con != None:
        con.close()

 

📍BLOB 읽기

import sys, pymysql

#접속
try:
    con = pymysql.connect(host='localhost', 
                          port=3306, 
                          user='root', 
                          password='__', 
                          database='scrooge',
                          charset='utf8mb4')
    #print(con)

    #SQL 실행 객체를 생성
    cursor = con.cursor()
   
    cursor.execute("SELECT * FROM sample")
    data = cursor.fetchone()
    print(data[0])
    print(data[1])
    f = open(data[1], 'wb')
    f.write(data[2])
    f.close()


except:
    #print("데이터베이스 접속에 실패했습니다.")
    print("작업 실패", sys.exc_info())

finally:
    #접속 해제
    if con != None:
        con.close()

 

 

5) ORM(Object Relational Mapping)

• 개요
  • 객체 지향 패러다임을 관계형 데이터베이스에 보존하는 기술
  • 객체와 관계형 데이터베이스의 테이블을 매핑해서 사용하는 방법
  • 관계형 데이터베이스의 테이블은 객체 지향 언어의 CLASS와 유사
  • CLASS와 테이블의 불일치를 ORM이 해결
  • 장점
    • 특정 데이터베이스에 종속되지 않음
    • 객체 지향적 프로그래밍
    • 생산성 향상
  • 단점
    • 복잡한 쿼리 처리
    • 설계를 잘못하면 성능 저하
    • 학습 시간
• SQLAlchemy
  • 개요
    • 파이썬에 많이 사용되는 ORM
    • 패키지: sqlalchemy
    • 설치: python -m pip install sqlalchemy
  • 샘플 작성

from sqlalchemy import create_engine, Column, String
from sqlalchemy.orm import declarative_base, sessionmaker

#데이터베이스 연결 url 설정
DB_URL = "mysql+pymysql://root:739458@localhost:3306/scrooge?charset=utf8mb4"

#데이터베이스 연결 통로 생성
engine = create_engine(DB_URL, echo=True)
#echo가 true이면 실제 수행되는 SQL을 확인할 수 있음

#세션 설정
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

#모델 생성할 기본 클래스를 생성
Base = declarative_base()

#테이블 모델 정의
class User(Base):
    __tablename__ = "usertbl"

    userid = Column(String(15), primary_key=True)
    password = Column(String(20), nullable=False)
    nickname = Column(String(6), nullable=False)
    emil = Column(String(30), nullable=False)
    phone = Column(String(11), nullable=False)

#테이블 자동 생성
# ⚠️ 이미 테이블이 있으므로 실제로는 생성하지 않지만, 문법상 유지
Base.metadata.create_all(bind=engine)

db = SessionLocal()
try:
    new_user = User(
        userid="six123",
        password="Test123!",
        nickname="강감찬",
        emil="kang@gmail.com",
        phone="01066666666"
    )
    db.add(new_user)
    db.commit()

except Exception as e:
    print("에러발생:", e)
    db.rollback()
finally:
    db.close()