How To Use an SQLite Database in a Flask Application for register / login get & post

Question

Hello everyone, I got a problem when I’m setting the register.html and would like to get the info to the database for login used.I’m struggling the login / register setup… the problem i got from now: sqlite3.OperationalError: table posts has no column named username {% extends 'base.html' %} {% block content %} {% endblock %} @app.route('/register/', methods=('GET', 'POST')) def register(): if request.method == 'POST': username = request.form['username'] email = request.form['email'] phone = request.form['phone'] if not username: flash('Usename is required!') elif not email: flash('Email is required!') elif not phone: flash('Phone is required!') else: conn = get_db_connection() conn.execute ('INSERT INTO posts (username, email, phone) VALUES (?, ?, ?)', (username, email, phone)) conn.commit() conn.close() return redirect(url_for('index')) return render_template('register.html')

Bobby Iliev · Answer

Hi there,

Here is a very informative tutorial on how to use SQLite together with Flask:

https://www.digitalocean.com/community/tutorials/how-to-use-an-sqlite-database-in-a-flask-application

Regarding the error that you are getting, sqlite3.OperationalError: table posts has no column named username, suggests that the database table posts doesn’t have a column named username.

To resolve this issue, you have two options:

Add a username column to the posts table: You would need to alter your posts table schema to include a username column. You can do this with a command like ALTER TABLE posts ADD COLUMN username text;. However, this is assuming that posts is indeed the table where you intend to store your users’ data.
Use a different table for user information: Typically, a posts table would be used to store blog posts or similar content, not user information. If you have a different table intended to store user information (like a users table), you should insert your user information there.

Here’s what your updated code could look like if you had a users table:

@app.route('/register/', methods=('GET', 'POST'))
def register():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        phone = request.form['phone']
        if not username:
            flash('Username is required!')
        elif not email:
            flash('Email is required!')
        elif not phone:
            flash('Phone is required!')
        else:
            conn = get_db_connection()
            conn.execute('INSERT INTO users (username, email, phone) VALUES (?, ?, ?)',
                          (username, email, phone))
            conn.commit()
            conn.close()
            return redirect(url_for('index'))
    return render_template('register.html')

In this example, I replaced posts with users in the SQL query. Now the app will try to insert user information into the users table, which would need to have username, email, and phone columns.

If you don’t have the users table you would need to create it first, here is a basic SQL query to do that:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT NOT NULL
);

This creates a table named users with 4 columns:

id: This is an integer that automatically increments with each new row. It’s the primary key for the table, which means it uniquely identifies each user.
username: This is a text field to hold the username. NOT NULL means this field must contain a value—it can’t be left empty.
email: Another text field, this one for the user’s email address. It also can’t be empty.
phone: A text field for the user’s phone number. It can’t be empty, either.

Note that this table structure is very basic. Depending on your needs, you might want to add additional fields (like a password field if you’re handling authentication), constraints (like UNIQUE to prevent duplicate usernames or emails), or other features. Here is an example with a password column:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    phone TEXT NOT NULL,
    password TEXT NOT NULL
);

Note the UNIQUE keyword added to username and email means that every username and email in the table must be unique.

Hope that this helps!

Best,

Bobby

Question

How To Use an SQLite Database in a Flask Application for register / login get & post

Try DigitalOcean for free

Popular Topics

Get our biweekly newsletter

Hollie's Hub for Good

Become a contributor

Featured on Community

DigitalOcean Products

Welcome to the developer cloud