Hello! ๐
- ๐ I'm Tom
- ๐ I live in London
- ๐ I like Python
- ๐ฆ And rust
- ๐ check out my blog
SQLAlchemy->Datatables
License: MIT License
Hello! ๐
I'm not a database expert, however from what I gather, paging results from a query with ORDER BY clause on a column that's not unique and contains lots of repeating values results in artifacts like rows repeating across pages
The solution I have found is including primary in order by, however doing this in datatables involves copypasting whole _json()
method
I'd like to know if my reasoning is sound, and at least suggest that perhaps
filtered_records = query.count()
query = query.slice(start, start + length)
return {
"draw": draw,
"recordsTotal": total_records,
"recordsFiltered": filtered_records,
"data": [
self.output_instance(instance) for instance in query.all()
]
}`
could be refactored for different method, for easier sublassing? :3
If I have users and addresses and i'm creating a table of addresses, i want to also show user name and sort by it, not just by address.user_id. how can i do this?
table = DataTable(request.args, Address, db.session.query(Address).join(User),
[("address_line_1"),
("address_line_2"),
("zip",),
("user", "user_id", lambda i: i.name), <-- this shows name in the table, but sorts by id i think
("user", "user.name"), <-- this does not work at all
])
Hi, not to nit pick but I can't see how searching could possibly work here.
in the init method you define search_func as a lambda that takes two arguments and just returns the first.
I don't see an intuitive way to add a search function so is this a placeholder?
Also, I tried using this with Flask-restful and there is no request.GET method and the multidict that request.values returns isn't parsable in my testing.
I have modified the code so that it works with Flask-restful.
It requires the import
from querystring_parser import parser
and where you pass request.GET in your example I am passing the following
parser.parse(request.query_string)
To get this to work I removed the query_into_dict method and just passed in the vars like:
#columns = self.query_into_dict("columns")
#ordering = self.query_into_dict("order")
#search = self.query_into_dict("search")
columns = self.params["columns"]
ordering = self.params["order"]
search = self.params["search"]
still working out how to get searching working though.
@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.
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?
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?
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.
Something I don't understand. DataTables sends parameters like iSortCol_0=0&sSortDir_0=asc
via GET method (example for ordering in this case) but in your code you expect that we create ourself the parameter with (?):
order=[{"column": 1, "dir": "desc"}]
for i, item in enumerate(order or []):
for key, value in item.items():
x["order[{}][{}]".format(i, key)] = str(value)
before passing it to the DataTable object as first parameter. You mention that we can pass the request parameter but I've seen nothing in your code that deal with the request parameters from DataTables. Am I missing something?
Thanks for you help.
0
down vote
favorite
I am using the same template and example which exists in sqlalchemy Datatable sample in: http://sqlalchemy-datatables.readthedocs.org/en/latest/ my code is the following, my problem is invalid json, that I could not find any problems in it. any helps? The output of rowTable.output_result() is:
{'aaData': [{'1': 'DOC - 1457715381', '0': '60352794'}, {'1': 'DOC - 1457715381', '0': '2768077336'}, {'1': 'DOC - 1457715381', '0': '6247239243'}, {'1': 'DOC - 1457715381', '0': '8257884017'}, {'1': 'DOC - 1457715381', '0': '8508822379'}], 'iTotalRecords': '5', 'sEcho': '1', 'iTotalDisplayRecords': '5'}
ERROR: DataTables warning: table id=mytable - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
MAKO:
@view_config(route_name='doclist', request_method='GET', renderer='service:/templates/partials/doclist.mako', permission = 'login') def viewListDocuments(self): r = self.r creator_id = 18 columns = [] columns.append(ColumnDT('doccode')) columns.append(ColumnDT('doctitle')) query = DBSession.query(Document).filter(Document.creator_id == creator_id) rowTable = DataTables(r.GET, Document, query, columns) return rowTable.output_result()
HTML PAGE:
</table>
<script type="text/javascript" charset="utf-8"> $(document).ready(function(){ $('#mytable').dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": "${request.route_path('doclist')}" }); });
doccode | doctitle |
---|
Hi @orf ,
Thank you for this Python package. It helped me understand how server-side DataTables worked and saved me a lot of work parsing the POST data.
I have successfully implemented a table-level search by calling table.searchable(my_search_function)
. I'm now looking to implement column-level search (columns[1][search][value]).
I would have implemented this in my_search_function
, but unfortunately, I have no access to the datatable object from there, and can therefore not walk datatable.columns to retrieve the column-level search-keys.
Am I missing something?
Thanks.
See error below
File "/app/.heroku/python/lib/python2.7/site-packages/datatables/__init__.py", line 138, in _json
query = self.search_func(query, str(search["value"]))
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe7' in position 4: ordinal not in range(128)
Only ascii search terms work currently
I know this isnt the way it should be done, but this project is truly dead otherwise. So to make it easy to find for others that are in need, this is the library upgraded to use SQLAlchemy2.
I dont know if its the correct way to code this or not, but it does seem to work.
from collections import defaultdict, namedtuple
import re
import inspect
BOOLEAN_FIELDS = (
"search.regex", "searchable", "orderable", "regex"
)
DataColumn = namedtuple("DataColumn", ("name", "model_name", "filter"))
class DataTablesError(ValueError):
pass
class DataTable(object):
def __init__(self, params, model, query, columns):
self.params = params
self.model = model
self.query = query
self.data = {}
self.columns = []
self.columns_dict = {}
self.search_func = lambda qs, s: qs
self.column_search_func = lambda mc, qs, s: qs
for col in columns:
name, model_name, filter_func = None, None, None
if isinstance(col, DataColumn):
self.columns.append(col)
continue
elif isinstance(col, tuple):
# col is either 1. (name, model_name), 2. (name, filter) or 3. (name, model_name, filter)
if len(col) == 3:
name, model_name, filter_func = col
elif len(col) == 2:
# Work out the second argument. If it is a function then it's type 2, else it is type 1.
if callable(col[1]):
name, filter_func = col
model_name = name
else:
name, model_name = col
else:
raise ValueError("Columns must be a tuple of 2 to 3 elements")
else:
# It's just a string
name, model_name = col, col
d = DataColumn(name=name, model_name=model_name, filter=filter_func)
self.columns.append(d)
self.columns_dict[d.name] = d
for column in (col for col in self.columns if "." in col.model_name):
parent_table = getattr(self.model, column.model_name.split(".")[0])
self.query = self.query.join(parent_table)
def query_into_dict(self, key_start):
returner = defaultdict(dict)
# Matches columns[number][key] with an [optional_value] on the end
pattern = "{}(?:\[(\d+)\])?\[(\w+)\](?:\[(\w+)\])?".format(key_start)
columns = (param for param in self.params if re.match(pattern, param))
for param in columns:
column_id, key, optional_subkey = re.search(pattern, param).groups()
if column_id is None:
returner[key] = self.coerce_value(key, self.params[param])
elif optional_subkey is None:
returner[int(column_id)][key] = self.coerce_value(key, self.params[param])
else:
# Oh baby a triple
subdict = returner[int(column_id)].setdefault(key, {})
subdict[optional_subkey] = self.coerce_value("{}.{}".format(key, optional_subkey),
self.params[param])
return dict(returner)
@staticmethod
def coerce_value(key, value):
try:
return int(value)
except ValueError:
if key in BOOLEAN_FIELDS:
return value == "true"
return value
def get_integer_param(self, param_name):
if param_name not in self.params:
raise DataTablesError("Parameter {} is missing".format(param_name))
try:
return int(self.params[param_name])
except ValueError:
raise DataTablesError("Parameter {} is invalid".format(param_name))
def add_data(self, **kwargs):
self.data.update(**kwargs)
def json(self):
try:
return self._json()
except DataTablesError as e:
return {
"error": str(e)
}
def get_column(self, column):
if "." in column.model_name:
column_path = column.model_name.split(".")
relationship = getattr(self.model, column_path[0])
model_column = getattr(relationship.property.mapper.entity, column_path[1])
else:
model_column = getattr(self.model, column.model_name)
return model_column
def searchable(self, func):
self.search_func = func
def searchable_column(self, func):
self.column_search_func = func
def _json(self):
draw = self.get_integer_param("draw")
start = self.get_integer_param("start")
length = self.get_integer_param("length")
columns = self.query_into_dict("columns")
ordering = self.query_into_dict("order")
search = self.query_into_dict("search")
query = self.query
total_records = query.count()
if callable(self.search_func) and search.get("value", None):
query = self.search_func(query, search["value"])
for column_data in columns.values():
search_value = column_data["search"]["value"]
if (
not column_data["searchable"]
or not search_value
or not callable(self.column_search_func)
):
continue
column_name = column_data["data"]
column = self.columns_dict[column_name]
model_column = self.get_column(column)
query = self.column_search_func(model_column, query, str(search_value))
for order in ordering.values():
direction, column = order["dir"], order["column"]
if column not in columns:
raise DataTablesError("Cannot order {}: column not found".format(column))
if not columns[column]["orderable"]:
continue
column_name = columns[column]["data"]
column = self.columns_dict[column_name]
model_column = self.get_column(column)
if isinstance(model_column, property):
raise DataTablesError("Cannot order by column {} as it is a property".format(column.model_name))
query = query.order_by(model_column.desc() if direction == "desc" else model_column.asc())
filtered_records = query.count()
if length > 0:
query = query.slice(start, start + length)
return {
"draw": draw,
"recordsTotal": total_records,
"recordsFiltered": filtered_records,
"data": [
self.output_instance(instance) for instance in query.all()
]
}
def output_instance(self, instance):
returner = {
key.name: self.get_value(key, instance) for key in self.columns
}
if self.data:
returner["DT_RowData"] = {
k: v(instance) for k, v in self.data.items()
}
return returner
def get_value(self, key, instance):
attr = key.model_name
if "." in attr:
tmp_list = attr.split(".")
attr = tmp_list[-1]
for sub in tmp_list[:-1]:
instance = getattr(instance, sub)
if key.filter is not None:
r = key.filter(instance)
else:
r = getattr(instance, attr)
try:
if not inspect.isbuiltin(r):
attributes = vars(r)
values = {}
for attribute in attributes.keys():
if attribute != '_sa_instance_state':
values[attribute] = getattr(r, attribute)
r = values
except Exception as e:
pass
return r() if inspect.isroutine(r) else r
It seems like the package not working on one to many relationships with the secondary table
Great library. Many thanks. You may want to update the PyPi page so that pip pulls the version with the columns search. Also may want to include an example template that uses the column search in the instructions.
Thanks again
Hi ,
I am using this code:
@amazon.route("/data")`
def datatables():
table = DataTable(request.args, Order, db.session.query(Order), [
"id",
("AmazonOrderId", "AmazonOrderId"),
("OrderStatus", "OrderStatus"),
])
return json.dumps(table.json())
But i get {"error": "Parameter draw is missing"}. what am I doing wrong?
Hi,
Thank you for this amazing module! It really does help as all the datatables examples that i've seen are PHP.
I've got a flask-alchemy project with flask-restless for REST API. Would you have an example or explanation of the lines:
@view_config(route_name="data", request_method="GET", renderer="json")
def users_data(request):
In the views.py sample file.
my views are more like
@app.route('/api/user', methods=['GET', 'POST'])
def users_data(request):
Thank you
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.