datatables

Installation

The package is available on PyPI and is tested on Python 2.7 to 3.4

pip install datatables

Usage

Using Datatables is simple. Construct a DataTable instance by passing it your request parameters (or another dict-like object), your model class, a base query and a set of columns. The columns list can contain simple strings which are column names, or tuples containing (datatable_name, model_name), (datatable_name, model_name, filter_function) or (datatable_name, filter_function).

Additional data such as hyperlinks can be added via DataTable.add_data, which accepts a callable that is called for each instance. Check out the usage example below for more info.

Example

models.py

class User(Base):
    __tablename__ = 'users'

    id          = Column(Integer, primary_key=True)
    full_name   = Column(Text)
    created_at  = Column(DateTime, default=datetime.datetime.utcnow)

    # Use lazy=joined to prevent O(N) queries
    address     = relationship("Address", uselist=False, backref="user", lazy="joined")

class Address(Base):
    __tablename__ = 'addresses'

    id          = Column(Integer, primary_key=True)
    description = Column(Text, unique=True)
    user_id     = Column(Integer, ForeignKey('users.id'))

views.py (pyramid)

@view_config(route_name="data", request_method="GET", renderer="json")
def users_data(request):
    # User.query = session.query(User)
    table = DataTable(request.GET, User, User.query, [
        "id",
        ("name", "full_name", lambda i: "User: {}".format(i.full_name)),
        ("address", "address.description"),
    ])
    table.add_data(link=lambda o: request.route_url("view_user", id=o.id))
    table.searchable(lambda queryset, user_input: perform_search(queryset, user_input))
    table.searchable_column(
        lambda model_column, queryset, user_input:
            perform_column_search(model_column, queryset, user_input)
    )

    return table.json()

views.py (flask)

@app.route("/data")
def datatables():
    table = DataTable(request.args, User, db.session.query(User), [
        "id",
        ("name", "full_name", lambda i: "User: {}".format(i.full_name)),
        ("address", "address.description"),
    ])
    table.add_data(link=lambda obj: url_for('view_user', id=obj.id))
    table.searchable(lambda queryset, user_input: perform_search(queryset, user_input))
    table.searchable_column(
        lambda model_column, queryset, user_input:
            perform_column_search(model_column, queryset, user_input)
    )

    return json.dumps(table.json())

Global and individual column searching

def perform_search(queryset, user_input):
    return queryset.filter(
        db.or_(
            User.full_name.like('%' + user_input + '%'),
            Address.description.like('%' + user_input + '%')
            )
        )

def perform_column_search(model_column, queryset, user_input):
    return queryset.filter(model_column.like("%" + user_input + "%"))

template.jinja2

<table class="table" id="clients_list">
    <thead>
        <tr>
            <th>Id</th>
            <th>User name</th>
            <th>Address</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

<script>
    $("#clients_list").dataTable({
        serverSide: true,
        processing: true,
        ajax: "{{ request.route_url("data") }}",
        columns: [
            {
                data: "id",
                "render": function(data, type, row){
                    return $("<div>").append($("<a/>").attr("href", row.DT_RowData.link).text(data)).html();
                }
            },
            { data: "name" },
            { data: "address" }
        ]
</script>