[Blogging Intensifies]

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

  • About
  • Code Projects
  • Photo Gallery

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 Tumblr (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)

Like this:

Like Loading...

Related

Posted in: Coding Tagged: Coding, COVID-19, Python, Scripting

Categories

  • collapsCat options: Array ( [title] => Categories [showPostCount] => 1 [inExclude] => exclude [inExcludeCats] => Photos, Uncategorized, mastodon-feed, goodreads [showPosts] => 0 [showPages] => 0 [linkToCat] => 1 [olderThan] => 0 [excludeAll] => 0 [catSortOrder] => ASC [catSort] => catName [postSortOrder] => ASC [postSort] => postTitle [expand] => 0 [defaultExpand] => Technology, Maker, Coding, Hobbies [debug] => 1 [postTitleLength] => 0 [catfeed] => none [taxonomy] => category [post_type] => post [postDateAppend] => after [postDateFormat] => m/d [showPostDate] => 1 [useCookies] => 1 [postsBeforeCats] => 1 [expandCatPost] => 1 [showEmptyCat] => 1 [showTopLevel] => 1 [useAjax] => 0 [customExpand] => [customCollapse] => [style] => kubrick [accordion] => 1 [title_link] => [addMisc] => 1 [addMiscTitle] => [number] => 2 [includeCatArray] => Array ( ) [expandSym] => ► [collapseSym] => ▼ ) postsToExclude: Array ( ) CATEGORY QUERY RESULTS Array ( [0] => WP_Term Object ( [term_id] => 641 [name] => 100DaysOfCode [slug] => 100daysofcode [term_group] => 0 [term_taxonomy_id] => 641 [taxonomy] => category [description] => [parent] => 172 [count] => 14 [filter] => raw ) [1] => WP_Term Object ( [term_id] => 486 [name] => Advent of Code [slug] => advent-of-code [term_group] => 0 [term_taxonomy_id] => 486 [taxonomy] => category [description] => [parent] => 172 [count] => 27 [filter] => raw ) [2] => WP_Term Object ( [term_id] => 666 [name] => AI Art [slug] => ai-art [term_group] => 0 [term_taxonomy_id] => 666 [taxonomy] => category [description] => [parent] => 153 [count] => 5 [filter] => raw ) [3] => WP_Term Object ( [term_id] => 438 [name] => Books [slug] => books [term_group] => 0 [term_taxonomy_id] => 438 [taxonomy] => category [description] => [parent] => 436 [count] => 4 [filter] => raw ) [4] => WP_Term Object ( [term_id] => 172 [name] => Coding [slug] => programming [term_group] => 0 [term_taxonomy_id] => 172 [taxonomy] => category [description] => [parent] => 153 [count] => 11 [filter] => raw ) [5] => WP_Term Object ( [term_id] => 541 [name] => Concerts [slug] => concertphotos [term_group] => 0 [term_taxonomy_id] => 541 [taxonomy] => category [description] => [parent] => 527 [count] => 7 [filter] => raw ) [6] => WP_Term Object ( [term_id] => 155 [name] => Devices (Phones and Tablets) [slug] => devices [term_group] => 0 [term_taxonomy_id] => 155 [taxonomy] => category [description] => [parent] => 166 [count] => 9 [filter] => raw ) [7] => WP_Term Object ( [term_id] => 622 [name] => Elite Dangerous [slug] => elite-dangerous [term_group] => 0 [term_taxonomy_id] => 622 [taxonomy] => category [description] => [parent] => 523 [count] => 8 [filter] => raw ) [8] => WP_Term Object ( [term_id] => 606 [name] => Fairs [slug] => fairs [term_group] => 0 [term_taxonomy_id] => 606 [taxonomy] => category [description] => [parent] => 527 [count] => 8 [filter] => raw ) [9] => WP_Term Object ( [term_id] => 523 [name] => Feeds [slug] => feeds [term_group] => 0 [term_taxonomy_id] => 523 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [11] => WP_Term Object ( [term_id] => 436 [name] => Hobbies [slug] => hobbies [term_group] => 0 [term_taxonomy_id] => 436 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [12] => WP_Term Object ( [term_id] => 656 [name] => IOT Projects [slug] => iot [term_group] => 0 [term_taxonomy_id] => 656 [taxonomy] => category [description] => [parent] => 153 [count] => 19 [filter] => raw ) [13] => WP_Term Object ( [term_id] => 446 [name] => Language [slug] => language [term_group] => 0 [term_taxonomy_id] => 446 [taxonomy] => category [description] => [parent] => 436 [count] => 1 [filter] => raw ) [14] => WP_Term Object ( [term_id] => 524 [name] => Letterboxed [slug] => letterboxed [term_group] => 0 [term_taxonomy_id] => 524 [taxonomy] => category [description] => [parent] => 523 [count] => 276 [filter] => raw ) [15] => WP_Term Object ( [term_id] => 653 [name] => Link List [slug] => link-list [term_group] => 0 [term_taxonomy_id] => 653 [taxonomy] => category [description] => [parent] => 523 [count] => 29 [filter] => raw ) [16] => WP_Term Object ( [term_id] => 224 [name] => Linux & Open Source [slug] => linux [term_group] => 0 [term_taxonomy_id] => 224 [taxonomy] => category [description] => [parent] => 166 [count] => 6 [filter] => raw ) [17] => WP_Term Object ( [term_id] => 153 [name] => Maker [slug] => maker [term_group] => 0 [term_taxonomy_id] => 153 [taxonomy] => category [description] => [parent] => 0 [count] => 2 [filter] => raw ) [19] => WP_Term Object ( [term_id] => 530 [name] => Micro Blog [slug] => microblog [term_group] => 0 [term_taxonomy_id] => 530 [taxonomy] => category [description] => [parent] => 0 [count] => 55 [filter] => raw ) [20] => WP_Term Object ( [term_id] => 437 [name] => Music [slug] => music [term_group] => 0 [term_taxonomy_id] => 437 [taxonomy] => category [description] => [parent] => 436 [count] => 17 [filter] => raw ) [21] => WP_Term Object ( [term_id] => 395 [name] => My DIY Projects [slug] => my-diy-projects [term_group] => 0 [term_taxonomy_id] => 395 [taxonomy] => category [description] => [parent] => 153 [count] => 7 [filter] => raw ) [22] => WP_Term Object ( [term_id] => 154 [name] => Opinion/Editorial/Life [slug] => articles [term_group] => 0 [term_taxonomy_id] => 154 [taxonomy] => category [description] => [parent] => 0 [count] => 18 [filter] => raw ) [23] => WP_Term Object ( [term_id] => 491 [name] => Organizing [slug] => organizing [term_group] => 0 [term_taxonomy_id] => 491 [taxonomy] => category [description] => [parent] => 436 [count] => 7 [filter] => raw ) [24] => WP_Term Object ( [term_id] => 534 [name] => Other Photos [slug] => otherphotos [term_group] => 0 [term_taxonomy_id] => 534 [taxonomy] => category [description] => [parent] => 527 [count] => 12 [filter] => raw ) [25] => WP_Term Object ( [term_id] => 617 [name] => Outdoor and Nature [slug] => outdoor [term_group] => 0 [term_taxonomy_id] => 617 [taxonomy] => category [description] => [parent] => 527 [count] => 4 [filter] => raw ) [26] => WP_Term Object ( [term_id] => 242 [name] => PC Hardware [slug] => pcs [term_group] => 0 [term_taxonomy_id] => 242 [taxonomy] => category [description] => [parent] => 166 [count] => 6 [filter] => raw ) [28] => WP_Term Object ( [term_id] => 712 [name] => Programming Projects [slug] => projects [term_group] => 0 [term_taxonomy_id] => 712 [taxonomy] => category [description] => [parent] => 172 [count] => 7 [filter] => raw ) [29] => WP_Term Object ( [term_id] => 241 [name] => Synology NAS [slug] => synology-nas [term_group] => 0 [term_taxonomy_id] => 241 [taxonomy] => category [description] => [parent] => 166 [count] => 5 [filter] => raw ) [30] => WP_Term Object ( [term_id] => 166 [name] => Technology [slug] => technology [term_group] => 0 [term_taxonomy_id] => 166 [taxonomy] => category [description] => [parent] => 0 [count] => 9 [filter] => raw ) [31] => WP_Term Object ( [term_id] => 424 [name] => The Basement [slug] => the-basement [term_group] => 0 [term_taxonomy_id] => 424 [taxonomy] => category [description] => [parent] => 153 [count] => 6 [filter] => raw ) [32] => WP_Term Object ( [term_id] => 557 [name] => Toy Photos [slug] => toyphotos [term_group] => 0 [term_taxonomy_id] => 557 [taxonomy] => category [description] => [parent] => 527 [count] => 0 [filter] => raw ) [33] => WP_Term Object ( [term_id] => 1 [name] => Uncategorized [slug] => uncategorized [term_group] => 0 [term_taxonomy_id] => 1 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [34] => WP_Term Object ( [term_id] => 280 [name] => Windows [slug] => windows [term_group] => 0 [term_taxonomy_id] => 280 [taxonomy] => category [description] => [parent] => 166 [count] => 2 [filter] => raw ) [35] => WP_Term Object ( [term_id] => 538 [name] => Zoos [slug] => zoophotos [term_group] => 0 [term_taxonomy_id] => 538 [taxonomy] => category [description] => [parent] => 527 [count] => 12 [filter] => raw ) ) POST QUERY: POST QUERY RESULTS
  • ►Feeds (313)
    • Elite Dangerous (8)
    • Letterboxed (276)
    • Link List (29)
  • ▼Hobbies (29)
    • Books (4)
    • Language (1)
    • Music (17)
    • Organizing (7)
  • ▼Maker (98)
    • AI Art (5)
    • ▼Coding (59)
      • 100DaysOfCode (14)
      • Advent of Code (27)
      • Programming Projects (7)
    • IOT Projects (19)
    • My DIY Projects (7)
    • The Basement (6)
  • ►Micro Blog (55)
  • ►Opinion/Editorial/Life (18)
  • ▼Technology (37)
    • Devices (Phones and Tablets) (9)
    • Linux & Open Source (6)
    • PC Hardware (6)
    • Synology NAS (5)
    • Windows (2)
  • ►Uncategorized (0)

MastodonLinkedIn

emailInstagramInstagram

GitHubLetterboxdDuolongo
GoodreadsLast.fmElite Dangerous INARA
Lameazoid Logo


Copyright © 2023 [Blogging Intensifies].

Me WordPress Theme by themehall.com

%d bloggers like this: