Setup postgres in goolge colab

postgres_colab.md

Copy and paste each block of code to your colab notebook and run them all so you can save data in your postgres database and query (retierive) data from it.

Setup

%%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

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

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

%sql $endpoint

Connected: tuser@None

Database operations

Creating e.g. TABLE

%%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;

If you want to read from a file e.g. csv file for bulk insert you need SUPERUSER access, add following to the bash setup

sudo -u postgres psql -U postgres -c "ALTER ROLE tuser WITH SUPERUSER;"

read from CSV

engine = sqlalchemy.create_engine(endpoint)
df.to_sql('birthdays', engine, index=False, if_exists="append")