!pip install pandas

"""
Explanation: Install new packages
You can add them into /docker/jupyter/requirements.txt or, if you are lazy, you can do it like that:
End of explanation
"""


df = pd.read_csv('../data/winequality-red.csv')

df.head()

"""
Explanation: Read the data
End of explanation
"""


from sqlalchemy import create_engine
engine = create_engine('postgresql://postgres:password@this_postgres')
name = 'winequality'
df.to_sql(name, engine, if_exists='replace')

"""
Explanation: Put your data into data folder
Upload to PostgreSQL
End of explanation
"""


import psycopg2 as pg2
import pandas as pd

con = pg2.connect(host='this_postgres',
                  user='postgres',
                  password='password',
                  database='postgres')
con.autocommit = True
cur = con.cursor()

def select(sql):
    return pd.read_sql(sql,con)

sql = '''select t.* from winequality t order by t.alcohol desc'''

df = select(sql)

df.head()

"""
Explanation: Kill postgres processes
If some of your queries to pg stuck -- go to kill-pg-processes.ipyng and kill all processes (or only particular ones)
Select from PostgreSQL
I repeat some imports and variables here because I usually copy this cell to the beging of a new project
End of explanation
"""


sql = '''select * from d_date t limit 10'''

select(sql)

"""
Explanation: Your data will be stored in PostgreSQL volume. Even if your delete containers and then recreate them, all data will be in place.
Calendar table
https://medium.com/@duffn/creating-a-date-dimension-table-in-postgresql-af3f8e2941ac
There is a table with all possible dates. Use it to create periodical reports, e.g. monthly report.
End of explanation
"""


con.autocommit = True
cur = con.cursor()

sql = '''
drop table if exists hello_table;
create table hello_table as 
select 'hello!!!!' as col
'''

cur.execute(sql)

sql = '''select * from hello_table t'''

select(sql)

"""
Explanation: How to create new tables
Make sure you ran "con.autocommit = True" before creating cursor otherwise you have to commit table creation
End of explanation
"""


