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()