The Art of PostgreSQL/

...

/

The First Use Case

The First Use Case

Learn how to use SQL in the application code for Python.

We'll cover the following...

The Intercontinental Exchange provides a chart with daily NYSE group volumes. We can fetch the Excel file, which is a CSV file that uses tab spacing as a separator, remove the headings, and load it into a PostgreSQL table.

Loading the data set

Here’s what the data looks like with comma-separated thousands and dollar signs, so we can’t readily process the figures as numbers:

2010	1/4/2010	1,425,504,460	4,628,115	$38,495,460,645
2010	1/5/2010	1,754,011,750	5,394,016	$43,932,043,406
2010	1/6/2010	1,655,507,953	5,494,460	$43,816,749,660
2010	1/7/2010	1,797,810,789	5,674,297	$44,104,237,184

We create an ad-hoc table definition, and once the data is loaded, we then transform it into a proper SQL data type, with the help of the alter table command.

Press + to interact
begin;
create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );
-- datestyle of the database to ISO, MDY
\copy factbook from 'factbook.csv' with delimiter E'\t' null ''
alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,
   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;
commit;

We use the PostgreSQL copy functionality ...