テーブルアクセス

employees.py

# coding:utf-8
from app.util import db_access

"""
SQLコマンドを実行する
"""
__author__ = "t.ebinuma"
__version__ = "1.2"
__date__ = "9 July 2018"


def select(param):
    """
    SELECTを行う
    """
    query = ["SELECT",
             "emp_no,",
             "first_name,",
             "last_name,",
             "gender,",
             "birth_date,",
             "hire_date,",
             "update_dt",
             "FROM employees",
             "WHERE 1=1"]

    if 'emp_no' in param:
        query.append("AND   emp_no = :emp_no")
    if 'first_name' in param:
        query.append("AND   first_name = :first_name")
    if 'last_name' in param:
        query.append("AND   last_name = :last_name")
    if 'gender' in param:
        query.append("AND   gender = :gender")
    if 'birth_date' in param:
        query.append("AND   birth_date = :birth_date")
    if 'hire_date' in param:
        query.append("AND   hire_date = :hire_date")

    return db_access.fetch(query, param)


def insert(param):
    """
    INSERTを行う
    """
    query = ["INSERT INTO",
             "employees(",
             "emp_no,",
             "first_name,",
             "last_name,",
             "gender,",
             "birth_date,",
             "hire_date",
             ")VALUES(",
             ":emp_no,",
             ":first_name,",
             ":last_name,",
             ":gender,",
             ":birth_date,",
             ":hire_date",
             ")"]
    return db_access.execute(query, param)


def update(param):
    """
    UPDATEを行う
    """
    query = ["UPDATE employees SET "]

    if 'first_name' in param:
        query.append("first_name = :first_name, ")
    if 'last_name' in param:
        query.append("last_name = :last_name, ")
    if 'gender' in param:
        query.append("gender = :gender, ")
    if 'birth_date' in param:
        query.append("birth_date = :birth_date, ")
    if 'hire_date' in param:
        query.append("hire_date = :hire_date, ")

    query.append("update_dt = CURRENT_TIMESTAMP")
    query.append("WHERE emp_no = :emp_no")

    return db_access.execute(query, param)

テーブル更新(update)を追加した