Data table create not success

Question

Hello all experts,

I try to follow the tutorial, “https://www.digitalocean.com/community/tutorials/how-to-use-web-forms-in-a-flask-application”, and because I didn’t use init--db.py and schema.sql , even I tried, but not working to display the webpage.

I would like to create an input database table from contact form.

I did get messages what I have input in app.py : messages = [{‘name’: ‘Message One’, ‘content’: ‘Message One Content’}, {‘name’: ‘Message Two’, ‘content’: ‘Message Two Content’} ]"

Above messages are not what I want to show, I want to have a data table which show on data.html webpage when a admin had login to the account.

May I know what have I did wrong and how can I create a data table from the contact form data and show on data.html? thank you so much!

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.config['secret_key']="ahskfjhisw"
# app.secret_key="ahskfjhisw"


messages = [{'name': 'Message One',
             'content': 'Message One Content'},
            {'name': 'Message Two',
             'content': 'Message Two Content'}
            ]

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/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']
            password = request.form['password']
            con = sqlite3.connect("database.db")
            cur = con.cursor()
            cur.execute('insert into customer(name, contact, mail, password) VALUES (?, ?, ?, ?)',(name, contact, mail, password))
            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")
#         else:
#             messages.append({'name': name, 'content': content})
#             return redirect(url_for('data.html'))
#         finally:
#             return redirect(url_for("contact"))
#             con.close()

#     return render_template("contact.html")

@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")
        else:
            messages.append({'name': name, 'content': content})
            return redirect(url_for('data.html'))
        finally:
            return redirect(url_for("contact"))
            con.close()

    return render_template("contact.html")



@app.route('/data')
def data():
    return render_template('data.html', messages=messages)



@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)


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

data.html


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


{% for message in messages %}
<div class='message'>
    <h3>{{ message['name'] }}</h3>
    <p>{{ message['content'] }}</p>
</div>

        {% endfor %}
{% 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" rows="5">{{ request.form['content']}}</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">Email Address</strong><br>
            <a href="mailto:#" style="color: #212529">info@acafe.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"></script>



{% endblock %}

{% endfor %} {% endblock %} contact.html {% extends 'base.html' %} {% block title %}Contact Us{% endblock %} {% block content %}

Contact Us

Our Cafe Address
Unit A, 1/F, 12 Harcourt Rd,
Central, Hong Kong

Telephone
+852 1234 5678

Email Address
info@acafe.com

{% endblock %}

KFSys · Answer

Heya, Let’s break down the problem and try to find the solutions: Storing Contact Form Data : Your contact() function is already storing the data correctly to the customer table. However, the table structure in your code doesn’t have fields like phone and content, but you’re trying to insert these fields. This is probably causing an error. Adjust the table structure to accommodate these fields. Displaying the Data : In the data() function, you need to fetch data from the customer table and pass it to data.html. The current code is just passing hardcoded messages to the template. Modify the customer table : Before: con.execute("create table if not exists customer(pid integer primary key, name text, address text, contact integer, mail text)") and after the modification: con.execute("create table if not exists customer(pid integer primary key, name text, mail text, phone text, content text, contact integer, password text, admin integer default 0)") add phone and content fields. (Please note: Adding fields like this will not modify your existing table. If you want to add fields to an existing table, you’ll need to use the ALTER TABLE command or recreate the table.) Fetch and Display Data in data.html : Modify the data() function in app.py: @app.route('/data') def data(): con = sqlite3.connect("database.db") con.row_factory = sqlite3.Row cur = con.cursor() messages = cur.execute("select * from customer").fetchall() con.close() return render_template('data.html', messages=messages) Update your contact.html form to POST method :from

to Recommendations: Separate Database Operations : It’s a good idea to separate your database operations from your application logic. This makes the code more modular and easier to maintain. Use Flask-SQLAlchemy : Consider using Flask extensions like Flask-SQLAlchemy for handling database operations. It provides a high-level ORM approach and helps in organizing your code better. Security : Always hash the passwords before storing them in the database. Ensure your application is safe from SQL injection. Using ORMs like SQLAlchemy can help mitigate this risk. Sanitize and validate form data before saving it to the database. Table Structure : It might be helpful to separate users’ login data and the contact form data into two different tables for better organization and clarity. By following the above steps and recommendations, you should be able to store contact form data in the database and display it on the data.html page.

Bobby Iliev · Answer

Hi there 👋,

Looking through your code, it looks like that you might be mixing up the customer table and messages.

If you want to display the data from the ‘contact’ form on ‘data’ page, you have to save the data from the contact form into the database and then retrieve this data when you render the ‘data’ page.

You can try the following:

In the contact function, you are trying to store the data in ‘customer’ table, but it does not have ‘content’ and ‘phone’ fields. Therefore, you need to create a new table ‘messages’ that will store the name, email, phone, and message from the contact form.

con = sqlite3.connect("database.db")
con.execute("create table if not exists messages(pid integer primary key, name text, email text, phone text, content text)")
con.close()

In the contact route, store the contact form data into the ‘messages’ table not ‘customer’:

@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 messages(name, email, phone, content) VALUES (?, ?, ?,?)',(name, mail, phone, content))
            con.commit()
            flash("Messages sent Successful!", "success")
            return redirect(url_for('data'))
        except:
            flash("Error in Insert Operation", "danger")
            return redirect(url_for("contact"))
        finally:
            con.close()
    return render_template("contact.html")

In the data route, fetch the data from the ‘messages’ table:

@app.route('/data')
def data():
    con = sqlite3.connect("database.db")
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    messages = cur.execute("select * from messages").fetchall()
    return render_template('data.html', messages=messages)

That way, you will be able to see the messages submitted through the ‘contact’ form in your ‘data’ page.

Let me know how it goes!

Best,

Bobby

Question

Data table create not success

Recommendations:

Try DigitalOcean for free

Popular Topics

Get our biweekly newsletter

Hollie's Hub for Good

Become a contributor

Featured on Community

DigitalOcean Products

Welcome to the developer cloud

Get started for free