STUDY OF U.S. MASS SCHOOL SHOOTINGS

By: Gavin Schilling

Introduction

While there are records of instances of school shootings in the United States dating back to the Enoch Brown school massacre in Greencastle, PA on July 26, 1764, the first mass school shooting according to the modern convention of counting a mass school shooting as being a three person or more casualty on the actual school grounds did not occur until March 26, 1893 in Plain Dealing, LA during an evening dance at the school. According to Wikipedia between then and 1966 only three other mass school shootings occurred. They were as follows:

• The December 13, 1898 Charleston, WV school exhibition incident killing 6 and wounding at least 1 other
• The May 6, 1940 Pasadena, CA - South Pasadena Junior High School - murders leaving 5 dead and 2 others wounded
• The February 2, 1960 Hartford City, IN - William Reed School - shooting killing 3

This means that only four modern day standard mass school shootings occurred before 1966 even though around 130 school-related shootings are on record with Wikipedia from this early time period in America history. Thankfully, the rest of these horrific events do not meet the modern definition of being a mass school shooting by today's standards as outlined above.

In 1968 & 1970, a total of 9 student protesters were killed and another 48 were injured at the hands of United States government officers. Those two years the following three major mass incidents occurred included the disgraceful South Carolina State University racist protesters in Orangeburg, SC on February 8, 1968, the Kent State University Vietnam War protesters being gunned down by armed National Guard solders on May 4, 1970 in Kent, OH, and when the police shot the protestors at the Jackson State University in Jackson, MS over the United States' military presence in Cambodia on May 15, 1970 (occurring only eleven days following the Kent State massacre).

While, the Columbine High School massacre did not happen until 1999, these attacks by the United States government and the August 1, 1966 University of Texas - Austin massacre in Austin, Texas killing 17 and wounding 31 paired with the November 12, 1966 (roughly 3.5 months after the University of Texas massacre) in Mesa, AZ where 5 died and another 2 were injured, set off a new type of shock value for the vast increase number killed in any particular incident.

Now, in the first month and a half of 2018, the United States not only has had the deadliest mass school shooting in United States history, but has had the school shooting rate increase to 1 occurring in every 60 hours. Many National Rifle Association (NRA) backed politicians continue to look the other way or actively try to stall any and all gun-control reform or legislation.

Procedures

First, as a best practice we import our required libraries at the top of a file (both for easy access and documenting reference; as well as, for the functional purpose of ensuring the file has been properly loaded prior to any need within the program itself).

try:        # Import Libraries
    %matplotlib inline

    from csv import DictReader
    from http import HTTPStatus
    from io import BytesIO
    from json import loads
    from sqlite3 import connect, Error
    from urllib.request import build_opener, urlopen
    from zipfile import ZipFile

    import matplotlib.pyplot as plt
    import pandas as pd
    import requests
    from bs4 import BeautifulSoup
    from openpyxl import load_workbook
    
    print("All imports loaded successfully")

except:
    print("WARNING: Error importing, so results are to be dismissed")

All imports loaded successfully

Next, let's retrieve the first dataset necessary for the project via the internet -- the Stanford MSA Database and transverse the virtual file structure to the appropriate worksheet for creating our first SQL database table.

import sqlite3


# Unzip Stanford MSA Database CSV file

msa_url = "https://stanford.box.com/shared/static/75lmj9rvw9mhtfqfjijhcc6oa15o5m4a.zip"

try:
    try:
        open_msa_url = urlopen(msa_url)

        try:
            msa_zipped = ZipFile(BytesIO(open_msa_url.read()))
        except:
            print("Error reading Stanford MSA zipped file")

        try:
            msa_zipped.extractall('store')
        except:
            print("Error extracting data from Standford MSA zipped file")

        try:
            msa_zipped.namelist() # Proves proper connection occured
            print("Data passes connection validation test")
        except:
            print("WARNING: Data did not pass connection validation test, so disregard results as RESULTS CANNOT AT THIS TIME BE INDIVIDUALLY AUTHENTICATED")

        try:
            msa_zipped.close()
        except:
            print("Error occurred closing Stanford MSA file")

        print(f'Special thanks to Stanford University for the Mass Shooting dataset \n')

    except:
        print("Sorry you are having trouble; please contact Stanford University regarding obtaining a different copy of the MSA dataset file")

    
    try:      # Load Workbook & Proper Worksheet
        workbook = load_workbook('store/MSA/Stanford_MSA_Database_for_release_06142016.xlsx')
        worksheet = workbook.active
        HTTPStatus.OK.description
    except Error as error:
        print(f"Error: {error} \n")      # Proof of Creation


    DB_FILE = 'temp/mass_shootings.db'

      
    try:        # Create a SQL database connection
        sqliteConnection = sqlite3.connect(DB_FILE)
    except Error as error:
        print(f"{sqliteConnection.status_code} occured while attempting to establish a SQL connection instance")     # Proof of Creation

    try:
        sqliteConnection.text_factory = str
    except:
        print(f"{sqliteConnection.text_factory} is instead the text factory read out on SQL connection instance")

    try:
        cursor = sqliteConnection.cursor()
        print("Successfully Connected to SQLite")
    except:
        print(f"{cursor.arraysize} is the improper array size of the cursor on the SQL connection instance")    # Proof of Creation

    try:
        table_name = "Mass_Shootings"
    except:
        print(f"{table_name.status_code} occured while attempting to name the {table_name} SQL table")

finally:       # Close the SQL connection to our SQLite database
    if (sqliteConnection.cursor()):
        sqliteConnection.cursor().close()
        print(f"Closed the SQL connection to the {table_name} SQL table")

Data passes connection validation test
Special thanks to Stanford University for the Mass Shooting dataset 

Successfully Connected to SQLite
Closed the SQL connection to the Mass_Shootings SQL table

Second, while every Congress is unique, within the following file is the makeup of the 2015 - 2016 U.S. Congress as reported by The Washington Post online that we must then convert the JavaScript file ready for web scrapping. In this case we cut off the top and bottom code sections and replace the unquoted keywords to being double-quoted per the JSON library's requirement, for parsing to our second SQL database table.

import sqlite3
import lxml


# File for Web Scrapping & Parsing
try:
    gun_legislation_url = 'https://www.washingtonpost.com/graphics/national/gun-legislation/'
    gun_legislation_html_soup = BeautifulSoup(urlopen(gun_legislation_url), 'lxml')
    gun_legislation = gun_legislation_html_soup.title.text
    stats_json_string = gun_legislation_html_soup.find('script', attrs={'src': 'js/base.js?c=a90e33a675e9d0ac31328c0ce0d2e12b16035540'})
except Error as error:
    print(f"{stats_json_string.status_code} is why {gun_legislation_url} did not load properly")

try:
    full_gun_legislation_url = 'https://www.washingtonpost.com/graphics/national/gun-legislation/js/base.js?c=a90e33a675e9d0ac31328c0ce0d2e12b16035540'
    legislation_request = requests.get(full_gun_legislation_url)
    print(f'Special thanks for the "{gun_legislation}" news article we just parsed via BeautifulSoup 4 from the following web address: {full_gun_legislation_url}\n')
    b = legislation_request.text.find("{first:")
    e = legislation_request.text.find(":6}]")
except Error as error:
    print(f"{legislation_request.status_code} is why {full_gun_legislation_url} did not load properly")

try:
    scrape = legislation_request.text[b - 1:e + 4]
except Error as error:
    print(f"{scrape.status_code} occured while attempting to web scrap {full_gun_legislation_url}")

try:
    scrape = scrape.replace('first:', '"first":')
    scrape = scrape.replace('last:', '"last":')
    scrape = scrape.replace('fullLast:', '"fullLast":')
    scrape = scrape.replace('party:', '"party":')
    scrape = scrape.replace('house:', '"house":')
    scrape = scrape.replace('state:', '"state":')
    scrape = scrape.replace('control:', '"control":')
    scrape = scrape.replace('rights:', '"rights":')
    scrape = scrape.replace('id:', '"id":')
    scrape = scrape.replace('score:', '"score":')
    scrape = scrape.replace('pi:', '"pi":')
    scrape = scrape.replace('grade:', '"grade":')
    scrape = scrape.replace('gi:', '"gi":')
except:
    print(f"{scrape.status_code} occured while attempting to prepare headers for JSON")

try:
    json_scrape = loads(scrape)
except Error as error:
    print(f"{json_scrape.status_code} occured while converting {full_gun_legislation} news article to JSON") 


# Headers
try:
    first = json_scrape[0]["first"]
except Error as error:
    print(f"{first.status_code} occured while scrapping 'first' JSON header")

try:
    last = json_scrape[0]["last"]
except Error as error:
    print(f"{last.status_code} occured while scrapping 'last' JSON header")

try:
    fullLast = json_scrape[0]["fullLast"]
except Error as error:
    print(f"{fullLast.status_code} occured while scrapping 'fullLast' JSON header")

try:
    party = json_scrape[0]["party"]
except Error as error:
    print(f"{party.status_code} occured while scrapping 'party' JSON header")

try:
    house = json_scrape[0]["house"]
except Error as error:
    print(f"{house.status_code} occured while scrapping 'house' JSON header")
        
try:
    state = json_scrape[0]["state"]
except Error as error:
    print(f"{state.status_code} occured while scrapping 'state' JSON header")
        
try:
    control = json_scrape[0]["control"]
except Error as error:
    print(f"{control.status_code} occured while scrapping 'control' JSON header")
        
try:
    rights = json_scrape[0]["rights"]
except Error as error:
    print(f"{rights.status_code} occured while scrapping 'rights' JSON header")
        
try:
    id = json_scrape[0]["id"]
except Error as error:
    print(f"{id.status_code} occured while scrapping 'id' JSON header")
        
try:
    score = json_scrape[0]["score"]
except Error as error:
    print(f"{score.status_code} occured while scrapping 'score' JSON header")
        
try:
    pi = json_scrape[0]["pi"]
except Error as error:
    print(f"{pi.status_code} occured while scrapping 'pi' JSON header")
        
try:
    grade = json_scrape[0]["grade"]
except Error as error:
    print(f"{grade.status_code} occured while scrapping 'grade' JSON header")
        
try:
    gi = json_scrape[0]["gi"]
except Error as error:
    print(f"{gi.status_code} occured while scrapping 'gi' JSON header")

try:
    data = [first, last, fullLast, party, house, state, control, rights, id, score, pi, grade, gi]
except Error as error:
    print(f"{data.status_code} occured while combining JSON headers")

try:
    table_name = "US_Congress"
except:
    print(f"{table_name.status_code} occured while attempting to name SQL table {table_name}")


counter = 0


try:
    try:
        sqliteConnection = sqlite3.connect('Congress.db')
        cursor = sqliteConnection.cursor()
        print("Successfully Connected to SQLite")
    except:
        print(f"Error {table_name} Cursor: {c.arraysize}")    # Proof of Creation

    try:
        cursor.execute(f'DROP TABLE IF EXISTS {table_name};')
    except:
        print(f'{table_name.status_code} occured dropping {table_name}')
    
    try:
        cursor.execute(f'CREATE TABLE IF NOT EXISTS {table_name}'
              '(first,'
              'last,'
              'fullLast,'
              'party,'
              'house,'
              'state,'
              'control,'
              'rights,'
              'id,'
              'score,'
              'pi,'
              'grade,'
              'gi);')
        print(f"{table_name} established")
    except:
        print(f"{table_name.status_code} occurred creating {table_name} SQL table")

    try:
        for row in json_scrape:
            insertsql = cursor.execute(f"INSERT INTO {table_name} VALUES {data};")
            counter += 1
        sqliteConnection.commit()
        print(f"Record(s) inserted successfully into {table_name} table {cursor.rowcount}")
    except sqlite3.Error as error:
        print(f"Failed to insert data into {table_name} table due to {error}")

finally:       # Close the SQL connection to our SQLite database
    if (sqliteConnection.cursor()):
        sqliteConnection.cursor().close()
        print(f"Closed the SQL connection to the {table_name} SQL table")

Special thanks for the "Do your Congress members support stricter gun control? - Washington Post" news article we just parsed via BeautifulSoup 4 from the following web address: https://www.washingtonpost.com/graphics/national/gun-legislation/js/base.js?c=a90e33a675e9d0ac31328c0ce0d2e12b16035540

['Ralph', 'Abraham', 'Abraham', 'r', 'h', 'LA', [], [59, 62, 64, 70, 74, 75, 90, 94, 84], 0, 9, 1, 'AQ', 13]
Successfully Connected to SQLite
US_Congress established
Failed to insert data into US_Congress table due to near "['Ralph', 'Abraham', 'Abraham', 'r', 'h', 'LA', []": syntax error
Closed the SQL connection to the US_Congress SQL table