내일배움캠프 4기_DB특강 13일차 TIL

내일배움캠프 4기_DB특강 13일차 TIL

 

오늘은 sql특강이 길어지면서 4시 20분에 끝났다.

과제를 내주셨는데, 이부분을 공부 중이다.

---

📄 MySQL

USE sparta_test

-- 학생 테이블 생성
CREATE TABLE student(
    id int PRIMARY KEY AUTO_INCREMENT
    ,
    name varchar(50) NOT NULL
    ,
    email varchar(50) UNIQUE
)

-- 도시 테이블 생성
CREATE TABLE city(
    id int PRIMARY KEY AUTO_INCREMENT
    ,
    name varchar(50) NOT NULL
)

-- 학생 데이터 입력
INSERT
    INTO
    student
VALUES(
    NULL
    ,'홍길동'
    , 'hjd@naver.com'
);

INSERT INTO student(name) VALUES ('김민수'),('한동주'),('우태현')

-- 도시 데이터 입력
INSERT INTO city(name) VALUES ('서울'),('도쿄'),('뉴욕'),('상하이'),('파리')

-- 학생테이블 수정(city_id) 칼럼 추가
ALTER TABLE student ADD column city_id int,
ADD CONSTRAINT `fk_city_table_id` FOREIGN KEY student(city_id) REFERENCES city(id);

-- 학생테이블 칼럼(city_id) 삭제
alter table student drop city_id;

-- 학생테이블 외래키(foreign key) 삭제
ALTER TABLE student DROP FOREIGN KEY fk_city_table_id

-- city랑 학생 테이블 조인
SELECT * FROM student s INNER JOIN city c 
ON s.city_id = c.id;

-- 학생 이메일 업데이트
UPDATE
    student
SET
    email = 'kms@gamil.com'
WHERE
    name = '김민수'
    
-- 학생 검색(이메일)
SELECT * FROM student s 
WHERE email IS NULL 

-- 학생 데이터 삭제
DELETE FROM student WHERE id = 1

-- 전체 학생 조회
SELECT * FROM student s

---

📌 Shell로 mysql 접근

 

저번에 환경변수에서 path 설정해준것 같은데, 또 안되서 다시시도~

set PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 8.0\bin

sql 경로 설정

 

📌 test DB 만들기

create database flask_test;

flask_test

flask_test라는 이름의 데이터 베이스 생성!

 

 

📌 python 라이브러리 설치

python interpreter 라이브러리 설치

• SQLAlchemy : 파이썬 코드에서 DB와 연결하기 위해 사용되는 라이브러리
• MySQL-Connector : 파이썬에서 DB를 사용하기 위한 공식 API

 

📄 config.py 생성

db = {
    'user'     : 'root',
    'password' : '[mysql 비밀번호]',
    'host'     : '127.0.0.1',
    'port'     : '3306',
    'database' : 'flask_test'
}

DB_URL = f"mysql+mysqlconnector://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"

config.py는 프로젝트 환경에 맞는 설정을 따로 해주는 파일이다.

주로 .gitignore 파일에 추가해 git repo에 올라가지 않도록 관리한다.

 

📄 DBeaver에서 user테이블 생성하기

USE flask_test;

CREATE TABLE users(
    id int NOT NULL AUTO_INCREMENT,
    email varchar(200),
    password varchar(200),
    PRIMARY key(id)
);

DBeaver users table data

생성한 테이블 확인

Shell명령어로도 한번 더 확인!

 

alter table users drop id;

오류 확인되어 id칼럼 삭제

 

import pymysql

db = pymysql.connect(host='localhost', user='root', password=[mysql 비번], charset='utf8')

cursor = db.cursor(pymysql.cursors.DictCursor)
cursor.execute('USE flask_test')

cursor.execute('insert into users (email, password) values ("xxxxhh@naver.com", "12345678")')

db.commit()
db.close()

db users확인

 

이렇게 하면 잘 되는데, 서버로 받는 걸 하면 작동되지 않는다...ㅠ

 

내가 설정한 비밀번호가 @로 끝나는데, 설마.... @@구분 못하는건가.

mysql+mysqlconnector://root:12345678@@127.0.0.1:3306/flask_test?charset=utf8

혹시 몰라 비밀 번호 변경 후, 재시도!!! 

비밀번호 끝에 @으로 끝나게 설정하면 sqlalchemy라이브러리 사용 db mysql연동 안된다! 이부분을 주의 하자!!

---

flask mysql 연동

 

📄 app.py

from flask import Flask, render_template, request, jsonify
from sqlalchemy import create_engine, text

app = Flask(__name__)

@app.route('/')
def root():
    return render_template('index.html')

@app.route('/users', methods=['POST'])
def users():
    users = request.json
    print(users)
    result = app.database.execute(text("""
                                            INSERT INTO users (
                                            email,
                                            password
                                            ) VALUES (
                                            :email,
                                            :password
                                            )
                                        """), users).lastrowid
    print("result", result)
    return "users insert success", 200


# 서버 실행
if __name__ == '__main__':
    app.config.from_pyfile('config.py')
    database = create_engine(app.config['DB_URL'], encoding='utf-8')
    app.database = database
    app.run()

 

📄 config.py

db = {
    'user': 'root',
    'password': 'mysql비번',
    'host': '127.0.0.1',
    'port': 3306,
    'database': 'flask_test'
}

DB_URL = f"mysql+mysqlconnector://{db['user']}:{db['password']}@{db['host']}:{db['port']}/{db['database']}?charset=utf8"
# print(DB_URL)

 

📌 postman으로 jsonquery 전달

postman post jsonquery

 

📌 DBeaver insert 확인

dbeaver insert 확인

비밀번호 변경해주니 잘 된다..

 

📌 mysql 비밀번호 변경

mysql -u root -p

use mysql;

alter user 'root@localhost' identified with mysql_native_password by 'new password';

flush privileges;