Python Flask OperationalError with SQLAlchemy

[pc510]

Hi all,

I'm following a tutorial on Flask, and I tried rewriting the whole program for practice. I am trying to use a Flask form to submit user input data to a database, but I keep getting this error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user
[SQL: INSERT INTO user (username, email_address, password_hash, budget) VALUES (?, ?, ?, ?)]
[parameters: ('jsc2', '[email protected]', 'apple1', 1000)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

My guess is that I have not set up the database appropriately somewhere, but I am not sure where.

Here is my code

__init__.py

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///market.db'
app.config['SECRET_KEY'] = 'key'

db = SQLAlchemy(app)

from market import routes

forms.py

from flask_wtf import FlaskForm
from wtforms import StringField, PasswordField, SubmitField

class RegisterForm(FlaskForm):
    username = StringField(label='Username:')
    email_address = StringField(label='Email')
    password1 = PasswordField(label='Password')
    password2 = PasswordField(label='Confirm Password')
    submit = SubmitField(label='Create Account')

models.py

from market import db

class User(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.String(30), unique=True, nullable=False)
    email_address = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(60), nullable=False)
    budget = db.Column(db.Integer(), nullable=False, default=1000)

    items = db.relationship('Item', backref='owned_user', lazy=True)

class Item(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(30), nullable=False, unique=True)
    price = db.Column(db.Integer, nullable=False)
    barcode = db.Column(db.String(length=2), nullable=False, unique=True)
    description = db.Column(db.String(length=1000), nullable=False, unique=True)
    owner = db.Column(db.Integer(), db.ForeignKey('user.id'))

    def __repr__(self):
        return f'Item {self.name}'

Any advice here would be very helpful. Thank you!

 

[menator01]

This works for me. No error checking and I have some extra imports due to working on an earlier project.
__init__.py

from flask import Flask, render_template, request, flash, url_for, redirect
from wtforms import Form, StringField, PasswordField, SubmitField, validators
from flask_wtf import FlaskForm
from wtforms.validators import DataRequired
from flask_bootstrap import Bootstrap5
from flask_sqlalchemy import SQLAlchemy

class SimpleForm(FlaskForm):
    username = StringField(label='Username', validators=[DataRequired()])
    email = StringField(label='Email', validators=[DataRequired()])
    password = PasswordField(label='Password', validators=[DataRequired()])
    password2 = PasswordField(label='Verify Password')
    submit = SubmitField(label='Submit')


db = SQLAlchemy()
app = Flask(__name__)
app.secret_key = 'one two three'
Bootstrap5(app)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///project.db'

db.init_app(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String, unique=True, nullable=False)
    password = db.Column(db.String, unique=True, nullable=False)

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

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

@app.route('/register_form', methods=['GET', 'POST'])
def register_form():
    form = SimpleForm()
    return render_template('form.html', form=form)

@app.route('/create', methods=['GET','POST'])
def create():
    if request.method == 'POST':
        user = User(
            username = request.form['username'],
            password = request.form['password'],
            email = request.form['email']
        )

        db.session.add(user)
        db.session.commit()
    flash(f'Successfully registered {user.username}.')
    return redirect(url_for('users'))


@app.route('/users')
def users():
    users = db.session.execute(db.select(User).order_by(User.username)).scalars()
    return render_template('users.html', users=users)

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

users.html

{%extends 'base.html'%}
{% block title %} Users Registery {% endblock %}
{% block content %}
<style>
    table {
        position: relative;
        left: 5%;
        top: 8em;
        width: 90%;
    }

    th {
        border: solid 1px black;
        background-color: coral;
        text-align: center;
        font-size: 2em;
    }

    td {
        border: solid 1px black;
        background-color: lightcoral;
        padding-left: 8px;
    }
</style>

<table>
    <tr>
        <th colspan="4">Registered Users</th>
    </tr>
    <tr>
        <td>ID</td>
        <td>Username</td>
        <td>Password</td>
        <td>Email</td>
    </tr>
    {%for user in users%}
    <tr>
        <td>{{user.id}}</td>
        <td>{{user.username}}</td>
        <td>{{user.password}}</td>
        <td>{{user.email}}</td>
    </tr>
    {%endfor%}
</table>
{%endblock%}

base.html

<!DOCTYPE html>
<html lang="us">
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>{%block title%}My Page{%endblock%}</title>
        {{bootstrap.load_css()}}
        <link rel="stylesheet" href="/static/css/base.css">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    </head>
    <body>
        <div style="position: relative; left: 5%; top: 8px;">
            <a href="{{url_for('index')}}">Home</a> |
            <a href="{{url_for('register_form')}}">Register</a> |
            <a href="{{url_for('users')}}">Users</a>
        </div>
        {% from 'bootstrap5/utils.html' import render_messages%}
        {{render_messages(container=True, dismissible=True, dismiss_animate=True)}}
        {%block content%}{%endblock%}

        {{bootstrap.load_js()}}
        
        <script>
            $(document).ready(function() {
                window.setTimeout(function() {
                    $(".alert").slideUp();
                    $(".alert").alert('close');
                }, 3000);
            });
        </script>

    </body>

</html>

index.html

{%extends 'base.html'%}

{%block title%}Index Page{%endblock%}



{%block content%}
{%endblock%}

form.html

{%extends 'base.html'%}
{%block title%}Register Form Page{%endblock%}
{% block content %}

{% from 'bootstrap5/form.html' import render_field %}
<div style="width:420px; padding:8px; box-shadow: 0 0 4px black;position:relative; left: 20%; top: 120px;">
    <form action="{{url_for('create')}}" method="post">
        {{form.csrf_token()}}
        {{render_field(form.username, placeholder='Enter a username')}}
        {{render_field(form.email, placeholder='Enter your email address')}}
        {{render_field(form.password, placeholder='Enter a password')}}
        {{render_field(form.password2, placeholder='Enter your password again')}}
        {{render_field(form.submit)}}
    </form>
</div>
{% endblock %}

 

[pc510]

This seems to be working for me too. But I am still unsure why mine is giving an error. I also edited the main post to include my 'market.py' file where my table is located.

 

[pc510]

This seems to be working for me too. But I am still unsure why mine is giving an error. I also edited the main post to include my 'market.py' file where my table is located.

Apologies. Unable to edit the main post. Here is the market.py file

{% extends 'base.html' %}
{% block title %}
Market Page Nov 21
{% endblock %}
{% block content %}


<table class="table table-hover table-dark">
  <thead>
  <tr>
    <th scope="col">ID</th>
    <th scope="col">NAME</th>
    <th scope="col">BARCODE</th>
    <th scope="col">PRICE</th>
    <th scope="col">OPTIONS</th>
  </tr>
  </thead>


  <tbody>
  {% for item in items2345 %}
  <!--       each tr tag is responsible for creating a new row inside the table -->
  <tr>
    <td>{{ item.id }}</td>
    <td>{{ item.name }}</td>
    <td>{{ item.barcode }}</td>
    <td>${{ item.price }}</td>
    <td>
      <button class="btn btn-outline btn-info">MORE INFO</button>
      <button class="btn btn-outline btn-success">PURCHASE ITEM</button>
    </td>
  </tr>
  {% endfor %}

  </tbody>
</table>

{% endblock %}

 

[menator01]

le to edit the main post. Here is the market.py file

py or html?

from market import routes

What does the routes look like?

 

[menator01]

If you do not have a working solution by the time I get home from work tonight, zip and attach your files and some mock data and I will start a new project and see if I can help. Please provide the file structure as well.

 

[pc510]

Thanks menator. Here are the files. I keep getting the same error saying that an Item table has not been created.

EDIT: So I am not sure if this solved the underlying issue, but apparently there were two market.db files that might have been causing this error. I had to delete both of them and remake one of them for it to go away. No idea if this get's if solved totally, but at least the app is working for now.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: item
[SQL: SELECT item.id AS item_id, item.name AS item_name, item.price AS item_price, item.barcode AS item_barcode, item.description AS item_description
FROM item]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

From the terminal

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: item
[SQL: INSERT INTO item (name, price, barcode, description) VALUES (?, ?, ?, ?)]
[parameters: ('iphone12', 1000, '88', 'a3')]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

I just attached the code files, not the venv or bin. Wasn't sure if those were needed.

 

[menator01]

Are you wanting something like this?
The file structure is a little different than what you have. You seemed to want to keep thing separated.
Side note port is set to 2020

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies

Files