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.
%%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
%%sql
CREATE TABLE birthdays (
id SERIAL PRIMARY KEY,
date DATE,
men_population INT,
wemen_population INT,
province VARCHAR(100)
);
%%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 setupsudo -u postgres psql -U postgres -c "ALTER ROLE tuser WITH SUPERUSER;"
read from CSV