#AFC drafts that have been rejected more than once within the past 30 days
import pandas as pd
import pymysql
import os 
"""
Your db login credentials are stored in os.environ. 
DO NOT print or run os.environ, or it will expose your credentials in the Notebook
"""
'\nYour db login credentials are stored in os.environ. \nDO NOT print or run os.environ, or it will expose your credentials in the Notebook\n'
conn = pymysql.connect(
    host=os.environ['MYSQL_HOST'],
    user=os.environ['MYSQL_USERNAME'],
    password=os.environ['MYSQL_PASSWORD'],
    database='enwiki_p',
    charset='utf8'
)
def db_to_dataframe(conn, db, q_string):
    with conn.cursor() as cur:
        cur.execute('use {};'.format(db))
        res = pd.read_sql(q_string, con=conn).str.decode("utf-8")
    return res
q_afc_multiple_declines = """select declines, rc_title from 
(select count(rc_id) as declines, rc_title, rc_user_text, rc_comment 
from recentchanges 
where rc_namespace  in (2,118) and rc_comment like "Declining submission:%" 
group by rc_title order by declines desc) tmp having declines >= 2;"""
multiple_declines = db_to_dataframe(conn, "enwiki_p", q_afc_multiple_declines)
q_afc_multiple_declines['rc_comment'] = q_afc_multiple_declines['rc_comment'].str.decode('utf-8')
q_afc_declines = """select count(rc_id) as declines, rc_comment 
from recentchanges 
where rc_namespace  = 118 and rc_comment like "Declining submission:%" 
group by rc_comment order by declines desc;"""
df_declines_bucketed = db_to_dataframe(conn, "enwiki_p", q_afc_declines)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-39-5729f7d2e971> in <module>()
----> 1 df_declines_bucketed = db_to_dataframe(conn, "enwiki_p", q_afc_declines)

<ipython-input-38-762fd7e94850> in db_to_dataframe(conn, db, q_string)
      2     with conn.cursor() as cur:
      3         cur.execute('use {};'.format(db))
----> 4         res = pd.read_sql(q_string, con=conn).str.decode("utf-8")
      5     return res

/srv/paws/lib/python3.4/site-packages/pandas/core/generic.py in __getattr__(self, name)
   2670             if name in self._info_axis:
   2671                 return self[name]
-> 2672             return object.__getattribute__(self, name)
   2673 
   2674     def __setattr__(self, name, value):

AttributeError: 'DataFrame' object has no attribute 'str'
df_declines_bucketed['rc_comment'] = df_declines_bucketed['rc_comment'].str.decode('utf-8')
#found out how to decode byte string here: 
#https://www.reddit.com/r/learnpython/comments/3gnpkj/pandasread_sql_and_varbinary_how_to_return_string/
df_declines_bucketed
declines rc_comment
0 322 Declining submission: bio - Submission is abou...
1 274 Declining submission: v - Submission is improp...
2 231 Declining submission: corp - Submission is abo...
3 178 Declining submission: nn - Submission is about...
4 166 Declining submission: web - Submission is abou...
5 157 Declining submission: adv - Submission reads l...
6 115 Declining submission: ilc - Submission is a BL...
7 84 Declining submission: music - Submission is ab...
8 41 Declining submission: npov - Submission is not...
9 39 Declining submission: not - Submission fails [...
10 31 Declining submission: lang - Submission is not...
11 29 Declining submission: exists - Submission is d...
12 27 Declining submission: blank - Submission is bl...
13 21 Declining submission: essay - Submission reads...
14 16 Declining submission: context - Submission pro...
15 15 Declining submission: mergeto - Submission sho...
16 13 Declining submission: test - Submission appear...
17 10 Declining submission: neo - Submission is abou...
18 10 Declining submission: prof - Submission is abo...
19 9 Declining submission: athlete - Submission is ...
20 8 Declining submission: This draft has [[WP:EL|e...
21 7 Declining submission: film - Submission is abo...
22 5 Declining submission: joke - Submission appear...
23 2 Declining submission: Please don't resubmit wi...
24 2 Declining submission: cv - Submission is a cop...
25 2 Declining submission: This draft contains [[WP...
26 1 Declining submission: Currently, I don't think...
27 1 Declining submission: This is not a proper lis...
28 1 Declining submission: Did you copy-paste this ...
29 1 Declining submission: There are two sections o...
... ... ...
69 1 Declining submission: Please see [[WP:WikiProj...
70 1 Declining submission: Please address concerns ...
71 1 Declining submission: You have not made any ch...
72 1 Declining submission: Sourcing uses Improper c...
73 1 Declining submission: AllMusic and Discogs are...
74 1 Declining submission: This draft has too many ...
75 1 Declining submission: This draft contains info...
76 1 Declining submission: This is a copy of [[Side...
77 1 Declining submission: Please stop. There has b...
78 1 Declining submission: You need more serious me...
79 1 Declining submission: Please remove [[WP:EL|ex...
80 1 Declining submission: This draft has [[WP:EL|e...
81 1 Declining submission: corp - Submission is abo...
82 1 Declining submission: It still has some format...
83 1 Declining submission: See [[WP:WikiProject Sch...
84 1 Declining submission: This has no article cont...
85 1 Declining submission: As the previous reviewer...
86 1 Declining submission: See [[WP:Notability (alb...
87 1 Declining submission: Fix reference errors.\n\...
88 1 Declining submission: This draft has no refere...
89 1 Declining submission: Image used is a copyrigh...
90 1 Declining submission: It has some ref formatti...
91 1 Declining submission: See [[WP:NOTMADEUP]]. ([...
92 1 Declining submission: The references are incon...
93 1 Declining submission: This draft is duplicated...
94 1 Declining submission: no actual content ([[WP:...
95 1 Declining submission: The example exterinal li...
96 1 Declining submission: If a given fact is prova...
97 1 Declining submission: This draft has multiple ...
98 1 Declining submission: This appears to be three...

99 rows × 2 columns