DigitalOcean

Question

How to get data from database and create a table or dashboard for admin check?

Hi there,

I’m using python3 flask to build a website for python class project. I created those forms (login, register, contact), but how can I get back those input data from database and create a table or dashboard for internal review and monitor?

Thank you all!

app.py

from flask import Flask, render_template, request, flash, redirect, url_for, session, abort
from werkzeug.security import check_password_hash
import sqlite3

app = Flask(__name__)
app.secret_key="ahskfjhisw"

con=sqlite3.connect("database.db")
con.execute("create table if not exists customer(pid integer primary key, name text, address text, contact interger, mail text)")
con.close()


@app.route('/')
def index():
    return render_template('index.html')

@app.route('/about')
def about():
    return render_template('/about.html')

@app.route('/story')
def story():
    return render_template('/about/story.html')

@app.route('/phil')
def phil():
    return render_template('/about/phil.html')

@app.route('/menu1')
def menu1():
    return render_template('menu1.html')

@app.route('/varieties')
def varieties():
    return render_template('/beans/varieties.html')

@app.route('/purchase')
def purchase():
    return render_template('/beans/purchase.html')


@app.route('/login')
def login():
    return render_template('login.html')

@app.route('/checkin/', methods=('GET', 'POST'))
def checkin():
    if request.method == 'POST':
        name = request.form['name']
        password = request.form['password']
        error = None
        con = sqlite3.connect("database.db")
        con.row_factory = sqlite3.Row
        cur = con.cursor()
        data = cur.execute("select * from customer where name=? and password=?", (name, password)).fetchone()

        if data:
            session["name"] = data["name"]
            session["password"] = data["password"]
            return redirect("customer")

        if data is None:
            error = "Incorrect User Info"
        elif not check_password_hash(data["password"], password):
            error = "Incorrect password"
            return redirect("login")


@app.route('/customer', methods=('GET', 'POST'))
def customer():
    return render_template('customer.html')

@app.route('/register', methods=('GET', 'POST'))
def register():
    if request.method == 'POST':
        try:
            name = request.form['name']
            contact = request.form['contact']
            mail = request.form['mail']
            con = sqlite3.connect("database.db")
            cur = con.cursor()
            cur.execute('insert into customer(name, contact, mail) VALUES (?, ?, ?)',(name, contact, mail))
            con.commit()
            flash("Register Successful! Please check your email!", "success")
        except:
            flash("Error in Insert Operation", "danger")
        finally:
            return redirect(url_for("login"))
            con.close()
    return render_template('register.html')


@app.route('/logout/')
def logout():
    session.clear()
    return redirect(url_for("login"))

@app.errorhandler(404)
def page_not_found(e):
    # note that we set the 404 status explicitly
    return render_template('404.html'), 404

def page_not_found(e):
    return render_template('404.html'), 404

def create_app(config_filename):
    app = Flask(__name__)
    app.register_error_handler(404, page_not_found)
    return app

@app.route('/contact', methods=('GET','POST'))
def contact():
    if request.method == 'POST':
        try:
            name = request.form['name']
            mail = request.form['mail']
            phone = request.form['phone']
            content = request.form['content']
            con = sqlite3.connect("database.db")
            cur = con.cursor()
            cur.execute('insert into customer(name, mail, phone, content) VALUES (?, ?, ?,?)',(name, mail, phone, content))
            con.commit()
            flash("Messages sent Successful!", "success")
        except:
            flash("Error in Insert Operation", "danger")
        finally:
            return redirect(url_for("contact"))
            con.close()
    return render_template("contact.html")

if __name__ == '__main__':
    app.run(debug=True)

login.html

{% extends 'base.html' %}
{% block title %}Login{% endblock %}
{% block content %}
<div class="bg-img">
    <div class="content" style="margin-top: 100px;">
        <header>Login</header>

        <!-- <div class="container" style="margin-top: 100px;">
    <div class="col-md-offset-1 col-md-10">
        <div class="col-md-offset-3 col-md-5"> -->

        <div class="shadow">
            <form method="POST" action="/customer" autocomplete="off">
                <!-- <h2 class="page header text-primary">Login</h2> -->

                {% with messages = get_flashed_messages() %}
                {% if messages %}
                {% for message in messages %}
                <div class="alert alert">{{message}}</div>
                {% endfor %}
                {% endif %}
                {% endwith %}
                <div class="form-group">
                    <label>Username</label>
                    <input type="text" name="name" class="form-control" required>
                </div>

                <div class="form-group">
                    <label>Password</label>
                    <input type="password" name="password" class="form-control" required>
                </div>

                <div class="form-group">
                    <br>
                    <input type="submit" value="Login" class="btn btn-success btn-block">
                    <a href="{{url_for('register')}}" class="btn btn-primary btn-block">Register</a>

                </div>

            </form>
        </div>
    </div>
</div>
</div>

{% endblock %}

register.html

{% extends 'base.html' %}
{% block title %}Register{% endblock %}
{% block content %}


<div class="bg-img">
    <div class="header">
        <br>
        <br>
    <h1 style="text-align:center; color: white;" >Join our membership to get rewards!</h1>
</div>
    <div class="content" style="margin-top: 100px;">
        <header>Register</header>

        <div class="shadow">
            <form method="POST" action="/register">
                <!-- <h2 class="page header text-primary text-center">Registration</h2> -->
                <div class="form-group">
                    <label>Name</label>
                    <input type="text" name="name" class="form-control" required>
                </div>
                <!-- <div class="form-group">
                    <label>Address</label>
                    <textarea type="text" name="address" class="form-control" required></textarea>
                </div> -->
                <div class="form-group">
                    <label>Contact</label>
                    <input type="text" name="contact" class="form-control" required>
                </div>
                <div class="form-group">
                    <label>Mail</label>
                    <input type="text" name="mail" class="form-control" required>
                </div>

                <div class="form-group">
                    <br>
                    <input type="submit" name="name" class="btn btn-success btn-block" value="Register">

                    <a href="{{url_for('index')}}" class="btn btn-primary btn-block">Back to Home</a>

                </div>



            </form>
            </div>
        </div>
</div>

<script>
    $(document).ready(function(){
        $("form").attr("autocomplete", "off");
    });
</script>
{% endblock %}

contact.html

{% extends 'base.html' %}
{% block title %}Contact Us{% endblock %}
{% block content %}
<br>
<br>
<br>
<div class="container">
    <div>
        <h1 style="color: #c2773a">Contact Us</h1>
    </div>
    <br />
    <div class="row">
        <div class="col-md-6">
            <iframe
                src="https://www.google.com/maps/embed?pb=!1m14!1m8!1m3!1d14767.72004438455!2d114.1629785!3d22.2806409!3m2!1i1024!2i768!4f13.1!3m3!1m2!1s0x34040066be23cdbb%3A0x84e479a5fbd1b24f!2z6aaZ5riv5Lit5paH5aSn5a245bCI5qWt6YCy5L-u5a246ZmiICjkuK3nkrDmlZnlrbjkuK3lv4Mp!5e0!3m2!1szh-TW!2shk!4v1686908863115!5m2!1szh-TW!2shk"
                allowfullscreen="" loading="lazy" referrerpolicy="no-referrer-when-downgrade" id="googlemap"
                style="width:100%; height:350px;"></iframe>
        </div>
        <br />
        <div class="col-md-6">
            <form class="my-form">
                <div class="form-group">
                    <label for="form-name">Name</label>
                    <input type="text" name="name" class="form-control" id="form-name" placeholder="Your Name" required>
                </div>
                <div class="form-group">
                    <label for="form-email">Email Address</label>
                    <input type="email" name="mail" class="form-control" id="form-email" placeholder="Your Email Address" required>
                </div>
                <div class="form-group">
                    <label for="form-subject">Telephone</label>
                    <input type="text" name="phone" class="form-control" id="form-subject" placeholder="Local Phone Number" required>
                </div>
                <div class="form-group">
                    <label for="form-message">What do you want to tell us?</label>
                    <textarea name="content"  class="form-control" id="form-message" placeholder="Your Message"></textarea required>
                </div>
                <br>
                <button class="btn btn-default" type="submit">Contact Us</button>
            </form>
        </div>
    </div>

<br>
<br>
<div class="row">
    <div class="col-sm-4">

        <address class="list-inline" >
            <strong style="color: #c2773a">Our Cafe Address</strong><br>
            Unit A, 1/F, 12 Harcourt Rd, <br>
            Central, Hong Kong<br>
        </address>
    </div>
    <div class="col-sm-4">
        <address>
            <strong style="color: #c2773a">Telephone</strong><br>
            <a href="tel:+85212345678" style="color: #212529">+852 1234 5678</a>
        </address>
    </div>
        <div class="col-sm-4">
        <address>
            <strong style="color: #c2773a">Full Name</strong><br>
            <a href="mailto:#" style="color: #212529">info@example.com</a>
        </address>
    </div>
    </div>
</div>
</div>


<style>
    .my-form {
        color: #c2773a;
    }

    .my-form .btn-default {
        background-color: #c2773a;
        color: #fff;
        border-radius: 0;
    }

    .my-form .btn-default:hover {
        background-color: #4498C6;
        color: #fff;
    }

    .my-form .form-control {
        border-radius: 0;
    }


</style>

<script src="https://maps.googleapis.com/maps/api/js"></script>
<script type="text/javascript">
    // jQuery(function ($) {
    //     // Google Maps setup
    //     var googlemap = new google.maps.Map(
    //         document.getElementById('googlemap'),
    //         {
    //             center: new google.maps.LatLng(44.5403, -78.5463),
    //             zoom: 8,
    //             mapTypeId: google.maps.MapTypeId.ROADMAP
    //         }
    //     );
    // });
</script>

{% endblock %}
Show comments
Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev
Site Moderator
Site Moderator badge
• June 19, 2023

Hi there 👋,

To create a table or dashboard for admins, you would typically create a new route like /admin for example that’s accessible only to logged-in admins.

In the route’s handler function, you can query the database for the data you want to display, then pass it to the template to be displayed.

Here is a simple example of what that could look like and use as a blueprint to expand on:

Let’s say you want to display all the customers in the table for the admins:

@app.route('/admin', methods=('GET', 'POST'))
def admin():
    if "name" not in session:
        return redirect(url_for("login"))
    # Add an extra check to verify if the logged in user is an admin
    # This is assuming that you have a column "admin" in your customer table
    con = sqlite3.connect("database.db")
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    data = cur.execute("select * from customer where name=?", (session["name"],)).fetchone()
    if not data["admin"]:
        return redirect(url_for("login"))
    
    customers = cur.execute("select * from customer").fetchall()
    return render_template('admin.html', customers=customers)

In the above example, admin is a new route. It first checks if a user is logged in, and then checks if the logged-in user is an admin. If either check fails, it redirects to the login page. If both checks pass, it queries the database for all customers, then passes the results to the admin.html template.

Here is a basic admin.html that would display the customers in a table:

{% extends 'base.html' %}
{% block title %}Admin Dashboard{% endblock %}
{% block content %}
<h1>Admin Dashboard</h1>
<table>
  <tr>
    <th>Name</th>
    <th>Contact</th>
    <th>Mail</th>
  </tr>
  {% for customer in customers %}
  <tr>
    <td>{{ customer.name }}</td>
    <td>{{ customer.contact }}</td>
    <td>{{ customer.mail }}</td>
  </tr>
  {% endfor %}
</table>
{% endblock %}

Here we loop over the customers variable (which we passed from the route) with a for loop, creating a new HTML table row for each customer.

This is a very simple and basic example. In a real-world application, you would likely have more complex requirements, including but not limited to:

  • Pagination: If you have many customers, you may want to only display a certain number at a time and provide links to view more.
  • Search and filtering: You may want to provide a way to search for customers by name, or filter by certain criteria.
  • More detailed views: You may want to provide a link to view more details about each customer.

Keep in mind that this example assumes that you have a column admin in your customer table that you can use to identify admins. If you don’t have such a column yet, you’ll need to create it (or use a different method to identify admins) and manually mark at least one user as an admin. Also, remember to securely hash the admin flag, just like passwords, as it’s sensitive data.

Hope that this helps!

Best,

Bobby

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Sign up

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Learn more

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Learn more

Featured Tutorials

Kubernetes CourseLearn Python 3Machine Learning in PythonGetting started with GoIntro to Kubernetes

DigitalOcean Products

CloudwaysVirtual MachinesManaged DatabasesManaged KubernetesBlock StorageObject StorageMarketplaceVPCLoad Balancers

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel
© 2024 DigitalOcean, LLC.Sitemap.