Flask Flask-SQLAlchemy

db init_app config

class DevConfig(Config):
    # Dev Config
    ENV = 'dev'
    DEBUG = True
    TESTING = True

    # Dev Database
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SQLALCHEMY_ECHO = False
    SQLALCHEMY_RECORD_QUERIES = True

    SQLALCHEMY_DATABASE_URI = 'mysql://아이디:비밀번호@192.168.1.222:3306/test'

    # 데이터베이스 Binds
    SQLALCHEMY_BINDS = {
        'test'    : 'mysql://아이디:비밀번호@아이피:포트/test',
        'EXPORT'  : 'mysql://아이디:비밀번호@아이피:포트/EXPORT',
        'WH'      : 'mysql://아이디:비밀번호@아이피:포트/WH',
        'WH_SLAVE': 'mysql://아이디:비밀번호@아이피:포트/WH'
    }

 

모델 클래스

"""
   Database Bind : Below Values Must Be Check
   __bind_key__  : Database Name Has Bound
   __tablename__ : Table Name Will Use
   
   API 문서 주소
   https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#models
"""


class Users(db.Model):
    __bind_key__ = 'test'
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key = True, nullable = False, autoincrement = True)
    name = db.Column(db.String(10), unique = True, nullable = False)
    regDate = db.Column(db.DateTime, nullable = False)

    # def __init__(self, name: str, regDate: str):
    #     self.name = name
    #     self.regDate = regDate
    #
    def __repr__(self):
        return '<test.users Model %r>' % self.name

 

쿼리 호출 문법...

from datetime import datetime

from flask import Blueprint, request

usertest_blueprint = Blueprint('usertest_blueprint', __name__)


@usertest_blueprint.route('/user/insert/<string:name>')
def user_insert(name):
    from sanggi.system.models import Users

    users = Users(name, datetime.today().strftime('%Y-%m-%d %H:%M:%S'))
    Users.add(users)
    Users.commit()

    return 'Insert Test'


@usertest_blueprint.route('/user/select', methods = ['GET', ])
def user_select():
    from sanggi.system.models import Users

    users_all = Users.query.all()

    # from flask_sqlalchemy import get_debug_queries
    # print(get_debug_queries())

    for item in users_all:
        print(item.name, ' | ', item.regDate)

    return 'Select Test'


@usertest_blueprint.route('/user/who', methods = ['GET'])
def user_who():
    from sanggi.system.models import Users

    name = request.args.get(key = 'name', default = 'Unknown', type = str)

    whois = Users.query.filter(name == name).first()

    msg = 'Name {name} | RegDate {regDate}'
    return msg.format(name = whois.regDate, regDate = whois.regDate)


"""
first 메소드는 결과가 없을 시에 None 을 뱉고, 있다면 충족하는 것들중 최상위 한개를 뱉음
one 메소드는 결과가 없을시 NoResultFound 예외를 뱉고, 있다면 MultipleResultFound 예외를 뱉음
상황에 맞게 유용하게 사용 가능할 것 같당.
"""

"""
filter 메소드 파라미터 설정에 따른 sql

equals:
query.filter(User.name == 'ed')

not equals:
query.filter(User.name != 'ed')

LIKE:
query.filter(User.name.like('%ed%'))

IN:
query.filter(User.name.in_(['ed', 'wendy', 'jack']))

# works with query objects too:
query.filter(User.name.in_(
    session.query(User.name).filter(User.name.like('%ed%'))
))

# use tuple_() for composite (multi-column) queries
from sqlalchemy import tuple_
query.filter(
    tuple_(User.name, User.nickname).\
    in_([('ed', 'edsnickname'), ('wendy', 'windy')])
)

NOT IN:
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

IS NULL:
query.filter(User.name == None)

# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))

IS NOT NULL:
query.filter(User.name != None)

# alternatively, if pep8/linters are a concern
query.filter(User.name.isnot(None))

AND:
# use and_()
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))

# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')

# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

OR:
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))

MATCH:
query.filter(User.name.match('wendy'))
"""