Quick Start

Quick Overview:

Database

Create the model

from sqlalchemy_tools import Database

db = Database('sqlite://')

class User(db.Model):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = db.Column(db.String(25))
    location = db.Column(db.String(50), default="USA")
    last_access = db.Column(db.Datetime)

Retrieve all records

User.query.all()

Create new record

user = User.create(name="Mardix", location="Moon")

# or

user = User(name="Mardix", location="Moon").save()

Get a record by primary key (id)

user = User.get(1234)

Update record from primary key

user = User.get(1234)
if user:
    user.update(location="Neptune")

Update record from query iteration

for user in User.query:
    user.update(last_access=db.utcnow())

Delete a record

user = User.get(1234)
if user:
    user.delete()

Query with filter

all = User.query.filter(User.location == "USA")

for user in users:
    ...

Migration

SqlAlchemyTools handles SQLAlchemy database migrations using Alembic. The database operations are made available through a command-line interface.

Why use SqlAlchemyTools migration vs. Alembic directly

SqlAlchemyTools configures Alembic in the proper way to work with your database whether it is with or without Flask. In terms of the actual database migrations, everything is handled by Alembic so you get exactly the same functionality.

Create manage.py

To support database migrations, you need to create a manage.py file.

The file can be called anything

from sqlalchemy_tools.migration import Migrate, migrate_manager
from sqlalchemy_tools import Database


# create/import your database
db = Database('sqlite:///tmp.db')

# create a `migrate` object that is linked to your database
migrate = Migrate(db)
migrate_manager.set_migrate(migrate)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    surname = db.Column(db.String(128))


if __name__ == '__main__':
    migrate_manager.main()

Initialise migrations folder

The migrations folder need to be initialise which will contain your database versions:

python manage.py init

Create a new migration

Whenever there is a change to the models that you want reflected in the database:

python manage.py migrate -m "Intial migration"

Upgrade database

To upgrade the database to the latest migration:

python manage.py upgrade

Downgrade database

To downgrade the database by 1 migration:

python manage.py downgrade

Help

To see all the commands that are available run:

python manage.py --help

ModelForm

Make a Flask compatible version of the WTForms-Alchemy ModelForm

from sqlalchemy_tools import create_model_form
from sqlalchemy_tools import Database

# create/import your database
db = Database('sqlite:///tmp.db')
ModelForm = create_model_form(db)

class UserForm(ModelForm):
    class Meta:
        model = User
        exclude = ['last_access']