I couldn't create tables with SQLAlchemy on Flask

Question

Hello I am triying to create database and their tables with SQLAlchemy, in order to do this I have the following code and structure:

.
├── api
│   ├── api.py
│   ├── config.py
│   ├── connector.py
│   ├── detector
│   │   ├── __init__.py
│   │   └── route.py
│   └── user
│       ├── __init__.py
│       └── model.py
├── database.db
├── manager.py
├── queue
│   ├── __init__.py
│   └── queue.py
└── requirements.txt

Requirements:

dictalchemy~=0.1.2.7
Flask-RESTful
redis
Flask-SQLAlchemy
Flask-Script
Flask-Migrate
python-dotenv
SQLAlchemy~=1.3.18
Flask~=1.1.2
flask-cors

user.model

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Text
from api.connector import BaseModel, engine


class User(BaseModel):
    __tablename__ = 'users'

    id = Column(
        Integer,
        name='id',
        nullable=False,
        primary_key=True,
        autoincrement=True,
    )

    email = Column(
        Text,
        name='email',
    )

Base.metadata.create_all(engine)

Connector

from contextlib import contextmanager
import functools

from dictalchemy import DictableModel
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///database.db", echo=True)
_db_session = sessionmaker(bind=engine)

BaseModel = declarative_base(cls=DictableModel)

Api

from flask import Flask
from flask_restful import Api
from api.detector.route import Detector
from flask_cors import CORS

app = Flask(__name__)
api = Api(app)

cors = CORS(app, resources={r"/api/*": {"origins": "*"}})

api.add_resource(Detector, '/api/v1.0/det')

Manager

from api.api import app
from flask_script import Manager
from api.connector import engine, BaseModel

from api.detector.route import Detector

manager = Manager(app)
app.config['DEBUG'] = True


@manager.command
def create_tables():
    "Create relational database tables."
    BaseModel.metadata.create_all(engine)

if __name__ == '__main__':
    manager.run()

In order to create tables I use this python manager.py create_tables, that return:

2020-07-23 00:45:15,815 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2020-07-23 00:45:15,815 INFO sqlalchemy.engine.base.Engine () 2020-07-23 00:45:15,816 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2020-07-23 00:45:15,816 INFO sqlalchemy.engine.base.Engine ()

The database file is created but this is empty.

Why the models are not loading on the database?

Thanks.

Answer 1

create_all() creates tables for the models it knows about. If it doesn't know about any, it's perfectly happy to make a database with an empty schema.

It appears as though create_all() runs before user.model has been imported. Fix that, and you should get tables.