sqlite3.OperationalError: no such table, using SQLite connection with flask application

Question

i am learning flask for my newest mission and i try to do the database connection using SQLite, i https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3 to do so i create all demanded files that you will find here and i got no error but when running the flask application i got a sqlite3.OperationalError: no such table. here are my files; first the schema.sql

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

and then it will be used to inisialize the db so here is the init_db.py file

import sqlite3

connection = sqlite3.connect('database.db')


with open('schema.sql') as f:
    connection.executescript(f.read())

cur = connection.cursor()

cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
            ('First Post', 'Content for the first post')
            )

cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
            ('Second Post', 'Content for the second post')
            )

connection.commit()
connection.close()

this will create the database.db file that we will use in the app.py flask application file:

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

app = Flask(__name__)

def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn


@app.route('/db')
def index():
    conn = get_db_connection()
    posts = conn.execute('SELECT * FROM posts').fetchall()
    conn.close()
    return render_template('index.html', posts=posts)

and fianly this is the template file:

{% extends "base.html" %}
{% block title %}
    db Page
{% endblock %}
{% block content %}
    <h1>{% block title %} Posts {% endblock %}</h1>
    {% for post in posts %}
        <div class='post'>
            <p>{{ post['created'] }}</p>
            <h2>{{ post['title'] }}</h2>
            <p>{{ post['content'] }}</p>
        </div>
    {% endfor %}
{% endblock %}

i really lost on this database connection and form creation on flask so if there is anyone that can give me ressource to follow or advice , that will be really helpful. thanks everyone !!!

Answer 1

You need to manually run the init_db file using command python init_db.py first before starting the application using command flask run.