Skip to content

Instantly share code, notes, and snippets.

@hammer
Created January 21, 2014 15:58
Show Gist options
  • Save hammer/8542756 to your computer and use it in GitHub Desktop.
Save hammer/8542756 to your computer and use it in GitHub Desktop.
Implementation of a server-side DataTable (cf. http://datatables.net/release-datatables/examples/data_sources/server_side.html) using Flask, Flask-RESTful, and Psycopg. You should only have to edit source_database, source_table, and source_columns to make it work. Of course you'll probably want to edit the resource name and URL as well.
from string import Template
from distutils.util import strtobool
from flask import Flask, request
from flask.ext.restful import Api, Resource
import psycopg2
# Flask-RESTful Api object
app = Flask(__name__)
api = Api(app)
class MyResource(Resource):
def get(self):
###################
# Setup
###################
# Data source information
source_database = 'my_database'
source_table = 'my_table'
source_columns = ['column1', 'column2', 'column3']
###################
# Build query
###################
# Convenient access to request arguments
rargs = request.args
# Base query
select_clause = 'SELECT %s' % ','.join(source_columns)
from_clause = 'FROM %s' % source_table
# Paging
iDisplayStart = rargs.get('iDisplayStart', type=int)
iDisplayLength = rargs.get('iDisplayLength', type=int)
limit_clause = 'LIMIT %d OFFSET %d' % (iDisplayLength, iDisplayStart) \
if (iDisplayStart is not None and iDisplayLength != -1) \
else ''
# Sorting
iSortingCols = rargs.get('iSortingCols', type=int)
orders = []
for i in range(iSortingCols):
col_index = rargs.get('iSortCol_%d' % i, type=int)
if rargs.get('bSortable_%d' % col_index, type=strtobool):
col_name = source_columns[col_index]
sort_dir = 'ASC' \
if rargs.get('sSortDir_%d' % i) == 'asc' \
else 'DESC NULLS LAST'
orders.append('%s %s' % (col_name, sort_dir))
order_clause = 'ORDER BY %s' % ','.join(orders) if orders else ''
# Filtering ("ac" is "all columns", "pc" is "per column")
ac_search = rargs.get('sSearch')
ac_like_exprs, ac_patterns, pc_like_exprs, pc_patterns = [], [], [], []
for i, col in enumerate(source_columns):
if rargs.get('bSearchable_%d' % i, type=strtobool):
like_expr = Template("$col LIKE %s").safe_substitute(dict(col=col))
if ac_search:
ac_like_exprs.append(like_expr)
ac_patterns.append('%' + ac_search + '%')
pc_search = rargs.get('sSearch_%d' % i)
if pc_search:
pc_like_exprs.append(like_expr)
pc_patterns.append('%' + pc_search + '%')
ac_subclause = '(%s)' % ' OR '.join(ac_like_exprs) if ac_search else ''
pc_subclause = ' AND '.join(pc_like_exprs)
subclause = ' AND '.join([ac_subclause, pc_subclause]) \
if ac_subclause and pc_subclause \
else ac_subclause or pc_subclause
where_clause = 'WHERE %s' % subclause if subclause else ''
sql = ' '.join([select_clause,
from_clause,
where_clause,
order_clause,
limit_clause]) + ';'
###################
# Execute query
###################
cursor = psycopg2.connect("dbname=%s" % source_database).cursor()
cursor.execute(sql, ac_patterns + pc_patterns)
things = cursor.fetchall()
###################
# Assemble response
###################
sEcho = rargs.get('sEcho', type=int)
# 3 queries required with PostgreSQL; very ugly
# Count of all values in table
cursor.execute(' '.join(['SELECT COUNT(*)', from_clause]) + ';')
iTotalRecords = cursor.fetchone()[0]
# Count of all values that satisfy WHERE clause
iTotalDisplayRecords = iTotalRecords
if where_clause:
sql = ' '.join([select_clause, from_clause, where_clause]) + ';'
cursor.execute(sql, ac_patterns + pc_patterns)
iTotalDisplayRecords = cursor.rowcount
response = {'sEcho': sEcho,
'iTotalRecords': iTotalRecords,
'iTotalDisplayRecords': iTotalDisplayRecords,
'aaData': things
}
return response
api.add_resource(MyResource, '/myresource')
@tanyewei
Copy link

class OrderItem(db.Model):
    """
    订单列表
    """
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, default=datetime.datetime.now)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id', ondelete="CASCADE ", onupdate="CASCADE "))
    order = db.relationship('Order', backref=db.backref('Order', lazy='dynamic'))
    lottery_id = db.Column(db.Integer, db.ForeignKey('lottery.id', ondelete="CASCADE ", onupdate="CASCADE "))
    lottery = db.relationship('Lottery', backref=db.backref('Lottery', lazy='dynamic'))


how to filter?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment