Fetch "Some" data from MySQL Database using Flask (Python)

Question

I have just started making a "Student Database Project" with Flask. I have set up a MySQL Database using the flask_mysqldb module. I have also inserted some student data into it. But I want to fetch the data (name, grade, phone number, address, email , etc) of a specific student. How should I do it? Please help me because I'm new to Flask and MySQL. Here's my code:

from flask import Flask,render_template, redirect, url_for, request, jsonify
from flask_mysqldb import MySQL

app = Flask(__name__)
app.config['SECRET_KEY'] = 'ILUVTOFART'
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'student_db'


mysql = MySQL(app)

@app.route("/add_s_data", methods=["GET","POST"]) #This is the page for adding student information to DB
def handle_s_data():

    if request.method == "POST":

        cur = mysql.connection.cursor()
        sid = request.form.get('id', False) # Student ID

        name = request.form.get('name', False) # Full Name
        f_name = request.form.get('fname', False) # Father's Name
        m_name = request.form.get('mname', False) # Mother's Name
        clas = request.form.get('class', False) # His grade/class
        roll = request.form.get('roll', False) # His roll number
        section = request.form.get('section', False) # His class section
        dob = request.form.get('dob', False) # Date of Birth
        join = request.form.get('join', False) # Admission date
        phone = request.form.get('phone', False) # Phone number
        address = request.form.get('address', False) # Address
        sequence = (sid, name, f_name, m_name, clas, roll, section, dob, join, phone, address)
        formula = "INSERT INTO student_data (id, name, f_name, m_name, class, roll, section, dob, joindate, phone, address) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s )"
        cur.execute(formula,sequence)
        mysql.connection.commit()
        return str(sequence) + " Done!"
    return render_template('add_s_data.html')

#I want to fetch these data from the database and show them on the page. I know how to use Jinja2 Variables, but how do I fetch the data?

@import url('https://fonts.googleapis.com/css?family=Josefin+Sans&display=swap');
@import url('https://fonts.googleapis.com/css?family=Noto+Sans+TC&display=swap');
@import url('https://fonts.googleapis.com/css?family=Lato&display=swap');
@import url('https://fonts.googleapis.com/css?family=Raleway&display=swap');

*{
  margin: 0;
  padding: 0;
  box-sizing: border-box;
  list-style: none;
  text-decoration: none;
  font-family: 'Josefin Sans', sans-serif;
}

body{
   background-color: #f3f5f9;
}

.wrapper{
  display: flex;
  position: relative;
}

.wrapper .sidebar{
  width: 240px;
  height: 100%;
  background: #0080ff;
  padding: 30px 0px;
  position: fixed;
}

.wrapper .sidebar h2{
  margin-left: 15px;
  padding-top: 05px;
  padding-bottom: 05px;
  color: white;
  font-family: 'Noto Sans TC', sans-serif;
  -webkit-text-stroke: 0.5px black;

}

.wrapper .sidebar ul li{
  
  padding: 15px;
  border-bottom: 1px solid #bdb8d7;
  border-bottom: 1px solid rgba(0,0,0,0.05);
  border-top: 1px solid rgba(255,255,255,0.05);
}    

.wrapper .sidebar ul li a{
  color: white;
  display: block;
}

.wrapper .sidebar ul li a .fas{
  width: 25px;
}

.wrapper .sidebar ul li:hover{
  background-color: #0f52ba;
}
    
.wrapper .sidebar ul li:hover a{
  color: #fff;
}
 
.wrapper .sidebar .social_media{
  position: absolute;
  bottom: 0;
  left: 50%;
  transform: translateX(-50%);
  display: flex;
}

.wrapper .sidebar .social_media a{
  display: block;
  width: 40px;
  background: #594f8d;
  height: 40px;
  line-height: 45px;
  text-align: center;
  margin: 0 5px;
  color: #bdb8d7;
  border-top-left-radius: 5px;
  border-top-right-radius: 5px;
}

.home {
	background: #0080ff;
}
.add{
  background: #0f52ba;
}

.wrapper .main_content{
  width: 100%;
  margin-left: 200px;
}

.wrapper .main_content .header{
  padding: 20px;
  background: #fff;
  color: #717171;
  border-bottom: 1px solid #e0e4e8;
}

.wrapper .main_content .info{
  margin: 20px;
  color: #717171;
  line-height: 25px;
}

.wrapper .main_content .info div{
  margin-bottom: 20px;
}

@media (max-height: 500px){
  .social_media{
    display: none !important;
  }
}



.id{
  font-size: 30px;
  margin-top: 5%;
  margin-left:20%;

}
.id input{
  margin-left: 16.5%;
  width: 30%;
  height: 5;
  font-size: 30px;
}

.name{

  font-size: 30px;
  margin-top: 3%;
  margin-left:20%;

}
.name input{
  width: 30%;
  height: 5;
  font-size: 30px;
  margin-left: 11.5%;
}

.fname{
  font-size: 30px;

  margin-top: 3%;
  margin-left:20%;

}
.fname input{
  width: 30%;
  height: 5;
  margin-left: 1%;
  font-size: 30px;
}
.mname{
  font-size: 30px;
  margin-top: 3%;
  margin-left:20%;

}
.mname input{
  width: 30%;
  height: 5;
  font-size: 30px;
}

.class{
  font-size: 30px;
  margin-top: 3%;
  margin-left:20%;

}
.class input{
  width: 30%;
  height: 5;
  font-size: 30px;
  margin-left: 12.5%;
}

.roll{
  font-size: 30px;
  margin-top: 3%;
  margin-left:20%;

}
.roll input{
  width: 30%;
  height: 5;
  font-size: 30px;
  margin-left: 14%;
}
.section{
  font-size: 30px;
  margin-top: 3%;
  margin-left:20%;

}
.section input{
  width: 30%;
  height: 5;
  font-size: 30px;
  margin-left: 9.5%;
}






.dob{

  position: absolute;
  margin-left: 65%;
  top: 18.5%;
  font-size: 30px;
}

.dob input{

  width: 50%;
  font-size: 30px;
  height: 1;
}

.joining{

  position: absolute;
  margin-left: 65%;
  top: 29.5%;
  font-size: 30px;
}

.joining input{
  margin-left: 2%;
  width: 50%;
  font-size: 30px;
  height: 1;
}

.phone{

  position: absolute;
  margin-left: 65%;
  top: 42.5%;
  font-size: 30px;
}

.phone input{
  margin-left: 10%;
  width: 50%;
  font-size: 30px;
  height: 1;
}
.address{

  position: absolute;
  margin-left: 65%;
  top: 54.5%;
  font-size: 30px;
}

.address input{
margin-left: 16%;
  width: 51%;
  font-size: 30px;
  height: 1;
}


button{
  font-size: 20px;
  border: 1px solid black;
  
  background: lightblue;
  color: white;
  margin-left: 77%;
  position: absolute;
  top: 70%;
  width: 100px;
  height: 50px;
  border-radius: 8px;
}

<!DOCTYPE html>
<html lang="eng">
<head>
	<meta charset="utf-8">
	<title>I am Ahnaf</title>
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link rel="stylesheet" type="text/css" href="{{url_for('static', filename = 'css/bootstrap.min.css')}}">
	<link rel="stylesheet" type="text/css" href="../static/css/add_s_data.css">
	<link rel="stylesheet" type="text/css" href="../static/fontawsome/css/all.css">
    
</head>
<body>
	
	<!-- Sidebar Section -->
	<div class="wrapper">
    <div class="sidebar">
        <h2>Ahnaf's SDMS</h2>
        <ul>
            <li class="home"><a href="/dashboard"><i class="fas fa-home"></i>Home</a></li>
            <li class="search"><a href=""><i class="fas fa-search"></i>Search Data</a></li>
            <li class="add"><a href="/add_s_data"><i class="fas fa-users"></i>Add Data</a></li>
            <li class="modify"><a href="#"><i class="fas fa-edit"></i>Modify Data</a></li>
            <li class="delete"><a href="#"><i class="fas fa-user-minus"></i>Delete Data</a></li>
            <li class="report"><a href="#"><i class="fas fa-flag"></i>Add Student Report</a></li>
            <li class="present"><a href="#"><i class="fas fa-address-card"></i>ID Card Maker</a></li>
            <li class="exam"><a href="#"><i class="fas fa-pen"></i>Exams</a></li>
            <li class="events"><a href="#"><i class="fas fa-calendar-week"></i>Events</a></li>
            <li class="contact"><a  href="#"><i class="fas fa-address-book"></i>Contact</a></li>
            

        </ul> 
      
    </div>
</div>
<div class="choice">
    <p style="color:blue;position: absolute; top:2%;margin-left: 35%;font-size: 30px;">Student</p>
    <hr style="position: relative; margin-top: 4%; margin-left: 29%; width: 20%">
    <div style="position:absolute;top:0;border-left:1px solid #000;height:50px; margin-left: 57%;"></div>
    <a style="color: black;position: absolute; top:2%;margin-left: 70%;font-size: 30px;"href="/add_t_data">Teacher</a>
</div>
<div class="form">
    <form action="/add_s_data" method='POST'>
        
       <div class="id"> 
        <label>ID:</label>
        <input type="text" name="id" ><br>
       </div> 
       <div class="name"> 
        <label>Name:</label>
        <input type="text" name="name" ><br>
       </div> 
       <div class="fname"> 
        <label>Father's Name:</label>
        <input type="text" name="fname" ><br>
       </div>
       <div class="mname"> 
        <label>Mother's Name:</label>
        <input type="text" name="mname" ><br>
       </div> 
       <div class="class"> 
        <label>Class:</label>
        <input type="text" name="class" ><br>
       </div> 
       <div class="roll"> 
        <label>Roll:</label>
        <input type="text" name="roll" ><br>
       </div> 
       <div class="section"> 
        <label>Section:</label>
        <input type="text" name="section" ><br>
       </div> 
       <div class="dob"> 
        <label>Date of Birth:</label>
        <input type="date" name="dob" ><br>
       </div> 
       <div class="joining"> 
        <label>Joining Date:</label>
        <input type="date" name="join" ><br>
       </div> 
       <div class="phone"> 
        <label>Phone No:</label>
        <input type="text" name="phone" ><br>
       </div> 
       <div class="address"> 
        <label>Address:</label>
        <input type="text" name="address" ><br>
       </div> 
       <button type="submit" value="Submit" >Submit</button>
    </form>
</div>

<script type="text/javascript">

</script>
</body>
</html>

Thank you for your convenience.

LNF · Accepted Answer · 2020-02-20 19:20:50Z

Here is how you possibly can do it.


@app.route("/user/<int:id>")
def user(id):
    cur = mysql.connection.cursor() 
    cur.execute("""SELECT * FROM student_data WHERE id = %s""", (id,))
    user = cur.fetchone()
    return render_template('user.html', user = user)

Inside user.html

<!DOCTYPE html>
<html lang="eng">
<head>
    <meta charset="utf-8">
    <title>User : {{ user.name }}</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" type="text/css" href="{{url_for('static', filename = 'css/bootstrap.min.css')}}">
    <link rel="stylesheet" type="text/css" href="../static/css/add_s_data.css">
    <link rel="stylesheet" type="text/css" href="../static/fontawsome/css/all.css">

</head>
<body>

  <div class="user">
    <p>ID: {{ user.id }}</p>
    <p>ID: {{ user.name }}</p>
    <p>...</p>
  </div>


</body>
</html>

I advise you to go and check Flask-SQLAlchemy, Flask-WTForms they will help you manager database and forms better.

Collectives™ on Stack Overflow

Fetch "Some" data from MySQL Database using Flask (Python)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related