[
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.
In [1]:
%run 02_function_get_database_connection.ipynb
In [2]:
%run 03_creating_database_table.ipynb
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db 1 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db Done. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 0 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db Done. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 1 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 10 rows affected. * postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 0 rows affected.
In [3]:
# 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()
In [4]:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
In [5]:
%env DATABASE_URL=postgresql://sms_user:itversity@pg.itversity.com:5432/sms_db
env: DATABASE_URL=postgresql://sms_user:itversity@pg.itversity.com:5432/sms_db
In [6]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 3 rows affected.
Out[6]:
| 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 | 2022-03-30 12:25:33.374502 | 2022-03-30 12:25:33.374502 |
| 2 | Tobe | Lyness | tlyness1@paginegialle.it | False | oEofndp | U | True | 2022-03-30 12:25:33.374502 | 2022-03-30 12:25:33.374502 |
| 3 | Addie | Mesias | amesias2@twitpic.com | False | ih7Y69u56 | U | True | 2022-03-30 12:25:33.374502 | 2022-03-30 12:25:33.374502 |
]