#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Created on 2017-11-20
@author: Negen
"""
import pyes
#创建ES连接，这是我的ip地址，个人本机上就用localhost，默认端口9200
conn = pyes.ES(['10.139.32.155:9200'])


'''
查询语句       must=查询条件      支持分页查询     按照 age 排序
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
'''

#查询first_name为john的人   相当于sql查询 select * from megacrop.employee where first_name regexp '[^a-zA-Z]john[^a-zA-Z]'
must = pyes.QueryStringQuery('john', 'first_name')
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

#查询last_name为Smith的人   相当于sql查询 select * from megacrop.employee where first_name regexp '[^a-zA-Z]Smith[^a-zA-Z]'
must = pyes.QueryStringQuery('Smith', 'last_name')
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where first_name = 'john'   (注意条件在前，值在后)
must = pyes.TermQuery('first_name','john')
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where age = 22 or age = 25   (注意条件在前，值在后)
must = pyes.TermsQuery('age',[22,25])
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where (age = 22 or age = 25) and last_name = 'Smith'
must = [pyes.TermsQuery('age',[22,25]),pyes.QueryStringQuery('Smith','last_name')]
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where first_name = 'john' and last_name = 'Smith'
must = [pyes.QueryStringQuery('john','first_name'),pyes.QueryStringQuery('Smith','last_name')]
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where first_name = 'john' or first_name = 'jack'
must = pyes.TermsQuery('first_name',['john','jack'])
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where age between 21 and 26
must = pyes.RangeQuery(pyes.ESRange('age',from_value=21,to_value=26))
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)

# 相当于sql查询 select * from megacrop.employee where first_name like 'jo%'
must = pyes.PrefixQuery('first_name','jo')
res = conn.search(pyes.BoolQuery(must=must), 'megacrop', 'employee', start=0, size=10, sort='age')
print list(res)