달란트(Talanton)

Python에서 MySQL 데이터베이스를 사용하는 방법을 적어본다.

- 환경

  + Python 3.9.10

  + MySQL : 8.0.29

  + anaconda3에서 pymysql 모듈 설치

- idle에서 파일 작성하여 run module 실행하면 pymysql 모듈이 없다고 동작하지 않음

- cmd 창에서 python Code13-02.py 입력하여 실행하면 정상 동작

[테이블 생성 및 데이터 초기화]

테이블 생성을 위해 사용한 SQL문은 아래와 같다.

create table userTable (
	id varchar(10),
    userName varchar(15),
    email varchar(20),
    birthYear int
);

insert into userTable values ('john', 'John Bann', 'john@naver.com', 1990);
insert into userTable values ('kim', 'Kim Chi', 'kim@daum.net', 1992);
insert into userTable values ('lee', 'Lee Pal', 'lee@paran.com', 1988);
insert into userTable values ('park', 'Park Su', 'park@gmail.com', 1980);

select * from userTable;

[userTable에 사용자 정보를 터미널에서 입력으로 받아 저장] : Code13-01.py

import pymysql

# 변수 선언 부분
conn, cursor = None, None
data1, data2, data3, data4 = "", "", "", ""
sql = ""

# 메인 코드 부분
conn = pymysql.connect(host='localhost', user='study', password='study', db='studydb', charset='utf8')
cursor = conn.cursor()

while (True):
    data1 = input("사용자ID ==> ")
    if data1 == "":
        break;
    data2 = input("사용자이름 ==> ")
    data3 = input("이메일 ==> ")
    data4 = input("출생년도 ==> ")
    sql = "insert into userTable values ('" + data1 + "','" + data2 + "','" + data3 + "','" + data4 + "')"
    cursor.execute(sql)
conn.commit()
conn.close()

cmd 창을 사용하여 Code13-01.py를 실행한 결과는 아래와 같다.

MySQL workbench를 사용하여 데이터를 확인하면 다음과 같이 잘 저장됨을 알 수 있다.

한빛 아카데미에서 나온 박우창, 남송휘, 이현룡 지음, "MySQL로 배우는 데이터베이스 개론과 실습" 에 나오는 마당서점의 물리적 모델링으로 이큽립스에서 제공되는 ER Master 도구를 이용하여 작업을 해보았다.

이를 물리적 SQL 파일로 만들기 위해서는 이클립스에서 오른쪽 마우스를 클릭하여 아래 그림과 같이 "내보내기->DDL" 메뉴를 선택하면 데이터베이스 연동을 위한 sql 파일을 생성할 수 있다.

이를 이용하여 데이터베이스 테이블을 생성할 수 있다.

/* Drop Triggers */
DROP TRIGGER TRI_author_id;
DROP TRIGGER TRI_comment_id;
DROP TRIGGER TRI_tauthor_id;
DROP TRIGGER TRI_tcomment_id;
DROP TRIGGER TRI_topic_id;

/* Drop Tables */
DROP TABLE dormant CASCADE CONSTRAINTS;
DROP TABLE tcomment CASCADE CONSTRAINTS;
DROP TABLE twrite CASCADE CONSTRAINTS;
DROP TABLE tauthor CASCADE CONSTRAINTS;
DROP TABLE topic CASCADE CONSTRAINTS;

/* Drop Sequences */
DROP SEQUENCE SEQ_author_id;
DROP SEQUENCE SEQ_comment_id;
DROP SEQUENCE SEQ_tauthor_id;
DROP SEQUENCE SEQ_tcomment_id;
DROP SEQUENCE SEQ_topic_id;

/* Create Sequences */
CREATE SEQUENCE SEQ_tauthor_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_tcomment_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_topic_id INCREMENT BY 1 START WITH 1;

/* Create Tables */
CREATE TABLE dormant
(
	author_id number NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (author_id)
);

CREATE TABLE tauthor
(
	id number NOT NULL,
	name varchar2(15) NOT NULL,
	introduction varchar2(100) NOT NULL,
	created date UNIQUE,
	PRIMARY KEY (id)
);

CREATE TABLE tcomment
(
	id number NOT NULL,
	description varchar2(4000) NOT NULL,
	created date NOT NULL,
	topic_id number NOT NULL,
	author_id number NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE topic
(
	id number NOT NULL,
	title varchar2(30) NOT NULL,
	description varchar2(4000) NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE twrite
(
	author_id number NOT NULL,
	topic_id number NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (author_id, topic_id)
);

/* Create Foreign Keys */
ALTER TABLE dormant
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE tcomment
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE twrite
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE tcomment
	ADD FOREIGN KEY (topic_id)
	REFERENCES topic (id)
;

ALTER TABLE twrite
	ADD FOREIGN KEY (topic_id)
	REFERENCES topic (id)
;

/* Create Triggers */
CREATE OR REPLACE TRIGGER TRI_author_id BEFORE INSERT ON author
FOR EACH ROW
BEGIN
	SELECT SEQ_author_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_comment_id BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
	SELECT SEQ_comment_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_tauthor_id BEFORE INSERT ON tauthor
FOR EACH ROW
BEGIN
	SELECT SEQ_tauthor_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_tcomment_id BEFORE INSERT ON tcomment
FOR EACH ROW
BEGIN
	SELECT SEQ_tcomment_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_topic_id BEFORE INSERT ON topic
FOR EACH ROW
BEGIN
	SELECT SEQ_topic_id.nextval
	INTO :new.id
	FROM dual;
END;

/

나는 Erwin 프로그램의 사용에 제약이 있고, 이클립스에 익숙하여 쉽게 데이터베이스 테이블을 생성할 수 있기 때문에 이 방법을 사용하였다. 또한 Data Source Exploror를 같이 이용할 수 있는 장점이 있다.

이클립스에서 ER Master를 설치하는 방법은 Help->Install New Software에서 다음과 같이 입력하여 설치를 한다.

홈 페이지를 만들다보면 게시판을 여러 개를 만들 필요가 있다. 또한 게시판 마다 게시글의 읽기, 쓰기, 댓글달기, 첨부파일 달기 등의 권한을 달리 설정해주어야 하는 경우가 발생한다.

여기서는 그런 경우에 고려하여야 할 사항과 구현방법에 대하여 기술한다.

1. 요구사항

- 여러 개의 게시판을 운영한다. : 게시판 생성, 게시판 정보 변경, 게시판 삭제, 게시판 목록 보기, 게시판 정보 보기 등의 기능이 필요

- 게시판에 대한 속성 설정 : 읽기, 쓰기, 댓글달기, 비밀글 여부, 첨부파일 허용여부, 첨부파일 허용용량, 게시글 수정, 게시글 삭제

- 게시판 종류 : 기본, 갤러리, 자료실, Q&A, 공지사항, 방명록

  + 기본 게시판 : 제목, 작성자, 비밀번호, 내용, 첨부 1개

  + 갤러리 : 이미지 첨부만 가능, 첨부는 thumbnail로 처리

  + 자료실 : 첨부 파일은 5개까지 가능

  + Q&A : 비밀글 표시 및 비밀번호 검사 후 내용 표시

  + 공지사항 : 글쓰기 버튼 비활성화, 관리자만 쓰기 가능

  + 방명록 : 내용과 첨부 사진 올리기만 가능, 비밀글(쓰기, 답변)

여기에 기술된 요구사항은 대략적인 것으로 개발을 위해서는 구체적인 요구사항이 세분화 되어져야 한다.

2. 데이터베이스 테이블

2.1 board table

- board_id : 게시판을 식별하는 ID (정수)

- board_name : 게시판 이름

- board_type : 게시판 종류 (기본 게시판, 갤러리, 자료실, Q&A, FAQ, 공지사항, 방명록)

- url : 게시판에 접속하기 위한 URL 주소

- secret : 비밀글 여부

- read_allow : 읽기 허용 여부 (사용자 유형에 따라 제어 : 로그인 안했을 때, 로그인 했을 때, 관리자)

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- write_allow : 쓰기 허용 여부

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 특정 사용자만 허용 : 별도의 쓰기 허용자 정보 저장 필요 (추후 고려)

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- reply_allow : 댓글 허용 여부

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- modify_allow : 수정 허용 여부

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 작성자만 허용

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- delete_allow : 삭제 허용 여부

  + 작성자만 허용

  + 관리자만 허용

- download : 첨부파일 다운로드 허용 여부

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- upload : 첨부파일 업로드 허용 여부

  + 모두 허용

  + 로그인한 경우 허용

  + 가입자 유형이 고객(customer)인 경우 허용

  + 가입자 유형이 상인(business)인 경우 허용

  + 특정 사용자만 허용 (추후 고려)

  + 특정 그룹에 속한 경우 허용 (추후 고려)

  + 관리자만 허용

- nAttach : 첨부 가능한 파일의 수 (0~최대 5개)

- aSize : 첨부 가능한 파일의 용량 (최대 100MB까지)

  + 0, 50KB, 200KB, 1MB, 10MB, 100MB

- display_format : 본문 표시 여부

  + 1 : 제목만 표시

  + 2 : 본문(내용) 일부 표시

  + 3 : 본문(내용) 전부 표시

- board_desc : 게시판 설명

2.2 article table

- article_id : 게시글 id

- boardId : 게시판 id (하나의 테이블에 모든 게시판의 게시글을 저장하므로 게시판을 구분해 주어야 함)

- writer_name : 게시글 작성자 이름(O) -> 불필요할 수 있음(원 저자)

- email : 게시글 작성자 id (이메일 형식)

- title : 제목(O)

- author : 시와 같은 창작물을 게시할 때 원 저자를 입력(O)

- password : 비밀번호 (로그인 하지 않은 사용자가 게시글을 작성시, 추후 게시글 삭제 때 필요)

- read_count : 게시글을 읽은 횟수

- comment_count : 게시글에 대한 댓글 수

- like_count : 좋아요 수

- ref : 게시글과 댓글에 대한 정렬에 사용

- step : 게시글과 댓글에 대한 정렬에 사용

- depth : 게시글과 댓글에 대한 정렬에 사용

- createdAt : 게시글 작성 시간

- modifiedAt : 게시글 수정 시간

- content : 게시글 본문 내용

- ip : 사용자가 접속한 IP address

- secret : 비밀글 여부 (default false)

3. 추가로 고려되어야 할 사항

- 게시판 생성 후, 메뉴와의 연계방법 입니다. 동적으로 게시판을 생성하였더라도 그 게시판을 사용하려면 홈 페이지의 메뉴 체계와 동적으로 연계가 되어야 하는데 그 부분은 상당히 난이도가 있는 부분 같습니다. 전체 구조와도 연계가 되기 때문 입니다. 그 부분은 나중에 시간이 있을 때 고려하기로 합니다.