def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "video_info"
self.t_episode = "video_episode"
# !/usr/bin/env python
# coding: utf-8
import os
import sys
from mysqldb_rich import DB, TableDB
from zh_config import db_conf_path
__author__ = 'meisa'
script_dir = os.path.dirname(__file__)
if __name__ == "__main__":
if len(sys.argv) >= 2:
root_password = sys.argv[1]
else:
root_password = "******"
db = DB(conf_path=db_conf_path, user="******", password=root_password)
db.root_init_conf('%')
db2 = TableDB(conf_path=db_conf_path)
db2.create_from_dir(os.path.join(script_dir, "table"))
class Video(object):
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "video_info"
self.t_episode = "video_episode"
def _insert_info(self, video_type, video_no, video_name, video_desc, episode_num, video_pic, accept_formats, adder,
status=1, video_extend=None, link_people=None):
kwargs = dict(video_type=video_type, video_no=video_no, video_name=video_name, video_desc=video_desc,
episode_num=episode_num, status=status, video_extend=video_extend, adder=adder,
video_pic=video_pic, insert_time=int(time.time()), accept_formats=accept_formats,
link_people=link_people)
l = self.db.execute_insert(self.t_info, kwargs=kwargs, ignore=True)
return l
def _insert_episode(self, video_no, episode_index, title, episode_url, episode_pic=None):
kwargs = dict(video_no=video_no, episode_index=episode_index, title=title, episode_pic=episode_pic,
insert_time=int(time.time()), episode_url=episode_url)
l = self.db.execute_insert(self.t_episode, kwargs=kwargs, ignore=True)
return l
def _update_info(self, video_type, video_no, where_cond=None, **update_value):
where_value = dict(video_no=video_no, video_type=video_type)
l = self.db.execute_update(self.t_info, update_value=update_value, where_value=where_value,
where_cond=where_cond)
return l
def _update_num(self, video_type, video_no):
where_value = dict(video_type=video_type, video_no=video_no)
l = self.db.execute_plus(self.t_info, "upload_num", where_value=where_value)
return l
def _update_listen_num(self, video_type, video_no, episode_index):
self.db.execute_plus(self.t_info, "listen_num", where_value=dict(video_no=video_no, video_type=video_type))
self.db.execute_plus(self.t_episode, "listen_num", where_value=dict(video_no=video_no,
episode_index=episode_index))
return True
def _update_status(self, video_no, add_status=None, sub_status=None):
where_value = dict(video_no=video_no)
if add_status is not None:
l = self.db.execute_logic_or(self.t_info, status=add_status, where_value=where_value)
elif sub_status is not None:
l = self.db.execute_logic_non(self.t_info, where_value=where_value, status=sub_status)
else:
l = 0
return l
def new_video(self, video_name, video_type, video_desc, episode_num, video_pic, accept_formats, link_people,
adder):
video_no = uuid.uuid4().hex
l = self._insert_info(video_type, video_no, video_name, video_desc, episode_num, video_pic, accept_formats,
adder, link_people=link_people)
if l <= 0:
return False, l
return True, video_no
def new_video_episode(self, video_type, video_no, episode_index, title, episode_url, episode_pic=None):
l = self._insert_episode(video_no, episode_index, title, episode_url, episode_pic)
if l == 1:
self._update_num(video_type, video_no)
return l
def update_video(self, video_type, video_no, **kwargs):
cols = ["video_name", "video_desc", "episode_num", "video_extend", "video_pic"]
update_value = dict()
for key in kwargs.keys():
if key not in cols:
continue
if kwargs[key] is None:
continue
update_value[key] = kwargs[key]
if "upload_num" in kwargs and "episode_num" in kwargs and kwargs["episode_num"] >= kwargs["upload_num"]:
where_cond = ["upload_num>=%s" % kwargs["upload_num"]]
update_value["upload_num"] = kwargs["upload_num"]
print("gg")
l = self._update_info(video_type, video_no, where_cond=where_cond, **update_value)
if l > 0:
self.delete_episode(video_no, episode_index=kwargs["upload_num"], delete_mode="multi")
else:
l = self._update_info(video_type, video_no, **update_value)
return l
def update_episode(self, video_no, episode_index, title=None, episode_url=None, episode_pic=None):
where_value = dict(video_no=video_no, episode_index=episode_index)
kwargs = dict(title=title, episode_pic=episode_pic, episode_url=episode_url)
l = self.db.execute_update(self.t_episode, update_value=kwargs, where_value=where_value)
return l
def select_video(self, video_type, video_no=None):
where_value = dict()
where_cond = ["status<>0"]
if video_type is not None:
where_value = dict(video_type=video_type)
if video_no is not None:
where_value["video_no"] = video_no
cols = ["video_type", "video_no", "video_name", "video_desc", "episode_num", "adder", "status",
"video_extend", "video_pic", "insert_time", "upload_num", "listen_num", "accept_formats",
"link_people"]
items = self.db.execute_select(self.t_info, cols=cols, where_value=where_value, where_cond=where_cond)
for item in items:
if item["video_extend"] is not None:
item.update(json.loads(item["video_extend"]))
del item["video_extend"]
return items
def select_multi_video(self, video_type, multi_no):
multi_no = filter(lambda x: re.match("^[a-z0-9A-Z]{32}$", x), multi_no)
if len(multi_no) <= 0:
return []
where_value = dict(video_type=video_type)
where_cond = ["status<>0", "video_no in ('%s')" % "','".join(multi_no)]
cols = ["video_type", "video_no", "video_name", "video_desc", "episode_num", "adder", "status",
"video_extend", "video_pic", "insert_time", "upload_num", "listen_num", "accept_formats",
"link_people"]
items = self.db.execute_select(self.t_info, cols=cols, where_value=where_value, where_cond=where_cond)
for item in items:
if item["video_extend"] is not None:
item.update(json.loads(item["video_extend"]))
del item["video_extend"]
return items
def select_episode(self, video_no):
where_value = dict(video_no=video_no)
cols = ["video_no", "episode_index", "title", "episode_url", "episode_pic", "listen_num"]
items = self.db.execute_select(self.t_episode, cols=cols, where_value=where_value)
return items
def add_record(self, video_type, video_no, episode_index):
r = self._update_listen_num(video_type, video_no, episode_index)
return r
def online_video(self, video_no):
l = self._update_status(video_no, add_status=64)
return l
def delete_video(self, video_type, video_no):
l = self._update_info(video_type, video_no, status=0)
return l
def delete_episode(self, video_no, episode_index, delete_mode="single"):
where_value = dict(video_no=video_no)
where_cond = []
if delete_mode == "single":
where_value["episode_index"] = episode_index
else:
where_cond = ["episode_index>%s" % episode_index]
l = self.db.execute_delete(self.t_episode, where_value=where_value, where_cond=where_cond)
return l
class People(object):
@property
def add_time(self):
return int(time.time())
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t = "people_info"
self.t_detail = "people_detail"
self.t_group = "people_group"
self.t_resource = "people_resource"
def _insert_info(self, people_name, people_photo, degree, company,
department, domain, star_level, service_times, labels):
kwargs = dict(people_name=people_name,
degree=degree,
company=company,
department=department,
domain=domain,
star_level=star_level,
service_times=service_times,
labels=labels,
people_photo=people_photo)
kwargs["people_no"] = uuid.uuid4().hex
kwargs["insert_time"] = int(time.time())
self.db.execute_insert(self.t, kwargs)
return kwargs
def _insert_detail(self, people_no, people_profile, tel, work_experience,
study_experience, honor, unit_price):
kwargs = dict(people_profile=people_profile,
work_experience=work_experience,
study_experience=study_experience,
honor=honor,
unit_price=unit_price,
people_no=people_no,
tel=tel)
l = self.db.execute_insert(self.t_detail, kwargs)
return l
def _insert_group(self, group_id, people_no):
kwargs = dict(group_id=group_id,
people_no=people_no,
add_time=self.add_time)
l = self.db.execute_insert(self.t_group, kwargs=kwargs, ignore=True)
return l
def _insert_resource(self, people_no, resource_id):
kwargs = dict(people_no=people_no,
resource_id=resource_id,
add_time=self.add_time)
l = self.db.execute_insert(self.t_resource, kwargs=kwargs, ignore=True)
return l
def _update_info(self, people_no, **kwargs):
where_value = dict(people_no=people_no)
l = self.db.execute_update(self.t,
update_value=kwargs,
where_value=where_value)
return l
def _update_detail(self, people_no, **kwargs):
where_value = dict(people_no=people_no)
l = self.db.execute_update(self.t_detail,
update_value=kwargs,
where_value=where_value)
return l
def _update_status(self, people_no, add_status=None, sub_status=None):
where_value = dict(people_no=people_no)
if add_status is not None:
l = self.db.execute_logic_or(self.t,
status=add_status,
where_value=where_value)
elif sub_status is not None:
l = self.db.execute_logic_non(self.t,
where_value=where_value,
status=sub_status)
else:
l = 0
return l
def _update_num(self, people_no):
where_value = dict(people_no=people_no)
l = self.db.execute_plus(self.t,
"service_times",
where_value=where_value)
return l
def new_info(self,
people_name,
people_photo,
degree,
company,
department,
domain,
star_level,
labels,
group_id=None):
service_times = 0
item = self._insert_info(people_name, people_photo, degree, company,
department, domain, star_level, service_times,
labels)
if group_id is not None:
self._insert_group(group_id, item["people_no"])
return item
def new_detail(self, people_no, people_profile, tel, work_experience,
study_experience, honor, unit_price):
l = self._insert_detail(people_no, people_profile, tel,
work_experience, study_experience, honor,
unit_price)
self._update_status(people_no, add_status=2)
return l
def add_resource(self, people_no, resource_id):
l = self._insert_resource(people_no, resource_id)
return l
def select_resource(self, people_no):
cols = ["people_no", "resource_id"]
where_value = dict(people_no=people_no)
items = self.db.execute_select(self.t_resource,
cols=cols,
where_value=where_value)
return items
def update_people(self, people_no, **kwargs):
cols = [
"people_name", "people_photo", "degree", "company", "department",
"domain", "star_level", "labels"
]
for key in kwargs.keys():
if kwargs[key] is None or key not in cols:
del kwargs[key]
return self._update_info(people_no, **kwargs)
def update_detail(self, people_no, **kwargs):
cols = [
"people_profile", "tel", "work_experience", "study_experience",
"honor", "unit_price"
]
for key in kwargs.keys():
if kwargs[key] is None or key not in cols:
del kwargs[key]
return self._update_detail(people_no, **kwargs)
def select_group_people(self, group_id):
cols = ["people_no"]
items = self.db.execute_select(self.t_group,
where_value=dict(group_id=group_id),
cols=cols)
if len(items) <= 0:
return []
p_nos = map(lambda x: x["people_no"], items)
return self.select_multi_people(p_nos)
def select_multi_people(self, p_nos):
where_value = dict(people_no=p_nos)
cols = [
"people_no", "people_name", "degree", "company", "department",
"domain", "star_level", "service_times", "labels", "insert_time",
"people_photo", "status"
]
items = self.db.execute_multi_select(self.t,
cols=cols,
where_value=where_value)
items = filter(lambda x: x["status"] != 0, items)
return items
def select_people(self, people_no=None):
where_cond = ["status<>0"]
if people_no is not None:
where_value = dict(people_no=people_no)
else:
where_value = None
cols = [
"people_no", "people_name", "degree", "company", "department",
"domain", "star_level", "service_times", "labels", "insert_time",
"people_photo", "status"
]
items = self.db.execute_select(self.t,
cols=cols,
where_value=where_value,
where_cond=where_cond)
for item in items:
for k, v in item.items():
k = k.replace("people", "doctor")
item[k] = v
return items
def select_detail(self, people_no, add_times=False):
cols = [
"people_no", "people_profile", "tel", "work_experience",
"study_experience", "honor", "unit_price"
]
where_value = dict(people_no=people_no)
items = self.db.execute_select(self.t_detail,
cols=cols,
where_value=where_value)
if len(items) <= 0:
return None
for item in items:
for k, v in item.items():
k = k.replace("people", "doctor")
item[k] = v
if add_times is True:
self._update_num(people_no)
return items[0]
def online_people(self, people_no):
l = self._update_status(people_no, add_status=64)
return l
def delete_people(self, people_no):
l = self._update_info(people_no, status=0)
return l
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t = "people_info"
self.t_detail = "people_detail"
self.t_group = "people_group"
self.t_resource = "people_resource"
# !/usr/bin/env python
# encoding: utf-8
from mysqldb_rich import DB
__author__ = 'meisanggou'
if __name__ == "__main__":
db = DB()
items = db.execute_select("zh_test",
prefix_value=dict(a=r"abc_", b="%"),
cols=["a"],
print_sql=True)
for item in items:
print(item)
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "music_info"
self.t_records = "listen_records"
class Music(object):
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "music_info"
self.t_records = "listen_records"
def _insert_info(self, music_type, music_no, music_name, music_desc, music_url, adder, status=1, music_extend=None):
kwargs = dict(music_type=music_type, music_no=music_no, music_name=music_name, music_desc=music_desc,
music_url=music_url, status=status, music_extend=music_extend, adder=adder)
l = self.db.execute_insert(self.t_info, kwargs=kwargs, ignore=True)
return l
def _insert_records(self, user_id, music_no, progress):
insert_time = int(time.time())
kwargs = dict(music_no=music_no, progress=progress, insert_time=insert_time, user_id=user_id)
l = self.db.execute_insert(self.t_records, kwargs=kwargs, ignore=True)
return l
def _update_info(self, music_type, music_no, **update_value):
where_value = dict(music_no=music_no, music_type=music_type)
l = self.db.execute_update(self.t_info, update_value=update_value, where_value=where_value)
return l
def _update_status(self, music_no, add_status=None, sub_status=None):
where_value = dict(music_no=music_no)
if add_status is not None:
l = self.db.execute_update(self.t_info, update_value_list=["status=status|%s" % add_status],
where_value=where_value)
elif sub_status is not None:
l = self.db.execute_update(self.t_info, update_value_list=["status=status&~%s" % sub_status],
where_value=where_value)
else:
l = 0
return l
def _update_num(self, music_type, music_no):
where_value = dict(music_no=music_no, music_type=music_type)
l = self.db.execute_plus(self.t_info, "listen_num", where_value=where_value)
return l
def new_music(self, music_name, music_type, music_desc, music_url, adder, **music_extend):
music_no = int(time.time())
l = self._insert_info(music_type, music_no, music_name, music_desc, music_url, adder, music_extend=music_extend)
if l <= 0:
return False, l
return True, music_no
def new_music_record(self, user_id, music_type, music_no, progress):
self._insert_records(user_id, music_no, progress)
self._update_num(music_type, music_no)
return True, None
def select_music(self, music_type, music_no=None):
where_value = dict()
where_cond = ["status<>0"]
if music_type is not None:
where_value = dict(music_type=music_type)
if music_no is not None:
where_value["music_no"] = music_no
cols = ["music_type", "music_no", "music_name", "music_desc", "music_url", "adder", "status",
"music_extend", "listen_num"]
items = self.db.execute_select(self.t_info, cols=cols, where_value=where_value, where_cond=where_cond)
for item in items:
if item["music_extend"] is not None:
item.update(json.loads(item["music_extend"]))
del item["music_extend"]
return items
def online_music(self, music_no):
l = self._update_status(music_no, add_status=64)
return l
def delete_music(self, music_type, music_no):
l = self._update_info(music_type, music_no, status=0)
return l
class ArticleManager(object):
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "article_info"
self.t_content = "article_content"
self.t_statistics = "article_statistics"
def insert_info(self,
article_type,
article_no,
author,
title,
abstract,
adder=None,
article_desc=None,
pic_url=None):
kwargs = dict(article_no=article_no,
author=author,
title=title,
abstract=abstract,
update_time=time(),
article_desc=article_desc,
pic_url=pic_url,
adder=adder,
article_type=article_type)
l = self.db.execute_insert(self.t_info, kwargs=kwargs)
return l
def insert_content(self, article_no, content):
kwargs = dict(article_no=article_no,
content=content,
insert_time=time())
l = self.db.execute_insert(self.t_content, kwargs=kwargs)
return l
def insert_statistics(self, article_no):
kwargs = dict(article_no=article_no,
update_times=1,
read_times=0,
self_read_times=1,
comment_num=0)
l = self.db.execute_insert(self.t_statistics, kwargs=kwargs)
return l
def new_article(self,
article_type,
author,
title,
abstract,
content,
adder=None,
article_desc=None,
pic_url=None):
article_no = uuid.uuid1().hex
l = self.insert_info(article_type, article_no, author, title, abstract,
adder, article_desc, pic_url)
l += self.insert_content(article_no, content)
l += self.insert_statistics(article_no)
return True, dict(article_no=article_no)
def _update_content(self, article_no, content):
update_value = dict(content=content)
l = self.db.execute_update(self.t_content,
where_value=dict(article_no=article_no),
update_value=update_value)
return l
def _update_info(self, article_type, article_no, update_value):
where_value = dict(article_no=article_no, article_type=article_type)
l = self.db.execute_update(self.t_info,
where_value=where_value,
update_value=update_value)
return l
def _update_status(self,
article_type,
article_no,
add_status=None,
sub_status=None):
where_value = dict(article_no=article_no, article_type=article_type)
if add_status is not None:
l = self.db.execute_logic_or(self.t_info,
status=add_status,
where_value=where_value)
elif sub_status is not None:
l = self.db.execute_logic_non(self.t_info,
where_value=where_value,
status=sub_status)
else:
l = 0
return l
def _update_statistics(self, article_no, *args):
update_value_list = []
for col in args:
update_value_list.append("%s=%s+1" % (col, col))
l = self.db.execute_update(self.t_statistics,
update_value_list=update_value_list,
where_value=dict(article_no=article_no))
return l
def update_article(self,
article_type,
article_no,
author=None,
title=None,
abstract=None,
content=None,
article_desc=None,
pic_url=None):
if content is not None:
self._update_content(article_no, content)
self._update_statistics(article_no, "update_times")
update_value = dict(update_time=time())
if author is not None:
update_value["author"] = author
if title is not None:
update_value["title"] = title
if abstract is not None:
update_value["abstract"] = abstract
if article_desc is not None:
update_value["article_desc"] = article_desc
if pic_url is not None:
update_value["pic_url"] = pic_url
self._update_info(article_type, article_no, update_value=update_value)
return True, dict(article_no=article_no)
def _select_content(self, article_no):
cols = ["article_no", "content", "insert_time"]
db_items = self.db.execute_select(
self.t_content, where_value=dict(article_no=article_no), cols=cols)
if len(db_items) < 0:
return None
return db_items[0]
def _select_info(self,
article_type,
article_no,
where_cond=None,
where_cond_args=None):
cols = [
"article_no", "author", "adder", "title", "article_desc",
"abstract", "pic_url", "update_time", "status", "article_type"
]
where_value = dict()
if article_type is not None:
where_value["article_type"] = article_type
if article_no is not None:
where_value["article_no"] = article_no
db_items = self.db.execute_select(self.t_info,
where_value=where_value,
cols=cols,
where_cond=where_cond,
where_cond_args=where_cond_args)
return db_items
def get_article(self, article_type, article_no, user_name):
articles = self._select_info(article_type, article_no)
if len(articles) <= 0:
return False, "不存在"
article_info = articles[0]
article_content = self._select_content(article_no)
if article_content is None:
return False, "文章异常"
article_info.update(article_content)
if article_info["adder"] != user_name:
self._update_statistics(article_no, "read_times")
else:
self._update_statistics(article_no, "self_read_times")
return True, article_info
def get_statistics(self, article_no):
cols = ["article_no", "update_times", "read_times", "self_read_times"]
items = self.db.execute_select(self.t_statistics,
cols=cols,
where_value=dict(article_no=article_no))
if len(items) > 0:
return items[0]
else:
item = dict(article_no=article_no)
for key in cols[1:]:
item[key] = 0
return item
def query_article(self, **kwargs):
where_cond = ["status<>0"]
where_cond_args = []
if "title" in kwargs:
where_cond.append("title like %%%s%%")
where_cond_args.append(kwargs["title"])
article_no = kwargs.pop("article_no", None)
article_type = kwargs.pop("article_type", None)
db_items = self._select_info(article_type, article_no, where_cond,
where_cond_args)
return True, db_items
def online(self, article_type, article_no):
l = self._update_status(article_type, article_no, add_status=64)
return l
def delete_article(self, article_type, article_no):
l = self._update_info(article_type, article_no, dict(status=0))
return l
def __init__(self, db_conf_path):
self.db = DB(conf_path=db_conf_path)
self.t_info = "article_info"
self.t_content = "article_content"
self.t_statistics = "article_statistics"