[
Inserting Data into Table¶
Let us understand how we can take care of insert into table using Python based approach.
- Here are the typical steps we need to follow:
- Make sure we have data in the form of an object or collection.
- Establish the connection to database.
- Create cursor object.
- Execute query statement using
cursor.executeby passing the query statement. - We need to pass the object or collection as an additional argument along with query statement.
- We can insert one object at a time using
executeand multiple objects at a time usingexecutemany.
- If you are not familiar about how to directly insert data into the table using Database level operations, you can go through this topic to understand the details.
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.* Here is the create table statement using which we have created users table.
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
);
In [1]:
%run 05_function_get_database_connection.ipynb
{note}
Here is an example to insert one record using hard coded values.In [2]:
# Here is the insert statement to insert one record
# INSERT INTO users (user_first_name, user_last_name, user_email_id)
# VALUES ('Scott', 'Tiger', 'scott@tiger.com')
cursor = sms_connection.cursor()
query = ("""
INSERT INTO users
(user_first_name, user_last_name, user_email_id)
VALUES
('Scott', 'Tiger', 'scott@tiger.com')
""")
cursor.execute(query)
sms_connection.commit()
cursor.close()
sms_connection.close()
In [3]:
%load_ext sql
In [6]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [7]:
%sql SELECT * FROM users
1 rows affected.
Out[7]:
| 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 | Scott | Tiger | scott@tiger.com | False | None | U | False | 2022-03-30 12:00:47.519479 | 2022-03-30 12:00:47.519479 |
{note}
Inserting one record using variables for column values.In [8]:
%run 05_function_get_database_connection.ipynb
In [9]:
# Here is the insert statement to insert one record
# INSERT INTO users (user_first_name, user_last_name, user_email_id)
# VALUES ('Donald', 'Duck', 'donald@duck.com')
cursor = sms_connection.cursor()
query = ("""
INSERT INTO users
(user_first_name, user_last_name, user_email_id)
VALUES
(%s, %s, %s)
""")
user = ('Donald', 'Duck', 'donald@duck.com')
cursor.execute(query, user)
sms_connection.commit()
cursor.close()
sms_connection.close()
In [10]:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
In [11]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [12]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 2 rows affected.
Out[12]:
| 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 | Scott | Tiger | scott@tiger.com | False | None | U | False | 2022-03-30 12:00:47.519479 | 2022-03-30 12:00:47.519479 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-03-30 12:01:18.237263 | 2022-03-30 12:01:18.237263 |
{note}
Creating function insert one user at a time.In [13]:
def add_user(connection, user):
cursor = connection.cursor()
query = ("""
INSERT INTO users
(user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES
(%s, %s, %s, %s, %s)
""")
cursor.execute(query, user)
connection.commit()
cursor.close()
In [14]:
%run 05_function_get_database_connection.ipynb
In [15]:
user = ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', True)
In [16]:
add_user(sms_connection, user)
In [17]:
sms_connection.close()
In [18]:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
In [19]:
%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
In [20]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 3 rows affected.
Out[20]:
| 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 | Scott | Tiger | scott@tiger.com | False | None | U | False | 2022-03-30 12:00:47.519479 | 2022-03-30 12:00:47.519479 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-03-30 12:01:18.237263 | 2022-03-30 12:01:18.237263 |
| 3 | Mickey | Mouse | mickey@mouse.com | False | None | U | True | 2022-03-30 12:01:49.150894 | 2022-03-30 12:01:49.150894 |
{note}
Inserting multiple records (list of objects) at once using `executemany`.In [21]:
%run 05_function_get_database_connection.ipynb
In [22]:
# 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 [23]:
%load_ext sql
The sql extension is already loaded. To reload it, use: %reload_ext sql
In [26]:
%env DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
env: DATABASE_URL=postgresql://itversity_sms_user:itversity@pg.itversity.com:5432/itversity_sms_db
In [27]:
%sql SELECT * FROM users
* postgresql://itversity_sms_user:***@pg.itversity.com:5432/itversity_sms_db 6 rows affected.
Out[27]:
| 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 | Scott | Tiger | scott@tiger.com | False | None | U | False | 2022-03-30 12:00:47.519479 | 2022-03-30 12:00:47.519479 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2022-03-30 12:01:18.237263 | 2022-03-30 12:01:18.237263 |
| 3 | Mickey | Mouse | mickey@mouse.com | False | None | U | True | 2022-03-30 12:01:49.150894 | 2022-03-30 12:01:49.150894 |
| 4 | Gordan | Bradock | gbradock0@barnesandnoble.com | False | h9LAz7p7ub | U | True | 2022-03-30 12:03:33.838432 | 2022-03-30 12:03:33.838432 |
| 5 | Tobe | Lyness | tlyness1@paginegialle.it | False | oEofndp | U | True | 2022-03-30 12:03:33.838432 | 2022-03-30 12:03:33.838432 |
| 6 | Addie | Mesias | amesias2@twitpic.com | False | ih7Y69u56 | U | True | 2022-03-30 12:03:33.838432 | 2022-03-30 12:03:33.838432 |
]