Improving Data Storage with SQL

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...

PostgreSQL

Python can interact with a wide variety of relational databases, including Microsoft SQL Server, Oracle, MariaDB, MySQL, and SQLite; in this book, we're going to focus on a very popular choice in the Python world, PostgreSQL. PostgreSQL (usually pronounced post-gress, with the "QL" silent) is a free, open source, cross-platform relational database system. It runs as a network service with which you can communicate using client programs or software libraries. At the time of writing, version 13 is the current stable.

Although ABQ has provided a PostgreSQL server that is already installed and configured, you'll need to download and install the software on your workstation for development purposes. Let's take a look at how we can get our workstation ready for PostgreSQL development.

Shared production resources such as databases and web services should never be used for testing or development. Always set up a separate development copy...

Modeling relational data

Our application currently stores data in a single CSV file; a file like this is often called a flat file, because the data has been flattened to two dimensions. While this format works acceptably for our application and could be translated directly to a SQL table, a more accurate and useful data model requires more complexity. In this section, we're going to go through some concepts of data modeling that will help us convert our CSV data into effective relational tables.

Primary keys

Every table in a relational database should have something called a primary key. The primary key is a value, or set of values, that uniquely identifies a record in the table; as such, it should be a value or set of values that is unique and non-null for every row in a table. Other tables in the database can use this field to reference particular rows of the table. This is called a foreign key relationship.

How do we figure out what the primary key is for a set...

Creating the ABQ database

Now that we've modeled our data and gotten a feel for the data types available, it's time to build our database. Make sure you've installed PostgreSQL and created the abq database as described in the first section of this chapter, and let's begin writing SQL to create our database structure.

Under your project root folder, create a new directory called sql. Inside the sql folder, create a file called create_db.sql. We'll start writing our table definition queries in this file.

Creating our tables

The order in which we create our tables is significant. Any table referred to in a foreign key relationship will need to exist before the relationship is defined. Because of this, it's best to start with your lookup tables and follow the chain of one-to-many relationships until all the tables are created. In our ERD, that takes us from roughly the upper left to the lower right.

Creating the lookup tables

We need to...

Connecting to PostgreSQL with psycopg2

Now that we have a nice database to work with, how do we get our application to use it? To make SQL queries from our application, we'll need to install a Python library that can talk directly to our database. In Python, each different SQL product has one or more libraries available that can be used to integrate with it.

For PostgreSQL, the most popular choice is psycopg2. The psycopg2 library is not a part of the Python standard library, so you'll need to install it on any machine running your application. You can find the most current installation instructions at http://initd.org/psycopg/docs/install.html; however, the preferred method is to use pip.

For Windows, macOS, and Linux, the following command should work:

$ pip install --user psycopg2-binary

If that doesn't work, or if you'd rather install it from the source, check the requirements on the website. Take note that the psycopg2 library is written...

Integrating SQL into our application

Converting our application to a SQL backend will be no small task. The application was built around the assumption of the CSV files, and although we've taken care to separate our concerns, many things are going to need to change.

Let's break down the steps we'll need to take:

• We'll need to create a new model to interface with the SQL database.
• Our Application class will need to use the SQL model, and may need to adjust some behaviors as a result.
• The record form will need to be reordered to prioritize our key fields, use the new lookup tables, and auto-populate using information in the database.
• The record list will need to be adjusted to work with the new data model and primary keys.

Let's get started!

Creating a new model

We'll start in models.py by importing psycopg2 and DictCursor:

# models.py
import psycopg2 as pg
from psycopg2.extras import DictCursor

...

Summary

In this chapter, you learned how to work with a relational SQL database. You installed and configured PostgreSQL. You converted a flat-file dataset into relational tables by identifying the primary key fields, choosing correct data types, and normalizing the data structure to reduce the possibility of inconsistencies, redundancies, and anomalies. You learned how to install and work with the psycopg2 library for retrieving and storing data in PostgreSQL. Finally, you went through the arduous task of building a SQL database to hold your ABQ data, building a database model class to interface with the database, and converting the application code to use the new SQL backend.

In the next chapter, we'll be reaching out to the cloud. We'll need to contact some remote servers using different networking protocols to exchange data. You'll learn about the Python standard library's module for working with HTTP, as well as third-party packages for connecting with...