SqlAlchemyTools

SqlAlchemyTools provides similar functionality to Flask-SqlAlchemy & Flask-Migrate without being dependant on Flask.

Installation

Install SqlAlchemyTools with pip:

pip install sqlalchemy-tools

Features

• Database:
  • Just by instantiating with Database(), SqlAlchemyTools automatically creates the session, model and everything necessary for SQLAlchemy.
  • Works with & without a Flask app with minimal code change
  • Thread safe by using scoped_session
  • Integration with Pandas to allow quick dataframe insertion and retriving queries as dataframes
  • It provides easy methods such as query(), create(), update(), delete(), to select, create, update, delete entries respectively.
  • Autogenerate the __tablename__ as the snake case equivalent on the model name if not explictly defined (not pluralised)
  • It uses Arrow for DateTime
  • DateTime is saved in UTC and uses the ArrowType from the SQLAlchemy-Utils
  • Added some data types: JSONType, EmailType, and the whole SQLAlchemy-Utils Type
  • db.now -> gives you the Arrow UTC type
  • Paginated results
  • Pretty object representation
  • It is still SQLAlchemy. You can access all the SQLAlchemy awesomeness
• Migration:
  • Inbuilt migration support similar to Flask-migrate
  • Create a manage.py file to easily migrate your database
• ModelFrom:
  • Quickly add all the fields of a model to a WTF form
  • Supports include, exclude, only

Contents

• SqlAlchemyTools
• Installation
• Features
• Contents
• Quick Overview:
  • Database
    • Create the model
    • Retrieve all records
    • Create new record
    • Get a record by primary key (id)
    • Update record from primary key
    • Update record from query iteration
    • Delete a record
    • Query with filter
    • SqlAlchemy Mixins
    • Repr
  • Migration
    • Why use SqlAlchemyTools migration vs. Alembic directly
    • Create manage.py
    • Initialise migrations folder
    • Create a new migration
    • Upgrade database
    • Downgrade database
    • Help
  • ModelForm
• How to use

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:
    ...

SqlAlchemy Mixins

A number of other very useful methods are provided by the sqlalchemy-mixins library.

The SerializeMixin and SmartQueryMixin are included in the base model by default.

Repr

The BaseModel has a default __repr__ to provide a human readable representation of the model object.

The default __repr__ will display ModelName(id=id_value)

If you want to change which columns are displayed by the repr then you will need to set some class variables on your Model.

• __repr_attrs__: if set to __all__ then all columns (apart from any excluded) will be included in the repr. If only some columns need to be displayed then set this to a list of the column names you want to include.
• __repr_exclude__: list of the columns you want to specifically exclude if setting __repr_attrs__ to __all__. Primary keys are always included unless explicitly excluded here.
• __repr_max_length__: the max length of a column value before it is cropped.

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)


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']

How to use

Complete guides for the different modules can be found below:

• Database
• Migration