[Blogging Intensifies]

Technology, Projects, Linux, Coding, Internet of Things, Music, Books, Life...

  • About

Tracking Covid-19 into a database using Python

March 29, 2020

At some point I need to do a little write up on my Home Dashboard Project, it’s inspired quite a few minor projects such as this one to make little web widgets. The dashboard is the simple part, it’s just dumping a database query into a table. Honestly, the script was easy too, because I adapted it from another script I built recently.

With COVID-19 all over the news, I wanted to add some stats to my dashboard for my state. Not so much because there aren’t already 1000 other places to get the numbers, but more to see if I could do it. The hardest part was finding a feed to stats. Then I found CovidTracking.com. Which has a nice little API. I then set to work adapting another script to pull from this API to dump stats for Illinois into the database. I am only interested in Illinois, but the script is built so the user can put a list of states into an array, and then it will loop through and add them all to the database.

The script is below, but this also requires some set up in SQL. Nothing complicated, mostly INT fields. an id as an int and primary key, negative_cases, positive_cases, and deaths, all as INT, state as a varchar with a length of 2, though technically the length is optional, then finally date_stamp as a DATETIME field with a default value of the current timestamp. The DATETIME isn’t directly touched here, but it makes it easier to manipulate the data later.

The code also requires you enter your database credentials. I’ve nammed my table “il_covid_stats, but you can change that to whatever you want down below in the “SQL = “INSERT….” line. I’ll leave it up to you what to do with the data, I pull mine into a PHP page.

Anyway, here is the python code:

# Python Covid Star Tracking to SQL
# use of json package
# Sample URL: https://covidtracking.com/api/states?state=IL

import json
import requests
import time
import MySQLdb

mydb = MySQLdb.connect(
  host="localhost",
  user="YOUR_DB_USERNAME",
  passwd="YOUR_DB_PASSWORD",
  database="YOUR_DB_NAME"
)
mycursor = mydb.cursor()
user_agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.47 Safari/537.36'

#States to check as an Array, two letter abbreviations
states = ['IL']

def data_getter(statename):
  ####when reading from remote URL
  url = 'https://covidtracking.com/api/states?state='+statename

  user_agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.47 Safari/537.36'
  headers = {'User-Agent': user_agent}
  response = requests.get(url,headers=headers)
  html = response.content
  statedata = json.loads(html)

  pos_cases = (statedata['positive'])
  neg_cases = (statedata['negative'])
  deaths = (statedata['death'])

  vals = (pos_cases,neg_cases,deaths,statename)

  mysqlinsert(vals)

def mysqlinsert(vals):
  ## This database name and columns can be changed but should be pre made in your database
  SQL = "INSERT INTO il_covid_stats (positive_cases, negative_cases, deaths, state) VALUES (%s, %s, %s, %s)"
  mycursor.execute(SQL, vals)
  mydb.commit()

# Loop through URLs for each state
for i in states:
  data_getter(i)

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email this to a friend (Opens in new window)

Related

Posted in: Coding Tagged: Coding, COVID-19, Python, Scripting
Twitter LinkedIn email
Instagram Instagram Instagram
GitHub
JoshMiller.net
Lameazoid.com
Photo Gallery

Categories

  • ►Devices (18)
    • Android (3)
    • PCs (3)
    • Synology NAS (4)
    • Windows Phone (3)
  • ►Lifestyle (22)
    • Books (4)
    • Language (1)
    • Music (10)
    • Organizing (5)
  • ▼Maker (65)
    • Arduino (8)
    • CHIP (5)
    • ▼Coding (25)
      • Advent of Code 2020 (12)
    • Hardware (1)
    • Home Security (2)
    • My DIY Projects (3)
    • Non-Tech (2)
    • Raspberry Pi (9)
    • The Basement (6)
    • The Cloud (3)
  • ►Opinion/Editorial (8)
    • Copyright and You (3)
    • Privacy (2)
    • Social Media (1)
  • ►OS (3)
    • Linux & Open Source (1)
    • Windows (2)
  • ►Technology (5)
    • Security (1)
  • ►What I Use (10)
    • Hardware (3)
    • Photography (2)
    • Software (5)

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 611 other subscribers

Hosted on…


Help support hosting with our referral link!

Copyright © 2021 [Blogging Intensifies].

Me WordPress Theme by themehall.com

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.