flask with data from reflected tables.

[okyere]

@orf Thanks for creating this library. I've been trying to use datatables with my flask application but most examples I see with serverside processing have been php.
I see that your example uses the pyramid framework - which I know nothing about. I'm a newbie and I've used flask for about 3 months now.

1. The rows I'm trying to display are not from a mapped sqlachemy class. I'm using sqlalchemy to reflect an existing table on the schema; and then attempting to display it using datatables. So in the views.py where you pass the User class as an argument to DataTable, I can't do that. Can I still use your library with reflected tables without mapped classes?

2. The way flask's view functions look is different from how pyramid's look. This is my view function that I'm hoping will provide data to the datatables plugin.

   @theapp.route('/upload/preview/', methods=['GET', 'POST'])
   def preview(tablename):
   m = db.MetaData()
   t = db.Table(tablename, m, autoload = True, autoload_with = db.engine)
   results = db.session.query(t).all()
   results = {"data":results}
   resp = make_response(json.dumps(results, cls=DateTimeEncoder, use_decimal=True))
   resp.headers['Content-type'] = "text/plain; charset=utf-8"
   return resp

   A table's name is passed to the view function; I use sqlalchemy to reflect that table to get all the rows of the table; and then I'm attempting provide a response object that contains the data. This is not working & that's why I'm attempting to use your library. Since I don't use requests explicitly in flask, how do I get the request.GET parameter to pass to DataTables?

3. You have table.add_data and table.searchable in the views function. I think you're trying to add the data to be displayed and make the data searchable respectively. How do I do those in flask? Do I have to write my own search and sort functions?

In brief, I have a simple case of pulling data from a reflected table. I want to be able to display that data in a datatable with serverside processing. Being able to search, sort, and show about 20 rows per page will be ideal. Please help me connect the dots. Thanks.

[cytec]

for searching and stuff, here is how i did it:

function that handles searching:

def perform_search(queryset, user_input):
    return queryset.filter(
        db.or_(
            models.Processed.title.like('%'+user_input+'%'),
            models.Processed.user.like('%'+user_input+'%'),
            models.Processed.platform.like('%'+user_input+'%')
            )
        )

in the view:

table.searchable(lambda queryset, user_input: perform_search(queryset, user_input))

afaik reflection should provide you with a "mapped class" when loaded, have you tried? basically giving the t from your view function above as Class object to Datatables? something like this (untested)

@theapp.route('/upload/preview/', methods=['GET', 'POST'])
def preview(tablename):
    m = db.MetaData()
    t = db.Table(tablename, m, autoload = True, autoload_with = db.engine)
    table = DataTable(request.args, t, db.query(t), [
        "id",
        ("name", "full_name", lambda i: "User: {}".format(i.full_name)),
        ("address", "address.description"),
    ])
    table.searchable(lambda queryset, user_input: perform_search(queryset, user_input))

    ....

[okyere]

cytec, good question. I actually use sqlalchemy extensively in my application; just not this part of the application. See, I'm building a data upload tool. User uploads and excel sheet; the data is taken and written to database. I have models for department, datasource, fields, and field types. Whenever there's a business need for a customer to send me data, new entries for department name, the name of the data source, the column/fields in the data and the types (number, text, etc) those columns have need to be defined. So sqlalchemy takes care of those. When the user actually uploads the data, it goes to a table whose name is one of the attributes of the datasource model. So the tables are created dynamically every time. That is why I have to use reflection to get the table object before querying it. There's not a way to dynamically create models hence my implementation.

Back to my question, I'm trying to add a functionality to the application to where the user can get a preview of the data they just uploaded; and I think datatables plugin will do a good job at it. But I'm doing things in flask and not php or pyramid.

[orf]

Hey there,
It's been a while since I looked at the code, but your use case should be fully supported. Interesting application by the way :)

So @cytec has it right as far as I can see (table.searchable(lambda queryset, user_input: perform_search(queryset, user_input)) should be just table.searchable(perform_search) though!). This library is completely framework agnostic, so the examples being in pyramid don't matter. The Datatables class just takes a dictionary-like object (request.form in flask), a model (so it can get the column definitions), a queryset (so you can pre-filter the results) and a list of columns. None of this has to come from any specific source, and dynamically generating it is an interested case that I hadn't thought of.

So the issue is, can you use Datatables with reflected tables. I've never used them before so I can't answer that, but @cytec's answer is reasonable. The second parameter doesn't have to be a SQLAlchemy model, it just has to be an object with some columns on it. You can get the columns via the .columns (or .c) attribute on any Table. I think this is a list though, so you could either make an object each time with the correct columns:

class FakeModel(object):
   pass

model = FakeModel()
for col in your_table_object.
    column_name = col.name # Or something, probably not right
    setattr(model, column_name, col)

d = DataTables(request.form, model, ...)

Or you could make a pull request to support accepting a list of columns as the second parameter, the only time it is used is in the get_column() function.

Hope this helps!

[okyere]

Thanks guys. Hard as I tried, I couldn't get this to work.