根据好未来题目id拉取试听课id

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

# In[1]:


import pandas as pd
import pymysql
from pyhive import presto


# In[2]:


conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="hfjydb",charset="utf8")
conn1 = presto.connect(host='dw-bigdata.hfjy.red', port='8334')


# In[3]:


data = pd.read_excel(r'E:\Desktop\各学科题目统计V2.xlsx')
data.head()


# In[4]:


sql1 = '''
SELECT
    hwlqk.hwlquizid,
    lp.lesson_plan_id,
    lp.adjust_start_time
FROM
        ods_hfjydb.lesson_plan lp
LEFT JOIN
        ods_hfjydb.lesson_plan_quiz_total lpq on lp.lesson_plan_id = lpq.lesson_plan_id
LEFT JOIN
        ods_hfjydb.view_student s on s.student_id = lp.student_id
LEFT JOIN
        ods_hfjydb.hwl_quiz_knowledge hwlqk on hwlqk.quiztempid = lpq.quiz_id
WHERE
        s.account_type = 1
        and lp.lesson_type  =  2
        and lp.status in (3,5)
        and lp.solve_status <> 6
        and hwlquizid in {0}
'''.format(tuple(data['题目ID']))

df1 = pd.read_sql(sql1,conn1)

df1.head()


# In[5]:


df1.shape


# In[6]:


df1.to_excel(r'E:/Desktop/0704题目对应试听课明细V2.xlsx')

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