def characteristic_search(info,weight,height):
db = opsql.Database()
para=[]
select_sql="where "
for key in info:
if not info[key]=='null':
select_sql+=key+"=%s and "
para.append(info[key])
select_sql+="height>=%s and height<=%s and weight>=%s and weight<=%s"
para.extend([str(height[0]),str(height[1]),str(weight[0]),str(weight[1])])
sql='select s.servent_id,s.servent_name from servent s ' \
'inner join servent_and_prototype sp on s.servent_id=sp.servent_id ' \
'inner join prototype_and_origin po on sp.prototype_id=po.prototype_id ' \
'inner join prototype_and_region pr on sp.prototype_id=pr.prototype_id ' \
'inner join region r on pr.region_id=r.region_id ' \
'inner join origin o on po.origin_id=o.origin_id ' \
'inner join servent_and_alignment sa on sa.servent_id=s.servent_id ' \
'inner join alignment a on a.alignment_id=sa.alignment_id ' \
'inner join servent_and_class sac on sac.servent_id=s.servent_id ' \
'inner join class c on sac.class_id=c.class_id '
sql+=select_sql+" group by s.servent_id;"
result = db.select(sql,para)
dic_info = [] # 所有人
for row in result:
dic_one_person_info = {} # 单个人
dic_one_person_info['id'] = str(row[0])
dic_one_person_info["servent_name"]=row[1]
servent_profile_pic_sql="select profile_pic from servent_profile_pic where servent_id=%s;"
dic_one_person_info["servent_profile_pic"] = [i[0] for i in db.select(servent_profile_pic_sql,[row[0]])][0]
dic_info.append(dic_one_person_info)
db.close()
return dic_info
def origin_search(origin):
db = opsql.Database()
search = '%'
for i in origin:
search += i
search += '%'
sql = "select s.servent_id,servent_name,profile_pic from servent s " \
"inner join servent_and_prototype sp on s.servent_id=sp.servent_id " \
"inner join prototype p on sp.prototype_id=p.prototype_id " \
"inner join servent_profile_pic spp on s.servent_id = spp.servent_id " \
"inner join prototype_and_origin po on p.prototype_id = po.prototype_id " \
"inner join origin o on po.origin_id = o.origin_id " \
"where origin_name like %s;"
result = db.select(sql, [search])
dic_info = [] # 所有人
for row in result:
dic_one_person_info = {} # 单个人
dic_one_person_info['id'] = str(row[0])
dic_one_person_info["servent_name"] = row[1]
dic_one_person_info["servent_profile_pic"] = row[2]
dic_info.append(dic_one_person_info)
db.close()
return dic_info
#print(name_search("兰陵王",c.conn2_info))
def get_attribute():
db = opsql.Database()
dic_info = {} # 4个list
region_sql="select region_name from region;"
origin_sql ="select origin_name from origin;"
alignment_sql ="select alignment_name from alignment;"
servent_class_sql ="select class_name from class;"
dic_info['region']=[i[0] for i in db.select(region_sql,[])]
dic_info['origin'] =[i[0] for i in db.select(origin_sql,[])]
dic_info['alignment'] =[i[0] for i in db.select(alignment_sql,[])]
dic_info['servent_class'] =[i[0] for i in db.select(servent_class_sql,[])]
db.close()
return dic_info
def name_search(name):
db = opsql.Database()
search_name = "%"
for i in name:
search_name += i
search_name += "%"
sql = "select servent_id,servent_name from servent where servent_name like %s;"
result = db.select(sql, [search_name])
dic_info = [] # 所有人
for row in result:
dic_one_person_info = {} # 单个人
dic_one_person_info['id'] = str(row[0])
dic_one_person_info["servent_name"] = row[1]
temp_sql = "select profile_pic from servent_profile_pic where servent_id= %s;"
re = [i[0] for i in db.select(temp_sql, [row[0]])]
dic_one_person_info["servent_profile_pic"] = re[0]
dic_info.append(dic_one_person_info)
db.close()
return dic_info
def region_search(region):
db = opsql.Database()
search = '%'
for i in region:
search += i
search += '%'
sql = "select s.servent_id,servent_name,profile_pic from servent s " \
"inner join servent_and_prototype sp on s.servent_id=sp.servent_id " \
"inner join prototype p on sp.prototype_id=p.prototype_id " \
"inner join servent_profile_pic spp on s.servent_id = spp.servent_id " \
"inner join prototype_and_region pr on p.prototype_id = pr.prototype_id " \
"inner join region r on pr.region_id = r.region_id " \
"where region_name like %s;"
result = db.select(sql, [search])
dic_info = [] # 所有人
for row in result:
dic_one_person_info = {} # 单个人
dic_one_person_info['id'] = str(row[0])
dic_one_person_info["servent_name"] = row[1]
dic_one_person_info["servent_profile_pic"] = row[2]
dic_info.append(dic_one_person_info)
db.close()
return dic_info
from basic_sql_op import op_database as opsql db = opsql.Database(opsql.conn2_info) str = "1 or 1=1;" sql = "select * from author where author_id=%s" sql2 = "select * from author where contains(author_name,超)" print(sql) result = db.select(sql, [str]) print(result)
def explorer_infomation(id):
db = opsql.Database()
dic_info = {}
big_sql = "select region_name,origin_name,prototype_name,region_id,origin_id,prototype_id from explorer_infomation1 where servent_id=%s;"
res = db.select(big_sql, [id])
region_res = list(set([i[0] for i in res]))
origin_res = list(set([i[1] for i in res]))
prototype_res = list(set([i[2] for i in res]))
dic_info["region"] = region_res[random.randint(0, len(region_res) - 1)]
dic_info["origin"] = origin_res[random.randint(0, len(origin_res) - 1)]
dic_info["prototype"] = prototype_res[random.randint(
0,
len(prototype_res) - 1)]
region_id = list(set([i[3] for i in res]))[0]
origin_id = list(set([i[4] for i in res]))[0]
prototype_id = list(set([i[5] for i in res]))[0]
dic_info['pedias'] = []
pedias_sql="select p.pedia_id,pedia_name,pedia_url from pedia p " \
"inner join pedia_name pn on p.pedia_id=pn.pedia_id " \
"where prototype_id=%s;"
pedias_res = db.select(pedias_sql, [prototype_id])
for i in pedias_res:
pedias_dic = {}
pedias_dic['pedia_id'] = i[0]
pedias_dic['pedia_name'] = i[1]
pedias_dic['pedia_url'] = i[2]
dic_info['pedias'].append(pedias_dic)
print(region_id)
print(origin_id)
print(prototype_id)
dic_info['articles'] = []
articles_sql = "select a.article_id,article_title,article_content,author_name from article a " \
"left join region_and_article raa on raa.article_id=a.article_id " \
"left join origin_and_article oaa on oaa.article_id=a.article_id " \
"left join author_and_article aaa on a.article_id = aaa.article_id " \
"inner join author a2 on aaa.author_id = a2.author_id " \
"where region_id=%s or origin_id=%s"
articles_res = db.select(articles_sql, [region_id, origin_id])
for i in articles_res:
articles_dic = {}
articles_dic['article_id'] = i[0]
articles_dic['article_title'] = i[1]
articles_dic['article_content'] = i[2].replace('\n', '')
articles_dic['author'] = i[3]
dic_info['articles'].append(articles_dic)
dic_info['books'] = []
books_sql = "select book_name,isbn,writer_name from book b " \
"inner join book_and_writer baw on b.book_id=baw.book_id " \
"inner join writer w on w.writer_id=baw.writer_id" \
" where b.book_id in " \
"(select region_and_book.book_id from region_and_book where region_id=%s " \
"union " \
"select origin_and_book.book_id from origin_and_book where origin_id=%s);"
books_res = db.select(books_sql, [region_id, origin_id])
for i in books_res:
books_dic = {}
books_dic['book_title'] = i[0]
books_dic['isbn_code'] = i[1]
books_dic['book_writer'] = i[2]
dic_info['books'].append(books_dic)
db.close()
return dic_info
def servent_infomation(id):
db = opsql.Database()
sql = "select * from servent where servent_id=%s;"
result = db.select(sql, [id])
dic_info = {}
row = result[0]
dic_info['servent_id'] = row[0]
dic_info['servent_name'] = row[1]
dic_info['servent_name_japanese'] = row[2]
dic_info['servent_name_english'] = row[3]
dic_info['height'] = row[4]
dic_info['weight'] = row[5]
dic_info['gender'] = row[6]
dic_info['strength'] = row[7]
dic_info['endurance'] = row[8]
dic_info['agility'] = row[9]
dic_info['mana'] = row[10]
dic_info['luck'] = row[11]
dic_info['noble_phantasm'] = row[12]
dic_info['craft_name'] = row[13]
dic_info['craft_description'] = row[14].replace('\n', '')
dic_info['craft_src'] = row[15]
alignment_sql="select alignment_name from servent_and_alignment sa " \
"inner join alignment a on sa.alignment_id=a.alignment_id " \
"where servent_id=%s;"
alignment_res = [i[0] for i in db.select(alignment_sql, [id])]
dic_info["alignment"] = alignment_res[random.randint(
0,
len(alignment_res) - 1)] if alignment_res != [] else 'unknown'
class_sql = "select class_name from servent_and_class sc " \
"inner join class c on sc.class_id=c.class_id " \
"where servent_id=%s;"
class_res = [i[0] for i in db.select(class_sql, [id])]
dic_info["class"] = class_res[random.randint(
0,
len(class_res) - 1)] if class_res != [] else 'unknown'
illustrator_sql = "select illustrator_name from servent_and_illustrator si " \
"inner join illustrator i on si.illustrator_id=i.illustrator_id " \
"where servent_id=%s;"
illustrator_res = [i[0] for i in db.select(illustrator_sql, [id])]
dic_info["illustrator"] = illustrator_res[random.randint(
0,
len(illustrator_res) - 1)] if illustrator_res != [] else 'unknown'
voice_actor_sql = "select voice_actor_name from servent_and_voice_actor sva " \
"inner join voice_actor va on sva.voice_actor_id=va.voice_actor_id " \
"where servent_id=%s;"
voice_actor_res = [i[0] for i in db.select(voice_actor_sql, [id])]
dic_info["voice_actor"] = voice_actor_res[random.randint(
0,
len(voice_actor_res) - 1)] if voice_actor_res != [] else 'unknown'
bond_text_sql = "select bond_text from servent_bond where servent_id=%s;"
dic_info["bond_text"] = [i[0] for i in db.select(bond_text_sql, [id])]
full_picture_sql = "select servent_picture from servent_full_pic where servent_id=%s;"
full_picture_res = [i[0] for i in db.select(full_picture_sql, [id])]
dic_info["full_picture"] = full_picture_res[random.randint(
0,
len(full_picture_res) - 1)] if full_picture_res != [] else 'unknown'
big_sql = "select region_name,origin_name,prototype_name from servent_and_prototype sp " \
"inner join prototype p on sp.prototype_id=p.prototype_id " \
"inner join prototype_and_origin po on sp.prototype_id=po.prototype_id " \
"inner join prototype_and_region pr on sp.prototype_id=pr.prototype_id " \
"inner join region r on pr.region_id=r.region_id " \
"inner join origin o on po.origin_id=o.origin_id " \
"where servent_id=%s;"
res = db.select(big_sql, [id])
region_res = list(set([i[0] for i in res]))
origin_res = list(set([i[1] for i in res]))
prototype_res = list(set([i[2] for i in res]))
dic_info["region"] = region_res[random.randint(
0,
len(region_res) - 1)] if region_res != [] else 'unknown'
dic_info["origin"] = origin_res[random.randint(
0,
len(origin_res) - 1)] if origin_res != [] else 'unknown'
dic_info["prototype"] = prototype_res[random.randint(
0,
len(prototype_res) - 1)] if prototype_res != [] else 'unknown'
db.close()
return dic_info
