Search code examples
pythonflaskflask-sqlalchemyweb-testingpython-webbrowser

Binding columns with a table in SQLAlchemy


I have been trying to build up a webpage using Flask.I am currently on the login form:

from flask import Flask,render_template,request
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.app_context().push()
app.secret_key = 'hello'
app.config['SQLALCHEMY_DATABASE_URI']='sqlite:///users.sqlite3'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=False

db = SQLAlchemy(app)
counter = 1

class users(db.Model):
   id = db.Column('id',db.Integer,primary_key=True)
   name = db.Column('name',db.String(100))
   password = db.Column('password',db.String(100))
   role = db.Column('role',db.String(100))

   def __init__(self,id:int,name:str,password:str,role:str):
      self.name = name
      self.password = password
      self.id = counter
      self.role = role

usr = users(0,'Samantha (Root) Groves','password','Grey-hat')
db.session.add(usr)

@app.route('/',methods=['POST','GET'])
def frontPage():
   if request.method=='POST':
      user = request.form['Username']
      password = request.form['Password']
      found_user = users.query.filter_by(name=user,password=password).first()

      #if found_user:
         #return 'Hello',found_user['name']

      return "Hi"
   else:
      return render_template('mywebsite.html')

and this is my HTML webpage:

<html>
<style>
  button {
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    font-size: medium;
    color: black;
    background-color: aliceblue;
    border-style: hidden;
    shape-outside: border-box;
    cursor: default;
    height: 36px;
    width: 144px;
    border-radius: 9px;
    top: 500px;
    left: 680px;
    position: absolute;
  }
  
  button:hover {
    background-color: orangered;
  }
</style>

<head>
  <title>Sign in/Sign up</title>
</head>

<body background="backColorWebsite.png">
  <form action="#" method="post">
    <label id="UsernameLabel" style="font-family: Verdana, Geneva, Tahoma, sans-serif; background-color: aliceblue;position:absolute; top: 250px;left:600px; width: 144px; height: 18px; border-radius:9px; text-align: center; font-size: medium; ">Username:</label>
    <input type="text" name="Username" style="font-family:Verdana, Geneva, Tahoma, sans-serif; background-color: aliceblue; position: absolute; top:250px;left:760px; width:144px; height:27px; border-radius: 9px;" maxlength="10"></input>
    <label id="PasswordLabel" style="font-family: Verdana, Geneva, Tahoma, sans-serif; background-color: aliceblue;position:absolute; top: 350px;left:600px; width: 144px; height: 18px; border-radius:9px; text-align: center; font-size: medium;">Password:</label>
    <input type="password" name="Password" style="font-family:Verdana, Geneva, Tahoma, sans-serif; background-color: aliceblue; position: absolute; top:350px;left:760px; width:144px; height:27px; border-radius: 9px;"></input>
    <button value="submit">Sign in</button>
  </form>
</body>

</html>

When I run it on cmd it prints out this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users
[SQL: SELECT users.id AS users_id, users.name AS users_name, users.password AS users_password, users.role AS users_role
FROM users
WHERE users.name = ? AND users.password = ?
 LIMIT ? OFFSET ?]
[parameters: ('', '', 1, 0)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
127.0.0.1 - - [17/Nov/2023 01:36:40] "POST / HTTP/1.1" 500 -

so obviously there isnt a table assosciated with the columns but how do we bind a table to the columns?

the db.create_all() method binds it to a key and other than this I dont see a function which could serve out my desired function.

there is the db.Table but I am following a series of videos starting with this but it says you are not required so I am a little bit confused.


Solution

  • Check Flask-SQLAlchemy documentation, about creating tables.

    After all models and tables are defined, call SQLAlchemy.create_all() to create the table schema in the database. This requires an application context. Since you’re not in a request at this point, create one manually.

    with app.app_context():
        db.create_all()
    

    If you define models in other modules, you must import them before calling create_all, otherwise SQLAlchemy will not know about them.

    So you are going to need to execute db.create_all() to create the table users.