Trying to send input from html form to database SQLite3

[RonnyGiezen]

Hello,

I am in my first year of software engineering and I'm fairly new to programming.
I'm trying to make a guest registration program for work (this is not for school, I'm just trying to learn).
I've got my form an created a database.

The only thing I get stuck on is making the program actually work (the most important park of the program )

I don't really know how to actually send the user input form my HTML form to the database so the user info can be saved.

If someone could help me that'll be awesome.

Thank you in advance!

Kind regards,
Ronny


Here is my HTML code:

{% block body %}

<form action="{{ url_for('my_form') }}" method="POST">
    <div class="container">
        <h1>Gasten registratie</h1>
        <p>Vul dit formulier in om te registreren</p>
        <hr>
     
        <label for="First-Name"><b>Voornaam</b></label>
        <input type="text" id="First-Name" placeholder="Vul hier uw voornaam in" name="Voornaam" required>
    
        <label for="Last-Name"><b>Achternaam</b></label>
        <input type="text" id="Last-Name" placeholder="Vul hier uw achternaam in" name="Achternaam" required>

   
        <label for="Company-name"><b>Bedrijfsnaam</b></label>
        <input type="text" id="Company-name" placeholder="Vul hier uw bedrijfsnaam in" name="Bedrijfsnaam" required>
    

        <label for="Date"><b>Datum</b></label>
        <input type="date" id="Date" placeholder="Selecteer de Datum" name="Datum" required />
        <hr>


        <p>In het kader van onze ISAE certificering registreren wij uw gegevens.</p>
        <p>Wij verwerken uw gegevens volgends de regels van het AVG.</p>
        <button type="submit" class="registerbtn">Registreren</button>
    </div>


</form>

{% endblock %}

and this is my python code:

from flask import Flask, render_template, url_for, request, redirect
from datetime import datetime
import sqlite3 
from sqlite3 import Error

app = Flask(__name__) 

# Here I create a DB and a connection
def create_connection(db_file):
    connection = None;
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    
    return conn 

# This is were I call the function for my db

create_connection("gast.db") 

# This is were I create the tables I want in the database 

def create_table(conn, guests):
    
    sql = """ INSERT INTO guests(voor_naam,achter_naam,bedrijfs_naam,datum)
              VALUES(?,?,?,?) """ 

    c = conn.cursor()
    c.execute(sql,guests)
    return cur.lastrowid


# here I insert the created tab;es
def main():
    database = r"gast.db"

    sql_create_guests_table = """ CREATE TABLE IF NOT EXISTS guests (
                                        id integer PRIMARY KEY,
                                        voor_naam text NOT NULL,
                                        achter_naam text NOT NULL,
                                        bedrijfs_naam text NOT NULL,
                                        datum date NOT NULL

                                    ); """

    conn = create_connection(database)

    if conn is not None:
        # maak gast table
        create_table(conn, sql_create_guests_table)

    else:
        print("Error! cannot create the database connection.")



# index for the html page
@app.route('/')
def index():
    return render_template("index.html")


# sending input form form to db
@app.route('/my_form', methods=['POST'])
def my_form():

    if request.method == 'POST':
        c = conn.cursor()
        guest_vnaam = request.form.get('Voornaam')
        guest_anaam = request.form.get('Achternaam')
        guest_cnaam = request.form.get('Bedrijfsnaam')
        guest_datum = request.form.get('Datum')

        try:
            sql = ("INSERT INTO databasename.tablename (columnName,columnName,columnName,columnName Ci) VALUES (%s, %s, %s, %s)")
            c.execute(sql,(guest_vnaam, guest_anaam, guest_cnaam,  guest_datum))
            connection.commit() 
            #or "conn.commit()" (one of the two)
            return redirect('/')
        except:
            return 'Er ging iets fout met het opslaan van uw gegevens'

# This is where I run the app 
if __name__ == '__main__':
    app.run(debug=True)

    

[ibreeden]

Your code looks good. We will need the error message. What sort of webserver are you using? There must be logfiles where the messages are logged.

[RonnyGiezen]

Thanks for having a look!

Sorry I forgot to post the error,.

The error i'm getting:

File "/home/ronny/Desktop/registratieinc/app.py", line 70, in my_form
c = conn.cursor()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 139874622285632 and this is thread id 139874510690048.

[ibreeden]

Yes the error message is crucial and tells exactly what is wrong.
Now I do not know SQLite3 so perhaps I'm not the right person to investigate, but I think you are messing around with the variable "conn". You seem to expect a global version and a local version. In "create_connection()" you create a connection (conn is local in that function). In line 21 you use the function but you do not assign the result to anyting, so the result of the connection gets lost.
In "main()" line 48 you do it right:

conn = create_connection(database)

But the "conn" variable remains local in the "main()" function.
In "my_form()" in line 70 things go wrong (as the error message says). You do:

c = conn.cursor()

... but conn is not available, neither local nor global.
I would suggest you remove line 21 (it does nothing in your program, but it may keep a connection open on your database thus using resources) and add a line conn = create_connection(database) to the function "my_form()".
Like I said I do not know SQLite3, but in general everything that has been opened should be closed too. So both the cursor and the connection should be closed somewhere I guess.