Getting Started with SQLAlchemy — Inserting and Fetching Data (Part 4)

In the previous part, we prepared the tables. Now we can insert some data into the tables. When the data is ready, we will create a query to access the data.

SQL

Let’s begin by defining the SQL. Although we typically don’t need to do it explicitly, doing so will help us visualize what we aim to build.

INSERT INTO employee (employee_id, name, supervisor, salary) VALUES
(3, 'Brad', null, 4000),
(1, 'John', 3, 1000),
(2, 'Dan', 3, 2000),
(4, 'Thomas', 3, 4000);

INSERT INTO bonus (employee_id, bonus) VALUES
(2, 500),
(4, 2000);

SQLAlchemy Core

First, we will prepare the data. The keys in the dictionary must match the column names.

new_employees = [
    {"employee_id": 3, "name": "Brad", "supervisor": None, "salary": 4000},
    {"employee_id": 1, "name": "John", "supervisor": 3, "salary": 1000},
    {"employee_id": 2, "name": "Dan", "supervisor": 3, "salary": 2000},
    {"employee_id": 4, "name": "Thomas", "supervisor": 3, "salary": 4000},
]

new_bonus = [
    {"employee_id": 2, "bonus": 500},
    {"employee_id": 4, "bonus": 2000},
]

Then, on connection, we call the execute method. With the first argument, we specify the table we want to use. We will use the insert…