flask_sqlalchemy

app.py

from database import db_session
from models import employees
from flask import Flask, Response, request
import pandas as pd
import json
import datetime

app = Flask(__name__)

@app.route('/employees/select', methods=['GET'])
def select():
        queryset = employees.query.limit(5)
        print(queryset)
        df = pd.read_sql(queryset.statement, queryset.session.bind)
        print(df)
        return Response(df.to_json(orient="records"), mimetype='application/json')

@app.route('/employees/insert', methods=['POST'])
def insert():
        emp_no = request.args.get('emp_no', default = 1, type = int)
        birth_date = request.args.get('birth_date', default = '9999-01-01')
        first_name = request.args.get('first_name', default='Gil-Dong', type=str)
        last_name = request.args.get('last_name', default='Hong', type=str)
        gender = request.args.get('gender', default='M')
        a = employees(emp_no, birth_date, first_name, last_name, gender)
        db_session.merge(a)
        db_session.commit()
        return 'done\n'

app.run(debug=True)

models.py

from sqlalchemy import Column, Integer, String, DateTime
from database import Base
import datetime

class employees(Base):
        __tablename__ = 'employees'
        emp_no = Column(Integer, primary_key=True)
        birth_date = Column(DateTime)
        first_name = Column(String)
        last_name = Column(String)
        gender = Column(String)
        hire_date = Column(DateTime)

        def __init__(self, emp_no, birth_date, first_name, last_name, gender):
                self.emp_no = emp_no
                self.birth_date = birth_date
                self.first_name = first_name
                self.last_name = last_name
                self.gender = gender
                self.hire_date =  datetime.date.today().strftime("%y-%m-%d")

        def __repr__(self):
                return f'{self.emp_no} : {self.first_name}'

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+mysqlconnector://root:0000@localhost/employees?charset=utf8', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import models
    Base.metadata.create_all(bind=engine)