Sqlalchemy Create_All() Does Not Create Tables

SQLAlchemy create_all() does not create tables

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', 'admin@example.com'))
    db.session.add(User('guest', 'guest@example.com'))
    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', 'admin@example.com'))
    db.session.add(User('guest', 'guest@example.com'))
    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.

SQLAlchemy create_all() unable to create new tables

I was able to resolve the issue by making use of flask's application context.

As sugested by @noslenkwah, you should use db object from single place by defining into single file database.py.

Here is my solution.

database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

db_models.py

from database import db

class Test(db.Model):
    __tablename__ = 'test'
    
    test_id = db.Column(db.Integer, primary_key=True)

create_table.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import connect_strng
from database import db
    
app = Flask(__name__)
    
app.config['SQLALCHEMY_DATABASE_URI'] = connect_string

with app.app_context():
    db.init_app(app)
    # Added this import just beore create_all
    from db_models import Test, CrawlStat
    db.create_all()
    db.session.commit()

SQLAlchemy does not create tables with db.create_all()

Finally I found the solution in this question. Since I use declarative_base() in my database model I need to use Base.metadata.create_all(db.engine) instead of db.create_all().

That been said, the full solution looks like this:

class APITestUser(unittest.TestCase):
    def setUp(self):
        self.app = create_app(Config.TESTING)
        self.app_context = self.app.app_context()
        self.app_context.push()
        Base.metadata.create_all(db.engine)
        self.client = self.app.test_client()

    def tearDown(self):
        db.session.remove()
        Base.metadata.drop_all(db.engine)
        self.app_context.pop()

db.create_all() not creating tables in Flask-SQLAclchemy

If you were following the flask quick start minimal application, the command worked by default since the User class was in the same place as the db instance. In your case, however, you will have to import the User class as mentioned in the comments from models import User into your app initialization

SQLAlchemy not creating tables

The Base in database.py isn't the same Base that is imported into models.py.

A simple test is to put a print('creating Base') function call just above the Base = declarative_base() statement, and you'll see it is being created twice.

Python calls the module that is being executed '__main__', which you know as you have the if __name__ == '__main__' conditional at the bottom of your module. So the first Base that is created is __main__.Base. Then, in models.py, from database import Base causes the database module to be parsed again, creating database.Base in the namespace, and that is the Base from which User inherits. Then back in database.py, the Base.metadata.create_all(bind=engine) call is using the metadata from __main__.Base which has no tables in it, and as such creates nothing.

Don't execute out of the module that creates the Base instance. Create another module called main.py (or whatever), and move your init_db() function there and import Base, db_session and engine from database.py into main.py. That way, you are always using the same Base instance. This is example of main.py:

from database import Base, db_session, engine
from models import User

def init_db():

    Base.metadata.create_all(bind=engine)

    db_session.add(
        User(username="testuser", password_hash=b"", password_salt=b"", balance=1)
    )
    db_session.commit()

    print("Initialized the db")

if __name__ == "__main__":
    init_db()

db.create_all() doesn't create tables defined in separate file

You created two separate db instances, one along with the app and one along with the models. Each instance has it's own metadata that stores the tables defined on it. The one you're using to issue the create table statement was not the one that the models were defined on. You should use only one instance of the extension, importing it when needed.

myapp/__init__.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

# import the models *after* the db object is defined
from myapp import models

myapp/models.py:

from myapp import db

class User(db.Model):
    ...

create_tables.py:

from myapp import app, db

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

---

Other things to note:

• You should structure your app as a package, so that everything is importable under one location.
• flask.ext is deprecated, import the extension directly from its package name.
• Flask-SQLAlchemy automatically generates __tablename__ from the class name, you don't need to define it yourself.
• You do not have to call commit after create_all.

---

Related Topics