取题目id对应题目数

#!/usr/bin/env python
# coding: utf-8

# In[ ]:


import sys
sys.path.append(r'e:\Desktop\新环境\DataMachine_for_timer\src')
import pandas as pd
import numpy as np
import pymysql
from ML import data_suit,plot_suit
from utils import mysqlhelper,elapse,prestohelper
from config import config
from datetime import datetime , time
from pyhive import presto

## 脱敏库
mysqlinstance_tm_new_hfjy = mysqlhelper(**config.tm_new_hfjy)
mysqlinstance_tm_new_bidata = mysqlhelper(**config.tm_new_bidata)

## dmart库
mysqlinstance_dmart_dmart = mysqlhelper(**config.dmart_dmart)
mysqlinstance_dmart_bidata = mysqlhelper(**config.dmart_bidata)

## hrm库
mysqlinstance_hrm_hrm_sale = mysqlhelper(**config.hrm_hrm_sale)
mysqlinstance_hrm_new_hrm = mysqlhelper(**config.hrm_new_hrm)
mysqlinstance_hrm_jobhunt = mysqlhelper(**config.hrm_jobhunt)

## hive库
prestoinstance = prestohelper(**config.hive_prosto)


# In[ ]:


data = pd.read_excel(r'e:\Desktop\学科试卷题目id.xlsx')
data


# In[ ]:


sql1 = '''
SELECT
    s.subject_name 学科,
    q.quiz_source 试卷来源,
    group_concat(q.test_paper_no) 题目明细
FROM
    quiz q
LEFT JOIN
    subject s ON q.subject_id = s.subject_id
WHERE
    q.quiz_source in {0}
    and s.subject_name in {1}
GROUP BY
    s.subject_name,q.quiz_source
'''.format(tuple(data['试卷来源']),tuple(data['学科']))


# In[ ]:


df1 = mysqlinstance_tm_new_hfjy.get_df(sql1)

df1


# In[ ]:


df_sub = pd.merge(data,df1,how='left',on=['学科','试卷来源'])


# In[ ]:


df_sub


# In[ ]:


df_sub.to_excel(r'e:\Desktop\0621题目id明细.xlsx',index=False,encoding='GB18030')


# In[ ]:


df1.to_excel(r'e:\Desktop\0621题目id明细1.xlsx',index=False,encoding='GB18030')

文档更新时间: 2019-08-19 11:40   作者:admin