def create_brnd(brnd_nm, like_cnt):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBBRND(brnd_nm, like_cnt)
VALUES (%s, %s)
"""
hairprd = cursor.execute(query, (brnd_nm, like_cnt))
if cursor.rowcount == 1:
retObj = {
"brnd_id": cursor.lastrowid,
"brnd_nm": brnd_nm,
"like_cnt": 0,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_list(keyword, limit, offset, ordering):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT brnd_id, brnd_nm, like_cnt
FROM TBBRND
WHERE brnd_nm like %s
"""
query = query + 'ORDER BY ' + ordering + ' DESC \n'
if limit is not None and offset is not None:
query = query + 'LIMIT ' + offset + ', ' + limit
try:
cursor.execute(query, ('%' + keyword + '%', ))
result = cursor.fetchall()
retObjList = []
for item in result:
brnd_id, brnd_nm, like_cnt = item
retObj = {
"brnd_id": cursor.lastrowid,
"brnd_nm": brnd_nm,
"like_cnt": like_cnt,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def create_magazine(title, contents, comment_cnt, like_cnt):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBMAGAZINE(title, contents, comment_cnt, like_cnt, reg_date, mod_date)
VALUES (%s, %s, %s, %s, now(), now())
"""
magazine = cursor.execute(query, (title, contents, comment_cnt, like_cnt))
if cursor.rowcount == 1:
retObj = {
"magazine_id": cursor.lastrowid,
"title": title,
"contents": contents,
"comment_cnt": 0,
"like_cnt": 0,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def create_bookmark(category, owner_id, magazine_id=None, hairprd_id=None,
med_id=None, hairshop_id=None, clinic_id=None):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBBOOKMARK(category, owner_id, magazine_id, hairprd_id,
med_id, hairshop_id, clinic_id)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
bookmark = cursor.execute(query, (category, owner_id, magazine_id, hairprd_id, med_id, hairshop_id, clinic_id))
if cursor.rowcount == 1:
retObj = {
"bookmark_id": cursor.lastrowid,
"category": category,
"owner_id": owner_id,
"magazine_id": magazine_id,
"hairprd_id": hairprd_id,
"med_id": med_id,
"hairshop_id": hairshop_id,
"clinic_id": clinic_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_detail(magazine_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT magazine_id, title, contents, comment_cnt, like_cnt, reg_date, mod_date
FROM TBMAGAZINE
WHERE magazine_id = %s
"""
try:
cursor.execute(query, (magazine_id,))
result = cursor.fetchall()
retObjList = []
for item in result:
magazine_id, title, price, contents, comment_cnt, like_cnt, reg_date, mod_date = item
retObj = {
"magazine_id": magazine_id,
"title": title,
"price": price,
"contents": contents,
"comment_cnt": comment_cnt,
"like_cnt": like_cnt,
"reg_date": reg_date,
"mod_date": mod_date,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def create_clinic(clinic_nm, branch, region, address, like_cnt, phone_num, mobile_num, kakao_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBCLINIC(clinic_nm, branch, region, address, like_cnt, phone_num, mobile_num, kakao_id, reg_date)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now())
"""
clinic = cursor.execute(query, (clinic_nm, branch, region, address, like_cnt, phone_num, mobile_num, kakao_id))
if cursor.rowcount == 1:
retObj = {
"clinic_id": cursor.lastrowid,
"clinic_nm": clinic_nm,
"branch": branch,
"region": "region",
"address": "address",
"like_cnt": 0,
"phone_num": phone_num,
"mobile_num": mobile_num,
"kakao_id": kakao_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_list(keyword):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT comp_id, comp_hg_nm, comp_eng_nm, grade
FROM TBCOMP
WHERE comp_hg_nm like %s
OR comp_eng_nm like %s
"""
cursor.execute(query, ('%'+keyword+'%',))
result = cursor.fetchall()
retObjList = []
try:
for item in result:
comp_id, comp_hg_nm, comp_eng_nm, grade = item
retObj = {
"comp_id": cursor.lastrowid,
"comp_hg_nm": comp_hg_nm,
"comp_eng_nm": comp_eng_nm,
"grade": grade,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def create_hairshop(hairshop_nm, region, address, like_cnt, phone_num,
mobile_num, kakao_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBHAIRSHOP(hairshop_nm, region, address, like_cnt, phone_num, mobile_num, kakao_id, reg_date)
VALUES (%s, %s, %s, %s, %s, %s, %s, now())
"""
hairshop = cursor.execute(query,
(hairshop_nm, region, address, like_cnt,
phone_num, mobile_num, kakao_id))
if cursor.rowcount == 1:
retObj = {
"hairshop_id": cursor.lastrowid,
"hairshop_nm": hairshop_nm,
"region": region,
"address": address,
"like_cnt": 0,
"phone_num": phone_num,
"mobile_num": mobile_num,
"kakao_id": kakao_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_check(owner_id, hairprd_id, med_id, hairshop_id, clinic_id, magazine_id, brnd_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBLIKE
WHERE owner_id = %s
AND hairprd_id = %s
AND med_id = %s
AND hairshop_id = %s
AND clinic_id = %s
AND magazine_id = %s
AND brnd_id = %s
"""
try:
cursor.execute(query, (owner_id, hairprd_id, med_id, hairshop_id, clinic_id, magazine_id, brnd_id))
result = cursor.fetchall()
count = 0
for item in result:
count = item
except Exception as e:
return False, None
finally:
conn.close()
return True, count
def fetch_list_cnt(keyword):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBMAGAZINE
WHERE title like %s
OR contents like %s
"""
try:
cursor.execute(query, ('%' + keyword + '%', '%' + keyword + '%'))
result = cursor.fetchall()
retObjList = []
count = 0
for item in result:
count = item
except Exception as e:
return False, None
finally:
conn.close()
return count
def create_hairprd(hairprd_nm, price, capacity, like_cnt, brnd_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBHAIRPRD(hairprd_nm, price, capacity, like_cnt, reg_date, brnd_id)
VALUES (%s, %s, %s, %s, now(), %s)
"""
hairprd = cursor.execute(
query, (hairprd_nm, price, capacity, like_cnt, brnd_id))
if cursor.rowcount == 1:
retObj = {
"hairprd_id": cursor.lastrowid,
"hairprd_nm": hairprd_nm,
"price": price,
"capacity": capacity,
"like_cnt": 0,
"brnd_id": brnd_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def create_magazine_comment(contents, magazine_id, owner_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBMAGAZINE_COMMENT(contents, reg_date, magazine_id, owner_id)
VALUES (%s, now(), %s, %s)
"""
magazine_comment = cursor.execute(query,
(contents, magazine_id, owner_id))
if cursor.rowcount == 1:
retObj = {
"comment_id": cursor.lastrowid,
"contents": contents,
"magazine_id": magazine_id,
"owner_id": owner_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_list_cnt(detail_category, keyword):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBMEDICINE a
LEFT JOIN TBBRND AS b
ON a.brnd_making_id = b.brnd_id
LEFT JOIN TBBRND AS c
ON a.brnd_sales_id = c.brnd_id
WHERE a.category = 'S'
AND a.detail_category = '%s'
AND a.med_nm like %s
"""
try:
cursor.execute(query, (detail_category, '%' + keyword + '%'))
result = cursor.fetchall()
count = 0
for item in result:
count = item
except Exception as e:
return False, None
finally:
conn.close()
return count
def fetch_list_cnt(keyword):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBHAIRPRD a, TBBRND b
WHERE a.hairprd_nm like %s
AND a.brnd_id = b.brnd_id
"""
try:
cursor.execute(query, ('%' + keyword + '%', ))
result = cursor.fetchall()
retObjList = []
count = 0
for item in result:
count = item
except Exception as e:
return False, None
finally:
conn.close()
return count
def fetch_list(contents):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT comment_id, contents, reg_date, magazine_id, owner_id
FROM TBMAGAZINE_COMMENT
WHERE contents like %s
"""
cursor.execute(query, ('%' + contents + '%'))
result = cursor.fetchall()
retObjList = []
try:
for item in result:
comment_id, contents, reg_date, magazine_id, owner_id = item
retObj = {
"comment_id": cursor.lastrowid,
"contents": contents,
"reg_date": reg_date,
"magazine_id": magazine_id,
"owner_id": owner_id,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def fetch_list(comp_id, hairprd_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT complist_id, category, comp_id, hairprd_id, med_id
FROM TBCOMP_LIST
WHERE category = 1
AND comp_id = %s
AND hairprd_id = %s
"""
cursor.execute(query, (comp_id, hairprd_id))
result = cursor.fetchall()
retObjList = []
try:
for item in result:
complist_id, category, comp_id, hairprd_id, med_id = item
retObj = {
"complist_id": complist_id,
"category": category,
"comp_id": comp_id,
"hairprd_id": hairprd_id,
"med_id": med_id,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def create_complist(category, comp_id, hairprd_id, med_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBCOMP_LIST(category, comp_id, hairprd_id, med_id)
VALUES (%s, %s, %s, %s)
"""
comp = cursor.execute(query, (category, comp_id, hairprd_id, med_id))
if cursor.rowcount == 1:
retObj = {
"complist_id": cursor.lastrowid,
"category": category,
"comp_id": comp_id,
"hairprd_id": hairprd_id,
"med_id": med_id,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def create_comp(comp_hg_nm, comp_eng_nm, grade):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
INSERT
INTO TBCOMP(comp_hg_nm, comp_eng_nm, grade)
VALUES (%s, %s, %s)
"""
comp = cursor.execute(query, (comp_hg_nm, comp_eng_nm, grade))
if cursor.rowcount == 1:
retObj = {
"comp_id": cursor.lastrowid,
"comp_hg_nm": comp_hg_nm,
"comp_eng_nm": comp_eng_nm,
"grade": grade,
}
except Exception as e:
print(e, file=sys.stdout)
return False, None
finally:
conn.close()
return True, retObj
def fetch_list_cnt(keyword):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBHAIRSHOP
WHERE hairshop_nm like %s
"""
try:
cursor.execute(query, ('%' + keyword + '%', ))
result = cursor.fetchall()
retObjList = []
count = 0
for item in result:
count = item
retObj = {
"count": count,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def fetch_list_summary(category, hairprd_id, med_id, hairshop_id, clinic_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT round(avg(score), 1) as average, count(*) as cnt
FROM TBREVIEW
"""
if category is '1':
if hairprd_id is not None:
query += "where category = 1 \n"
query += " and hairprd_id = %s \n"
query += "group by category, hairprd_id"
else:
return False, None
elif category is '2':
if med_id is not None:
query += "where category = 2 \n"
query += " and med_id = %s \n"
query += "group by category, med_id"
else:
return False, None
elif category is '3':
if hairshop_id is not None:
query += "where category = 3 \n"
query += " and hairshop_id = %s \n"
query += "group by category, hairshop_id"
else:
return False, None
elif category is '4':
if clinic_id is not None:
query += "where category = 4 \n"
query += " and clinic_id = %s \n"
query += "group by category, clinic_id"
else:
return False, None
else:
return False, None
try:
cursor.execute(query)
result = cursor.fetchall()
retObjList = []
for item in result:
average, cnt = item
retObj = {
"average": average,
"count": cnt,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def fetch_list_cnt(owner_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*)
FROM TBLIKE a
LEFT JOIN TBUSER AS b
ON a.owner_id = b.user_id
LEFT JOIN TBHAIRPRD AS c
ON a.hairprd_id = (CASE WHEN a.category = '1'
THEN c.hairprd_id
ELSE null
END)
LEFT JOIN TBMEDICINE AS d
ON a.med_id = (CASE WHEN a.category = '2'
THEN d.med_id
ELSE null
END)
LEFT JOIN TBHAIRSHOP AS e
ON a.hairshop_id = (CASE WHEN a.category = '3'
THEN e.hairshop_id
ELSE null
END)
LEFT JOIN TBCLINIC AS f
ON a.clinic_id = (CASE WHEN a.category = '4'
THEN f.clinic_id
ELSE null
END)
LEFT JOIN TBMAGAZINE AS g
ON a.magazine_id = (CASE WHEN a.category = '5'
THEN g.magazine_id
ELSE null
END)
LEFT JOIN TBBRND AS h
ON a.brnd_id = (CASE WHEN a.category = '6'
THEN h.brnd_id
ELSE null
END)
WHERE a.owner_id = %s
"""
try:
cursor.execute(query, (owner_id, ))
result = cursor.fetchall()
retObjList = []
count = 0
for item in result:
count = item
except Exception as e:
return False, None
finally:
conn.close()
return count
def fetch_list(detail_category, keyword, limit, offset, ordering, asc):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT a.med_id, a.med_nm, a.category, a.detail_category, a.like_cnt, a.insur_yn, a.effect,
a.usg_cap, a.forbid, a.careful_med, a.side_effect, a.reg_date,
b.brnd_nm as brnd_making_nm, c.brnd_nm as brnd_sales_nm
FROM TBMEDICINE a
LEFT JOIN TBBRND AS b
ON a.brnd_making_id = b.brnd_id
LEFT JOIN TBBRND AS c
ON a.brnd_sales_id = c.brnd_id
WHERE a.category = 'S'
AND a.detail_category = '%s'
AND a.med_nm like %s
"""
query = query + 'ORDER BY ' + ordering + ' ' + asc + ' \n'
if limit is not None and offset is not None:
query = query + 'LIMIT ' + offset + ', ' + limit
try:
cursor.execute(query, (detail_category, '%' + keyword + '%'))
result = cursor.fetchall()
retObjList = []
for item in result:
med_id, med_nm, category, detail_category, like_cnt, insur_yn, effect, \
usg_cap, forbid, careful_med, side_effect, reg_date, brnd_making_nm, brnd_sales_nm = item
retObj = {
"med_id": med_id,
"med_nm": med_nm,
"category": category,
"detail_category": detail_category,
"like_cnt": like_cnt,
"insur_yn": insur_yn,
"effect": effect,
"usg_cap": usg_cap,
"forbid": forbid,
"careful_med": careful_med,
"side_effect": side_effect,
"reg_date": reg_date,
"brnd_making_nm": brnd_making_nm,
"brnd_sales_nm": brnd_sales_nm,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def create_user(check_id, is_staff, username):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
INSERT
INTO TBUSER(check_id, last_login, is_staff, username, is_active, date_joined)
VALUES (%s, now(), %s, %s, 1, now())
"""
cursor.execute(query, (check_id, is_staff, username))
return True
def fetch_user(check_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT username
FROM TBUSER
WHERE check_id = %s
"""
cursor.execute(query, (check_id, ))
result = cursor.fetchone()
return result
def fetch_list(limit, offset, ordering, asc, nickname=None):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT user_id, check_id, nickname, birth, app_type, push_tkn, push_yn, device,
gender, location, is_staff, last_login, is_active, date_joined
FROM TBUSER
"""
try:
if nickname is not None:
query = query + "where nickname = %s"
cursor.execute(query, (nickname, ))
else:
query = query + 'ORDER BY ' + ordering + ' ' + asc + ' \n'
if limit is not None and offset is not None:
query = query + 'LIMIT ' + offset + ', ' + limit
cursor.execute(query)
result = cursor.fetchall()
retObjList = []
for item in result:
user_id, check_id, nickname, birth, app_type, push_tkn, push_yn, device, \
gender, location, is_staff, last_login, is_active, date_joined = item
retObj = {
"user_id": user_id,
"check_id": check_id,
"nickname": nickname,
"birth": birth,
"app_type": app_type,
"push_tkn": push_tkn,
"push_yn": push_yn,
"device": device,
"gender": gender,
"location": location,
"is_staff": is_staff,
"last_login": last_login,
"is_active": is_active,
"date_joined": date_joined
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
print(retObjList, file=sys.stdout)
return True, retObjList
def fetch_detail(med_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT a.med_id, a.med_nm, a.category, a.detail_category, a.like_cnt, a.insur_yn, a.effect,
a.usg_cap, a.forbid, a.careful_med, a.side_effect, a.reg_date,
b.brnd_nm as brnd_making_nm, c.brnd_nm as brnd_sales_nm
FROM TBMEDICINE a
LEFT JOIN TBBRND AS b
ON a.brnd_making_id = b.brnd_id
LEFT JOIN TBBRND AS c
ON a.brnd_sales_id = c.brnd_id
WHERE a.category = 'S'
AND a.med_id = %s
"""
try:
cursor.execute(query, (med_id,))
result = cursor.fetchall()
retObjList = []
for item in result:
med_id, med_nm, category, detail_category, like_cnt, insur_yn, effect, \
usg_cap, forbid, careful_med, side_effect, reg_date, brnd_making_nm, brnd_sales_nm = item
retObj = {
"med_id": med_id,
"med_nm": med_nm,
"category": category,
"detail_category": detail_category,
"like_cnt": like_cnt,
"insur_yn": insur_yn,
"effect": effect,
"usg_cap": usg_cap,
"forbid": forbid,
"careful_med": careful_med,
"side_effect": side_effect,
"reg_date": reg_date,
"brnd_making_nm": brnd_making_nm,
"brnd_sales_nm": brnd_sales_nm,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def fetch_list_summary(hairprd_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT count(*), round(avg(a.score), 1) as score
FROM TBREVIEW a
LEFT JOIN TBUSER AS b
ON a.owner_id = b.user_id
LEFT JOIN TBHAIRPRD AS c
ON a.hairprd_id = (CASE WHEN a.category = '1'
THEN c.hairprd_id
ELSE null
END)
LEFT JOIN TBMEDICINE AS d
ON a.med_id = (CASE WHEN a.category = '2'
THEN d.med_id
ELSE null
END)
LEFT JOIN TBHAIRSHOP AS e
ON a.hairshop_id = (CASE WHEN a.category = '3'
THEN e.hairshop_id
ELSE null
END)
LEFT JOIN TBCLINIC AS f
ON a.clinic_id = (CASE WHEN a.category = '4'
THEN f.clinic_id
ELSE null
END)
WHERE a.category = '1'
AND a.hairprd_id = %s
"""
try:
cursor.execute(query, (hairprd_id,))
result = cursor.fetchall()
count = 0
score = 0.0
for item in result:
count, score = item
except Exception as e:
return False, None
finally:
conn.close()
return count, score
def fetch_list(keyword, limit, offset, ordering, asc, region=None):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT hairshop_id, hairshop_nm, region, address, like_cnt, phone_num, mobile_num, kakao_id, reg_date
FROM TBHAIRSHOP
WHERE hairshop_nm like %s
"""
if region is not None:
query += '\n AND region = %s \n'
query = query + 'ORDER BY ' + ordering + ' ' + asc + ' \n'
if limit is not None and offset is not None:
query = query + 'LIMIT ' + offset + ', ' + limit
try:
if region is None:
cursor.execute(query, ('%' + keyword + '%', ))
else:
cursor.execute(query, ('%' + keyword + '%', region))
result = cursor.fetchall()
retObjList = []
for item in result:
hairshop_id, hairshop_nm, region, address, like_cnt, phone_num, mobile_num, kakao_id, reg_date = item
retObj = {
"hairshop_id": hairshop_id,
"hairshop_nm": hairshop_nm,
"region": region,
"address": address,
"like_cnt": like_cnt,
"phone_num": phone_num,
"mobile_num": mobile_num,
"kakao_id": kakao_id,
"reg_date": reg_date,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def fetch_list(comp_id, med_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
query = """
SELECT a.complist_id, a.category, b.comp_id, b.comp_hg_nm, b.comp_eng_nm, c.hairprd_id, c.hairprd_nm, d.med_id, d.med_nm
FROM TBCOMP_LIST a
LEFT JOIN TBCOMP as b
ON a.comp_id = b.comp_id
LEFT JOIN TBHAIRPRD as c
ON a.hairprd_id = (CASE WHEN a.category = '1'
THEN c.hairprd_id
ELSE null
END)
LEFT JOIN TBMEDICINE d
ON a.med_id = (CASE WHEN a.category = '2'
THEN d.med_id
ELSE null
END)
WHERE a.category = 2
AND a.comp_id = %s
AND a.med_id = %s
"""
cursor.execute(query, (comp_id, med_id))
result = cursor.fetchall()
retObjList = []
try:
for item in result:
complist_id, category, comp_id, hairprd_id, med_id = item
retObj = {
"complist_id": complist_id,
"category": category,
"comp_id": comp_id,
"hairprd_id": hairprd_id,
"med_id": med_id,
}
retObjList.append(retObj)
except Exception as e:
return False, None
finally:
conn.close()
return True, retObjList
def update_user(check_id, nickname, birth, app_type, push_tkn, push_yn, device,
gender, location, is_staff, user_id):
conn = cnx_pool.get_connection()
cursor = conn.cursor()
retObj = {}
try:
query = """
UPDATE TBUSER
SET check_id = %s,
nickname = %s,
birth = %s,
app_type = %s,
push_tkn = %s,
push_yn = %s,
device = %s,
gender = %s,
location = %s,
is_staff = %s
WHERE user_id = %s
"""
user = cursor.execute(
query, (check_id, nickname, birth, app_type, push_tkn, push_yn,
device, gender, location, is_staff, user_id))
if cursor.rowcount == 1:
print(cursor.rowcount, file=sys.stderr)
retObj = {
"user_id": cursor.lastrowid,
"check_id": check_id,
"nickname": nickname,
"birth": birth,
"app_type": app_type,
"push_tkn": push_tkn,
"push_yn": push_yn,
"device": device,
"gender": gender,
"location": location,
"is_staff": is_staff,
}
except Exception as e:
return False, None
finally:
conn.close()
return True, retObj
