Working with databases - Reading an online dataset and storing it in SQLite

Go to Stats NZ, we will work on this dataset: https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-March-2022-quarter/Download-data/consumers-price-index-march-2022-seasonally-adjusted.csv

First, we load the ipython-sql extension and establish a connection with the database

In [1]:
import csv, sqlite3

con = sqlite3.connect("statsnz.db")
cur = con.cursor()

Preparing the enviroment

In [2]:
!pip install -q pandas==1.1.5
In [3]:
%load_ext sql
In [4]:
%sql sqlite:///statsnz.db

Second, we will read the csv file from Stats NZ and store it into pandas dataframe

In [5]:
import pandas
df = pandas.read_csv("https://www.stats.govt.nz/assets/Uploads/Consumers-price-index/Consumers-price-index-March-2022-quarter/Download-data/consumers-price-index-march-2022-seasonally-adjusted.csv")
df.to_sql("statsnz", con, if_exists='replace', index=False,method="multi")

Third, we will verify that the table was created by retrieving the tables in the database

In [6]:
%%sql
SELECT name
FROM sqlite_master
WHERE type='table'

 * sqlite:///statsnz.db
Done.
Out[6]:
name
statsnz

Fourth, let's now perform a simple query on our table, for example, let's get the period in the dataset with the highest data value

In [7]:
%%sql
SELECT max(Data_value), Period
FROM statsnz

 * sqlite:///statsnz.db
Done.
Out[7]:
max(Data_value) Period
11898.0 2006.06
In [ ]: