SQLAlchemy create_all() does not create tables

Question

I'm trying to integrate PostgreSQL and SQLAlchemy but SQLAlchemy.create_all() is not creating any tables from my models.

My code:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://login:pass@localhost/flask_app'
db = SQLAlchemy(app)
db.create_all()
db.session.commit()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

admin = User('admin', '[email protected]')
guest = User('guest', '[email protected]')
db.session.add(admin)
db.session.add(guest)
db.session.commit()
users = User.query.all()
print users

But I get this error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "user" does not exist

How can I fix this?

Did you check this from yourapplication import db db.create_all() using shell? — Syed Habib M
– Syed Habib M, Commented Dec 23, 2013 at 13:23
This question is stale, but I came across it while trying to solve the same problem and wanted to add that overriding init without calling super() could be contributing to issues the OP was experiencing. In the OP's case, it's not necessary to override init at all. The OP's iniit function doesn't add anything the superclass isn't already doing. — datasmith
– datasmith, Commented Jan 23, 2018 at 23:07

davidism · Accepted Answer · 2022-11-01 15:25:23Z

You should put your model class before create_all() call, like this:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://login:pass@localhost/flask_app'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

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

    db.session.add(User('admin', '[email protected]'))
    db.session.add(User('guest', '[email protected]'))
    db.session.commit()

    users = User.query.all()
    print(users)

If your models are declared in a separate module, import them before calling create_all().

Say, the User model is in a file called models.py,

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://login:pass@localhost/flask_app'
db = SQLAlchemy(app)

# See important note below
from models import User

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

    db.session.add(User('admin', '[email protected]'))
    db.session.add(User('guest', '[email protected]'))
    db.session.commit()
    
    users = User.query.all()
    print(users)

Important note: It is important that you import your models after initializing the db object since, in your models.py you also need to import the db object from this module.

Wonderful answer. The implemented fix follows a logical order of operations (OOP) for the Flask app. The OOP indicates the db object must first be defined, along with any tables/customizations, before a call to the Python server can be made to create the database and its dependent tables for the "staging area", prior to commiting. In the original post, the db.create_all command was too early in the sequence, hence the error message that the relation 'user' could not be found.
Am I the only one wondering how db.create_all knows that User has been defined?
is there no way to do this using a factory method? app and db variables are scoped, therefore cant be referenced in a separate models file?

Stefano Paviot · Accepted Answer · 2021-01-02 09:01:01Z

If someone is having issues with creating tables by using files dedicated to each model, be aware of running the "create_all" function from a file different from the one where that function is declared. So, if the filesystem is like this:

Root  
--app.py     <-- file from which app will be run
--models
----user.py      <-- file with "User" model
----order.py    <-- file with "Order" model
----database.py <-- file with database and "create_all" function declaration

Be careful about calling the "create_all" function from app.py.

This concept is explained better by the answer to this thread posted by @SuperShoot

Ying · Accepted Answer · 2021-02-07 19:54:47Z

This is probably not the main reason why the create_all() method call doesn't work for people, but for me, the cobbled together instructions from various tutorials have it such that I was creating my db in a request context, meaning I have something like:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

def get_db():
  if 'db' not in g:
    g.db = SQLAlchemy(current_app)
  return g.db

I also have a separate cli command that also does the create_all:

# tasks/db.py
from lib.db import get_db

@current_app.cli.command('init-db')
def init_db():
  db = get_db()
  db.create_all()

I also am using a application factory.

When the cli command is run, a new app context is used, which means a new db is used. Furthermore, in this world, an import model in the init_db method does not do anything, because it may be that your model file was already loaded(and associated with a separate db).

The fix that I came around to was to make sure that the db was a single global reference:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

db = None
def get_db():
  global db
  if not db:
    db = SQLAlchemy(current_app)
  return db

I have not dug deep enough into flask, sqlalchemy, or flask-sqlalchemy to understand if this means that requests to the db from multiple threads are safe, but if you're reading this you're likely stuck in the baby stages of understanding these concepts too.

David Medinets · Accepted Answer · 2024-01-07 17:55:33Z

I used the information above in my db.py file as shown below. I found it important to create separate app.py, db.py, and server.py files to avoid circular dependencies.

from flask_sqlalchemy import SQLAlchemy
from app import app
from werkzeug.security import generate_password_hash

db = SQLAlchemy(app)

#
# The models need to be imported after the db is initialized.
#
from models.user import User
from models.video_attributes import VideoAttributes
from models.model_development import ModelDevelopment

with app.app_context() as ctx:
    ctx.push()
    db.create_all()

    # Check for existing users
    if User.query.count() == 0:
        # Create first user as admin
        db.session.add(User(login="admin", password=generate_password_hash("admin")))
        db.session.commit()

Collectives™ on Stack Overflow

SQLAlchemy create_all() does not create tables

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Related