统计讲义当月使用题目数

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

# In[1]:


import pandas as pd
import pymysql
from pyhive import presto

conn_mysql = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="hfjydb",charset="utf8")
conn_mysql_bidata = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com",port=3306,user="yanyanglong_read",passwd="jZYhtZNwD6kWbCiJlNBI",db="bidata",charset="utf8")
conn_hive = presto.connect(host='dw-bigdata.hfjy.red', port='8334')
conn_dmart = pymysql.connect(host="rm-bp1303fj95u76622lmo.mysql.rds.aliyuncs.com",port=3306,user="wangguoqiang",passwd="rwpbDG8ahhJ5QlLfcOBV",db="bidata",charset="utf8")


# In[22]:


sql1 = '''
SELECT
        lpqh.paperid,
        count(lpqh.quizid) quiz_count
FROM
        dwd_db_hfjydb.lesson_plan lp
INNER JOIN
        dwd_db_hfjydb.subject sub ON sub.subject_id = lp.subject_id
INNER JOIN
        dwd_db_hfjydb.lesson_plan_quiz_hwlinfo lpqh ON lpqh.lessonplanid = lp.lesson_plan_id
LEFT JOIN
        dwd_db_hfjydb.jx_handouts jh ON jh.paper_id = lpqh.paperid
WHERE
        lp.status in (3,5)
        AND lp.adjust_start_time >= '2019-07-01'
        AND lp.adjust_end_time < '2019-08-01'
        AND lp.solve_status <> 6
        AND lp.lesson_type in (1,2)
GROUP BY
        lpqh.paperid
'''

df1 = pd.read_sql(sql1,conn_hive)

df1.head()


# In[35]:


sql3 = '''
SELECT
        lpqh.paperid,
        lesson_plan_id,
        lpqh.quizid
FROM
        dwd_db_hfjydb.lesson_plan lp
INNER JOIN
        dwd_db_hfjydb.subject sub ON sub.subject_id = lp.subject_id
INNER JOIN
        dwd_db_hfjydb.lesson_plan_quiz_hwlinfo lpqh ON lpqh.lessonplanid = lp.lesson_plan_id
LEFT JOIN
        dwd_db_hfjydb.jx_handouts jh ON jh.paper_id = lpqh.paperid
WHERE
        lp.status in (3,5)
        AND lp.adjust_start_time >= '2019-07-01'
        AND lp.adjust_end_time < '2019-08-01'
        AND lp.solve_status <> 6
        AND lp.lesson_type in (1,2)
        AND lpqh.paperid = '167972C540D54D4483A8D0B66CC36E55'
        AND lpqh.lessonplanid=14570487
'''

df3 = pd.read_sql(sql3,conn_hive)

df3


# In[7]:


df1.dtypes


# In[16]:


print(df1[df1.paperid == 'BB03041048644CCBA1F3125F28097382'].quiz_count)


# In[19]:


df1[df1.paperid == '167972C540D54D4483A8D0B66CC36E55']


# In[14]:


import datetime
period = ('%d%02d' % (datetime.date.today().year - (datetime.date.today().month==1),datetime.date.today().month - 1 or 12))
period


# In[25]:


sql2 = '''SELECT * FROM dwd_db_hfjydb.lesson_plan_quiz_hwlinfo WHERE paperid  = '167972C540D54D4483A8D0B66CC36E55' and lesson_plan_id=14145739 ''' 

df2 = pd.read_sql(sql2,conn_hive)

df2

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