def download_tag_img(subject):
update = "update t_res_{subject_key}_tag_question set tag_img = '{tag_img}' WHERE question_id = '{question_id}';"
data = get_tags(subject)
with mysql(db="kuaik", host="localhost", password="******",
port=3333) as cur:
for d in data:
path = "F:/img/{subject_key}_{month}{day}/tag/{question_id}".format(
subject_key=subject_key,
month=now.month,
day=now.day,
question_id=d.get('question_id'))
if not os.path.exists(path): os.makedirs(path)
response = requests.get(d.get('tag_url'))
img_type = response.headers.get('Content-Type').split("/")[-1]
img_path = path + '/' + d.get('question_uuid') + '.' + img_type
with open(img_path, mode="wb") as f:
f.write(response.content)
print(
update.format(subject_key=subject,
tag_img=img_path,
question_id=d.get('question_id')))
cur.execute(
update.format(subject_key=subject,
tag_img=img_path,
question_id=d.get('question_id')))
def tag_count(subject_key):
"""统计章节知识点数量"""
with mysql(db="sit_exue_resource") as cur:
sql = "SELECT qc.chapter_id,count(DISTINCT tq.tag_id) as num from t_res_{subject_key}_tag_question tq " \
"LEFT JOIN t_res_{subject_key}_question_chapter qc " \
"on tq.question_uuid = qc.question_uuid where qc.chapter_id is not null GROUP BY qc.chapter_id"
cur.execute(sql.format(subject_key=subject_key))
return cur.fetchall()
def file_name(file_dir):
with mysql(db="sit_exue_resource") as cur:
for root, dirs, files in os.walk(file_dir):
for d in dirs:
if not d.isdigit(): continue
cur.execute(select_chapter.format(chapter_id=d))
data = cur.fetchone()
if data:
print(os.path.join(root, d))
print(os.path.join(root, data.get('chapter_name')))
def show(subject, start):
count = start * 1000 + 1
options = ['A', 'B', 'C', 'D', 'E', 'F', 'G']
with mysql(db="sit_exue_resource") as cur:
# print(sel_questions.format(subject=subject, start=start * 1000))
# cur.execute(sel_questions.format(subject=subject, start=start * 1000))
print(
sel_questions_item_img.format(subject=subject, start=start * 1000))
cur.execute(
sel_questions_item_img.format(subject=subject, start=start * 1000))
questions = cur.fetchall()
f = open("F:/html/{subject}_img{count}-{end}.html".format(
subject=subject, count=count, end=count + 999),
mode="a",
encoding="utf8")
f.write(
'<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>Title</title></head><body>'
)
for q in questions:
html = ["第" + str(count) + '题, 题目ID：' + q.get('uuid') + ' ']
cur.execute(
sel_book.format(subject=subject, question_uuid=q.get('uuid')))
data = cur.fetchone()
if data:
html.append(
data.get('subject_name') + '\t' + data.get('grade') +
'年级' + '\t' + data.get('press_name') +
data.get('edition_name') + '\t' + data.get('book_name') +
' 章节：' + data.get('chapter_name') + '<br/>')
cur.execute(
sel_item.format(subject=subject, question_uuid=q.get('uuid')))
items = cur.fetchall()
cur.execute(
sel_tag_question.format(subject=subject,
question_uuid=q.get('uuid')))
tags = cur.fetchall()
html.append(q.get('context') + '<br/>')
answer = ""
for index, item in enumerate(items):
if item.get('iscorrect') == 'Y':
answer = options[index]
html.append(options[index] + ":" + item.get('content') +
'<br/>')
html.append("答案:" + answer + '<br/>')
if tags:
html.append("知识点:")
data = [item.get('tag_name') for item in tags]
html.append(",".join(data))
html.append("<br/>")
html.append("<br/>")
html.append("<br/>")
count += 1
f.write("".join(html))
f.write('</body></html')
f.close()
def question_count(subject_key):
with mysql(db="uat_exue_resource",
host="192.168.121.159",
user="******",
password="******",
port=42578) as cur:
sql = """SELECT qc.chapter_id,count(qc.question_uuid) as num from t_res_%s_question_chapter qc
LEFT JOIN t_res_%s_question q on qc.question_uuid = q.uuid where type in ('2','11')
GROUP BY qc.chapter_id"""
cur.execute(sql % (subject_key, subject_key))
return cur.fetchall()
def tag_count(subject_key):
"""统计章节知识点数量"""
with mysql(db="uat_exue_resource",
host="192.168.121.159",
user="******",
password="******",
port=42578) as cur:
sql = "SELECT qc.chapter_id,count(DISTINCT tq.tag_id) as num from t_res_{subject_key}_tag_question tq " \
"LEFT JOIN t_res_{subject_key}_question_chapter qc " \
"on tq.question_uuid = qc.question_uuid where qc.chapter_id is not null GROUP BY qc.chapter_id"
cur.execute(sql.format(subject_key=subject_key))
return cur.fetchall()
def book_count(subject_name):
"""查询某个学科信息"""
with mysql(db="sit_exue_resource") as cur:
sql = "SELECT b.subject_name,gb.grade,b.book_name,u.unit_name,c.chapter_name,b.edition_id,c.chapter_id,"
sql += "CONCAT(e.press_name,e.edition_name) as edition from t_res_chapter c "
sql += "LEFT JOIN t_res_units u on c.unit_id = u.unit_id "
sql += "LEFT JOIN t_res_book b on c.book_id = b.book_id "
sql += "LEFT JOIN t_res_graduate_book gb on c.book_id = gb.book_id "
sql += "LEFT JOIN t_res_editor e on b.edition_id = e.edition_id "
sql += "WHERE b.subject_name = '{subject_name}' and b.book_name != '橘子学院营销培训' "
cur.execute(sql.format(subject_name=subject_name))
return cur.fetchall()
def main(subject):
result = [['年级', '知识点']]
with mysql(host="192.168.121.159",
user="******",
password="******",
db="uat_exue_resource",
port=42578) as cur:
for grade in range(1, 10):
data = grade_tag(cur, subject, grade)
for d in data:
result.append([grade, d.get('tag_name')])
create_excel(result, "F:/导出/%s知识点.xlsx" % subjects.get(subject))
def chapter_question_tag(subject, subject_code):
with mysql(db="uat_exue_resource",
host="192.168.121.159",
user="******",
password="******",
port=42578) as cur:
sql = """SELECT c.chapter_id,count(DISTINCT tq.question_uuid) as num from t_res_{subject}_tag_question tq
LEFT JOIN t_res_{subject}_question_chapter qc on tq.question_uuid = qc.question_uuid
LEFT JOIN t_res_{subject}_question q on tq.question_uuid = q.uuid
LEFT JOIN t_res_chapter c on qc.chapter_id = c.chapter_id
LEFT JOIN t_res_book b on c.book_id = b.book_id
WHERE q.type in ('2','11') and b.subject_code = '{subject_code}' GROUP BY c.chapter_id;"""
cur.execute(sql.format(subject_code=subject_code, subject=subject))
return cur.fetchall()
def change_unit(path):
with mysql(db="topic_standard_test") as cur:
books = os.listdir(path)
for book in books:
cur.execute("select * from t_res_units where book_id = '{book_id}'".format(book_id=book))
res = cur.fetchall()
units = os.listdir(os.path.join(path, book))
for unit in units:
for r in res:
if get_similarity(valid_name(r.get('unit_name')), valid_name(unit)) > 0.7:
print(r.get('unit_name'), unit)
res.remove(r)
old_name = path + '/' + book + '/' + unit
new_name = path + '/' + book + '/' + r.get('unit_id')
os.rename(old_name, new_name)
break
def book_count(subject_code):
"""查询某个学科信息"""
with mysql(db="uat_exue_resource",
host="192.168.121.159",
user="******",
password="******",
port=42578) as cur:
sql = "SELECT b.subject_name,gb.grade,b.book_name,u.unit_name,c.chapter_name,b.edition_id,c.chapter_id,"
sql += "CONCAT(e.press_name,e.edition_name) as edition from t_res_chapter c "
sql += "LEFT JOIN t_res_units u on c.unit_id = u.unit_id "
sql += "LEFT JOIN t_res_book b on c.book_id = b.book_id "
sql += "LEFT JOIN t_res_graduate_book gb on c.book_id = gb.book_id "
sql += "LEFT JOIN t_res_editor e on b.edition_id = e.edition_id "
sql += "WHERE b.subject_code = '{subject_code}' and b.book_name != '橘子学院营销培训' "
cur.execute(sql.format(subject_code=subject_code))
return cur.fetchall()
def get_tags(subject):
with mysql(db="kuaik", host="localhost", password="******",
port=3333) as cur:
sql = "SELECT * from t_res_{subject_key}_tag_question WHERE tag_img is null limit 1000"
cur.execute(sql.format(subject_key=subject))
return cur.fetchall()
def select_all(sql):
with mysql(db="sit_exue_resource") as cur:
cur.execute(sql)
return cur.fetchall()
units = os.listdir(os.path.join(path, book))
for unit in units:
for r in res:
if get_similarity(valid_name(r.get('unit_name')), valid_name(unit)) > 0.7:
print(r.get('unit_name'), unit)
res.remove(r)
old_name = path + '/' + book + '/' + unit
new_name = path + '/' + book + '/' + r.get('unit_id')
os.rename(old_name, new_name)
break
if __name__ == '__main__':
# change_unit("F:/运营0423/yy")
book_ids = os.listdir("F:/运营0423/yy")
with mysql(db="topic_standard_test") as cur:
for book_id in book_ids:
book_path = os.path.join("F:/运营0423/yy", book_id)
unit_ids = os.listdir(book_path)
for unit_id in unit_ids:
unit_path = os.path.join(book_path, unit_id)
chapter_ids = os.listdir(unit_path)
cur.execute("select * from t_res_chapter where unit_id='{unit_id}'".format(unit_id=unit_id))
data = cur.fetchall()
for chapter_id in chapter_ids:
chapter_path = os.path.join(unit_path, chapter_id)
for item in data:
if get_similarity(valid_name(item.get('chapter_name')), valid_name(chapter_id)) > 0.7:
new_name = os.path.join(unit_path, item.get('chapter_id'))
os.rename(chapter_path, new_name)
data.remove(item)
from common.mysql_util import mysql
if __name__ == '__main__':
res = [
'18410000000', '18410000001', '18410000002', '18410000003',
'18410000004', '18410000005', '18410000006', '18410000007',
'18210000008'
]
with mysql(db="sit2_exue") as cur:
for r in res:
cur.execute(
"SELECT * from sit2_exue.t_user_base WHERE s_phone = '{phone}' and s_is_delete = '0'"
.format(phone=r))
data = cur.fetchone()
if data:
print(
"update sit2_exue_pay.t_pay_user_account set s_money = '100000' WHERE f_user_id = '{user_id}';"
.format(user_id=data.get('p_id')))
def get_chapter_id(book_id):
with mysql(host='localhost', port=3333, user='******', password='******', db='kuaik') as cur:
select_chapter_sql = "SELECT * from chapter WHERE book_id = '{}'".format(book_id)
cur.execute(select_chapter_sql)
data = cur.fetchall()
return [item.get('zj_chapter_id') for item in data]
def get_contexts_question(subject_key):
with mysql(db="zujuan_spark_test") as cursor:
cursor.execute("select context,uuid from t_res_{}_question WHERE context like '%MathMLToImage%' "
"and create_time >= '2018-04-17'".format(subject_key))
return cursor.fetchall()
