<jupyter_start><jupyter_text>Introduction to Python Day 2 Workbook Contents 1. [Example - Web Scraping](1)2. [Example - Mars weather API](2)3. [Case study - Retrieve JSON data from API](3)4. [Example - Basic matplotlib](4)5. [Case study - Free choice visualisation](5)6. [Exercise - DataFrame to SQL](6)  1. Example - Web scraping[Back to contents](cont)<jupyter_code># required modules
import requests

#a url we are interested in
test_url = r'https://www.ft.com/'

# use requests module to send a request
page = requests.get(test_url)

# check the status code of the request (starting with 2 is good, 4& 5 are bad..)
page.status_code
# check the first chunk of html code that was returned
page.text[0:500]
from bs4 import BeautifulSoup

# use beautiful soup to format this
soup = BeautifulSoup(page.text, 'html.parser')
# friendly screen print format
print(soup.prettify())
# looks better - now let's look for certain characteristics
# e.g. I know from investigating that linked headlines are tagged with <a>
# and class = 'js_teaser-heading-link

# let's find all instances like this:
found_list = soup.find_all('a', class_ = 'js-teaser-heading-link')

# list comprehension to create a list of just headline text
headline_list = [i.contents[0] for i in found_list]

# use pprint module to nicely print this list
from pprint import pprint
pprint(headline_list)<jupyter_output>['UK strikes ‘historic’ trade deal with Japan',
 '<NAME>: ‘I thought it would be trial by tweet’',
 'Coronavirus latest: US consumer prices rise for third month as demand picks '
 'up',
 'New Covid cases in England doubling every 7-8 days',
 'Sunak gives himself option of postponing autumn Budget',
 'Rio Tinto CEO quits after backlash over Aboriginal site destruction',
 'Bankruptcy, jail, ruined lives: inside the Post Office scandal',
 'Is China the closest thing to investing normality now? ',
 'Young people want to let off steam, not kill their grans',
 'Brexit tensions give sterling its worst week since March',
 'ECB chief economist warns against complacency over recovery',
 '<NAME> seeks to tame the Citigroup ‘beast’',
 '<NAME> and the Swedish Covid experiment',
 'UK economy grows 6.6% in July as coronavirus restrictions eased',
 'Covid vaccine becomes divisive issue in US election campaign',
 'Coronavirus tracked: the latest figures as countries fight Covid-19 '
 [...]<jupyter_text>2. Example - Mars weather API[Back to contents](cont)<jupyter_code>import requests

# using bare url with key embedded
url = 'https://api.nasa.gov/insight_weather/?api_key=LtPq1YXjShLrSdmJtYPe0XANbCnTRhrK5vfK0sls&feedtype=json'

# make the request and store in response object
response = requests.get(url)

#check status code
response.status_code
# look at the data we have returned
response.json() 
# looking at the data there are sol_keys which indicate which 'Martian day'
response.json()["sol_keys"]
# looks good - let's try to loop over the keys and flatten each day
import pandas as pd

day_dict = {}
# iterate over the key for each day
for day in response.json()['sol_keys']:
    # get data for that day
    day_data = response.json()[day]
    # flatten data for that day and put in dataframe
    df = pd.json_normalize(day_data)
    # store df in dictionary
    day_dict[day] = df
    
    #or in one line
    #day_dict[day] = pd.json_normalize(response.json()[day])

# join our dicts together
pd.concat(day_dict)<jupyter_output><empty_output><jupyter_text>3. Case Study - retrieve JSON data from API[Back to contents](cont) RequirementRetrieve COVID data for the UK from the following url: https://api.covid19api.com/dayone/country/united-kingdom/live and format it into a DataFrame.Use the pandas and requests modules. You shouldn't need the json moduleAPI documentation can be found here https://documenter.getpostman.com/view/10808728/SzS8rjbc. You can tweak the URL if you want to return a different dataset - just follow the rules listed here.<jupyter_code># your code goes here<jupyter_output><empty_output><jupyter_text>4. Example - basic matplotlib [Back to contents](cont)<jupyter_code># The following line is only for jupyter notebooks:
%matplotlib inline
# inline doesn't work in an ide for example
"""
Simple demo of a scatter plot.
"""
import numpy as np
import matplotlib.pyplot as plt

# generate some random x,y coords
N = 50
x = np.random.rand(N)
y = np.random.rand(N)
colors = np.random.rand(N)

# 0 to 15 point radiuses
# point area based on random
area = np.pi * (15 * np.random.rand(N))**2

#optional step define figure for control on size etc
fig, ax = plt.subplots(figsize = (10,4))

# product plot
ax.scatter(x, y, s=area, c=colors, alpha=0.5)

# display plot
plt.show<jupyter_output><empty_output><jupyter_text>5. Case study - explore visualisations[Back to contents](cont) RequirementPick a module which we discussed and make a visualisation using the UK COVID dataset retrieved earlier. Matplotlib is easiest - but other modules may produce better visualisations. Make liberal use of google to determine how to make the type of plot you want!<jupyter_code># your code here<jupyter_output><empty_output><jupyter_text>6. Exercise - DataFrame to SQL[Return to contents](cont) Requirement
Use the function below (which creates a SQL engine connection based on your Windows AD permissions) to push a sql table to the database using .to_sql().

N.B.
- Do this in pairs / groups to minimise database load
- BE CAREFUL WITH TABLE NAMES - you may overwrite tables if names clash
- If you have access to a sandbox please use this, otherwise please use common sense
- You must have access to the DB through your Windows permissions or the transfer will fail

Let facilitators know if you want to be added to a sandbox and we can add you to **SERVER_NAME**   **DB_NAME**<jupyter_code># run this to declare the f_connect_sql function
from sqlalchemy import create_engine
from urllib.parse import quote_plus
from sys import exit

def f_connect_sql(server: str, db: str, driver: str = '{ODBC Driver 17 for SQL Server}', dbapi: str = 'mssql+pyodbc:///?odbc_connect='):
    ver_list = ['17','13.1','13','11']
    for i, v in enumerate(ver_list):
        try:
            conn_string = 'DRIVER={ODBC Driver %s for SQL Server};SERVER=%s;DATABASE=%s;Trusted_Connection=Yes;TrustServerCertificate=No;APP={Microsoft® Windows® Operating System}' % (v, server, db)
            conn_url = quote_plus(conn_string)
            engine = create_engine(dbapi+conn_url, echo=False, fast_executemany = True)
            engine.execute('SELECT TOP 1 * FROM sys.Tables')
            print("\nSQL connection succeeded with driver 'ODBC Driver %s for SQL Server'..." %v)
            break
        except:
            if i == len(ver_list) -1:
                print("\nSearch for SQL driver failed - exiting. Refer to additional errors below:\n")
                exit()
            else:
                print("\nSearch for driver 'ODBC Driver %s for SQL Server failed' - reverting to next..." %v)
            
    return engine
# your code goes here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08883536412581468, 'fraction_numerical': 0.010626239727968263, 'mean_word_length': 4.225018504811251, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '126648', 'license': 'MIT', 'n_tokens_mistral': 2156, 'n_tokens_neox': 2068, 'n_words': 857, 'path': 'Day2 - Questions.ipynb', 'repo_name': 'jasboyle/training', 'size': '7076'}