def dbchangepwd():
pwd = request.json
loginuser = session.get('loginUser')
userinfo = Users.query.filter(Users.user_no == loginuser['userid']).first()
print(">>>>>>>>>>>>>>", pwd['pwd'])
u = Users(userinfo.email, pwd['pwd'], userinfo.name, userinfo.phone_number,
userinfo.nickname, userinfo.address)
u.user_no = loginuser["userid"]
# u.email = userinfo.email
# u.password = pwd
# u.name = userinfo.name
# u.phone_number = userinfo.phone_number
# u.nickname = userinfo.nickname
# u.address = userinfo.address
print(u)
try:
db_session.merge(u)
db_session.commit()
except Exception as err:
print(err)
db_session.rollback()
if "Duplicate" in str(err):
data = {}
data['error'] = 'error'
return jsonify(data)
return jsonify("OK")
def sign_up_post():
email = request.form.get('email')
name = request.form.get('name')
password = request.form.get('password')
password2 = request.form.get('password2')
print('email=', email, 'name=', name, 'passwd=', password, 'pw2=',
password2)
if password != password2:
flash("암호를 정확히 입력하세요!!")
return render_template("sign_up3.html", email=email, name=name)
else:
token = s.dumps(email, salt='email_confirm')
print('token>>>', token)
link = url_for('confirm_email', token=token, _external=True)
print('link>>>>', link)
send_email(to=email, subject='hey', msg=link)
print('mail sent')
p = Patient(name, email, password, True)
print(p)
try:
db_session.add(p)
db_session.commit()
except:
db_session.rollback()
flash("%s 님, 메일을 보냈으니 확인 해주세요." % name)
return redirect("/login")
def signup_modal():
form_data = request.json
email = form_data['email']
password = form_data['password']
cfpassword = form_data['cfpassword']
name = form_data['name']
phone_number = form_data['phone']
nickname = form_data['nickname']
address = form_data['address']
u = Users(email, password, name, phone_number, nickname, address)
print(u)
try:
db_session.add(u)
db_session.commit()
except Exception as err:
print(err)
db_session.rollback()
if "Duplicate" in str(err):
data = {}
data['error'] = 'error'
data['email'] = u.email
print(data)
return jsonify(data)
print(u.email)
return jsonify(u.email)
def delmemo(userid, id):
dmemo = Memo.query.filter('user_id =:userid and id=:id').params(userid=userid, id=id).first()
try:
db_session.delete(dmemo)
db_session.commit()
print("-----------SUCCESSFULLY DELETED MEMO---------------")
return ''
except Exception as err:
db_session.rollback()
print("Failed to delete the memo", err)
return ''
def ttt_delete(myalbumid):
try:
# db_session.query(Ttt).
Ttt.query.filter(Ttt.id == myalbumid).delete()
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK'})
def mycoms_delete(myalbumid):
print("DDDDDDDDDDDDDDDDD>>>", request.form.get('mycomid'))
try:
Mycom.query.filter(Mycom.id == request.form.get('mycomid')).delete()
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK'})
def delpost(userid, postid):
dpost = Post.query.filter('postid=:postid and user_id = :userid').params(postid=postid, userid=userid).first()
try:
db_session.delete(dpost)
db_session.commit()
print("-------------------------------------------------Delete success")
return ''
except Exception as err:
db_session.rollback()
print("\n\n\n--------------------------Fail Delete", err, "\n\n\n\n")
return ''
def myalbum_post():
songno = request.form.get('songno')
myalbum = Myalbum(session.get('loginUser').get('userid'), songno)
try:
db_session.add(myalbum)
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK'})
def ttt_post(myalbumid):
content = request.form.get('content')
mycom = Ttt(myalbumid, session.get('loginUser').get('userid'), content)
try:
db_session.add(mycom)
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK', 'mycom': mycom.json()})
def delchecklist(userid, ids):
print("delete view")
dchecklist = Checklist.query.filter('user_id=:userid and id=:id').params(userid=userid,id=ids).first()
print("checklist to delete >>>>>>>>> ", dchecklist)
try:
db_session.delete(dchecklist)
db_session.commit()
print("-------------------------------------------------Delete success")
return jsonify({'status':'success'})
except Exception as err:
db_session.rollback()
print("\n\n\n--------------------------Fail Delete", err, "\n\n\n\n")
return ''
def del_board(boardid):
print(request.form.get('boardid'))
try:
Board.query.filter(
Board.board_id == request.form.get('boardid')).delete()
BoardInstrument.query.filter(BoardInstrument.id > 0).filter(
BoardInstrument.board_id == request.form.get('boardid')).delete()
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK'})
def sql2():
try:
ret = db_session.query(SongInfo).options(joinedload(
SongInfo.album)).filter(SongInfo.likecnt < 10000)
except SQLAlchemyError as sqlerr:
db_session.rollback()
print("SqlError>>", sqlerr)
except:
print("Error!!")
finally:
db_session.close()
return render_template('main.html', ret=ret)
def save(userid):
j={}
savelst = request.json
print("sssssssssssssssssssssssssssssssssssssssssss> ", savelst['checklist'], len(savelst['checklist']))
savelst['checklist'] = ",".join(savelst['checklist'])
c = Checklist(userid, savelst['name'], savelst['checklist'])
try:
db_session.add(c)
db_session.commit()
print("checklist data inserted")
j['status'] = 'success'
except Exception as err:
db_session.rollback()
print("Error?>>>>>>>", err)
j['status'] = 'fail'
return jsonify(j)
def new_post():
form = PostForm()
if session.get('loginUser'):
loginUser = session.get('loginUser')
else:
session['next'] = request.url
return redirect('/login')
if session.get('loginUser') and form.validate_on_submit():
post = Post(form.title.data, form.content.data,
loginUser.get('userid'))
try:
db_session.add(post)
db_session.commit()
flash('Your post has been created!', 'success')
except:
db_session.rollback()
return redirect(url_for('posting'))
return render_template('create.htm', title="New Post", form=form)
def editmemolist(userid, id):
req = request.json
userid = session['loginUser']['userid']
name = req['name']
content = req['content']
print("name>>", name, "content>>", content)
memo = Memo(userid, name, content)
memo.id = id
try:
db_session.merge(memo)
db_session.commit()
print("Update Success!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
return jsonify({'status':'success'})
except Exception as err:
db_session.rollback()
print("\n \n \n Error!!!!!!!!!!!!!!!!!!!!!!", err)
return jsonify({'status':'fail'})
def editchecklist(userid, id):
req = request.json
userid = session['loginUser']['userid']
name = req['name']
content = ",".join(req['content'])
print("name>>", name, "content>>", content)
check = Checklist(userid, name, content)
print("check>>>>>",check)
check.id = id
try:
db_session.merge(check)
db_session.commit()
print("Update Success!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
return jsonify({'status':'success'})
except Exception as err:
db_session.rollback()
print("\n \n \n Error!!!!!!!!!!!!!!!!!!!!!!", err)
return jsonify({'status':'fail'})
def postmemo():
mJson = {}
memoJson = request.json
print("\n\n\n\n\n\n", memoJson)
userid = session['loginUser']['userid']
title = memoJson['name']
memo = memoJson['content']
m = Memo(userid, title, memo)
try:
db_session.add(m)
db_session.commit()
mJson['status'] = 'success'
print("Done!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
except Exception as err:
db_session.rollback()
mJson['status'] = 'fail'
print("\n \n \n Error!!!!!!!!!!!!!!!!!!!!!!", err)
return jsonify(mJson)
def sqltest():
ret = 'OK'
try:
# u = User('*****@*****.**', 'hong')
# db_session.add(u)
# u = User.query.filter(User.id == 10).first()
# print("user.id=", u.id)
# db_session.delete(u)
# u.email = '*****@*****.**'
# db_session.add(u)
s = db_session()
result = s.execute(
'select id, email, nickname from User where id > :id', {'id': 10})
Record = namedtuple('User', result.keys())
rrr = result.fetchall()
print(">>", type(result), result.keys(), rrr)
records = [Record(*r) for r in rrr]
for r in records:
print(r, r.nickname, type(r))
s.close()
ret = records
db_session.commit()
# ret = User.query.all()
# ret = User.query.filter(User.id > 5)
except SQLAlchemyError as sqlerr:
db_session.rollback()
print("SqlError>>", sqlerr)
# except:
# print("Error!!")
# finally:
# db_session.close()
# return "RET=" + str(ret)
return render_template('main.html', userlist=ret)
def register_post():
register = RegistrationForm()
if register.validate_on_submit():
flash('Account created for {}'.format(register.username.data),
'success')
u = User(register.password.data, register.email.data,
register.username.data)
try:
db_session.add(u)
db_session.commit()
print("data inserted")
except:
db_session.rollback()
return redirect('/login')
return render_template('register.htm',
title='Register Page',
register=register)
def regist_post():
email = request.form.get('email')
passwd = request.form.get('passwd')
passwd2 = request.form.get('passwd2')
nickname = request.form.get('nickname')
if passwd != passwd2:
flash("암호를 정확히 입력하세요!!")
return render_template("regist.html", email=email, nickname=nickname)
else:
u = User(email, passwd, nickname, True)
try:
db_session.add(u)
db_session.commit()
except:
db_session.rollback()
flash("%s 님, 가입을 환영합니다!" % nickname)
return redirect("/login")
def idx():
ret = 'OK'
try:
# Create(insert)
u = User('*****@*****.**', 'hong')
db_session.add(u)
print("user.id=", u.id)
# Read(select)1
# u = User.query.filter(User.id == 1).first()
# lst = User.query.all()
# Read(select)2
# s = db_session()
# result = s.execute('select id, email, nickname from User where id > :id', {'id':1})
# Record = namedtuple("User", result.keys())
# rrr = result.fetchall()
# print(">>", type(result), result.keys(), rrr)
# records = [Record(*r) for r in rrr]
# for r in records:
# print(r, r.nickname, type(r))
# s.close()
db_session.commit()
# ret = User.query.all()
except SQLAlchemyError as sqlerr:
db_session.rollback()
print("SqlError>>", sqlerr)
except:
print("Error!!")
finally:
db_session.close()
# return "RET=" + str(ret)
return render_template('main.html', userlist=ret)
def mycoms_post(myalbumid):
if not session.get('loginUser'):
session['next'] = request.url
return redirect('/login')
loginUser = session.get('loginUser')
content = request.form.get('content')
cmt = Mycom(myalbumid, loginUser.get('userid'), content)
cmt.content = content
# cmt.id = 7
print("77777777777777777777777777777777777777777777777>>", cmt.id)
try:
# db_session.add(cmt)
db_session.merge(cmt)
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error!!", err)
return jsonify({"result": 'OK'})
def edit(userid, id):
userid=session['loginUser']['userid']
title = request.form.get('title')
print("title>>", title)
content = request.form.get('content')
print("content>>", content)
post = Post(title, content, userid)
print(post)
post.postid = id
try:
db_session.merge(post)
db_session.commit()
print("Update Success!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
return redirect('/posting')
except Exception as err:
db_session.rollback()
print("\n \n \n Error!!!!!!!!!!!!!!!!!!!!!!", err)
return render_template('writelayout.htm', title="Revise", userid=userid, postid=id, action="/write/edit/{}/{}".format(userid, id), mode="revision")
def upload():
upfile = request.files['file'] # aa.jpg
myalbumid = request.form.get('myalbumid')
print("mmmmmmmmmmmmmmmmmm>>", myalbumid)
try:
filename = upfile.filename.replace('..', '')
path = rename(os.path.join("./helloflask/static/upfiles", filename))
upfile.save(path)
path = path[12:]
myalbum = Myalbum.query.filter("id=:id").params(id=myalbumid).first()
myalbum.upfile = path
db_session.merge(myalbum)
db_session.commit()
except SQLAlchemyError as err:
db_session.rollback()
print("Error >>", err)
return jsonify({"path": path})
def log_write():
pat_id = session['loginUser']['userid']
request_list = request.form
print(">>>>>>>>>>>>>>>>>>>>>>>>", request_list)
pattern = re.compile("[0-9]+")
data_list = []
date = None
for i, k in enumerate(request_list):
data = {}
if i == 0:
date = request_list[k]
else:
kk = re.findall(pattern, k)[0]
data['pat_id'] = pat_id
data['usercol_id'] = kk
data['value'] = request_list[k]
data['date'] = date
data_list.append(data)
print("<<<<<<<<<<<<<<<<<<<<<<<<<<", data_list)
# Log table에 executemany
try:
db_session.bulk_insert_mappings(Log, data_list)
db_session.commit()
# custom_res = Response("Custom Response", 200, {'message': 'success'})
custom_res = {"code": 200, "message": "sucess"}
except SQLAlchemyError as sqlerr:
db_session.rollback()
# custom_res = Response("Custom Response", 500, {'message': sqlerr})
custom_res = {"code": 500, "message": sqlerr}
return jsonify(custom_res)
def sqltest():
ret = 'aaaa'
ds = 'aa'
rs = 'aq'
al = ''
try:
ds = Song.query.filter(Song.genre == 'Dance').all()
rs = Song.query.filter(Song.genre == 'Rap / Hip-hop').first()
al = Album.query.filter(Album.album_genre == 'Ballad').all()
except SQLAlchemyError as sqlErr:
print(sqlErr)
db_session.rollback()
except:
print('Errorrrrrrr!')
return render_template('a.htm',
title="sql",
dancesong=ds,
rapsong=rs,
albums=al)
def register_post():
register = RegistrationForm()
if register.validate_on_submit():
flash('Account created for {}'.format(register.username.data), 'success')
u = User(register.password.data, register.email.data, register.username.data)
try:
subject = "회원가입 메일"
mail = "회원가입을 축하합니다"
# s = URLSafeTimedSerializer('The_Key') # QQQ secret key 바꾸기
# token = s.dumps(register.email.data, salt = 'email_confirm')
# confirm_email(token)
# link = url_for('confirm_email', token = token, _external = True)
send_email(subject, mail ,register.email.data)
db_session.add(u)
db_session.commit()
print("data inserted")
except Exception as err:
db_session.rollback()
print("ROLL BACK FOR REGISTER", err)
return redirect('/login')
return render_template('register.htm', title='Register Page', register = register)
def postwrite():
loginUser = session.get('loginUser')
userid = session['loginUser']['userid']
title = request.form.get('title')
content = request.form.get('content')
# if select:
print("ccc>> ", content)
print("title>> ", title)
print("userid>>", userid)
post = Post(title, content, userid)
try:
db_session.add(post)
db_session.commit()
print("Done!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
except Exception as err:
db_session.rollback()
print("\n \n \n Error!!!!!!!!!!!!!!!!!!!!!!", err)
return redirect('/posting')
def signup_modal():
email = request.form.get('email')
password = request.form.get('password')
cfpassword = request.form.get('cfpassword')
name = request.form.get('name')
phone_number = request.form.get('phone')
nickname = request.form.get('nickname')
address = request.form.get('address')
if password != cfpassword:
flash("암호를 입력주세요!!!")
else:
u = User(email, password, name, phone_number, nickname, address)
print(u)
try:
db_session.add(u)
db_session.commit()
except Exception as err:
print(err)
db_session.rollback()
return redirect('/perform')
def write():
print("::::::")
isAdded = False
doc_id = session['loginUser']['userid']
patients_list = Doc_Pat.query.filter(Doc_Pat.doc_id == doc_id).all()
immutableMultiDict = request.form
jsonData = immutableMultiDict.to_dict(flat=False)
request_data_list = [jsonData[d] for i, d in enumerate(jsonData)]
pat_id = int(request_data_list.pop(0)[0])
discode = int(request_data_list.pop(0)[0])
col_list = [int(col_id) for col_id in request_data_list.pop(0)]
# 기존 환자인지 체크
for patient in patients_list:
if (patient.get_json()['id'] == pat_id):
isAdded = True
break
# 신규 환자 추가
if (isAdded == False):
dp = Doc_Pat(pat_id, doc_id)
try:
db_session.add(dp)
db_session.commit()
except SQLAlchemyError as sqlerr:
db_session.rollback()
# 환자의 진단된 질병코드가 있는지 확인 및 추가
assigned_discode_list = DocPat_Disc.query.join(
Doc_Pat, DocPat_Disc.docpat_id == Doc_Pat.id).filter(
Doc_Pat.pat_id == pat_id).all()
assigned_docpat = Doc_Pat.query.filter(
Doc_Pat.pat_id == pat_id and Doc_Pat.doc_id == doc_id).first()
assigned_docpat_id = assigned_docpat.get_json()['id']
# req의 discode가 전체가 아니라, 특정한 discode가 왔고, 그 것이 등록이 안 되어 있을 때 추가
if (discode != 0):
assigned_discode_id_list = [
assigned_discode.get_json()['discode_id']
for assigned_discode in assigned_discode_list
]
if (discode not in assigned_discode_id_list):
dpd = DocPat_Disc(assigned_docpat_id, discode)
try:
db_session.add(dpd)
db_session.commit()
except SQLAlchemyError as sqlerr:
db_session.rollback()
# 리퀘스트 칼럼
data_list = [{
'pat_id': pat_id,
'usercol_id': col_id
} for col_id in col_list]
# 기존 칼럼 확인 및 새로 추가할 칼럼으로만 데이터 구성
patuser_col_list_from_db = Pat_Usercol.query.filter(
Pat_Usercol.pat_id == pat_id).all()
patuser_col_list_from_db_min = [
col_list.get_json()['usercol_id']
for col_list in patuser_col_list_from_db
]
input_data_list = []
if (len(patuser_col_list_from_db_min) != 0):
for data in data_list:
if data['usercol_id'] in patuser_col_list_from_db_min:
patuser_col_list_from_db_min.remove(data['usercol_id'])
else:
input_data_list.append(data)
delete_data_list = [{
'pat_id': pat_id,
'usercol_id': col
} for col in patuser_col_list_from_db_min]
# 삭제할 데이터가 있으면 delete
if (len(delete_data_list) != 0):
for delete_data in delete_data_list:
# pu = Pat_Usercol(delete_data['pat_id'], delete_data['usercol_id'])
delete_col = Pat_Usercol.query.filter(
Pat_Usercol.pat_id == delete_data['pat_id'],
Pat_Usercol.usercol_id == delete_data['usercol_id']).first()
delete_col_id = delete_col.get_json()['id']
try:
# db_session.delete(pu)
Pat_Usercol.query.filter_by(id=delete_col_id).delete()
db_session.commit()
custom_res = {"code": 200, "message": "sucess"}
# QQQ SQLAlchemyError define 에러 해결하기.
except:
db_session.rollback()
custom_res = {"code": 500, "message": "eerrorr"}
# 데이터가 있으면 입력
if len(input_data_list) != 0:
try:
db_session.bulk_insert_mappings(Pat_Usercol, input_data_list)
db_session.commit()
custom_res = {"code": 200, "message": "sucess"}
except SQLAlchemyError as sqlerr:
db_session.rollback()
custom_res = {"code": 500, "message": sqlerr}
else:
custom_res = {"code": 200, "message": "sucess"}
return jsonify(custom_res)
