经典题目使用率

#!/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[3]:


sql1 = '''
SELECT
        lpq.quiz_id,
        COUNT(lp.lesson_plan_id) 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_total lpq on lpq.lesson_plan_id = lp.lesson_plan_id
WHERE
        lp.adjust_start_time >= '2019-01-01'
        and lp.adjust_start_time < '2019-08-01'
        and sub.subject_id in (20,13,1,19,14,2,21,15,3,16,4,5,24,23)
GROUP BY
        lpq.quiz_id
'''

df1 = pd.read_sql(sql1,conn_hive)

df1.head()


# In[4]:


df1.dtypes


# In[5]:


df1.shape



# In[34]:


sql1 = '''
SELECT
        lpq.quiz_id,
        COUNT(lp.lesson_plan_id) 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_total lpq on lpq.lesson_plan_id = lp.lesson_plan_id
WHERE
        lp.adjust_start_time >= '2019-07-29'
        and lp.adjust_start_time < '2019-08-01'
        and sub.subject_id in (20,13,1,19,14,2,21,15,3,16,4,5,24,23)
GROUP BY
        lpq.quiz_id
'''

df1 = pd.read_sql(sql1,conn_hive)

df1.head()


# In[55]:


aa = df1.loc[0]
aa


# In[56]:


aa['quiz_id']


# In[57]:


aa['quiz_count']


# In[58]:


bb = df1.loc[0]['quiz_id']
bb

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