Python issues when trying to pull from database using SQLite3

[Reevow]

Hi, I'm a bit of a noob with python flask so I'm unsure if this is an issue for here or for the SQLite (Have already tried flask and databases but their solutions lead me to think this is a issue with the DB but they said to go to python) page or something else, so apologies for that off the bat. But here is my issue. I'm attempting to connect to my database and this seems to work since the print statement returns "Connected to database successfully!".

My form seems to be working correctly too as the output does give the username and password. When I attempt to print the results of the query however, this does not seem to get any data, just returning [].

The database does exist and is in the same directory, i would add an image however, it seems to delete them:/

Does anyone know what the issue would be here? I have been stuck looking for a solution for a week and this is my last resort. Once again, apologies if this is the incorrect subreddit, I'm just stuck and running out of time. If this isn't correct, please could you point me in the right direction🙂

Some extra info: I created the database using command line and then populated this database using a software called TablePlus (not sure if the issue could just be that?). The code and the database are within the same folder (Should i add something?)

 

[menator01]

Please post code instead of images.
I would try and get the python part working first them implement the flask.

A quick example

import sqlite3
connect = sqlite3.connect('user.db')
if connect:
    print(f'Connected to user.db')
cursor = connect.cursor()

def database(name, passwd):
    query = f'select name, passwd from users where name = "{name}" and passwd = "{passwd}"'
    result = cursor.execute(query).fetchall()
    if len(result) > 0:
        print(f'Found user {name}')
        for data in result:
            print(data[0], data[1])
    else:
        print(f'Sorry, {name} is not in database')
# User in database
database('john', 'mypass')
print()
# User not in database
database('mary', 'apass')

Output

Connected to user.db
Found user john
john mypass

Sorry, mary is not in database

 

[menator01]

Here is a example with flask and sqlite3. Hope it helps.

app.py

import sqlite3
from flask import Flask , render_template, url_for, request, redirect, session
from database import Database

app = Flask(__name__)
app.secret_key = 'my secret key'

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

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

@app.route('/validate', methods=['POST', 'GET'])
def validate():
    if request.method == 'POST':
        user = request.form['user']
        passwd = request.form['mypass']
        db = Database()
        if db.getuser(user, passwd):
            session['user'] = user
            session['password'] = passwd
            return redirect(url_for('success'))
        else:
            return render_template('error.html')

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


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

database.py

import sqlite3
import os


class Database:
    def __init__(self):
        self.connection = sqlite3.connect('users.db')
        self.cursor = self.connection.cursor()

    def getuser(self, user, passwd):
        query = f'select user, password from users where user="{user}" and password="{passwd}"'
        return self.cursor.execute(query).fetchone()

Templates

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>FlaskBlog</title>
</head>
<body>
   <h1>Welcome to Flask</h1>
   <a href="{{url_for('form')}}">Login</a>
</body>
</html>

success.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h1>Success Page</h1>
    <h3>Hello {{session.get('user')}}</h3>
   <a href="{{url_for('index')}}">Index</a>

</body>
</html>

form.html

<form action="{{url_for('validate')}}" method="post">
    <table>
        <tr>
            <td>User:</td><td><input type="text", name="user"></td>
        </tr>
        <tr>
            <td>Password:</td><td><input type="password" name="mypass"></td>
        </tr>
        <tr>
            <td><input type="submit" value="Submit"></td>
        </tr>
    </table>
</form>

error.html

<h2>Sorry, sorry that user does not exist.</h2>
<a href="{{url_for('form')}}">Login</a>