flask-sqlalchemy-extension 0.0.5

Package provided mixins and extension for sqlalchemy and flask-sqlalchemy packages.

Flask SQLAlchemy Extension

Install

pip install flask-sqlalchemy-extension

Usage

1. Create you own model and extend with mixins.

model.py

from flask_sqlalchemy import SQLAlchemy
from flask_sqlalchemy_extension.mixins import SerializeMixin, DeserializeMixin, QueryMixin

db = SQLAlchemy()


class Category(SerializeMixin, DeserializeMixin, QueryMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)


class Product(SerializeMixin, DeserializeMixin, QueryMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)
    price = db.Column(db.Float, nullable=False)
    quantity = db.Column(db.Integer, nullable=False)

    def in_stock(self):
        return self.quantity > 0


class ProductCategory(SerializeMixin, DeserializeMixin, QueryMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'), nullable=False)
    product = db.relationship('Product', backref=db.backref('categories', lazy=True), lazy='joined')
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)
    category = db.relationship('Category', backref=db.backref('products', lazy=True), lazy='joined')

2. Create you own queries with complex_query function

# Select all products with price lower or equal 100.
products = Product.complex_query(filter_by=dict(
    price__lte=100
))
# Select all products with price lower or equal 100 and name (case insensitive) contains `phone`.
products = Product.complex_query(filter_by=dict(
    price__lte=100,
    name__ilike='phone'
))
# Select all products, order result with price field by ascending.
products = Product.complex_query(order_by=dict(
    price='ASC'
))

3. Use new special functions in REST controller to operate with models.

from flask import Flask, g, request
from flask_sqlalchemy_extension.func import serialize

from model import Product, db  # import your model here 


def create_app():
    app = Flask(__name__)
    # place here your create app code
    return app


app = create_app()
db.init()


# First create Before Request handler, that determinate attribute 
# to manipulate with data from URL Query part.
# e.g. http://<host>/<route>?page=5&per_page=100,
#      http://<host>/<route>?filter_by_age__gte=18
#      http://<host>/<route>?order_by_price=desc
#      http://<host>/<route>?include_something
@app.before_request
def app_context():
    g.per_page = int(request.args.get('per_page', 20))
    g.page = int(request.args.get('page', 1))

    filter_type = request.args.get('filter_type', 'and')
    filter_by = dict((k[len('filter_by_'):], v) for k, v in request.args.items()
                     if k.startswith('filter_by_') and len(k) > len('filter_by_'))
    order_by = dict((k[len('order_by_'):], v) for k, v in request.args.items()
                    if k.startswith('order_by_') and len(k) > len('order_by_'))
    g.complex_query = dict(
        filter_type=filter_type,
        filter_by=filter_by,
        order_by=order_by
    )

    g.includes = list(k[len('include_'):] for k, v in request.args.items()
                      if k.startswith('include_') and len(k) > len('include_'))


# Second create app.route handler and use new extension for easy querying data by filters, ordering,
# spliting by pages and allow include not default serializable or computed fields in response
@app.route('/products', methods=['GET'])
def get_products():
    return serialize(Product.complex_query(**g.complex_query).paginate(page=g.page, per_page=g.per_page),
                     include=g.includes)


# And you can define Create or Update data handler, that allow transfer a json-serializable object and
# easy deserialize it and save in DB. 
# Don't forget check constrains by call check_constrains() methods to prevent DB error with not-null fields.
@app.route('/product', methods=['POST'])
@app.route('/product/<int:id>', methods=['PUT'])
def create_or_update_product(id=None):
    if id is not None:
        product = Product.query.filter_by(id=id).first_or_404()
    else:
        product = Product()
    product.deserialize(request.json).check_constrains()
    db.session.add(product)
    db.session.commit()
    return product.serialize(include=g.includes)

Web API

Pagination

Pagination with REST accept URL Query attributes page (default is 1 in example) and per_page (default is 20 in example). e.g. URL query: http://<host>/products?page=2&per_page=10

Return response:

{
  "pagination": {
    "has_next": <bool>, // has next page (true or false)
    "next_num": <int>,  // next page number (int or None)
    "has_prev": <bool>, // has previous page (true or false)
    "prev_num": <int>,  // previous page number (int or None)
    "page": <int>,      // current page number (int)
    "page_range": [<int>, ..., <int>] // list of available pages (by default 5 pages left and 5 pages right of current page)
  },
  "items": [<obj>, ...] // list of objects in current page
}

Filtering

Filtering with REST accept URL Query attributes starts with filter_by_. Pass field name and filtering modifier split by two underlines after prefix.

Nested filters allowed, split child field from parent with two underline.

Use two and more filters will be added by and operator. For override this pass filter_type=or in URL Query.

Filtering modifier	Meaning
__gte	greater or equal
__gt	strict greater
__lt	strict lower
__lte	lower or equal
__neq or __ne	not equal
__like	pattern search
__like	case insensitive pattern search
__ieq	case insensitive equal
__eq or empty	strict equal

Examples:

1. http://<host>/products?filter_by_price__lte=100. Select all products with price lower or equal 100.
2. http://<host/products?filter_by_price__lte=100&filter_by_name__ilike=phone. Select all products with price lower or equal 100 and name (case insensitive) contains phone.

Includes

Includes with REST accept URL Query attributes starts with include_. Pass field name to include after prefix.

Nested includes allowed, split child field name from parent with dot.

Example:

1. http://<host>/products?include_categories.category&include_in_stock Select all products, include product categories and computed field in_stock.

Ordering

Ordering with REST accept attributes starts with order_by_ and value DESC or ASC.

Nesting ordering allowed. Split child field name from parent with two underlines.

Example:

1. http://<host>/products?order_by_price=ASC Select all products, order result with price field by ascending.