python在前端頁面使用 MySQLdb 連線資料

2022-03-24 16:00:23

1.檔案結構

MySQLdb和pymysql的使用差不多閱讀的小夥伴可以自己嘗試實現

2.實驗效果

3.主檔案：main.py

import MySQLdb
from flask_wtf import FlaskForm
from wtforms.validators import DataRequired,EqualTo,Length
from wtforms import StringField,SubmitField,PasswordField,TelField
from flask import Flask,render_template,redirect,url_for,abort,request,jsonify

app=Flask(__name__)
app.secret_key='stu'

#連線資料mysql
conn=MySQLdb.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root',
    db='main'
)
cur=conn.cursor()

#增加使用者表單
class StuForm(FlaskForm):
    name=StringField(label='使用者名稱: ',validators=[DataRequired()])
    password=PasswordField(label='密碼: ',validators=[DataRequired(),Length(min=3,max=8)])
    submit=SubmitField(label='提交')

#搜尋使用者表單
class SStuForm(FlaskForm):
    name = StringField(label='使用者名稱: ', validators=[DataRequired()])
    submit=SubmitField(label='提交')

#更新使用者表單
class UStuForm(FlaskForm):
    name = StringField(label='使用者名稱: ', validators=[DataRequired()])
    password = PasswordField(label='密碼: ', validators=[DataRequired(), Length(min=3, max=8)])
    submit = SubmitField(label='提交')

#刪除使用者表單
class DStuForm(FlaskForm):
    name = StringField(label='使用者名稱: ', validators=[DataRequired()])
    submit = SubmitField(label='提交')

def CreateTab():
    sql="create table student(name varchar(20),password varchar(30))"
    cur.execute(sql)
    conn.commit()
    cur.close()

@app.route('/add',methods=['POST','GET'])
def add():
    stuform=StuForm()
    if request.method=='POST':
        if stuform.validate_on_submit():
            name=stuform.name.data
            password=stuform.password.data
            print('name: {}'.format(name))
            print('password: {}'.format(password))
            sql=f"insert into student(name,password) values('{name}','{password}')"
            cur.execute(sql)
            conn.commit()
            return jsonify('Add Successed!')
    return render_template('add.html',stuform=stuform)

@app.route('/search',methods=['POST','GET'])
def search():
    sstuform=SStuForm()
    if request.method=='POST':
        if sstuform.validate_on_submit():
            name=sstuform.name.data
            sql=f"select count(name) from student where name='{name}'"
            cur.execute(sql)
            conn.commit()
            count=cur.fetchone()[0]
            if count<=0:
                return jsonify('The User is not exist!')
            else:
                sql=f"select name,password from student where name='{name}'"
                cur.execute(sql)
                conn.commit()
                result=cur.fetchall()
                return jsonify(result)
    return render_template('search.html',sstuform=sstuform)

@app.route('/update',methods=['POST','GET'])
def update():
    ustuform=UStuForm()
    if request.method=='POST':
        if ustuform.validate_on_submit():
            name = ustuform.name.data
            password=ustuform.password.data
            sql = f"select count(name) from student where name='{name}'"
            cur.execute(sql)
            conn.commit()
            count = cur.fetchone()[0]
            if count <= 0:
                return jsonify('The User is not exist!')
            else:
                sql = f"update student set name='{name}',password='{password}'  where name='{name}'"
                cur.execute(sql)
                conn.commit()
                return jsonify('Update Successed!')
    return render_template('update.html',ustuform=ustuform)
@app.route('/delete',methods=['POST','GET'])
def delete():
    dstuform=DStuForm()
    if request.method=='POST':
        if dstuform.validate_on_submit():
            name=dstuform.name.data
            sql = f"select count(name) from student where name='{name}'"
            cur.execute(sql)
            conn.commit()
            count = cur.fetchone()[0]
            if count <= 0:
                return jsonify('The User is not exist!')
            else:
                sql=f"delete from student where name='{name}'"
                cur.execute(sql)
                conn.commit()
                return jsonify('Delete Successed!')

    return render_template('delete.html',dstuform=dstuform)

@app.route('/function',methods=['POST','GET'])
def function():
    if request.method=='POST':
        submit1 = request.form.get('submit1')
        submit2 = request.form.get('submit2')
        submit3 = request.form.get('submit3')
        submit4 = request.form.get('submit4')
        print('submit1: {}'.format(submit1))
        print('submit2: {}'.format(submit2))
        print('submit3: {}'.format(submit3))
        print('submit4: {}'.format(submit4))
        if submit1 is not None:
            return redirect(url_for('add'))
        if submit2 is not None:
            return redirect(url_for('search'))
        if submit3 is not None:
            return redirect(url_for('update'))
        if submit4 is not None:
            return redirect(url_for('delete'))
    return render_template('base.html')
if __name__ == '__main__':
    print('Pycharm')
    # CreateTab()
    app.run(debug=True)

4.base.html檔案

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        .h1{
            position:relative;
            margin:auto;
            width:500px;
            height:50px;
            margin-top:100px;
            margin-left:650px;
        }
        .form {
            position:relative;
            width:500px;
            height:50px;
            margin:auto;
            margin-top:50px;
            border:2px solid #000000;
            color:#000000;
            font-size:20px;
            font-weight:400;
        }
        .form1{
            position:absolute;
            margin-top:10px;
            margin-left:80px;
        }
        .form2{
            position:absolute;
            margin-top:10px;
            margin-left:180px;
        }
        .form3{
            position:absolute;
            margin-top:10px;
            margin-left:280px;
        }
        .form4{
            position:absolute;
            margin-top:10px;
            margin-left:380px;
        }
    </style>
</head>
<body>
    <div class="h1">
        <h1>功能選擇</h1>
    </div>
    <div class="form">
        <form class="form1" action="http://127.0.0.1:5000/add" method="POST">
            <input type="submit" name="submit1" value="新增">
        </form>
        <form class="form2" action="http://127.0.0.1:5000/search" method="POST">
            <input type="submit" name="submit2" value="搜尋">
        </form>
        <form class="form3" action="http://127.0.0.1:5000/update" method="POST">
            <input type="submit" name="submit3" value="更新">
        </form>
        <form class="form4" action="http://127.0.0.1:5000/delete" method="POST">
            <input type="submit" name="submit4" value="刪除">
        </form>
    </div>
</body>
</html>

5.update.html檔案

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Insert</title>
    <style>
        div{
          width:255px;
          height:100px;
          margin:auto;
          margin-top:200px;
          border:2px solid #000000;
          font-size:20px;
          font-weight:400px;
          background:#FFFFFF;
        }
        .submit{
            margin-top:10px;
            margin-left:100px;
        }
    </style>
</head>
<body>
  <div>
      <form action="" method="POST">
          {{ustuform.csrf_token()}}
          {{ustuform.name.label}}{{ustuform.name}}<br>
          {{ustuform.password.label}}{{ustuform.password}}<br>
          <input class="submit" type="submit" name="submit" value="更新">
      </form>
  </div>
</body>
</html>

6.delete.html檔案

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Insert</title>
    <style>
        div{
          width:255px;
          height:100px;
          margin:auto;
          margin-top:200px;
          border:2px solid #000000;
          font-size:20px;
          font-weight:400px;
          background:#FFFFFF;
        }
        .submit{
            margin-top:10px;
            margin-left:100px;
        }
    </style>
</head>
<body>
  <div>
      <form action="" method="POST">
          {{dstuform.csrf_token()}}
          {{dstuform.name.label}}{{dstuform.name}}<br>
          <input class="submit" type="submit" name="submit" value="刪除">
      </form>
  </div>
</body>
</html>

7.search.html檔案

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Insert</title>
    <style>
        div{
          width:255px;
          height:100px;
          margin:auto;
          margin-top:200px;
          border:2px solid #000000;
          font-size:20px;
          font-weight:400px;
          background:#FFFFFF;
        }
        .submit{
            margin-top:10px;
            margin-left:100px;
        }
    </style>
</head>
<body>
  <div>
      <form action="" method="POST">
          {{sstuform.csrf_token()}}
          {{sstuform.name.label}}{{sstuform.name}}<br>
          <input class="submit" type="submit" name="submit" value="搜尋">
      </form>
  </div>
</body>
</html>

到此這篇關於MySQ Ldb 連線資料的使用的文章就介紹到這了,更多相關MySQLdb連線資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

python在前端頁面使用 MySQLdb 連線資料

目錄

1.檔案結構

2.實驗效果

3.主檔案：main.py

4.base.html檔案

5.update.html檔案

6.delete.html檔案

7.search.html檔案

熱門文章