231107_DB 개론 2

create table emp (
	e_id int not null primary key,
	e_name varchar(50),
	gen varchar(1),
	addr varchar(400)
);

select * from emp;

---

* 1시간 오류 딜레이로 MSSQL은 안함. MariaDB로 변경.

---

https://mariadb.org/download/?t=mariadb

Download MariaDB Server - MariaDB.org

https://www.heidisql.com/download.php

Download HeidiSQL

---

python 하고 반드시 체크!

기본 : 3306

3305로 변경

---

USE python;

create table emp (
	e_id int not null,
	e_name varchar(50),
	gen varchar(1),
	addr varchar(400),
	primary KEY(e_id)
);

select * from emp;

---

INSERT INTO emp (e_id, e_name, gen, addr)
VALUES (1, 'test', 'm', '대전시 서구 도마동 101번지');

select * from emp;

---

INSERT INTO emp (e_id, e_name, gen, addr)
VALUES (1, '1', '1', '1');
INSERT INTO emp (e_id, e_name, gen, addr)
VALUES (2, '2', '2', '2');
INSERT INTO emp (e_id, e_name, gen, addr)
VALUES (3, '3', '3', '3');
INSERT INTO emp (e_id, e_name, gen, addr)
VALUES (4, '4', '4', '4');

---

UPDATE emp SET 
e_name = '6'
, gen = '6'
, addr = '6'
WHERE e_id = 4;

---

DELETE FROM emp
WHERE e_id = 4;

---

[day06.mysql_select]

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    cur = conn.cursor()
    sql = "select * from emp"
    cur.execute(sql)
    
    rows = cur.fetchall()
    print(rows)
    
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = "select * from emp"
    cur.execute(sql)
    
    rows = cur.fetchall()
    print(rows)
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

[day06.mysql_insert]

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = """
        INSERT INTO emp (e_id, e_name, gen, addr) 
             VALUES (%s, %s, %s, %s)
    """
    
    # cur.execute(sql, ("4", "4", "4", "4"))
    cur.execute(sql, ("5", "5", "5", "5"))
    
    conn.commit()
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

[day06.mysql_insert2]

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    e_id = "6"
    e_name = "6"
    gen = "6"
    addr = "6"
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
        INSERT INTO emp (e_id, e_name, gen, addr) 
             VALUES ('{e_id}', '{e_name}', '{gen}', '{addr}')
    """
    
    cur.execute(sql)
    conn.commit()
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    e_id = "9"
    e_name = "9"
    gen = "9"
    addr = "9"
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
        INSERT INTO emp (e_id, e_name, gen, addr) 
             VALUES ('{e_id}', '{e_name}', '{gen}', '{addr}')
    """
    
    # cur.execute(sql)
    
    # print(cur.rowcount, "record inserted.")
    # print(cur.rowcount, "개가 insert 되었습니다.")
    
    cnt = cur.execute(sql)
    print(cnt, "개가 insert 되었습니다.")
    
    conn.commit()
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

[day06.mysql_update]

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    e_id = "8"
    e_name = "100"
    gen = "1"
    addr = "100"
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
        UPDATE emp SET 
            e_name = '{e_name}'
            , gen = '{gen}'
            , addr = '{addr}'
            WHERE e_id = '{e_id}'
    """
    
    cnt = cur.execute(sql)
    print(cnt, "개가 업데이트 되었습니다.")
    
    conn.commit()
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

[day06.mysql_delete]

import pymysql # PyMySQL-1.1.0

def dbconnect():
    conn = pymysql.connect(host='127.0.0.1', port=3305, user='root', password='python', db='python', charset='utf8') # 접속 정보
    return conn

def main():
    conn = dbconnect() # DB 연결
    print("연결 완료")
    
    e_id = "9"
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    sql = f"""
        DELETE FROM emp 
        WHERE e_id = '{e_id}'
    """
    
    cnt = cur.execute(sql)
    print(cnt, "개가 삭제 되었습니다.")
    
    conn.commit()
    
    cur.close()
    conn.close() # 종료
    print("연결 종료")

if __name__ == "__main__" : 
    main()

---

* 웹 프로그래밍

- 최종 종착지는 CRUD

- 게시판을 응용해서 만드는 것. 파일 업로드 구현시 자료실, 이미지를 업로드하면 갤러리 게시판 등등

- WAS는 자바는 아파치를 자바랑 엮기 위해 톰캣을 사용하고, PHP는 APM을 사용

- 대용량 트래픽 발생시는 톰캣을 사용하지 않고 JBoss, Weblogie, JEUS 등을 사용한다.

- WAS => 웹 컨테이너에 해당하는 것이 톰캣, EJB 컨테이너는 따로 하이버네이트로 있음.

- 프로그램을 실행시키는 방식으로 서버 돌림.

(1) 장고

https://namu.wiki/w/Django

Django - 나무위키

(2) 플라스크

https://namu.wiki/w/Flask

Flask - 나무위키

(3) FastApi

https://fastapi.tiangolo.com/ko/

FastAPI

* 장고로 결정!

---

 

[MyHello.java]

package kr.or.aiai.web;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet("/myhello")
public class MyHello extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		out.println("HELLO SERVLET");
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

- http://localhost:8090/HELLO_WEB/myhello

---