pymysql
flask + mysql +jinja2 웹사이트 만들기 -1단계 저장, 상세보기
bumychoi
2025. 4. 29. 14:43
app.py
import pymysql
import flask
conn, cur = None,None
data1,data2,data3,data4 ='','','',''
row=None
conn = pymysql.connect(
host="127.0.0.1",
user="root",
passwd="0000",
db='mylist',
charset='utf8'
)
cur =conn.cursor()
# cur.execute("DROP TABLE IF EXISTS userTable")
# # 입력하기
# cur.execute(f"CREATE TABLE userTable(\
# number INT PRIMARY KEY AUTO_INCREMENT,\
# user_id char(4),\
# userName char(15),\
# email char(20),\
# birthYear int)")
# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('hong','홍지윤','hong@naver.com',1996)")
# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('kim','김태연','kim@daum.net',2011)")
# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('star','별사탕','star@paran.com',1990)")
# cur.execute("INSERT INTO userTable (user_id, userName, email, birthYear) VALUES('yang','양지은','yang@gmail.com',1993)")
# 삭제
cur.execute(f"DELETE FROM userTable WHERE number='{3}'")
# conn.close()
#변경
new_name = "니도변신"
cur.execute(f'UPDATE userTable SET userName="{new_name}" WHERE number={5}')
# 조회하기
cur.execute("SELECT * FROM userTable")
print(F" ID 사용자ID 사용자이름 이메일 출생연도")
print("-------------------------------------------------------------------")
rows = cur.fetchall()
for row in rows:
data1=row[0]
data2=row[1]
data3=row[2]
data4=row[3]
data5=row[4]
print(f"{data1:<7} {data2:<10} {data3:<10} {data4:<18} {data5:<7}")
# 조회
cur.execute(f"SELECT * FROM userTable WHERE number='{70}'")
row = cur.fetchone()
data1=row[0]
data2=row[1]
data3=row[2]
data4=row[3]
data5=row[4]
print(f'번호:{data1} 아이디:{data2} 이름:{data3} 이메일:{data4} 출생연도:{data5}')
conn.commit()
conn.close()
write.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>form</title>
</head>
<body>
<table>
<form name="from" method="POST" action="/write">
<tr>
<td>아이디</td>
<td><input type="text" name="user_id"></td>
</tr>
<tr>
<td>이 름</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="email" name="email"></td>
</tr>
<tr>
<td>출생년도</td>
<td><input type="number" name="birthYear" min="1900" max="2030"></td>
</tr>
<tr>
<td colspan="2"><input type="submit"></td>
</tr>
</form>
</table>
</body>
</html>
view.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=view, initial-scale=1.0">
<title>view</title>
</head>
<body>
<table>
<tr>
<td>
{{result.user_id}}
</td>
</tr>
<tr>
<td>
{{result.userName}}
</td>
</tr>
<tr>
<td>
{{result.email}}
</td>
</tr>
<tr>
<td>
{{result.birthYear}}
</td>
</tr>
</table>
</body>
</html>
run.py
import pymysql
from flask import Flask,request,render_template,abort
app=Flask(__name__)
conn =pymysql.connect(
host="127.0.0.1",
user="root",
passwd="cb02077075",
db='mylist',
charset = "utf8",
autocommit = True
)
cur= conn.cursor()
@app.route("/write",methods=["GET","POST"])
def board_write():
if request.method == "POST":
user_id = request.form.get("user_id")
userName = request.form.get("userName")
email = request.form.get("email")
birthYear= request.form.get("birthYear")
sql = ("INSERT INTO userTable(user_id,userName, email,birthYear) VALUES(%s,%s,%s,%s)")
cur.execute(sql,(
user_id,
userName,
email,
birthYear
))
x=cur.lastrowid #키값받기
return str(x)
else:
return render_template("write.html")
@app.route("/view",methods=["GET"])
def board_view():
idx=request.args.get("idx")
if idx is not None:
try:
idx = int(idx)
cur.execute("SELECT * FROM userTable WHERE number=%s",(idx,)) #콤마필수수
row = cur.fetchone()
print(row)
if row is not None:
result={
"number":row[0],
"user_id":row[1],
"userName":row[2],
"email":row[3],
"birthYear":row[4]}
# print(f'번호:{nu} 아이디:{data2} 이름:{data3} 이메일:{data4} 출생연도:{data5}')
return render_template("view.html",result=result)
except ValueError:
return "잘못된 번호 형식입니다."
return "해당내용이 없습니다."
if __name__ == "__main__":
app.run(debug=True,port=9000)