sqlalchemy-datatables

sqlalchemy-datatables is a library providing an SQLAlchemy integration of jQuery DataTables. It helps you manage server side requests in your application.

It is framework agnostic, tested with Pyramid and Flask mainly.

It only depends on SQLAlchemy, and is compatible with version 1.10.x of DataTables.

Small contribution: In this fork has been added a natural sort method for PostgreSQL models. It needs a custom function to be created in the db:

--
-- This file replaces a previous one which is now available at
-- /junk/naturalsort-hack.sql which had certain drawbacks and
-- was far more complex. This approach is simpler, though it
-- has the drawback of not dealing with locales (all comparisons
-- end up made as if in C locale).
--
-- To use:
--
--  SELECT ... ORDER BY naturalsort(column);
--
-- +optionally,
--
--  CREATE INDEX ON yourtable (naturalsort(column));
--
-- (The basic method is to prefix each numeric substring with its
-- length, then sort as a bytea to get C locale and \x00 delimiters
-- between fragments)
--

create or replace function naturalsort(text)
returns bytea
language sql
immutable strict
as $f$
select string_agg(convert_to(coalesce(r[2],
                            length(length(r[1])::text) || length(r[1])::text || r[1]),
                            'SQL_ASCII'),'\x00')
    from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
$f$;

-- end

Installation

To install the stable version:

pip install sqlalchemy-datatables

Usage

views.py

@view_config(route_name='data', renderer='json')
def data(request):
    """Return server side data."""
    # defining columns
    #  - explicitly cast date to string, so string searching the date
    #    will search a date formatted equal to how it is presented
    #    in the table
    columns = [
        ColumnDT(User.id),
        ColumnDT(User.name),
        ColumnDT(Address.description),
        ColumnDT(func.strftime('%d-%m-%Y', User.birthday)),
        ColumnDT(User.age)
    ]

    # defining the initial query depending on your purpose
    #  - don't include any columns
    #  - if you need a join, also include a 'select_from'
    query = DBSession.query().\
        select_from(User).\
        join(Address).\
        filter(Address.id > 4)

    # instantiating a DataTable for the query and table needed
    rowTable = DataTables(request.GET, query, columns)

    # returns what is needed by DataTable
    return rowTable.output_result()

You can find a detailed working example for Pyramid and Flask in the repository, including per column search, and one with the yadcf plugin.

Pyramid example:

# go to directory
cd sqlalchemy-datatables/examples/pyramid_tut

# install example app
pip install -e .

# initialize the database
initialize_pyramid_tut_db development.ini

# start server
pserve development.ini

# browse to localhost:6543

Documentation

The latest documentation is available online on Read the Docs.

Changelog

All notable changes to this project will be documented in this section.

This project adheres to Semantic Versioning and Keep A Changelog.

License

The project is licensed under the MIT license.