def note_list_json_route(): """list notes, data endpoint""" columns = [ ColumnDT(Note.id, mData='id'), ColumnDT(Host.id, mData='host_id'), ColumnDT(Host.address, mData='host_address'), ColumnDT(Host.hostname, mData='host_hostname'), # break pylint duplicate-code ColumnDT(Service.proto, mData='service_proto'), ColumnDT(Service.port, mData='service_port'), ColumnDT(func.concat_ws('/', Service.port, Service.proto), mData='service'), ColumnDT(Note.xtype, mData='xtype'), ColumnDT(Note.data, mData='data'), ColumnDT(Note.tags, mData='tags'), ColumnDT(Note.comment, mData='comment'), ColumnDT(literal_column('1'), mData='_buttons', search_method='none', global_search=False) ] query = db.session.query().select_from(Note).outerjoin( Host, Note.host_id == Host.id).outerjoin(Service, Note.service_id == Service.id) if 'filter' in request.values: query = apply_filters(query, filter_parser.parse( request.values.get('filter')), do_auto_join=False) notes = DataTables(request.values.to_dict(), query, columns).output_result() return jsonify(notes)
def path(self): return func.concat_ws( sql.text("'/'"), self.python_version, func.substring(self.name, sql.text("1"), sql.text("1")), self.name, self.filename, )
def path(self): return func.concat_ws( "/", self.python_version, func.substring(self.name, 1, 1), self.name, self.filename, )
def get_items(self, **kwargs): event_id = kwargs.get('event_id') lookup_item = kwargs.get('q') deployment = getattr(g, 'deployment', None) if event_id is None: event = getattr(g, 'event', None) else: event = Event.query.filter_by(id=event_id).one() if deployment: deployment_id = deployment.id else: deployment_id = None if event and event.participant_set_id: participant_set_id = event.participant_set_id else: participant_set_id = None full_name_lat_query = ParticipantFullNameTranslations.lateral( 'full_name') first_name_lat_query = ParticipantFirstNameTranslations.lateral( 'first_name') other_names_lat_query = ParticipantOtherNamesTranslations.lateral( 'other_names') last_name_lat_query = ParticipantLastNameTranslations.lateral( 'last_name') queryset = Participant.query.select_from(Participant).join( Participant.participant_set).outerjoin( full_name_lat_query, true()).outerjoin(first_name_lat_query, true()).outerjoin( other_names_lat_query, true()).outerjoin(last_name_lat_query, true()).filter( Participant.participant_set_id == participant_set_id, ParticipantSet.deployment_id == deployment_id, ParticipantSet.id == participant_set_id) if lookup_item: queryset = queryset.filter( or_( text('full_name.value ILIKE :name'), func.btrim( func.regexp_replace( func.concat_ws( ' ', text('first_name.value'), text('other_names.value'), text('last_name.value'), ), r'\s+', ' ', 'g')).ilike(f'%{lookup_item}%'), Participant.participant_id.ilike( bindparam('pid')))).params(name=f'%{lookup_item}%', pid=f'{lookup_item}%') return queryset
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(Service.proto, mData='service_proto'), ColumnDT(Service.port, mData='service_port'), ColumnDT(func.concat_ws('/', Service.port, Service.proto), mData='service'), ColumnDT(Vuln.via_target, mData='via_target'), 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 queryset_(self, query, value): if value: full_name_query = func.jsonb_each_text( Participant.full_name_translations).lateral( 'full_name_translations') first_name_query = func.jsonb_each_text( Participant.first_name_translations).lateral( 'first_name_translations') other_names_query = func.jsonb_each_text( Participant.other_names_translations).lateral( 'other_names_translations') last_name_query = func.jsonb_each_text( Participant.last_name_translations).lateral( 'last_name_translations') subquery = Participant.query.outerjoin( full_name_query, true()).outerjoin(first_name_query, true()).outerjoin( other_names_query, true()).outerjoin(last_name_query, true()).filter( or_( text('full_name_translations.value ILIKE :name'), func.btrim( func.regexp_replace( func.concat_ws( ' ', text('first_name_translations.value'), text('other_names_translations.value'), text('last_name_translations.value'), ), r'\s+', ' ', 'g')).ilike(f'%{value}%'))).params( name=f'%{value}%').with_entities( Participant.id).subquery() return query.join(subquery, subquery.c.id == Participant.id) return query
Street.district_id.label('districtid'), City.id.label('cityid'), City.name.label('city'), func.addr_format_compact(Street.name, House.number, House.second_number, House.number_suffix, House.building, None).label('address'), House.entrances.label('entrnum'), House.postal_code.label('postindex') ).select_from(House).join(Street).join(Street.city).order_by(City.name, Street.name, House.number, House.second_number, House.number_suffix, House.building)) AddrExtraView = View('addr_extra', DBSession.query( House.id.label('houseid'), House.street_id.label('streetid'), Street.district_id.label('districtid'), City.id.label('cityid'), func.concat_ws(' ', func.concat(City.name, ','), func.concat(District.name, ','), func.addr_format(Street.name, Street.prefix, Street.suffix, House.number, House.second_number, House.number_suffix, House.building, None) ).label('address'), House.entrances.label('entrnum'), House.postal_code.label('postindex') ).select_from(House).join(Street).outerjoin(District).join(Street.city).order_by(City.name, District.name, Street.name, House.number, House.second_number, House.number_suffix, House.building)) AddrFullView = View('addr_full', DBSession.query( House.id.label('houseid'), House.street_id.label('streetid'), Street.district_id.label('districtid'), City.id.label('cityid'), City.name.label('city'), func.addr_format(Street.name, Street.prefix, Street.suffix, House.number, House.second_number, House.number_suffix, House.building, None).label('address'), House.entrances.label('entrnum'), House.postal_code.label('postindex')
sql = """ SELECT emp.*, CONCAT_WS('--', s.from_date, s.to_date) AS 'times', s.salary FROM employees emp JOIN salaries s ON emp.emp_no = s.emp_no WHERE emp.emp_no = 10004 """ sql_data = [(d.emp_no, d.birth_date, d.first_name, d.last_name, d.gender, d.hire_date, d.times, d.salary) for d in session.execute(sql)] '''使用 sqlalchemy 方式进行查询''' alchemy_data = session.query(Employee.emp_no, Employee.birth_date, Employee.first_name, Employee.last_name, Employee.gender, Employee.hire_date, func.concat_ws('--', Salary.from_date, Salary.to_date).label('times'), Salary.salary).\ filter(Employee.emp_no==10004, Salary.emp_no==10004).all() '''比较两个结果,应该是True''' for d in zip(sql_data, alchemy_data): print(d) print('第一例结果是:{}'.format(operator.eq(sql_data, alchemy_data))) '''-------------------------------------------------------------------------------------------------''' '''----------------------------------------------第二例----------------------------------------------- 功能说明: 查询主键为 10004 的员工的所有年薪,需 Employees,Salaries,Title 三个表联合查询。 结果是: 返回字段为 emp_no, birth_date, first_name, last_name, gender, hire_date, title(新增字段,需联表 Title), times, salary ''' '''使用 sql 语句方式进行查询''' sql = """ SELECT
'activity_code': act.code, 'activity_date': wi.last_updated, 'canceller': cu.username, 'cancel_date': o.cancel_date, 'cancel_reason': can.comments, 'charge_date': pay.payment_date, 'client': sc.name, 'comments': select([func.group_concat(oc.txt)]) .where(o.order_id == oc.order_id) .correlate(o.__table__), 'cover_color': cc.name, 'cover_material': cm.name, 'currency': cur.code, 'customer_name': func.concat_ws(' ', cus.first_name, cus.last_name), 'destination': func.concat_ws(', ', a.city, a.state, a.country), 'email': e.email, 'error_date': err.error_date, 'error_reason': err.error_reason, 'foid': oi.order_item_id, 'gross': oi.gross, 'last_update': func.date(wi.last_updated), 'location': lv.location, 'net': oi.net, 'order_date': func.date(o.order_date), 'order_datetime': o.order_date, 'pages': pi.num_pages, 'total_pages': oi.qty * pi.num_pages, 'partner': par.name, 'pdf': func.concat_ws('/', dwn.uri, pi.product_file),