Copy and paste each block of code to your [colab](https://colab.research.google.com/) notebook and run them all so you can save data in your postgres database and query (retierive) data from it. 


## Setup
```bash
%%bash
# Install postgresql server
sudo apt-get -y -qq update
sudo apt-get -y -qq install postgresql

sudo service postgresql start

sudo -u postgres psql -U postgres -c "CREATE USER tuser WITH PASSWORD '12345';"
sudo -u postgres psql -U postgres -c 'CREATE DATABASE mydb WITH OWNER tuser ENCODING="UTF8";'
sudo -u postgres psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE mydb to tuser;"

# install python requiremends
pip install ipython-sql psycopg2 sqlalchemy pyxlsb --quiet
```

```python
%env DATABASE_NAME=mydb
%env DATABASE_HOST=localhost
%env DATABASE_PORT=5432
%env DATABASE_USER=tuser
%env DATABASE_PASS=12345
```


```python
import sqlalchemy 
import os

endpoint="postgresql://{}:{}@{}?port={}&dbname={}".format(
    os.environ['DATABASE_USER'],
    os.environ['DATABASE_PASS'],
    os.environ['DATABASE_HOST'],
    os.environ['DATABASE_PORT'],
    os.environ['DATABASE_NAME'],
)

print(f"endpoint={endpoint}")
sqlalchemy.create_engine(endpoint)
%load_ext sql
```
> endpoint=postgresql://tuser:12345@localhost?port=5432&dbname=mydb

```python
%sql $endpoint
```
> Connected: tuser@None


## Database operations

### Creating e.g. TABLE
```bash
%%sql
CREATE TABLE birthdays (
    id SERIAL PRIMARY KEY,
    date DATE,
    men_population INT,
    wemen_population INT,
    province VARCHAR(100)
);
```

### Reteriving e.g. public table names.
```
%%sql 
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
```