Insert Date from HTML form into SQlite database using flask

Struggling a bit with the below.

I have a simple HTML page with 3 Forms, User, Startdate and EndDate. The code is as follows :

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>date picker</title>
</head>
<body>
<h1>Make A Short URL</h1>
<form action="/" method="post">
    <label for="name">Enter Your Name</label>
    <input type="text" name="name" value="" required>
    <label for="date1">Enter date Beginning </label>
    <input type="date" name="date1" value="" required>
    <label for="date2">Enter date End </label>
    <input type="date" name="date2" value="" required>
    <input type="submit" value="Submit">
</form>

</body>
</html>

I am then using the Python Flask Framework to insert the results of this into a Database. I have fetched the values of the Form and can get these to print correctly in python. The issue i am facing is that when passing these date values to SQLalchemy to insert them into my database it inserts incorrectly.

Using print statments i have tracked each stage to see if the values are coming across from HTML form correctly. All seems correct untill passing it to the database.

The code executes correctly with no error, However the data is inserted into multiple rows in the database? Is there a reason for this ?

Any input is greatly appreciated!

Example of Database after insert :

Python Code :

from flask import Flask, render_template, url_for, request, redirect
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

class database(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user = db.Column(db.String(200))
    startdate = db.Column(db.String(10))
    enddate = db.Column(db.String(10))
@app.route('/', methods = ['POST','GET'],)
def signup():
    if request.method == 'GET':
        return render_template('home.html')
    elif request.method == 'POST':
        name = request.form['name']
        print("The name is '"   name   "'")
        startdate1 = request.form['date1']
        print("The Start date is '"   startdate1   "'")
        enddate1 = request.form['date2']
        print("The end date is '"   enddate1   "'")

        dbname = database(user=name)
        dbstartdate = database(startdate=startdate1)
        print(dbstartdate)
        dbenddate = database(enddate=enddate1)
        try:
            db.session.add(dbname)
            db.session.add(dbstartdate)
            db.session.add(dbenddate)
            db.session.commit()
            return redirect('/')
        except:
            return 'There was an issue adding your task'
        return redirect('/')
if __name__ == '__main__':
    app.run(debug=True)

CodePudding user response：

The problem is that you're calling db.session.add() three times. This makes three separate transactions. Instead you want a single record insertion:

@app.route('/', methods = ['POST','GET'],)
def signup():
    if request.method == 'GET':
        return render_template('home.html')

    elif request.method == 'POST':
        name = request.form['name']
        print("The name is '"   name   "'")
        startdate1 = request.form['date1']
        print("The Start date is '"   startdate1   "'")
        enddate1 = request.form['date2']
        print("The end date is '"   enddate1   "'")

        record = database(user=name,
                          startdate=startdate1,
                          enddate=enddate1)
        
        try:
            db.session.add(record)
            db.session.commit()
            return redirect('/')
        except:
            return 'There was an issue adding your task'
        return redirect('/')

In other words, you want to make one instance of the database class with all attributes in a single go. Then you insert that as a single, complete record, rather than three instances with one a single attribute each.

Things to note:

return redirect('/') at the very end of the view function is redundant. It's impossible for your code to get to that point (it either throws an exception or it doesn't, but both paths already have their own return value)
Having a generic except is not good practice. This will actually catch KeyboardInterrupt so it might be difficult to even stop your app on long tasks. Even if you don't want to handle individual exceptions, make sure you use except Exception: so you can actually interrupt the code
There is no harm in having whitespace in code. Make sure you use blank lines to conceptually separate functions/classes etc. Crushing everything together is difficult to read