Doctorbase

Introduction

A common task in data management systems is receiving data from an external system in XML, JSON, or CSV files and storing it in a relational database. Python includes libraries for easily reading these files and interacting with databases. We store data in databases for record keeping and so that we can answer questions about the data.

In this assignment you will

• write a Python program that parses input data files and inserts the data from them into a database, and

• create a SQL script with queries to answer questions about data in the database.

Problem Description

You’re starting a new job as a junior data scientist at the Centers for Disease Control (CDC). In this role you need to manage and analyze data about doctors and patient care. You receive doctor data from outside sources in the form of XML, CSV and JSON files which you need to insert into your database, and you use the database to answer questions about the data.

Solution Description

Write a Python script to import data from files and insert the data into the database. Create your database with this database schema script: doctors-schema.sql. You should read the database schema script to understand the database.

Import data into your database

Write a Python script named import_doctors.py that takes five command line arguments:

• the database user with which to execute SQL commands in your script,
• the password of the database user (if empty string, use"" as command line argument),
• an XML file containing doctor information (example: doctors.xml),
• a CSV files containing patient information (example: patients.csv), and
• a JSON file containing visit information (example: visits.json).

Your script should insert information from the files above into the appropriate tables in the database with appropriate key and foreign key values.

Query your database

Write a SQL script, doctors-queries.sql, that includes queries to answer the following questions:

• What are the first names and last names of the patients who have cardiologists for primary care providers (PCP)?

• What are the first names and last names of the patients who saw their doctor (PCP) in May 2010?

• OPTIONAL BONUS (5 points): What are the first name and last name of the doctor who has the most patients (not the most visits)?

• OPTIONAL BONUS (5 points): What are the first names and last names of the doctors who have no patients (not visits)?

Your doctors-queries.sql should contain only the SELECT queries requested above.

Tips and Considerations

• Create the database using the SQL script procedures we leared in class.
• Try SQL statements and queries at the interactive MySQL prompt.
• Make sure you’ve installed the PyMySQL module with conda install pymysql.
• You can also try Python DB-API functions in a Python shell like iPython.
• Many tables have AUTO_INCREMENT primary keys, meaning you don’t have to supply a key value when you insert new rows into those tables. You’ll need those ids after inserting the rows, which you can get with cursor.lastrowid. See the course slides for the Python DB-API or the Python DB-API docs.

Turn-in Procedure

Submit your import_doctors.py, doctors-queries.sql to the assignment on Canvas as attachments.

Verify the Success of Your Submission to Canvas

Practice safe submission! Verify that your HW files were truly submitted correctly, the upload was successful, and that your program runs with no syntax or runtime errors. It is solely your responsibility to turn in your homework and practice this safe submission safeguard.

• After submitting the files to Canvas, return to the Assignment menu option and this homework. It should show the submitted files.
• Download copies of your submitted files from the Canvas Assignment page placing them in a new folder.
• Re-run and test the files you downloaded from Canvas to make sure it’s what you expect.

• This procedure helps guard against a few things.

  • It helps insure that you turn in the correct files.
  • It helps you realize if you omit a file or files.\footnote{Missing files will not be given any credit, and non-compiling homework solutions will receive few to zero points. Also recall that late homework will not be accepted regardless of excuse. Treat the due date with respect. Do not wait until the last minute! (If you do discover that you omitted a file, submit all of your files again, not just the missing one.)
  • Helps find syntax errors or runtime errors that you may have added after you last tested your code.