Creating Database Table¶
Before getting into action with respect to CRUD operations, we need to prepare tables.
We can either use
psqlor SQL Workbench to connect to the database and create table directly.Let us drop and create the table
usersif it already exists.I am using Jupyter based environment for demo - feel free to use what ever is relevant to your project or comfortable for you.
One need to have decent database and SQL skills to be comfortable with all types of application development. Feel free to Master SQL using Postgresql as target database using this course or playlist.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
LIMIT 10
10 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | users_range_part_default | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_range_part_2016 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_range_part_2019 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_range_part_2020 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | employees | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_hash_part_0_of_8 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_hash_part | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_hash_part_1_of_8 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_hash_part_2_of_8 | BASE TABLE | None | None | None | None | None | YES | NO | None |
| itversity_sms_db | public | users_hash_part_3_of_8 | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
DROP TABLE IF EXISTS users CASCADE;
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
AND table_name = 'users'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|
%%sql
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,
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
Let us validate the objects that are created in the underlying database. We can either run query against information_schema or use Database Explorer in SQL Workbench or even
psql.
%%sql
SELECT * FROM information_schema.tables
WHERE table_catalog = 'itversity_sms_db' AND table_schema = 'public'
AND table_name = 'users'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
| table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | users | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
SELECT * FROM information_schema.columns
WHERE table_name = 'users'
LIMIT 10
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
10 rows affected.
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_sms_db | public | users | user_id | 1 | nextval('users_user_id_seq'::regclass) | NO | integer | None | None | 32 | 2 | 0 | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | int4 | None | None | None | None | 1 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_first_name | 2 | None | NO | character varying | 30 | 120 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 2 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_last_name | 3 | None | NO | character varying | 30 | 120 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 3 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_email_id | 4 | None | NO | character varying | 50 | 200 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 4 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_email_validated | 5 | false | YES | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | bool | None | None | None | None | 5 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_password | 6 | None | YES | character varying | 200 | 800 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 6 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | user_role | 7 | 'U'::character varying | NO | character varying | 1 | 4 | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | varchar | None | None | None | None | 7 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | is_active | 8 | false | YES | boolean | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | bool | None | None | None | None | 8 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | create_ts | 9 | CURRENT_TIMESTAMP | YES | timestamp without time zone | None | None | None | None | None | 6 | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | timestamp | None | None | None | None | 9 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
| itversity_sms_db | public | users | last_updated_ts | 10 | CURRENT_TIMESTAMP | YES | timestamp without time zone | None | None | None | None | None | 6 | None | None | None | None | None | None | None | None | None | None | None | itversity_sms_db | pg_catalog | timestamp | None | None | None | None | 10 | NO | NO | None | None | None | None | None | NO | NEVER | None | YES |
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | create_ts | last_updated_ts |
|---|