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(
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 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
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')
'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),