def data(): """Return server side data.""" # defining columns columns = [ ColumnDT(User.id), ColumnDT(User.name, sort_method=func.public.naturalsort(User.name)), ColumnDT(Address.description), ColumnDT(User.created_at) ] # defining the initial query depending on your purpose query = db.session.query().select_from(User).join(Address).filter( Address.id > 14) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def data_yadcf(request): """Return server side data.""" # defining columns columns = [ ColumnDT(User.id, search_method='yadcf_range_number'), ColumnDT(User.name, search_method='yadcf_multi_select'), ColumnDT(Address.description, search_method='yadcf_autocomplete'), ColumnDT(User.birthday, search_method='yadcf_range_date'), ColumnDT(User.age, search_method='yadcf_range_number_slider') ] # defining the initial query depending on your purpose query = DBSession.query().\ select_from(User).\ join(Address).\ filter(Address.id > 4) # instantiating a DataTable for the query and table needed rowTable = DataTables(request.GET, query, columns) # returns what is needed by DataTable return rowTable.output_result()
def test_set_mdata(self): """Test if result's data have mData set.""" self.populate(5) columns = [] columns.append(ColumnDT('id', mData='ID')) columns.append(ColumnDT('name', mData='Username')) columns.append(ColumnDT('address.description', mData='Address')) columns.append(ColumnDT('created_at', mData='Created at')) req = self.create_dt_params() rowTable = DataTables(req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 5 assert 'ID' in res['data'][0] assert 'Username' in res['data'][0] assert 'Address' in res['data'][0] assert 'Created at' in res['data'][0]
def get_supported_vendor_cm_file_format_for_datatable(): """ Get support file format for each vendor and technology """ metadata = MetaData() table = Table('vw_vendor_cm_file_formats', metadata, autoload=True, autoload_with=db.engine) columns = [] for c in table.columns: columns.append(ColumnDT(c, column_name=c.name, mData=c.name)) query = db.session.query(table) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())
def data(): """Return server side data.""" # defining columns columns = [ ColumnDT(ModuleBlob.id), ColumnDT(ModuleBlob.transcription_text_id), ColumnDT(ModuleBlob.created_at), ColumnDT(TranscriptionText.text) ] # defining the initial query depending on your purpose query = db.session.query().select_from(ModuleBlob).join(TranscriptionText) # .filter(Address.id > 14) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def profile_webauthn_list_json_route(): """get registered credentials list for current user""" columns = [ ColumnDT(WebauthnCredential.id, mData='id', search_method='none', global_search=False), ColumnDT(WebauthnCredential.registered, mData='registered'), ColumnDT(WebauthnCredential.name, mData='name'), ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False) ] query = db.session.query().select_from(WebauthnCredential) \ .filter(WebauthnCredential.user_id == current_user.id) \ .order_by(WebauthnCredential.registered.asc()) creds = DataTables(request.values.to_dict(), query, columns).output_result() return Response(json.dumps(creds, cls=SnerJSONEncoder), mimetype='application/json')
def data(): """Return server side data.""" # defining columns columns = [ ColumnDT(contacts.business_name), ColumnDT(contacts.city), ColumnDT(contacts.contact_one), ColumnDT(contacts.contact_two), ColumnDT(contacts.address), ] # defining the initial query depending on your purpose query = db.session.query().select_from(contacts) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def test_null_field_filtering(self): """Test if a None field is not filtered.""" self.populate(5) user6, addr6 = self.create_user('Empty', None) self.session.add(user6) self.session.commit() columns = self.create_columns(['id', 'name', 'address.description', 'created_at']) req = self.create_dt_params() rowTable = DataTables( req, User, self.session.query(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 6 assert res['recordsTotal'] == '6' assert res['recordsFiltered'] == '6'
def urusan_act(request): ses = request.session req = request params = req.params url_dict = req.matchdict if url_dict['act'] == 'grid': columns = [] columns.append(ColumnDT('id')) columns.append(ColumnDT('kode')) columns.append(ColumnDT('nama')) query = DBSession.query(Urusan) rowTable = DataTables(req, Urusan, query, columns) return rowTable.output_result() elif url_dict['act'] == 'headofnama': term = 'term' in params and params['term'] or '' rows = DBSession.query(Urusan.id, Urusan.nama).filter( Urusan.nama.ilike('%%%s%%' % term)).all() r = [] for k in rows: d = {} d['id'] = k[0] d['value'] = k[1] r.append(d) return r elif url_dict['act'] == 'headofkode': term = 'term' in params and params['term'] or '' rows = DBSession.query(Urusan.id, Urusan.kode).filter( Urusan.kode.ilike('%%%s%%' % term)).all() r = [] for k in rows: d = {} d['id'] = k[0] d['value'] = k[1] r.append(d) return r
def get_relations_dt_data(): """Get relations in jQuery datatable data format""" metadata = MetaData() relations_table = Table('vw_relations', metadata, autoload=True, autoload_with=db.engine, schema='live_network') columns = [] for c in relations_table.columns: columns.append(ColumnDT(c, column_name=c.name, mData=c.name)) query = db.session.query(relations_table) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())
def service_grouped_json_route(): """view grouped services, data endpoint""" info_column = service_info_column(request.args.get('crop')) columns = [ ColumnDT(info_column, mData='info'), ColumnDT(func.count(Service.id), mData='cnt_services', global_search=False), ] # join allows filter over host attrs query = db.session.query().select_from(Service).join(Host).group_by( info_column) if 'filter' in request.values: query = apply_filters(query, FILTER_PARSER.parse( request.values.get('filter')), do_auto_join=False) services = DataTables(request.values.to_dict(), query, columns).output_result() return jsonify(services)
def vuln_grouped_json_route(): """view grouped vulns, data endpoint""" columns = [ ColumnDT(Vuln.name, mData='name'), ColumnDT(Vuln.severity, mData='severity'), ColumnDT(Vuln.tags, mData='tags'), ColumnDT(func.count(Vuln.id), mData='cnt_vulns', global_search=False), ] # join allows filter over host attrs query = db.session.query().select_from(Vuln).join(Host).group_by( Vuln.name, Vuln.severity, Vuln.tags) if 'filter' in request.values: query = apply_filters(query, filter_parser.parse( request.values.get('filter')), do_auto_join=False) vulns = DataTables(request.values.to_dict(), query, columns).output_result() return Response(json.dumps(vulns, cls=SnerJSONEncoder), mimetype='application/json')
def test_outerjoin(self): """Test if outerjoin works.""" self.populate(5) f = faker.Faker() a = Address(description=f.address()) self.session.add(a) columns = self.create_columns(['id', 'description', 'user.name']) req = self.create_dt_params() rowTable = DataTables(req, Address, self.session.query(Address).outerjoin(User), columns) res = rowTable.output_result() assert len(res['aaData']) == 6 assert res['iTotalRecords'] == '6' assert res['iTotalDisplayRecords'] == '6' assert res['aaData'][5]['1'] == a.description assert res['aaData'][5]['2'] == 'None'
def list_companies_data(): """Return server side data.""" # defining columns columns = [ ColumnDT(models.Company.id), ColumnDT(models.Company.symbol), ColumnDT(models.Company.name + "|" + models.Company.name_ar), ColumnDT(models.Company.phone), ColumnDT(models.Company.website), ColumnDT( "<a class=\"fa fa-edit\" href=\"{{url_for('website.edit_com', tn=" + models.Company.symbol + ")}}\"></a>") ] # defining the initial query depending on your purpose query = db.session.query().select_from(models.Company).filter(1 == 1) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def data_advanced(request): """Return server side data.""" # defining columns columns = [ ColumnDT(User.id, search_method='numeric'), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.birthday, search_method='date'), ColumnDT(User.age, search_method='numeric') ] # defining the initial query depending on your purpose query = DBSession.query().\ select_from(User).\ join(Address).\ filter(Address.id > 4) # instantiating a DataTable for the query and table needed rowTable = DataTables(request.GET, query, columns) # returns what is needed by DataTable return rowTable.output_result()
def test_global_search_filtering_with_regex(self): """Test if result's are filtered from global search field.""" self.populate(5) user6, addr6 = self.create_user('Run To', 'The Hills') user7, addr7 = self.create_user('Fear Of', 'The Dark') user8, addr8 = self.create_user('More fear of', 'The Daaaaark') self.session.add(user6) self.session.add(user7) self.session.add(user8) self.session.commit() columns = [ ColumnDT(User.id, ), ColumnDT(User.name), ColumnDT(Address.description), ColumnDT(User.created_at), ] req = self.create_dt_params(search='Da*rk') req['search[regex]'] = 'true' rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns, allow_regex_searches=True) res = rowTable.output_result() if 'error' in res: # unfortunately sqlite doesn't support regexp out of the box' assert 'no such function: REGEXP' in res['error'] else: assert len(res['data']) == 1 assert res['recordsTotal'] == '8' assert res['recordsFiltered'] == '8' assert res['data'][0]['2'] == 'The Dark' assert res['data'][1]['2'] == 'The Daaaaark'
def vuln_list_json_route(): """list vulns, data endpoint""" columns = [ ColumnDT(literal_column('1'), mData='_select', search_method='none', global_search=False), ColumnDT(Vuln.id, mData='id'), ColumnDT(Host.id, mData='host_id'), ColumnDT(Host.address, mData='host_address'), ColumnDT(Host.hostname, mData='host_hostname'), ColumnDT(func.concat_ws('/', Service.port, Service.proto), mData='service'), ColumnDT(Vuln.name, mData='name'), ColumnDT(Vuln.xtype, mData='xtype'), ColumnDT(Vuln.severity, mData='severity'), ColumnDT(Vuln.refs, mData='refs'), ColumnDT(Vuln.tags, mData='tags'), ColumnDT(Vuln.comment, mData='comment'), ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False) ] query = db.session.query().select_from(Vuln).outerjoin( Host, Vuln.host_id == Host.id).outerjoin(Service, Vuln.service_id == Service.id) if 'filter' in request.values: query = apply_filters(query, filter_parser.parse( request.values.get('filter')), do_auto_join=False) vulns = DataTables(request.values.to_dict(), query, columns).output_result() return Response(json.dumps(vulns, cls=SnerJSONEncoder), mimetype='application/json')
def data(): from db_models import UnitModel """Return server side data.""" # defining columns columns = [ ColumnDT(UnitModel.id), ColumnDT(UnitModel.name), ColumnDT(UnitModel.value_per_kg), ColumnDT(UnitModel.is_deleted), ColumnDT(UnitModel.created_at), ] # defining the initial query depending on your purpose query = db.session.query().select_from(UnitModel).order_by(UnitModel.id) # GET parameters params = request.args.to_dict() # print(params) # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def team_data_table1(): columns = [ ColumnDT(Team.team_name), ColumnDT(Team.team_city), ColumnDT(Team.team_year) ] # print(request.args) # GET方法从args获取参数 # print(request.form) # POST方法从form获取参数 # request.values # 获取所有参数 if request.method == 'POST': team_name = request.form.get('team_name') # season = request.form.get('season') # team_name = request.args.get('team_name') query = db.session.query().select_from(Team).filter(Team.team_name == team_name) # params = request.args.to_dict() # GET方法 params = request.form.to_dict() # POST方法 rowTable = DataTables(params, query, columns) return jsonify(rowTable.output_result())
def service_list_json_route(): """list services, data endpoint""" columns = [ ColumnDT(Service.id, mData='id'), ColumnDT(Host.id, mData='host_id'), ColumnDT(Host.address, mData='host_address'), ColumnDT(Host.hostname, mData='host_hostname'), ColumnDT(Service.proto, mData='proto'), ColumnDT(Service.port, mData='port'), ColumnDT(Service.name, mData='name'), ColumnDT(Service.state, mData='state'), ColumnDT(Service.info, mData='info'), ColumnDT(Service.tags, mData='tags'), ColumnDT(Service.comment, mData='comment'), ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False) ] query = db.session.query().select_from(Service).outerjoin(Host) if 'filter' in request.values: query = apply_filters(query, filter_parser.parse(request.values.get('filter')), do_auto_join=False) services = DataTables(request.values.to_dict(), query, columns).output_result() return jsonify(services)
def test_fields_global_search_filtering_with_regex(self): """Test if result's are filtered from global search field.""" columns = [ColumnDT(User.id, ), ColumnDT(User.name)] query = self.session.query().select_from(User) params = self.create_dt_params(columns, search='Fe*ar') params['search[regex]'] = 'true' rowTable = DataTables(params, query, columns, allow_regex_searches=True) res = rowTable.output_result() if 'error' in res: # unfortunately sqlite doesn't support regexp out of the box' assert 'no such function: REGEXP' in res['error'] else: assert len(res['data']) == 1 assert res['recordsTotal'] == '1' assert res['recordsFiltered'] == '1' assert res['data'][0]['1'] == 'Feeeeear Of'
def get_dt_data(): """Get baseline values in jQuery datatables format""" # Define columns columns = [ ColumnDT(NetworkBaselineView.vendor, column_name="vendor", mData="vendor"), ColumnDT(NetworkBaselineView.technology, column_name="technology", mData="technology"), ColumnDT(NetworkBaselineView.mo, column_name="mo", mData="mo"), ColumnDT(NetworkBaselineView.parameter, column_name="parameter", mData="parameter"), ColumnDT(NetworkBaselineView.value, column_name="value", mData="value"), ColumnDT(NetworkBaselineView.date_added, column_name="date_added", mData="date_added"), ColumnDT(NetworkBaselineView.date_modified, column_name="date_modified", mData="date_modified") ] query = db.session.query( NetworkBaselineView.vendor, NetworkBaselineView.technology, NetworkBaselineView.mo, NetworkBaselineView.parameter, NetworkBaselineView.value, NetworkBaselineView.date_added, NetworkBaselineView.date_modified) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())
def userTable(): """Return server side data.""" print('stoff') print('creating user table') # defining columns columns = [ ColumnDT(User.id), ColumnDT(User.email), ColumnDT(User.first_name), ColumnDT(User.last_name), ColumnDT(User.lastLoginDate) ] print('params') params = request.args.to_dict() # defining the initial query depending on your purpose query = db.session.query(User.id, User.email, User.first_name, User.last_name, User.lastLoginDate).filter(User.is_admin == False) print('query: ', query) params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) print('params: ', params) print('cols: ', columns) print('rowTable: ', rowTable.output_result()) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def get_rule_data(audit_id): """Get fields in audit data table""" rule = AuditRule.query.filter_by(pk=audit_id).first() metadata = MetaData() rule_table = Table(rule.table_name, metadata, autoload=True, autoload_with=db.engine, schema='network_audit') query = db.session.query(rule_table) columns = [] for c in rule_table.columns: columns.append(ColumnDT(c, column_name=c.name, mData=c.name)) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())
def get_relations(): """Get a list of all network relations""" params = request.args.to_dict() params['draw'] = 1 params['length'] = request.args.get("length", 10) params['start'] = request.args.get("start", 0) metadata = MetaData() relations_table = Table('vw_relations', metadata, autoload=True, autoload_with=db.engine, schema='live_network') columns = [] column_index = 0 for c in relations_table.columns: search_method = 'string_contains' if request.args.get( "columns[{}][search][regex]".format(column_index)) == 'true': search_method = 'regex' columns.append( ColumnDT(c, column_name=c.name, mData=c.name, search_method=search_method)) column_index += 1 query = db.session.query(relations_table) row_table = DataTables(params, query, columns) app.logger.info(params) return jsonify(row_table.output_result())
def excl_list_json_route(): """list target exclusions, data endpoint""" columns = [ ColumnDT(Excl.id, mData='id'), ColumnDT(Excl.family, mData='family'), ColumnDT(Excl.value, mData='value'), ColumnDT(Excl.comment, mData='comment'), ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False) ] query = db.session.query().select_from(Excl) if 'filter' in request.values: query = apply_filters(query, filter_parser.parse( request.values.get('filter')), do_auto_join=False) excls = DataTables(request.values.to_dict(), query, columns).output_result() return Response(json.dumps(excls, cls=SnerJSONEncoder), mimetype='application/json')
def data(): """Return server side data""" # defining columns columns = [ ColumnDT(User.id), ColumnDT(User.first_name), ColumnDT(User.last_name), ColumnDT(User.email), ColumnDT(User.username), ColumnDT(User.admin), ColumnDT(User.disabled) ] # defining initial query query = database.session.query().select_from(User) # GET parameters params = request.args.to_dict() # instantiating a DataTable for the query and table needed rowTable = DataTables(params, query, columns) # returns what is needed by DataTable return jsonify(rowTable.output_result())
def test_set_mdata(self): """Test if result's data have mData set.""" self.populate(5) columns = [ ColumnDT(User.id, mData='ID'), ColumnDT(User.name, mData='Username'), ColumnDT(Address.description, mData='Address'), ColumnDT(User.created_at, mData='Created at'), ] req = self.create_dt_params() rowTable = DataTables( req, self.session.query().select_from(User).join(Address), columns) res = rowTable.output_result() assert len(res['data']) == 5 assert 'ID' in res['data'][0] assert 'Username' in res['data'][0] assert 'Address' in res['data'][0] assert 'Created at' in res['data'][0]
def api_User_Info(): columns = [ ColumnDT(Users.id), ColumnDT(Users.name), ColumnDT(Users.role_id) ] query = db.session.query().select_from(Users).filter(Users.role_id == 1) query2 = db.session.query(Users).filter_by(role_id=1) # query = db.session.query().select_from(Team).filter(Team.team_name == 'OKC') # query = Users.query.filter_by(Users.role_id == 1).order_by(Users.id) # 需要draw, length, start三个参数,来正常运行DataTable包 params = request.args.to_dict() rowTable = DataTables(params, query, columns) # print(query.scalar()) # print(query2) # print(query2.all()) # print(query2.all()[3].name) # print(rowTable.output_result()['data']) # print((Users.query.filter(Users.name == 'tuige')).all()) return jsonify(rowTable.output_result())
def get_supported_vendor_technologies_for_datatable(): """ Get supported technologies and vendors in the network """ metadata = MetaData() supported_vendor_tech = Table('vw_supported_vendor_tech', metadata, autoload=True, autoload_with=db.engine, schema='public') columns = [] for c in supported_vendor_tech.columns: columns.append(ColumnDT(c, column_name=c.name, mData=c.name)) query = db.session.query(supported_vendor_tech) # GET request parameters params = request.args.to_dict() row_table = DataTables(params, query, columns) return jsonify(row_table.output_result())