python MySQLdb 连接数据的使用

目录

• 1.文件结构
• 2.实验效果
• 3.主文件：main.py
• 4.base.html文件
• 5.update.html文件
• 6.delete.html文件
• 7.search.html文件

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连接数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！