Flask-SQLAlchemy doesn't create table

Question

I am trying to use PostgreSQL with Flask-SQLAlchemy. I made a database named data_collector using pgAdmin4. When I try to create a table it's not getting created. I think the connection to the database is not getting established.

I am trying to run it from cmd as:

from app import db
db.create_all()

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

app=Flask(__name__)
app.config['SQLALCHEMY DATABASE_URI'] = 'postgresql://postgres:postgresql@localhost/data_collector'

db=SQLAlchemy(app)

class Data(db.Model):
    __tablename__="data"
    id=db.Column(db.Integer,primary_key=True)
    email_=db.Column(db.String(120),unique=True)
    height_=db.Column(db.Integer)

    def __init__(self,email_,height_):
        self.email_=email_
        self.height_=height_

db.create_all()

score 1 · Answer 1 · answered Apr 12 '19 at 07:49

1

You didn't commit to the database after creating the tables.
You can do that by:

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

Do something like this.

from flask_sqlalchemy import SQLAlchemy
from flask import Flask


app = Flask(__name__)
db = SQLAlchemy(app)

# ---snip---

with app.app_context():
    db.create_all()
    db.session.commit()    # <- Here commit changes to database


@app.route("/")
def index():
    return "Hello, World!"

This should solve your problem.

If you want to reset(delete) your database then:

with app.app_context():
    db.drop_all()
    db.session.commit()

Nothing is written or deleted or updated in database unless you commit using
db.session.commit()

If you want to revert the changes before comitting use: db.session.rollback()

answered Apr 12 '19 at 07:49

AVX-42

755
2
13
21

I included with app.app_context(): db.create_all() db.session.commit() above class data, its still not working – user11350030 Apr 12 '19 at 09:02
Put that after creating all Classes. – AVX-42 Apr 12 '19 at 09:09
Thank you but still not working.Can you suggest how to check whether the connection is established or not. – user11350030 Apr 12 '19 at 09:20
If connection fails then it will throw an error. Try with a `sqlite` database to see if the tables are created or not. – AVX-42 Apr 12 '19 at 09:22
Failed connection will throw an error. But try this query `SELECT * FROM pg_stat_activity;` from ->https://stackoverflow.com/questions/27435839/how-to-select-list-of-active-connections-to-a-postgresql-database . Also make sure that you installed a database driver like `psychopg2`. – AVX-42 Apr 12 '19 at 09:29
Works fine with sqlite but I have to use postgres.Can you help? – user11350030 Apr 12 '19 at 09:32
Install psycopg2 by `pip3 install psycopg2` if you haven't as SQLAlchemy uses it by default and check your database permissions for the specific user and try to run the above query for checking connections. – AVX-42 Apr 12 '19 at 09:34

score 0 · Answer 2 · answered May 31 '22 at 13:40

Finally got the solution after alot of Googling.

You must import all the models before calling db.create_all() function like this,

def create_db():

from src import models

db.create_all()
db.session.commit()

I have all my models in single file but if you have different files, make sure to import them all.

Flask-SQLAlchemy doesn't create table

2 Answers2