Recap of Insert¶

Let us recap about INSERT statement as we are going to use it for batch loading.

We can either insert one record at a time or multiple records.
Inserting one record at a time is typically used to insert records for transaction based use cases.
For batch processing we typically try to insert multiple records at a time.
At times we even might consider native database utilities for batch loading into table. This process is also known as direct path load.

%run 02_function_get_database_connection.ipynb

%run 03_creating_database_table.ipynb

env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
10 rows affected.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
10 rows affected.
 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.

# Here is the insert statement to insert one record
# INSERT INTO users 
#     (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active) 
# VALUES 
#     ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
#     ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
#     ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)

cursor = sms_connection.cursor()
query = ("""
    INSERT INTO users 
        (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active)
    VALUES 
        (%s, %s, %s, %s, %s, %s)
""")

users = [
    ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', True),
    ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', True),
    ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', True)
]

cursor.executemany(query, users)
sms_connection.commit()

cursor.close()
sms_connection.close()

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_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 users

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 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
1	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	True	2020-12-29 11:03:47.302382	2020-12-29 11:03:47.302382
2	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	True	2020-12-29 11:03:47.302382	2020-12-29 11:03:47.302382
3	Addie	Mesias	amesias2@twitpic.com	False	ih7Y69u56	U	True	2020-12-29 11:03:47.302382	2020-12-29 11:03:47.302382

Mastering Python

Recap of Insert¶