Class03 Answer:

Write/Run a script which combines:

If I have Postgres installed, this is an easy lab.

If I don't have Postgres installed, I install it with the help from the instructions at the URL listed below:

https://ml4.herokuapp.com/cclasses/class03pg01

After I installed Postgres, using the above instructions, I wrote this script:


"""
pdpost11.py

This script should copy gspc prices from web into a Postgres table.

Demo:
python pdpost11.py
"""

import pandas as pd

prices_df = pd.read_csv('https://tkrprice.herokuapp.com/static/gspc.csv')

prices_df.columns = ['cdate','openp','highp','lowp','closep','adjp','volume']

# Ref:
# https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html
# https://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql

# I should use sqlalchemy to connect to ann role in Postgres:

from sqlalchemy import create_engine
engine = create_engine('postgresql://ann:ann@127.0.0.1:5432/ann')

# I should write to a table named prices:
prices_df.to_sql('prices',engine, index=False, if_exists='replace')

# I should read from a table named prices:
sql_s      = 'select * from prices order by cdate;'
prices2_df = pd.read_sql(sql_s,engine,parse_dates=['cdate'])
print(prices2_df.tail())

'bye'

When I ran the above script I saw this:


ml4@ub100:~/ml4/public/class03demos $ python pdpost11.py 
           cdate        openp     ...             adjp      volume
17350 2018-12-14  2629.679932     ...      2599.949951  4035020000
17351 2018-12-17  2590.750000     ...      2545.939941  4616350000
17352 2018-12-18  2559.899902     ...      2546.159912  4470880000
17353 2018-12-19  2547.050049     ...      2506.959961  5127940000
17354 2018-12-20  2496.770020     ...      2474.790039  2590001801

[5 rows x 7 columns]
ml4@ub100:~/ml4/public/class03demos $ 
ml4@ub100:~/ml4/public/class03demos $ 
ml4@ub100:~/ml4/public/class03demos $ 

The above script does these tasks:

Class03 Lab


learn4.us About Blog Contact Class01 Class02 Class03 Class04 Class05 Class06 Class07 Class08 Class09 Class10 dan101 Forum Google Hangout Vboxen