Chapter 3: Reading Time Series Data from Databases

Databases extend what you can store to include text, images, and media files and are designed for efficient read and write operations at a massive scale. Databases can store terabytes and petabytes of data with efficient and optimized data retrieval capabilities, such as when we are performing analytical operations on data warehouses and data lakes. A data warehouse is a database designed to store large amounts of structured data, mostly integrated from multiple source systems, built specifically to support business intelligence reporting, dashboards, and advanced analytics. A data lake, on the other hand, stores a large amount of data that is structured, semi-structured, or unstructured in its raw format. In this chapter, we will continue to use the pandas library to read data from databases. We will create time series DataFrames by reading data from relational (SQL) databases and non-relational (NoSQL) databases.

Additionally...

Technical requirements

In this chapter, we will be using pandas 1.4.2 (released April 2, 2022) extensively.

You will be working with different types of databases, such as PostgreSQL, Amazon Redshift, MongoDB, InfluxDB, and Snowflake. You will need to install additional Python libraries to connect to these databases.

You can also download the Jupyter notebooks from this book's GitHub repository (https://github.com/PacktPublishing/Time-Series-Analysis-with-Python-Cookbook) to follow along.

Reading data from a relational database

In this recipe, you will read data from PostgreSQL, a popular open source relational database.

You will explore two methods for connecting to and interacting with PostgreSQL. First, you will start by using psycopg2, a PostgreSQL Python connector, to connect and query the database, then parse the results into a pandas DataFrame. In the second approach, you will query the same database again but this time using SQLAlchemy, an object-relational mapper (ORM) that is well integrated with pandas.

Getting ready

In this recipe, it is assumed that you have the latest PostgreSQL installed. At the time of writing, version 14 is the latest stable version (version 15 is still in beta).

To connect to and query the database in Python, you will need to install psycopg2, a popular PostgreSQL database adapter for Python. You will also need to install SQLAlchemy, which provides flexibility regarding how you want to manage the database, whether it is...

Reading data from Snowflake

A very common place to extract data for analytics is usually a company's data warehouse. Data warehouses host a massive amount of data that, in most cases, contains integrated data to support various reporting and analytics needs, in addition to historical data from various source systems.

The evolution of the cloud brought us cloud data warehouses such as Amazon Redshift, Google BigQuery, Azure SQL Data Warehouse, and Snowflake.

In this recipe, you will work with Snowflake, a powerful Software as a Service (SaaS) cloud-based data warehousing platform that can be hosted on different cloud platforms, such as Amazon Web Services (AWS), Google Cloud Platform (GCP), and Microsoft Azure. You will learn how to connect to Snowflake using Python to extract time series data and load it into a pandas DataFrame.

Getting ready

This recipe assumes you have access to Snowflake. To connect to Snowflake, you will need to install the Snowflake Python connector...

Reading data from a document database (MongoDB)

MongoDB, a NoSQL database, stores data in documents and uses BSON (a JSON-like structure) to store schema-less data. Unlike relational databases, where data is stored in tables that consist of rows and columns, document-oriented databases store data in collections and documents.

A document represents the lowest granular level of data being stored, as rows do in relational databases. A collection, like a table in relational databases, stores documents. Unlike relational databases, a collection can store documents of different schemas and structures.

Getting ready

In this recipe, it is assumed that you have a running instance of MongoDB. To get ready for this recipe, you will need to install the PyMongo Python library to connect to MongoDB.

To install MongoDB using conda, run the following command:

$ conda install -c anaconda pymongo -y

To install MongoDB using pip, run the following command:

$ python -m pip install...

Reading third-party financial data using APIs

In this recipe, you will use a very useful library, pandas-datareader, which provides remote data access so that you can extract data from multiple data sources, including Yahoo Finance, Quandl, and Alpha Vantage, to name a few. The library not only fetches the data but also returns the data as a pandas DataFrame and the index as a DatetimeIndex.

Getting ready

For this recipe, you will need to install pandas-datareader.

To install it using conda, run the following command:

>>> conda install -c anaconda pandas-datareader -y

To install it using pip, run the following command:

>>> pip install pandas-datareader 

How to do it…

In this recipe, you will use the Yahoo API to pull stock data for Microsoft and Apple. Let's get started:

1. Let's start by importing the necessary libraries:

   import pandas as pd
   import datetime
   import matplotlib.pyplot as plt
   import pandas_datareader.data...

Reading data from a time series database (InfluxDB)

A time series database, a type of NoSQL database, is optimized for time-stamped or time series data and provides improved performance, especially when working with large datasets containing IoT data or sensor data. In the past, common use cases for time series databases were mostly associated with financial stock data, but their use cases have expanded into other disciplines and domains. InfluxDB is a popular open source time series database with a large community base. In this recipe, we will be using InfluxDB's latest release; that is, v2.2. The most recent InfluxDB releases introduced the Flux data scripting language, which you will use with the Python API to query our time series data.

For this recipe, we will be using the National Oceanic and Atmospheric Administration (NOAA) water sample data provided by InfluxDB. For instructions on how to load the sample data, please refer to the InfluxDB official documentation at...