Python github api demo

get_events.py

#!/usr/bin/env python3
import json
import random
import requests
import sqlite3

EVENTS_URL = 'https://api.github.com/events'

db = sqlite3.connect("github.sqlite")


def init_db():
    c = db.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS events(
                id INTEGER PRIMARY KEY,
                type TEXT,
                actor_login TEXT,
                repo_name TEXT,
                json TEXT);
                ''')
    db.commit()


def main():
    init_db()

    page = 1  # random.randint(1, 10)
    r = requests.get(f"{EVENTS_URL}?page={page}")
    events = r.json()

    c = db.cursor()
    for event in events:
        event_type = event['type']
        actor_login = event['actor']['login']
        repo_name = event['repo']['name']
        json_raw = json.dumps(event)

        c.execute('''
            INSERT OR REPLACE INTO events
                    (id, type, actor_login, repo_name, json)
            VALUES (?,?,?,?,?);
        ''', (event['id'], event_type, actor_login, repo_name, json_raw))
        db.commit()

    c.execute('''
        SELECT type, COUNT(*) FROM events GROUP BY type ORDER BY COUNT(*) DESC
    ''')
    for row in c.fetchall():
        print(f"{row[0]}: {row[1]}")

    print("---")
    c.execute('''
        SELECT COUNT(*) FROM events
    ''')
    print("Total events:", c.fetchone()[0])


if __name__ == '__main__':
    main()

github_report.py

#!/usr/bin/env python3
import sqlite3
from jinja2 import Template

db = sqlite3.connect("github.sqlite")

def top_users():
    c = db.cursor()
    c.execute('''
        SELECT actor_login, COUNT(*) FROM events 
        GROUP BY actor_login 
        ORDER BY COUNT(*) DESC 
        LIMIT 10
    ''')
    return c.fetchall()

def total_events():
    c = db.cursor()
    c.execute('''
        SELECT COUNT(*) FROM events
    ''')
    return c.fetchone()[0]

def main():
    # load jinja template
    with open("templates/top_ten.txt.j2") as template_file:
        t = Template(template_file.read())

    context = {
        'top_users': top_users(),
        'total_events': total_events(),
    }
    output = t.render(context)
    print(output)

if __name__ == '__main__':
    main()

github_xls_report.py

#!/usr/bin/env python3
import sqlite3
from openpyxl import Workbook
from openpyxl.styles import Font


db = sqlite3.connect("github.sqlite")


def top_users():
    c = db.cursor()
    c.execute('''
        SELECT actor_login, COUNT(*) FROM events 
        GROUP BY actor_login 
        ORDER BY COUNT(*) DESC 
        LIMIT 10
    ''')
    return c.fetchall()


def total_events():
    c = db.cursor()
    c.execute('''
        SELECT COUNT(*) FROM events
    ''')
    return c.fetchone()[0]



def main():
    # load jinja template

    bold = Font(bold=True)
    workbook = Workbook()
    # get the active worksheet
    sheet = workbook.active
    sheet.title = "Top 10 Users"

    sheet.cell(row=1, column=1).value = "Top 10 users"
    sheet.cell(row=1, column=1).font = bold


    for n,row in enumerate(top_users()):
        sheet.cell(row=n+2, column=1).value = row[0]
        sheet.cell(row=n+2, column=2).value = row[1]

    # set column 1 width
    sheet.column_dimensions['A'].width = 30

    workbook.save(filename="output.xlsx")



if __name__ == '__main__':
    main()

top_ten.txt.j2

{# jinja2 #}

## Top 10 users by number of events

{%  for login,count in top_users %}
* {{ login }}: {{ count }}
{% endfor %}

## Total number of events

{{ total_events }}