Improving Data Storage with SQL

In this chapter, you will learn how to work with a relational SQL database. You'll install and configure PostgreSQL.

As weeks have passed by, there is a growing problem at the lab: CSV files are everywhere! Conflicting copies, missing files, records getting changed by non-data entry staff, and other CSV-related frustrations are plaguing the project. Unfortunately, the password protection in the application does nothing meaningful to prevent anyone from editing the files and corrupting data. It's clear that the current data storage solution is not working out. Something better is needed!

The facility has an older Linux server with a PostgreSQL database installed. You've been asked to update your program so that it stores data in the PostgreSQL database rather than in the CSV files, and authenticates users against the database. This way there can be one authoritative source of data to which the support staff can easily manage access. In addition, the SQL database will help enforce correct data types and allow for more complex data relationships than the simple flat file. This promises to be a major update to your application!

In this chapter, you'll learn the following topics:

  • In PostgreSQL, we'll install and configure the PostgreSQL database system.
  • In Modeling relational data, we'll discuss the art of structuring data in a database for good performance and reliability.
  • In Creating the ABQ database, we'll build a SQL database for the ABQ Data Entry application.
  • In Connecting to PostgreSQL with psycopg2 , we'll use the psycopg2 library to connect our program to PostgreSQL.
  • Finally, in Integrating SQL into our application, we'll update ABQ Data Entry to utilize the new SQL database.

This chapter assumes you have a basic knowledge of SQL. If you don't, please see Appendix B , A Quick SQL Tutorial.