DML – Data Manipulation Language¶

Once the tables are created, we typically have to manipulate data inside the tables.

All the statements that are used to manipulate data in tables are categorized under DML.
Each entry in a table is typically termed as row or record.
We use INSERT to insert one or more new records into a table.
UPDATE can be used to update existing records inside a table.
One can use DELETE to delete one or more records from a table.
We can also use TRUNCATE to delete all the records in one shot. However it is DDL Statement and we should not use as part of web or mobile application development.
All DML Statements can be committed or rolled back with in a transaction.
Here is sample insert statement which uses all not null columns with out any default values.

%load_ext sql

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

%%sql result_set <<

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com')

%sql SELECT * FROM users

%%sql result_set <<

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES 
    ('Tobe', 'Lyness', 'tlyness1@paginegialle.it'),
    ('Addie', 'Mesias', 'amesias2@twitpic.com'),
    ('Corene', 'Kohrsen', 'ckohrsen3@buzzfeed.com'),
    ('Darill', 'Halsall', 'dhalsall4@intel.com')

%sql SELECT * FROM users

%sql UPDATE users SET user_email_validated = true, is_active = true

%sql SELECT * FROM users

Fails due to check constraint violation

%sql UPDATE users SET user_role = 'C' WHERE user_id = 1

%sql UPDATE users SET user_role = 'A' WHERE user_id = 1

%sql SELECT * FROM users

%sql DELETE FROM users WHERE user_role = 'U'

%sql SELECT * FROM users

Mastering Python

DML – Data Manipulation Language¶