class TestLinkMethods(unittest.TestCase):
def setUp(self):
self.c = Link()
pass
def tearDown(self):
pass
def test_connectMysql(self):
db = self.c.connectMysql(db = 'maimai')
db.query('show tables')
rows = db.fetchAllRows()
self.assertEqual(type(rows), tuple)
def test_connectRedis(self):
redis = self.c.connectRedis()
keys = redis.keys()
self.assertEqual(type(keys), list)
def test_connectSphinx(self):
sphinx = self.c.connectSphinx()
res = sphinx.query('a')
self.assertIn('status', res)
self.assertIn('matches', res)
class TestLinkMethods(unittest.TestCase):
def setUp(self):
self.c = Link()
pass
def tearDown(self):
pass
def test_connectMysql(self):
db = self.c.connectMysql(db='maimai')
db.query('show tables')
rows = db.fetchAllRows()
self.assertEqual(type(rows), tuple)
def test_connectRedis(self):
redis = self.c.connectRedis()
keys = redis.keys()
self.assertEqual(type(keys), list)
def test_connectSphinx(self):
sphinx = self.c.connectSphinx()
res = sphinx.query('a')
self.assertIn('status', res)
self.assertIn('matches', res)
class Model(object):
"""docstring for Model"""
db = None # mysql
r = None # redis
sp = None # sphinx
# 教育信息对应关系
eduDegreeMap = {
'0': '专科',
'1': '本科',
'2': '硕士',
'3': '博士',
'4': '博士后',
'5': '中学',
'255': '其他',
}
def __init__(self):
self.tool = Tool()
self.link = Link()
if not self.db:
self.db = self.link.connectMysql(db='maimai')
if not self.r:
self.r = self.link.connectRedis()
if not self.sp:
self.sp = self.link.connectSphinx()
cf = ConfigParser()
cf.read('conf/config.ini')
self.imageHost = cf.get('aliyun_oss', 'host')
def getImageUrlFromOSS(self, keyword, imgType='user'):
"""从OSS获取图片地址
Args:
keyword string 关键词, 分别对应类型为(user[id], company[pycompany], school[pyschool])
imgType string 类型：user/company/school
Returns:
The image url saved OSS.
"""
if imgType == 'user':
intId = keyword
url = '/avatar/' + self.tool.md5(u'm_d_' + str(intId)) + '.jpeg'
elif imgType == 'company':
pass
elif imgType == 'school':
pass
else:
return ''
return self.imageHost + url
""" 用户信息 start"""
def getUser(self, intId):
"""
查询用户信息，工作经历，教育经历
Args:
intId - 用户主键id
Returns:
user info dict.
{
'user': [],
'works': [],
'edus': [],
}
"""
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category, create_time from user where id = '%s' " % (
intId)
self.db.query(sql)
userInfo = self.db.fetchOneRow()
user = {
'id': userInfo[0],
'uid': userInfo[1],
'name': userInfo[2],
'company_id': userInfo[3],
'company_name': userInfo[4],
'position': userInfo[5],
# 'avatar': userInfo[6],
'avatar': self.getImageUrlFromOSS(userInfo[0], 'user'),
'gender': userInfo[7],
'rank': userInfo[8],
'loc': userInfo[9],
'trade': userInfo[10],
'trade_category': userInfo[11],
'create_time': str(userInfo[12]),
}
uid = user['uid']
# 工作经历
sql = "select id, uid, name, company_id, company_name, position, description, start_date, end_date, update_time, create_time from work where uid = '%s'" % (
uid)
self.db.query(sql)
works = []
for row in self.db.fetchAllRows():
# 暂时取用serial_info的头像
clogo = 'http://i9.taou.com/maimai/c/interlogo/default.png'
tmp = {
'id':
row[0],
'uid':
row[1],
'name':
row[2],
'company_id':
row[3],
'company_name':
row[4],
'position':
row[5],
'description':
markdown.markdown(row[6],
extensions=['markdown.extensions.nl2br']),
'start_date':
row[7].replace('-', '.'),
'end_date':
row[8].replace('-', '.').replace('None', '至今'),
'update_time':
str(row[9]),
'create_time':
str(row[10]),
'clogo':
clogo,
}
works.append(tmp)
# 教育经历
sql = "select id, uid, name, school, department, degree, start_date, end_date, update_time, create_time from education where uid = '%s'" % (
uid)
self.db.query(sql)
edus = []
for row in self.db.fetchAllRows():
# 暂时取用serial_info的公司头像
schoolUrl = 'http://i9.taou.com/maimai/c/interlogo/default.png'
tmp = {
'id': row[0],
'uid': row[1],
'name': row[2],
'school': row[3],
'department': row[4],
'degree': row[5],
'degree_name': self.eduDegreeMap.get(str(row[5]), '其他'),
'start_date': row[6].replace('-', '.'),
'end_date': row[7].replace('-', '.').replace('None', '至今'),
'update_time': str(row[8]),
'create_time': str(row[9]),
'school_url': schoolUrl,
}
edus.append(tmp)
return {
'user': user,
'works': works,
'edus': edus,
}
def getUserByIds(self, ids):
"""
查询一批用户
Args:
ids[list] - 用户主键id
Returns:
user list.
"""
users = []
if ids:
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where id in (%s)" % (
','.join(ids))
self.db.query(sql)
users = []
uids = []
for row in self.db.fetchAllRows():
uids.append(str(row[1]))
users.append({
'id': row[0],
'uid': row[1],
'name': row[2],
'company_id': row[3],
'company_name': row[4],
'position': row[5],
'avatar': self.getImageUrlFromOSS(row[0], 'user'),
'gender': row[7],
'rank': row[8],
'loc': row[9],
'trade': row[10],
'trade_category': row[11],
})
# 获取最高学历的学校
sql = "select uid, school from education where uid in (%s) group by uid order by degree desc" % (
','.join(uids))
self.db.query(sql)
schoolDict = {}
for row in self.db.fetchAllRows():
schoolDict[row[0]] = row[1]
for row in users:
if schoolDict.has_key(row['uid']):
row['school'] = schoolDict[row['uid']]
else:
row['school'] = 'None'
return users
def getUserByUids(self, uids):
"""
根据uid查询一批用户
Args:
uids[list] - 用户uid列表
Returns:
user list.
"""
users = []
if uids:
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where uid in (%s)" % (
','.join(uids))
self.db.query(sql)
users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], \
avatar=self.getImageUrlFromOSS(row[0], 'user'), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
for row in self.db.fetchAllRows()]
# 获取最高学历的学校
sql = "select uid, school from education where uid in (%s) group by uid order by degree desc" % (
','.join(uids))
self.db.query(sql)
schoolDict = {}
for row in self.db.fetchAllRows():
schoolDict[row[0]] = row[1]
for row in users:
if schoolDict.has_key(row['uid']):
row['school'] = schoolDict[row['uid']]
else:
row['school'] = 'None'
return users
def getUsersByCid(self, cid=0, page=0, pageSize=20):
"""
根据公司id获取用户列表
Args:
cid - 公司id
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
user list.
"""
start = page * pageSize
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where company_id = '%s' limit %s, %s" % (
cid, start, pageSize)
self.db.query(sql)
users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], \
avatar=self.getImageUrlFromOSS(row[0], 'user'), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
for row in self.db.fetchAllRows()]
# users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], avatar=getUserAvatarByLocal(row[0]), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
# for row in g.db.fetchAllRows()]
return users
""" 照片photo start"""
def getPhotos(self, gender=2, page=0, pageSize=20):
"""
获取头像图片列表
Args:
gender - 性别：1男，2女
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
photo list. id is user AUTO_INCREMENT id.
"""
start = page * pageSize
# sql = "select id, avatar, trade_category from user where gender = '%s' and avatar like '%%a160%%' limit %s, %s" % (gender, start, pageSize)
# 随机获取id 条件: gender、160头像、行业trade(IT互联网\通信电子\文化传媒\学生\金融\教育培训)
sql = 'SELECT t1.id, name, position, trade_category ' \
'FROM `user` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `user`) - (SELECT MIN(id) FROM `user`)) + (SELECT MIN(id) FROM `user`)) AS id) AS t2 ' \
'WHERE t1.id >= t2.id AND gender = "%s" AND avatar like "%%a160%%" AND trade IN ("IT互联网", "文化传媒", "学生")'\
'ORDER BY t1.id LIMIT %s' % (gender, pageSize)
self.db.query(sql)
photos = [dict(id=row[0], avatar=self.getImageUrlFromOSS(row[0], 'user'), name=row[1], position=row[2], trade_category=row[3]) \
for row in self.db.fetchAllRows()]
return photos
def getUserAvatarByLocal(self, intId):
""" (该方法已弃用)
从本地获取用户图片
Args:
intId - 用户主键Id
Returns:
image file name.
"""
# 读取配置文件, 获得加密key
cf = ConfigParser()
cf.read('conf/config.ini')
key = cf.get('DES', 'image_key')
# 加密对象
pc = pydes.PyCrypt(key)
encryId = pc.encrypt(intId)
# 由于出现 '/' 问题，导致如法正在写入图片，这里替换 '/' 为 '*_*'
encryId = encryId.replace('/', '*_*')
dirPath = '/users/160/'
fileName = dirPath + encryId + '.jpg'
return fileName
""" 公司信息 start"""
def getCompany(self, page=0, pageSize=20):
"""
查询公司列表
Args:
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
company list.
"""
start = page * pageSize
self.db.query("select company_id from company limit %s, %s" %
(start, pageSize))
company = []
for row in self.db.fetchAllRows():
key = REDIS_PRE + 'company_' + str(row[0])
companyExtend = self.r.hmget(key, 'company_id', 'company_name',
'num', 'trade')
companyExtend = [
x.decode('utf8') for x in companyExtend
if isinstance(x, (bytes))
]
if companyExtend:
oneCompany = {
'company_id': companyExtend[0],
'company_name': companyExtend[1],
'num': companyExtend[2],
'trade': companyExtend[3],
}
company.append(oneCompany)
return company
""" 搜索Search start """
def getSearchIndexByConf(self):
"""
读取配置文件获取搜索的索引
依赖:
local_init.int
"""
cf = ConfigParser()
cf.read('conf/local_init.ini')
# logging.info('test main logger')
result = {}
for section in cf.sections():
result[section] = cf.items(section)
return result
def getSearchBySphinx(self, q, index='*'):
"""
从sphinx搜索结果
Args:
q - 搜索词
index - 需要使用的索引, default: '*'
Returns:
Id list.
"""
res = self.sp.query(q, index)
lastError = self.sp.getLastError()
lastWarning = self.sp.getLastWarning()
if lastError:
logging.error('getSearchBySphinx has error: %s' % (lastError))
if lastWarning:
logging.warning('getSearchBySphinx has warning: %s' %
(lastWarning))
return res
def getSearch(self, strType, q, page=0, pageSize=20):
"""
搜索
Args:
strType - 搜索类型: user/company/school/position
q - 搜索关键词
page - 分页页码
pageSize - 每页数量
Returns:
User List.
"""
# 设置分页
pageSize = min(pageSize, 100)
result = {}
if strType == 'user':
result = self.getSearchUser(q, page, pageSize)
elif strType == 'company':
result = self.getSearchCompany(q, page, pageSize)
elif strType == 'school':
result = self.getSearchSchool(q, page, pageSize)
elif strType == 'position':
result = self.getSearchPosition(q, page, pageSize)
return result
def getSearchUser(self, q, page=0, pageSize=20):
"""
搜索 User
Args:
q - 搜索词
page - 分页参数
Returns:
users list.
"""
self.sp.matchMode(0)
# 设置分页
self.sp.limit(page * pageSize, pageSize)
res = self.getSearchBySphinx(q, index='user')
if res:
ids = []
if res.has_key('matches') and res['matches']:
ids = [str(item['id']) for item in res['matches']]
users = self.getUserByIds(ids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchCompany(self, q, page=0, pageSize=20):
"""
搜索 User
Args:
q - 公司名称
page - 分页参数
Returns:
users list.
"""
# self.sp.matchMode(0)
# 设置分页
self.sp.limit(page * pageSize, pageSize)
res = self.getSearchBySphinx(q, index='company')
if res:
ids = []
if res.has_key('matches') and res['matches']:
ids = [str(item['id']) for item in res['matches']]
users = self.getUserByIds(ids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchSchool(self, q, page=0, pageSize=20):
"""
搜索 User
Args:
q - 学校名称
page - 分页参数
Returns:
users list.
"""
## search语句
# ./search -c /usr/local/coreseek/etc/csft.conf --index school --sortby "uid asc" --filter degree 255 --group uid 北京大学
# self.sp.matchMode(0)
# 设置分页
self.sp.limit(page * pageSize, pageSize)
# @todo ，后期增加过滤
# self.filter('degree', [255])
self.sp.groupby('uid')
res = self.getSearchBySphinx(q, index='school')
if res:
uids = []
if res.has_key('matches') and res['matches']:
uids = [str(item['uid']) for item in res['matches']]
users = self.getUserByUids(uids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchPosition(self, q, page=0, pageSize=20):
"""
搜索 User
Args:
q - 职位
page - 分页参数
Returns:
users list.
"""
# self.sp.matchMode(0)
# ./search -c /usr/local/coreseek/etc/csft.conf --index position --group uid php
# 设置分页
self.sp.limit(page * pageSize, pageSize)
self.sp.groupby('uid')
res = self.getSearchBySphinx(q, index='position')
if res:
uids = []
if res.has_key('matches') and res['matches']:
uids = [str(item['uid']) for item in res['matches']]
users = self.getUserByUids(uids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchSuggest(self, q, type='company'):
"""
搜索推荐
Args:
q - 搜索关键词
type - 搜索类型, [company/school/position]
Returns:
suggestion string list.
"""
limitNum = 5 # 默认只取5条
result = []
if type == 'company':
sql = "select company_name from company where company_name like '%%%s%%' limit %s" % (
q, limitNum)
self.db.query(sql)
result = [row[0] for row in self.db.fetchAllRows()]
elif type == 'school':
# ./search -c /usr/local/coreseek/etc/csft.conf --index school --group school --groupsort '@count desc' 北方工业大学
self.sp.groupby('school')
self.sp.limit(0, limitNum)
res = self.getSearchBySphinx(q, index='school')
if res:
result = []
if res.has_key('matches') and res['matches']:
result = [str(item['school']) for item in res['matches']]
elif type == 'position':
# ./search -c /usr/local/coreseek/etc/csft.conf --index position --group position --groupsort '@count desc' php
self.sp.groupby('position')
self.sp.limit(0, limitNum)
res = self.getSearchBySphinx(q, index='position')
if res:
result = []
if res.has_key('matches') and res['matches']:
result = [str(item['position']) for item in res['matches']]
return result
def __del__(self):
""" 资源释放（GC自动调用）"""
if self.db is not None:
self.db.close()
class Model(object):
"""docstring for Model"""
db = None # mysql
r = None # redis
sp = None # sphinx
# 教育信息对应关系
eduDegreeMap = {
'0': '专科',
'1': '本科',
'2': '硕士',
'3': '博士',
'4': '博士后',
'5': '中学',
'255': '其他',
}
def __init__(self):
self.tool = Tool()
self.link = Link()
if not self.db:
self.db = self.link.connectMysql(db = 'maimai')
if not self.r:
self.r = self.link.connectRedis()
if not self.sp:
self.sp = self.link.connectSphinx()
cf = ConfigParser()
cf.read('conf/config.ini')
self.imageHost = cf.get('aliyun_oss', 'host')
def getImageUrlFromOSS(self, keyword, imgType = 'user'):
"""从OSS获取图片地址
Args:
keyword string 关键词, 分别对应类型为(user[id], company[pycompany], school[pyschool])
imgType string 类型：user/company/school
Returns:
The image url saved OSS.
"""
if imgType == 'user':
intId = keyword
url = '/avatar/' + self.tool.md5(u'm_d_' + str(intId)) + '.jpeg'
elif imgType == 'company':
pass
elif imgType == 'school':
pass
else:
return ''
return self.imageHost + url
""" 用户信息 start"""
def getUser(self, intId):
"""
查询用户信息，工作经历，教育经历
Args:
intId - 用户主键id
Returns:
user info dict.
{
'user': [],
'works': [],
'edus': [],
}
"""
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category, create_time from user where id = '%s' " % (intId)
self.db.query(sql)
userInfo = self.db.fetchOneRow()
user = {
'id': userInfo[0],
'uid': userInfo[1],
'name': userInfo[2],
'company_id': userInfo[3],
'company_name': userInfo[4],
'position': userInfo[5],
# 'avatar': userInfo[6],
'avatar': self.getImageUrlFromOSS(userInfo[0], 'user'),
'gender': userInfo[7],
'rank': userInfo[8],
'loc': userInfo[9],
'trade': userInfo[10],
'trade_category': userInfo[11],
'create_time': str(userInfo[12]),
}
uid = user['uid']
# 工作经历
sql = "select id, uid, name, company_id, company_name, position, description, start_date, end_date, update_time, create_time from work where uid = '%s'" % (uid)
self.db.query(sql)
works = []
for row in self.db.fetchAllRows():
# 暂时取用serial_info的头像
clogo = 'http://i9.taou.com/maimai/c/interlogo/default.png'
tmp = {
'id': row[0],
'uid': row[1],
'name': row[2],
'company_id': row[3],
'company_name': row[4],
'position': row[5],
'description': markdown.markdown(row[6], extensions=['markdown.extensions.nl2br']),
'start_date': row[7].replace('-','.'),
'end_date': row[8].replace('-','.').replace('None','至今'),
'update_time': str(row[9]),
'create_time': str(row[10]),
'clogo': clogo,
}
works.append(tmp)
# 教育经历
sql = "select id, uid, name, school, department, degree, start_date, end_date, update_time, create_time from education where uid = '%s'" % (uid)
self.db.query(sql)
edus = []
for row in self.db.fetchAllRows():
# 暂时取用serial_info的公司头像
schoolUrl = 'http://i9.taou.com/maimai/c/interlogo/default.png'
tmp = {
'id': row[0],
'uid': row[1],
'name': row[2],
'school': row[3],
'department': row[4],
'degree': row[5],
'degree_name': self.eduDegreeMap.get(str(row[5]), '其他'),
'start_date': row[6].replace('-','.'),
'end_date': row[7].replace('-','.').replace('None','至今'),
'update_time': str(row[8]),
'create_time': str(row[9]),
'school_url': schoolUrl,
}
edus.append(tmp)
return {
'user': user,
'works': works,
'edus': edus,
}
def getUserByIds(self, ids):
"""
查询一批用户
Args:
ids[list] - 用户主键id
Returns:
user list.
"""
users = []
if ids:
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where id in (%s)" % (','.join(ids))
self.db.query(sql)
users = []
uids = []
for row in self.db.fetchAllRows():
uids.append(str(row[1]))
users.append({
'id': row[0],
'uid': row[1],
'name': row[2],
'company_id': row[3],
'company_name': row[4],
'position': row[5],
'avatar': self.getImageUrlFromOSS(row[0], 'user'),
'gender': row[7],
'rank': row[8],
'loc': row[9],
'trade': row[10],
'trade_category': row[11],
})
# 获取最高学历的学校
sql = "select uid, school from education where uid in (%s) group by uid order by degree desc" % (','.join(uids))
self.db.query(sql)
schoolDict = {}
for row in self.db.fetchAllRows():
schoolDict[row[0]] = row[1]
for row in users:
if schoolDict.has_key(row['uid']):
row['school'] = schoolDict[row['uid']]
else:
row['school'] = 'None'
return users
def getUserByUids(self, uids):
"""
根据uid查询一批用户
Args:
uids[list] - 用户uid列表
Returns:
user list.
"""
users = []
if uids:
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where uid in (%s)" % (','.join(uids))
self.db.query(sql)
users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], \
avatar=self.getImageUrlFromOSS(row[0], 'user'), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
for row in self.db.fetchAllRows()]
# 获取最高学历的学校
sql = "select uid, school from education where uid in (%s) group by uid order by degree desc" % (','.join(uids))
self.db.query(sql)
schoolDict = {}
for row in self.db.fetchAllRows():
schoolDict[row[0]] = row[1]
for row in users:
if schoolDict.has_key(row['uid']):
row['school'] = schoolDict[row['uid']]
else:
row['school'] = 'None'
return users
def getUsersByCid(self, cid = 0, page = 0, pageSize = 20):
"""
根据公司id获取用户列表
Args:
cid - 公司id
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
user list.
"""
start = page * pageSize
sql = "select id, uid, name, company_id, company_name, position, avatar, gender, rank, loc, trade, trade_category from user where company_id = '%s' limit %s, %s" % (cid, start, pageSize)
self.db.query(sql)
users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], \
avatar=self.getImageUrlFromOSS(row[0], 'user'), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
for row in self.db.fetchAllRows()]
# users = [dict(id=row[0], uid=row[1], name=row[2], company_id=row[3], company_name=row[4], position=row[5], avatar=getUserAvatarByLocal(row[0]), gender=row[7], rank=row[8], loc=row[9], trade=row[10], trade_category=row[11]) \
# for row in g.db.fetchAllRows()]
return users
""" 照片photo start"""
def getPhotos(self, gender = 2, page = 0, pageSize = 20):
"""
获取头像图片列表
Args:
gender - 性别：1男，2女
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
photo list. id is user AUTO_INCREMENT id.
"""
start = page * pageSize
# sql = "select id, avatar, trade_category from user where gender = '%s' and avatar like '%%a160%%' limit %s, %s" % (gender, start, pageSize)
# 随机获取id 条件: gender、160头像、行业trade(IT互联网\通信电子\文化传媒\学生\金融\教育培训)
sql = 'SELECT t1.id, name, position, trade_category ' \
'FROM `user` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `user`) - (SELECT MIN(id) FROM `user`)) + (SELECT MIN(id) FROM `user`)) AS id) AS t2 ' \
'WHERE t1.id >= t2.id AND gender = "%s" AND avatar like "%%a160%%" AND trade IN ("IT互联网", "文化传媒", "学生")'\
'ORDER BY t1.id LIMIT %s' % (gender, pageSize)
self.db.query(sql)
photos = [dict(id=row[0], avatar=self.getImageUrlFromOSS(row[0], 'user'), name=row[1], position=row[2], trade_category=row[3]) \
for row in self.db.fetchAllRows()]
return photos
def getUserAvatarByLocal(self, intId):
""" (该方法已弃用)
从本地获取用户图片
Args:
intId - 用户主键Id
Returns:
image file name.
"""
# 读取配置文件, 获得加密key
cf = ConfigParser()
cf.read('conf/config.ini')
key = cf.get('DES', 'image_key')
# 加密对象
pc = pydes.PyCrypt(key)
encryId = pc.encrypt(intId)
# 由于出现 '/' 问题，导致如法正在写入图片，这里替换 '/' 为 '*_*'
encryId = encryId.replace('/', '*_*')
dirPath = '/users/160/'
fileName = dirPath + encryId + '.jpg'
return fileName
""" 公司信息 start"""
def getCompany(self, page = 0, pageSize = 20):
"""
查询公司列表
Args:
page - 分页参数，页码
pageSize - 分页参数，每页数量
Returns:
company list.
"""
start = page * pageSize
self.db.query("select company_id from company limit %s, %s" % (start, pageSize))
company = []
for row in self.db.fetchAllRows():
key = REDIS_PRE + 'company_' + str(row[0])
companyExtend = self.r.hmget(key, 'company_id', 'company_name', 'num', 'trade')
companyExtend = [x.decode('utf8') for x in companyExtend if isinstance(x, (bytes))]
if companyExtend:
oneCompany = {
'company_id': companyExtend[0],
'company_name': companyExtend[1],
'num': companyExtend[2],
'trade': companyExtend[3],
}
company.append(oneCompany)
return company
""" 搜索Search start """
def getSearchIndexByConf(self):
"""
读取配置文件获取搜索的索引
依赖:
local_init.int
"""
cf = ConfigParser()
cf.read('conf/local_init.ini')
# logging.info('test main logger')
result = {}
for section in cf.sections():
result[section] = cf.items(section)
return result
def getSearchBySphinx(self, q, index = '*'):
"""
从sphinx搜索结果
Args:
q - 搜索词
index - 需要使用的索引, default: '*'
Returns:
Id list.
"""
res = self.sp.query(q, index)
lastError = self.sp.getLastError()
lastWarning = self.sp.getLastWarning()
if lastError:
logging.error('getSearchBySphinx has error: %s' % (lastError))
if lastWarning:
logging.warning('getSearchBySphinx has warning: %s' % (lastWarning))
return res
def getSearch(self, strType, q, page = 0, pageSize = 20):
"""
搜索
Args:
strType - 搜索类型: user/company/school/position
q - 搜索关键词
page - 分页页码
pageSize - 每页数量
Returns:
User List.
"""
# 设置分页
pageSize = min(pageSize, 100)
result = {}
if strType == 'user':
result = self.getSearchUser(q, page, pageSize)
elif strType == 'company':
result = self.getSearchCompany(q, page, pageSize);
elif strType == 'school':
result = self.getSearchSchool(q, page, pageSize);
elif strType == 'position':
result = self.getSearchPosition(q, page, pageSize);
return result
def getSearchUser(self, q, page = 0, pageSize = 20):
"""
搜索 User
Args:
q - 搜索词
page - 分页参数
Returns:
users list.
"""
self.sp.matchMode(0)
# 设置分页
self.sp.limit(page*pageSize, pageSize)
res = self.getSearchBySphinx(q, index = 'user')
if res:
ids = []
if res.has_key('matches') and res['matches']:
ids = [ str(item['id']) for item in res['matches']]
users = self.getUserByIds(ids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchCompany(self, q, page = 0, pageSize = 20):
"""
搜索 User
Args:
q - 公司名称
page - 分页参数
Returns:
users list.
"""
# self.sp.matchMode(0)
# 设置分页
self.sp.limit(page*pageSize, pageSize)
res = self.getSearchBySphinx(q, index = 'company')
if res:
ids = []
if res.has_key('matches') and res['matches']:
ids = [ str(item['id']) for item in res['matches']]
users = self.getUserByIds(ids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchSchool(self, q, page = 0, pageSize = 20):
"""
搜索 User
Args:
q - 学校名称
page - 分页参数
Returns:
users list.
"""
## search语句
# ./search -c /usr/local/coreseek/etc/csft.conf --index school --sortby "uid asc" --filter degree 255 --group uid 北京大学
# self.sp.matchMode(0)
# 设置分页
self.sp.limit(page*pageSize, pageSize)
# @todo ，后期增加过滤
# self.filter('degree', [255])
self.sp.groupby('uid')
res = self.getSearchBySphinx(q, index = 'school')
if res:
uids = []
if res.has_key('matches') and res['matches']:
uids = [ str(item['uid']) for item in res['matches']]
users = self.getUserByUids(uids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchPosition(self, q, page = 0, pageSize = 20):
"""
搜索 User
Args:
q - 职位
page - 分页参数
Returns:
users list.
"""
# self.sp.matchMode(0)
# ./search -c /usr/local/coreseek/etc/csft.conf --index position --group uid php
# 设置分页
self.sp.limit(page*pageSize, pageSize)
self.sp.groupby('uid')
res = self.getSearchBySphinx(q, index = 'position')
if res:
uids = []
if res.has_key('matches') and res['matches']:
uids = [ str(item['uid']) for item in res['matches']]
users = self.getUserByUids(uids)
result = {
'users': users,
'time': res['time'],
'total': res['total'],
'total_found': res['total_found'],
}
else:
result = {
'users': [],
'time': '0',
'total': 0,
'total_found': 0,
}
return result
def getSearchSuggest(self, q, type = 'company'):
"""
搜索推荐
Args:
q - 搜索关键词
type - 搜索类型, [company/school/position]
Returns:
suggestion string list.
"""
limitNum = 5 # 默认只取5条
result = []
if type == 'company':
sql = "select company_name from company where company_name like '%%%s%%' limit %s" % (q, limitNum)
self.db.query(sql)
result = [row[0] for row in self.db.fetchAllRows()]
elif type == 'school':
# ./search -c /usr/local/coreseek/etc/csft.conf --index school --group school --groupsort '@count desc' 北方工业大学
self.sp.groupby('school')
self.sp.limit(0, limitNum)
res = self.getSearchBySphinx(q, index = 'school')
if res:
result = []
if res.has_key('matches') and res['matches']:
result = [ str(item['school']) for item in res['matches']]
elif type == 'position':
# ./search -c /usr/local/coreseek/etc/csft.conf --index position --group position --groupsort '@count desc' php
self.sp.groupby('position')
self.sp.limit(0, limitNum)
res = self.getSearchBySphinx(q, index = 'position')
if res:
result = []
if res.has_key('matches') and res['matches']:
result = [ str(item['position']) for item in res['matches']]
return result
def __del__(self):
""" 资源释放（GC自动调用）"""
if self.db is not None:
self.db.close()