Skip to content
forked from orf/datatables

About

SQLAlchemy->Datatables

Resources

Stars

0 stars

Watchers

1 watching

Forks

 
 

Latest commit

 

History

56 Commits

Folders and files

Repository files navigation

datatables PyPi Version TravisCI Coverage

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>

About

SQLAlchemy->Datatables

Resources

Stars

0 stars

Watchers

1 watching

Forks

Releases

Packages

Contributors

Languages