본문 바로가기
카테고리 없음

로그인해서 리스트들어가기

keymyo 2026. 3. 16. 18:00 

create table bbs(
	idx int(8) primary key auto_increment
	,subject varchar(100)
	,content longtext
	,user_name varchar(100)
	,b_hit int(8) default 0
	,reg_date date default current_date()
);
desc bbs;

select 
	idx,subject,content,user_name,b_hit,reg_date 
from bbs order by idx desc;

desc bbs;

insert into bbs(subject,content,user_name)
	values('테스트 제목','테스트 내용','테스터');

-- 3번글 보기
select 
	idx,subject,content,user_name,b_hit,reg_date  
from bbs where idx = 3;

-- 3번글 지우기
delete 
	idx,subject,content,user_name,b_hit,reg_date
from bbs where idx =3;

select * from bbs ;

update bbs set b_hit = b_hit+1 where idx =3;

insert into bbs (subject,user_name,content)
values(:subject,:user_name,:content)

-- 11번 글에 대한 수정하는 내용을 쿼리문으로...
update bbs set content=:content, subject=:subject where idx =3;
show databases;

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

id = 'web_user'
pw= 'pass'
host='localhost'
port = 3306
database='mydb'
url=f'mysql+pymysql://{id}:{pw}@{host}:{port}/{database}'

en = create_engine(url,echo=True,pool_size=1)
sess=sessionmaker(bind=en)

def get_conn():
    return sess()

 

import logging
from typing import Dict


import sqlalchemy
from fastapi import FastAPI

from starlette.middleware.sessions import SessionMiddleware
from starlette.requests import Request
from starlette.responses import RedirectResponse
from starlette.staticfiles import StaticFiles
from db import get_conn

app=FastAPI()
app.mount("/view",StaticFiles(directory="view"))
app.add_middleware(SessionMiddleware,secret_key="secret_key",max_age=1800)

logging.basicConfig(level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S',)
logger = logging.getLogger(__name__)

def chk_session(req:Request):
    return req.session.get('login_id','')

@app.get("/")
def main():
    return RedirectResponse(url="/view/login.html")

@app.post("/login")
def login(info:Dict[str,str], req:Request):
    success = 0
    session = req.session
    logger.info(f'login 정보 : {info}')
    conn = None
    sql = sqlalchemy.text("SELECT COUNT(id) AS cnt FROM member WHERE id=:id AND pw=:pw")

    try:
        conn = get_conn()
        result = conn.execute(sql,info).mappings().fetchone()
        logger.info(f'login success : {result}')
        success = result['cnt']
        if success >0:
            req.session["login_id"] = info["id"]
    except Exception as e:
        logger.info(f'login failed : {info}')
    finally:
        if conn is not None:
            conn.close()
    return {"success": success}

@app.get("/list")
def bbs_list(req:Request):
    login_id = chk_session(req)
    list =[]
    conn = None
    sql = sqlalchemy.text("SELECT * FROM bbs ORDER BY idx DESC")

    if login_id !='':
        try:
            conn = get_conn()
            list = conn.execute(sql).mappings().fetchall()
        except Exception as e:
            logger.error(e)
        finally:
            if conn is not None:
                conn.close()

    return {'list':list,'login_id':login_id}

 

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

id = 'web_user'
pw= 'pass'
host='localhost'
port = 3306
database='mydb'
url=f'mysql+pymysql://{id}:{pw}@{host}:{port}/{database}'

en = create_engine(url,echo=True,pool_size=1)
sess=sessionmaker(bind=en)

def get_conn():
    return sess()

 

<html>
    <head>
        <meta charset="UTF-8">
        <title>LOGIN</title>
        <script src="https://code.jquery.com/jquery-4.0.0.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
        <style>
            table,th,td{
                border: 1px solid black;
                border-collapse: collapse;
                padding: 10px;
            }
            #login{
                height: 60px;
            }
        </style>
    </head>
    <body>
        <h1>로그인</h1>
        <hr/>
        <table>
            <tr>
                <th>ID</th>
                <td><input type="text" name="id"/></td>
                <td rowspan="2">
                    <button id="login">로그인</button>
                </td>
            </tr>
            <tr>
                <th>PW</th>
                <td><input type="password" name="pw"/></td>
            </tr>
            <tr>
                <th colspan="3">
                    <button>회원가입</button>
                </th>
            </tr>
        </table>
    </body>
<script>
        $('#login').on('click',async function(){
            let params ={
                id:$('input[name="id"]').val(),
                pw:$('input[name="pw"]').val()
            };
            console.log(params);

            // 반환되는 값의 하위 요소중 data 만 받겠다.
            let {data} = await axios.post('/login',params);
            console.log(data);

            if(data.success > 0){
                location.href='list.html';
            }else{
                alert('아이디 또는 비밀번호를 확인해 주세요');
            }
        });
    </script>
</html>

 

<html>
    <head>
        <meta charset="UTF-8">
        <title>리스트 보기</title>
        <link rel="icon" href="./img01.jpg">
        <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
        <style>
            table,th,td{
                border: 1px solid black;
                border-collapse: collapse;
            }
            th,td{
                padding: 10px;
            }
        </style>
    </head>
    <body>
        <h1>게시글 리스트</h1>
        <div id="loginBox"></div>
        <hr/>
        <table>
            <thead>
                <tr>
                    <th>글번호</th>
                    <th>제목</th>
                    <th>작성자</th>
                    <th>작성일</th>
                    <th>조회수</th>
                    <th>삭제</th>
                </tr>
            </thead>
            <tbody id="list">
                <tr>
                    <th colspan="6">
                        작성된 게시글이 없습니다.
                    </th>
                </tr>
            </tbody>
        </table>
        <button>글쓰기</button>
    </body>
    <script>
        get_list();
        async function get_list(){
            let {data} = await axios('/list');
            console.log(data);

            if(data.login_id == ''){
                alert('로그인이 필요한 서비스 입니다.')
                location.href = 'login.html'
            }else{
                let content = `${data.login_id}님 안녕하세요!<a href="/logout">로그아웃</a>`;
                $(`#loginBox`).html(content);
                let list;
                for(const item of data.list){
                    list +='<tr>';
                    list +=`<td><a href="detail.html?idx=${item.idx}">${item.subject}</a><td>`
                    list +=`<td>${item.user_name}</td>`;
                    list +=`<td>${item.reg_date}</td>`;
                    list +=`<td>${item.b_hit}</td>`;
                    list +=`<td>$<a href="/del?idx=${item.idx}">삭제</a><td>`;
                    list +=`</tr>`;
            }
            $('#list').html(list);

        }
    }
    </script>

 

show tables;
/* 테이블명 :member
*id varchar(50) pk
*pw varchar(100)
*name varchar(50)
*age int(3)
*gender varchar(4)
*email varchar(50) 
 * */

create table member(
	id varchar(50)
	,pw varchar(100)
	,name varchar(50)
	,age int(3)
	,gender varchar(4)
	,email varchar(50)
);
desc member;

-- member 테이블 안에는 'admin' 이라는 아이디가 몇개 있어?
select count(id)as cnt from member where id ='admin';

-- 회원가입 쿼리문
insert into member(id,pw,name,age,gender,email)
	values('','','','','','');

select*from member;
select count(id) from member where id= 'admin' and pw ='1234';

select * from member where id = 'admin';

 

 

티스토리툴바