Create Database and Users Table¶

Let us create a simple table by name users for now.

We can run database commands using %%sql with in Jupyter Notebook or psql or SQL Alchemy to create the tables in the database. You can use the tool as per your preference.
If you are using our labs, you will get a database and user which will be prefixed with your OS username and the password which is published via our portal.

Here are the commands to create the database using psql, in case if you are planning to use your own environment. You can only run these commands if you have access to database as super user.

psql -U postgres -h localhost -p 5433 -W
docker exec -it sms_pg psql -U postgres # in case postgres is running in docker container

CREATE DATABASE itversity_sms_db;
CREATE USER itversity_sms_user WITH ENCRYPTED PASSWORD 'itversity!23'; -- Make sure to use complex password
GRANT ALL ON DATABASE itversity_sms_db TO itversity_sms_user;

Once the database is created you can use %%sql with in Jupyter Notebook or psql or SQL Alchemy to create the tables in the database.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:itversity!23@localhost:5433/itversity_sms_db

%sql SELECT * FROM information_schema.tables LIMIT 10

%%sql result_set <<

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  user_first_name VARCHAR(30) NOT NULL,
  user_last_name VARCHAR(30) NOT NULL,
  user_email_id VARCHAR(50) NOT NULL,
  user_email_validated BOOLEAN DEFAULT FALSE,
  user_password VARCHAR(200),
  user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
  is_active BOOLEAN DEFAULT FALSE,
  created_dt DATE DEFAULT CURRENT_DATE,
  last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

%%sql result_set <<

SELECT * 
FROM information_schema.tables 
WHERE table_name = 'users'

display(result_set)

Mastering Python

Create Database and Users Table¶