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.execute by 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 execute and multiple objects at a time using executemany.
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
);

%run 05_function_get_database_connection.ipynb

Note

Here is an example to insert one record using hard coded values.

# 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()

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%sql SELECT * FROM users

Note

Inserting one record using variables for column values.

%run 05_function_get_database_connection.ipynb

# 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()

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%sql SELECT * FROM users

Note

Creating function insert one user at a time.

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()

%run 05_function_get_database_connection.ipynb

user = ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', True)

add_user(sms_connection, user)

sms_connection.close()

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%sql SELECT * FROM users

Note

Inserting multiple records (list of objects) at once using executemany.

%run 05_function_get_database_connection.ipynb

# 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

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

%sql SELECT * FROM users

Mastering Python

Inserting Data into Table¶