<jupyter_start><jupyter_text>Try MediaWiki's RecentChanges API  Setup  imports<jupyter_code>import pandas as pd, dateutil.parser as dp
import os, requests, datetime, time, json
from sseclient import SSEClient as EventSource<jupyter_output><empty_output><jupyter_text>define function ```get_rc```<jupyter_code>def get_rc(rc_list:list, params:dict, url: str, sesh) -> str:
    '''
    Inputs:  rc_list: list to be populated with recentchanges jsons
             params: dictionary of parameters for the API request
                 this fn expects at least these parameters:
                    'rcprop' : 'timestamp|ids', (more is okay)
                    'action' : 'query',
                    'rcdir'  : 'newer',
                    'format' : 'json',
                    'list'   : 'recentchanges',
             url: API url (designates which wiki)
             sesh: requests session
    Outputs: timestamp of latest  
    '''
    raw_output= sesh.get(url=url, params=params)
    json_data = raw_output.json()
    recent_changes = json_data['query']['recentchanges']
    rc_list.append(recent_changes)
    timestamps = [rc['timestamp'] for rc in recent_changes]
    timestamps = sorted(map(dp.isoparse, timestamps))
    ts = timestamps[-3]
    return ts.strftime('%Y-%m-%dT%H:%M:%SZ')<jupyter_output><empty_output><jupyter_text>Collect 500 recent changes  get ```rc_list```  initialize requests session<jupyter_code>sesh = requests.Session()<jupyter_output><empty_output><jupyter_text>set parameters<jupyter_code>rc_list=[]
url = 'https://en.wikipedia.org/w/api.php'
params = {
    'rcstart'     : '2021-10-20T00:30:01Z',
    'rcnamespace' : '0',
    'rcshow'      : '!bot',
    'rclimit'     : '50',
    
    'rcprop': 'user|userid|timestamp|title|ids|sizes',
    
    'action'      : 'query',
    'rcdir'       : 'newer',
    'format'      : 'json',
    'list'        : 'recentchanges',
}

# Dictionary keys that output from these parameters:
['timestamp', 'type', 'title', 'anon', 'rcid', 'ns', 'revid', 'pageid', 'user', 'userid', 'oldlen', 'old_revid', 'newlen'];<jupyter_output><empty_output><jupyter_text>populate rc_list<jupyter_code>for i in range(10):
    latest_timestamp = get_rc(rc_list, params, url, sesh)
    params['rcstart'] = latest_timestamp
    print(f'{i} {latest_timestamp}')
    time.sleep(.5)<jupyter_output>0 2022-01-29T11:08:54Z
1 2022-01-29T11:09:35Z
2 2022-01-29T11:10:19Z
3 2022-01-29T11:10:59Z
4 2022-01-29T11:11:42Z
5 2022-01-29T11:12:29Z
6 2022-01-29T11:13:16Z
7 2022-01-29T11:13:58Z
8 2022-01-29T11:14:22Z
9 2022-01-29T11:14:36Z<jupyter_text>peek at ```rc_list```  check dimensions of nested list<jupyter_code>len(rc_list), len(rc_list[0]), len(rc_list[0][0])<jupyter_output><empty_output><jupyter_text>look at one JSON element of nested list<jupyter_code>rc_list[0][0]<jupyter_output><empty_output><jupyter_text>timestamp of latest recentchanges record<jupyter_code>latest_timestamp<jupyter_output><empty_output><jupyter_text>flatten rc_list to get unique_jsons  flatten<jupyter_code># flatten the jsons
all_jsons = [item for sublist in rc_list for item in sublist]
# remove jsons with duplicate rcid's
all_rcids = {j['rcid']:i for i,j in enumerate(all_jsons)}
unique_jsons = [all_jsons[i] for i in all_rcids.values()]<jupyter_output><empty_output><jupyter_text>make dataframe<jupyter_code>df = pd.DataFrame.from_records(unique_jsons)<jupyter_output><empty_output><jupyter_text>peek at jsons as dataframe<jupyter_code>df
# df.to_csv('../data/interim/2021-10-20T00:30:01Z_2021-10-20T01:39:40Z.csv')<jupyter_output><empty_output><jupyter_text>Build SQL schema for import into database  This is for postgreSQL; (Update for MySQL)  make "create table" query<jupyter_code>sql_create_table = """
DROP TABLE IF EXISTS data_raw;

CREATE TABLE data_raw(
    row_index SERIAL,
    time_string char varying(25),
    unix_time bigint,
    instance char varying(35),
    product char varying(5),
    username char varying(35),
    event char varying(100),
    attributes text
);
"""<jupyter_output><empty_output><jupyter_text>connect to database<jupyter_code>try:
    conn = psycopg2.connect("host="+dbhost+" dbname="+dbname+" user="+dbuname+" password="+dbpassword)
    cur = conn.cursor()
except:
    print('Database connection error - check creds')<jupyter_output><empty_output><jupyter_text>create table and import data<jupyter_code>cur.execute(sql_create_table)
sql_import = "COPY data_raw(time_string,unix_time,instance,product,username,event,attributes) FROM STDIN DELIMITER E'\t';"
cur.copy_expert(sql_import, open('jira_clean.tsv', "r",encoding="utf8"))
conn.commit()<jupyter_output><empty_output><jupyter_text>update table to have date<jupyter_code>sql_calc_table = """
DROP TABLE IF EXISTS data_prep;

CREATE TABLE data_prep as (
    select 
        row_index
        , to_timestamp(time_string,'YYYY-MM-DD HH24:MI,MS')::timestamp without time zone as time_parsed
        , unix_time
        , instance
        , username
        , event
        , attributes
    from
        data_raw
);
"""
cur.execute(sql_calc_table)
conn.commit()<jupyter_output><empty_output><jupyter_text>SQLAlchemy<jupyter_code># Finally, let's instantiate a SQL alchemy engine, so we can pass results sets into pandas and evaluate them here 
connection_str = 'postgresql+psycopg2://'+dbuname+':'+dbpassword+'@'+dbhost+':'+dbport+'/'+dbname
try:
    engine1 = sqlalchemy.create_engine(connection_str)
    conn1 = engine1.connect()
except:
    print('Database connection error - check creds')
engine1.table_names() # Confirm connection and tables are present as expect<jupyter_output><empty_output><jupyter_text>More detailed requests from wikipedia<jupyter_code>import requests
requests_session = requests.Session()
url = 'https://en.wikipedia.org/w/api.php'
params = {
    'rcstart'     : '2021-10-20T00:30:01Z',
    'rcdir'       : 'newer',
    'rcnamespace' : '0',
    'format'      : 'json',
    'rcprop': 'user|userid|comment|flags|timestamp|title|ids|sizes|redirect|tags|loginfo',
    'list'        : 'recentchanges',
    'action'      : 'query',
    'rclimit'     : '50',
    'rcshow'      : '!bot'
}
fields = ['anon', 'comment', 'logaction', 'logid', 'logparams', 
          'logtype', 'minor', 'new', 'newlen', 'ns', 
          'old_revid', 'oldlen', 'pageid', 'rcid', 'redirect', 
          'revid', 'tags', 'tagstags', 'timestamp', 'title', 
          'type', 'user', 'userid']
len(fields);
# List of wikipedias is used to filter wikipedia edits out from other wiki projects
wkps = pd.read_csv('../data/external/wikipedias.csv').assign(code=lambda x: x.abbrev + 'wiki')
wkps.columns<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13719282375998793, 'fraction_numerical': 0.036635006784260515, 'mean_word_length': 4.500829187396351, 'pattern_counts': {'":': 0, '<': 68, '<?xml version=': 0, '>': 69, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '10320', 'license': 'MIT', 'n_tokens_mistral': 2525, 'n_tokens_neox': 2365, 'n_words': 471, 'path': 'notebooks/1.xx-sfb-try-mediawikis-recentchanges-api.ipynb', 'repo_name': 'bhrdj/predwikt', 'size': '6633'}