def select_by_name(name):
with DarkBuddyCommonMapper() as mapper:
sql = "select * from dark_buddy_user where name = '{0}'".format(name)
mapper.cursor.execute(sql)
result = mapper.cursor.fetchall()
if len(result):
return result[0]
else:
return None
def select_by_statusId_and_userId(status_id, user_id):
with DarkBuddyCommonMapper() as mapper:
sql = "select * from dark_buddy_user_status where status_id = {0} and user_id = {1}".format(status_id, user_id)
mapper.cursor.execute(sql)
result = mapper.cursor.fetchall()
if len(result):
return result[0]
else:
return None
def update_user_status(user_status):
founded = select_by_statusId_and_userId(user_status['status_id'], user_status['user_id'])
if not founded:
return insert_user_status(user_status)
if user_status.get('value'):
founded['value'] = str(user_status['value'])
with DarkBuddyCommonMapper() as mapper:
sql = "update `dark_buddy_user_status` set `value`='{0}', `update_time`=now() where status_id = {1} and user_id = {2}".format(
founded['value'], founded['status_id'], founded['user_id'])
mapper.cursor.execute(sql)
return founded
def update_user(user):
founded = select_by_senderId(user['sender_id'])
if not founded:
return insert_user(user)
if user.get('name'):
founded['name'] = user['name']
if user.get('status'):
founded['status'] = user['status']
with DarkBuddyCommonMapper() as mapper:
sql = "update `dark_buddy_user` set `name`='{0}', `status`={1}, `update_time`=now() where `sender_id`='{2}'".format(
user['name'], user['status'], user['sender_id'])
mapper.cursor.execute(sql)
return founded
def select_all():
with DarkBuddyCommonMapper() as mapper:
mapper.cursor.execute(
"select * from dark_buddy_dark_spy_words where status = 0")
total_words = mapper.cursor.fetchall()
return total_words
def insert_user(user):
with DarkBuddyCommonMapper() as mapper:
sql = "insert into `dark_buddy_user` (`sender_id`, `name`, `create_time`, `update_time`) VALUES ('{0}', '{1}', now(), now())".format(
user['sender_id'], user['name'])
mapper.cursor.execute(sql)
def select_by_user_id(user_id):
with DarkBuddyCommonMapper() as mapper:
mapper.cursor.execute(
"select * from dark_buddy_sweet_talk where user_id = {0}".format(user_id))
records = mapper.cursor.fetchall()
return records
def select_all():
with DarkBuddyCommonMapper() as mapper:
sql = "select * from dark_buddy_user_status_property"
mapper.cursor.execute(sql)
return mapper.cursor.fetchall()
def insert_message_record(message_record):
with DarkBuddyCommonMapper() as mapper:
sql = "insert into `dark_buddy_message_record` (`user_id`, `message`, `create_time`) VALUES ({0}, '{1}', now())".format(
message_record['user_id'], message_record['message'])
mapper.cursor.execute(sql)
def select_word_frequency():
with DarkBuddyCommonMapper() as mapper:
sql = """select message, count(message) as `count` from dark_buddy_message_record where message not like '**%' and message not like '%:%' and message not like '开启%' group by message order by count(message) desc limit 300"""
return mapper.cursor.execute(sql).fetchall()
def insert_user_status(user_status):
with DarkBuddyCommonMapper() as mapper:
sql = "insert into `dark_buddy_user_status` (`user_id`, `status_id`, `status_code`, `value`, `create_time`, `update_time`) VALUES ({0}, {1}, '{2}', '{3}', now(), now())".format(
user_status['user_id'], user_status['status_id'], user_status['status_code'], str(user_status['value']))
mapper.cursor.execute(sql)