DB Library

Python flask

test edit : db 데이터 수정 이후, 실행 결과를 리턴

test list : db의 table 조회 이후, 여러 행을 리스트 형태로 리턴

test detail : db 데이터 조회 결과가 1건인 경우, json 형태로 리턴

from flask import render_template, request, flash, redirect, url_for, Blueprint, Markup, jsonify, session, make_response
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text, update
from sqlalchemy.exc import SQLAlchemyError

@bts_blueprint.route('test/edit', methods=['POST'])
def test_edit():

    print("## test_edit ##")

    try:
        # 처리 변수
        numId = request.form['id']
        strName = request.form['name']

        # 처리 로직
        sql = text('update tblTest set name = :strName where id = :numId')
        connection = db.session.connection()
        db.engine.execute(sql, numId=numId, strName=strName)
                
        return jsonify(message="Edit Success"), 200

    except (RuntimeError, TypeError, NameError, SQLAlchemyError) as e:
        print("#### Edit Failed !! " + str(e))
        db.session.rollback()

        return jsonify(message=str(e)), 1000
        
        
@bts_blueprint.route('test/list', methods=['POST'])
def test_list():

    print("## test_list ##")

    # 처리 변수
    numId = request.form['id']

    # 처리 로직
    sql = text('select a, b, c, d, e from tblTest where id > :numId')
    connection = db.session.connection()
    results = db.engine.execute(sql, numId=numId)
                
    return render_template('test.html', results=list(results))


@bts_blueprint.route('test/detail', methods=['POST'])
def test_detail():

    print("## test_detail ##")

    # 처리 변수
    numId = request.form['id']

    # 처리 로직
    sql = text('select a, b, c from tblTest where id = :numId')
    connection = db.session.connection()
    results = db.engine.execute(sql, numId=numId)

    if results != None:
        results_detail = {}
        for r in results:
            results_detail['a'] = r[0]
            results_detail['b'] = r[1]
            results_detail['c'] = r[2]
            
    return jsonify(results_detail=results_detail)

javascript

funtion test_edit()
{
    // Test edit
    $.ajax({
        url:'/test/edit',
        type:'POST',
        data: {id:id, name:name},
        success: function(data){
            //Success
            showAlert('Edit Successful!!!', '', 2, 2000);
        },
        error: function(xhr, ajaxOptions, thrownError) {
            var err_msg = JSON.parse(xhr.responseText);

            if (thrownError == 'UNKNOWN'){
                fail_msg = err_msg.message + ' (' + xhr.status + ')'
            } else {
                fail_msg = err_msg.message + ' (' + thrownError + ',' + xhr.status + ')';
            }
            showAlert('Edit Fail...','', 4, 3000);
        }
    });
}


funtion test_list()
{
    // Test list
    $.ajax({
        url:'/test/list',
        type:'POST',
        data: {id:id},
        success: function(data){
            //Success
            for(var key in data){
                console.log(key, data[key]);
            }
            
            showAlert('detail Successful!!!', '', 2, 2000);
        },
        error: function(xhr, ajaxOptions, thrownError) {
            var err_msg = JSON.parse(xhr.responseText);

            if (thrownError == 'UNKNOWN'){
                fail_msg = err_msg.message + ' (' + xhr.status + ')'
            } else {
                fail_msg = err_msg.message + ' (' + thrownError + ',' + xhr.status + ')';
            }
            showAlert('list Fail...','', 4, 3000);
        }
    });
}


funtion test_detail()
{
    // Test detail
    $.ajax({
        url:'/test/detail',
        type:'POST',
        data: {id:id},
        success: function(data){
            //Success
            detail_a = data.results_detail.a
            detail_b = data.results_detail.b
            detail_c = data.results_detail.c
            
            showAlert('detail Successful!!!', '', 2, 2000);
        },
        error: function(xhr, ajaxOptions, thrownError) {
            var err_msg = JSON.parse(xhr.responseText);

            if (thrownError == 'UNKNOWN'){
                fail_msg = err_msg.message + ' (' + xhr.status + ')'
            } else {
                fail_msg = err_msg.message + ' (' + thrownError + ',' + xhr.status + ')';
            }
            showAlert('detail Fail...','', 4, 3000);
        }
    });
}

올해 01월 01일, 올해 12월 마지막일

//현재 시간
> var Nowdate = new Date()
> Nowdate 
Tue Jul 06 2021 23:36:20 GMT+0900 (한국 표준시)
//현재 년도
> FullYear = Nowdate.getFullYear()
2021
//현재 달
> Tmp_Month = Nowdate.getMonth()+1
7
> CurrentMonth = Tmp_Month >= 10 ? Tmp_Month : '0' + Tmp_Month
"07"
//마지막 달의 마지막 일
> (new Date(FullYear, 12, 0))
Fri Dec 31 2021 00:00:00 GMT+0900 (한국 표준시)
> (new Date(FullYear, 12, 0)).getDate()
31
// 올해 01월 01일
> FullYear + '-01-01 00:00:00'
"2021-01-01 00:00:00"
// 올해 12월 마지막일
> FullYear + '-12-' + LastDay + ' 23:59:59'
"2021-12-31 23:59:59"

일주일전

> var Nowdate = new Date()
> Nowdate
Tue Jul 06 2021 23:55:01 GMT+0900 (한국 표준시)
> var Agodate = new Date(Nowdate.getTime() - (7*24*60*60*1000))
//일주일전
> Agodate 
Tue Jun 29 2021 23:55:01 GMT+0900 (한국 표준시)

//현재 달
> var NowMonth = ((Nowdate.getMonth()+1) >= 10 ? (Nowdate.getMonth()+1) : '0' + (Nowdate.getMonth()+1))
"07"
//현재 일
> var NowDay = (Nowdate.getDate() >= 10 ? Nowdate.getDate() : '0' + Nowdate.getDate())
"06"
// 일주일전 달
> var AgoMonth = ((Agodate.getMonth()+1) >= 10 ? (Agodate.getMonth()+1) : '0' + (Agodate.getMonth()+1))
"06"
// 일주일전 일
> var AgoDay = (Agodate.getDate() >= 10 ? Agodate.getDate() : '0' + Agodate.getDate())
29

//현재 날짜
> Nowdate.getFullYear() + '-' + NowMonth + '-' + NowDay + ' 00:00:00'
"2021-07-06 00:00:00"

//일주일전 날짜
> Agodate.getFullYear() + '-' + AgoMonth + '-' + AgoDay + ' 00:00:00'
"2021-06-29 00:00:00"

1일 추가, 1시간 추가

//1일 추가
> tmpdate = new Date(2021,07,07)
Sat Aug 07 2021 00:00:00 GMT+0900 (한국 표준시)
> tmpdate.setTime(tmpdate.getTime() + (24*60*60*1000))
1628348400000
> new Date(tmpdate)
Sun Aug 08 2021 00:00:00 GMT+0900 (한국 표준시)

//1시간 추가
> tmpdate = new Date(2021,07,07)
Sat Aug 07 2021 00:00:00 GMT+0900 (한국 표준시)
> tmpdate.setTime(tmpdate.getTime() + (1*60*60*1000))
1628265600000
> new Date(tmpdate)
Sat Aug 07 2021 01:00:00 GMT+0900 (한국 표준시)

포멧 변경

function sDateToDate(strDate)
{
    //Ex) '04/26/2021 4:37 PM'
    var yyyyMMDD = String(strDate)
    var sYear = yyyyMMDD.substring(6,10);
    var sMonth = yyyyMMDD.substring(0,2);
    var sDate = yyyyMMDD.substring(3,5);
    var sTime = yyyyMMDD.substring(10,19);

    //Ex) '2021-04-26 4:37 PM'
    return sYear + '-' + sMonth + '-' + sDate + sTime
}