import pandas as pd
import os
from dotenv import load_dotenv

%load_ext sql

load_dotenv() # take environment variables from .env
user = os.environ['DB_USER']
password = os.environ['DB_PASSWORD']
connection_string = "postgresql://{user}:{password}@localhost/ecommerce".format(user=user, password=password)

%%time
%%sql $connection_string

SELECT * FROM public.ecommerce
LIMIT 10

10 rows affected.
CPU times: user 33 ms, sys: 16.7 ms, total: 49.7 ms
Wall time: 74 ms

%%time
%%sql $connection_string

SELECT count(*) FROM public.ecommerce

1 rows affected.
CPU times: user 12.7 ms, sys: 12.8 ms, total: 25.5 ms
Wall time: 4min 23s

%%time
%%sql $connection_string

SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'ecommerce_2';

9 rows affected.
CPU times: user 240 µs, sys: 8.54 ms, total: 8.78 ms
Wall time: 19.4 ms

%%time

query = """
SELECT    date_part('year', event_time)*100+date_part('month', event_time) as year_month
          ,count(*) as cnt
from      ecommerce_2
where     event_time >= '2019-10-01'
group by  year_month
order by  year_month
"""

result = %sql $connection_string $query
print (result)

7 rows affected.
+------------+----------+
| year_month |   cnt    |
+------------+----------+
|  201910.0  | 42482059 |
|  201911.0  | 67503595 |
|  201912.0  | 67514039 |
|  202001.0  | 56019468 |
|  202002.0  | 55291449 |
|  202003.0  | 56335286 |
|  202004.0  | 66539750 |
+------------+----------+
CPU times: user 13.2 ms, sys: 14.7 ms, total: 27.9 ms
Wall time: 4min 4s

import matplotlib.pyplot as plt

df = result.DataFrame()
df['year_month_2'] = df['year_month'].astype('int32').astype('str')
df.plot(x='year_month_2', y='cnt')