Add database to store results

[dgarros]

I've built a database based on SQLite to store & query the results of the surveys.

The schema has been inspired by the work from the Winona university to be able to track the responses of the same questions over multiple edition. https://www.winona.edu/ipar/Media/AIRUM_2011_Building_a_Database.pdf

Query the database

There is small library of functions to help query the data from the database.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from lib.schema import *
from lib.query import *

# Initialize the session to the database
engine = create_engine(f"sqlite:///results/netdevops_survey.sqlite3")
Base.metadata.bind = engine
Session = sessionmaker(bind=engine)
session = Session()

# Get question object for `operation-automated`
q = session.query(Questions).get("operation-automated")

# Get results for all survey for a given question (here `operation-automated`)
get_q_results_over_time(session, q)

Out[11]:
                                              2016    2019
Anomaly detection                           17.466  14.706
Compliance Checks                           24.315  39.338
Configuration archiving                     70.548  63.235
Configuration deployment                    58.904  66.912
Configuration generation                    64.041  69.853
Data Collection / Reporting                 51.370  46.691
Day 0 Provisioning using a vendor solution  26.712  33.088
Fault Remediation                              NaN  13.235
Pre/Post Change Checks                      23.630  38.235
Software qualification                       6.164  15.074
Software upgrade                            25.000  30.147
Topology Mapping                               NaN  21.324
Troubleshooting                             15.411  22.794

Load the data into the database

There is a tool to initialize and load the data into the database from the TSV files

cd tools
python manage_db.py --init --load-response --load-question

Schema