How can I create a table if not exist on Flask with SQLAlchemy?

user_3672883 0
Viewed 6549

I am using SQLAlchemy and I have the following code:

Model:

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = {'schema': 'task', 'useexisting': True}
    id = Column(Integer, primary_key=True, autoincrement=True)
    firstname = Column(String)

.env

SQLALCHEMY_DATABASE_URI = os.getenv('SQLALCHEMY_DATABASE_URI')

app.py

def create_app(config_file):
    """Create a Flask application using the app factory pattern."""

    app = Flask(__name__)

    """Load configuration."""

    app.config.from_pyfile(config_file)

    """Init app extensions."""
    from .extensions import db

    db.init_app(app)

This creates the SQLite file if it does not exist, but not the tables of each model.

The question is what can I do in order to create the tables for each model?

flask-sqlalchemy flask sqlalchemy python
2 Answers

Just add:

db.create_all()

in app.py at the end of create_app().

create_all() will create the tables only when they don't exist and would not change the tables created before.

If you want to create the database and the tables from the command line you can just type:

python
from app.py import db
db.create_all()
exit()

The working example:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = "Secret key"
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///my_database.sqlite3"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class Data(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(50))
    email = db.Column(db.String(50))
    phone = db.Column(db.String(50))

db.create_all()

# add a row
# comment out after the 1st run
table_row = Data(name="My Name", email="myemail@mail.com", phone="123456")
db.session.add(table_row)
db.session.commit()
print "A row was added to the table"

# read the data
row = Data.query.filter_by(name="My Name").first()
print "Found:", row.email, row.phone

if __name__ == "__main__":
    app.run(debug=True)

This is for Python 2.7, to run with Python 3.x just change the the print statements to call the print() function.

NOTE:
When using automatic model class constructor the arguments passed to model class constructor must be keyword arguments or there will be an error. Otherwise you can override the __init__() inside Data() class like this:

def __init__(self, name, email, phone, **kwargs):
    super(Data, self).__init__(**kwargs)
    self.name = name
    self.email = email
    self.phone = phone

In that case you don't have to use keyword arguments.

user_13241750

you need first to use Shell Context Processor to load automatically all Model objects

in app.py add

# import all models from all blueprints you have
from .users.models import User


@app.shell_context_processor
def make_shell_context():
    return { 'db': db, 'User': User .. }

and then use Flask shell command

(venv) $ flask shell
>>> db
<SQLAlchemy engine=sqlite:///data-dev.sqlite> # something similar to that
>>> 
>>> User
<class 'api.users.models.User'>
>>> 
>>> # to create database if not exists and all tables, run the command below
>>> db.create_all()

maybe you'll need Flask-Migrate for advanced operations (migrations) on your database: create new table, update tables / fields ...

user_123684190
Related