Lesson 2 - Defining Tables and Inserting Data

Welcome to Defining Tables#

In Lesson 1 you reflected a table that already existed. Now you will design one from scratch — deciding column names, data types, and constraints yourself, the way you would when building a new application’s database. You will create two related tables, departments and employees, and insert real data into both. Every other lesson in this module — querying, joins, updates, transactions, and the ORM — builds on the database you create here.

By the end of this lesson, you will be able to:

• Define a table’s structure with Table() and Column()
• Choose appropriate column types: Integer, String, Numeric, and Date
• Add constraints — primary_key, nullable, unique — and a ForeignKey relationship
• Create tables in the database with metadata.create_all()
• Insert one row and many rows at once with insert()

Let’s design a small company database.

Designing the Tables#

The database models a small company: a handful of departments, and employees, each belonging to one department and (except for the CEO) reporting to a manager who is also an employee.

The departments table#

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String

engine = create_engine("sqlite:///company.db")
metadata = MetaData()

departments = Table(
    "departments", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(50), nullable=False, unique=True),
)

Two constraints are doing real work here:

• primary_key=True on id tells SQLAlchemy this column uniquely identifies each row. SQLite will auto-increment it for you when you insert a row without specifying an id.
• nullable=False, unique=True on name means every department must have a name, and no two departments can share one.

The employees table#

The employees table needs a wider range of types, plus two foreign keys — one pointing at departments, and one pointing back at employees itself, since a manager is also an employee:

from sqlalchemy import Numeric, Date, ForeignKey

employees = Table(
    "employees", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(50), nullable=False),
    Column("job", String(30), nullable=False),
    Column("mgr_id", Integer, ForeignKey("employees.id"), nullable=True),
    Column("hiredate", Date, nullable=False),
    Column("salary", Numeric(9, 2), nullable=False),
    Column("commission", Numeric(9, 2), nullable=True),
    Column("department_id", Integer, ForeignKey("departments.id"), nullable=True),
)

A few choices worth explaining:

• Numeric(9, 2) stores a decimal number with up to 9 total digits and 2 after the decimal point — the right choice for money, since floating-point types can introduce rounding errors in currency values.
• Date stores a calendar date. SQLAlchemy expects Python date objects when you insert into a Date column, not date strings — you will see that below.
• ForeignKey("employees.id") on mgr_id is a self-referential foreign key: it points at the primary key of the very table it belongs to. This lets an employee’s manager be looked up as another row in the same table — you will use this in Lesson 4 to write a self-join.
• nullable=True on mgr_id, commission, and department_id reflects reality: the CEO has no manager, most employees earn no commission, and (in a larger company) someone might be between departments.

Creating the Tables#

Defining Table objects only builds Python descriptions of the schema — nothing exists in the database yet. metadata.create_all(engine) issues the CREATE TABLE statements for every table registered with that MetaData:

metadata.create_all(engine)

from sqlalchemy import inspect
inspector = inspect(engine)
print(inspector.get_table_names())

Output:

['departments', 'employees']

create_all() is safe to call more than once — it only creates tables that do not already exist, so re-running a script that includes this line will not raise an error or wipe your data.

Inserting Data#

With both tables created, you can insert rows using SQLAlchemy’s insert() construct.

Inserting the departments#

from sqlalchemy import insert

department_rows = [
    {"id": 1, "name": "Finance"},
    {"id": 2, "name": "Engineering"},
    {"id": 3, "name": "Sales"},
    {"id": 4, "name": "Operations"},
]

with engine.connect() as conn:
    conn.execute(insert(departments), department_rows)
    conn.commit()

Passing a list of dictionaries to execute() inserts every row in the list in a single call — this is the multi-row form of insert(). Each dictionary’s keys must match column names.

Inserting the employees#

The employees table needs a Python date object for hiredate, so the raw hire-date strings are parsed first:

from datetime import datetime

raw_employees = [
    (1, "JOHNSON", "ADMIN", 6, "12-17-1990", 18000, None, 4),
    (2, "HARDING", "MANAGER", 9, "02-02-1998", 52000, 300, 3),
    (3, "TAFT", "SALES I", 2, "01-02-1996", 25000, 500, 3),
    (4, "HOOVER", "SALES I", 2, "04-02-1990", 27000, None, 3),
    (5, "LINCOLN", "TECH", 6, "06-23-1994", 22500, 1400, 4),
    (6, "GARFIELD", "MANAGER", 9, "05-01-1993", 54000, None, 4),
    (7, "POLK", "TECH", 6, "09-22-1997", 25000, None, 4),
    (8, "GRANT", "ENGINEER", 10, "03-30-1997", 32000, None, 2),
    (9, "JACKSON", "CEO", None, "01-01-1990", 75000, None, 4),
    (10, "FILLMORE", "MANAGER", 9, "08-09-1994", 56000, None, 2),
    (11, "ADAMS", "ENGINEER", 10, "03-15-1996", 34000, None, 2),
    (12, "WASHINGTON", "ADMIN", 6, "04-16-1998", 18000, None, 4),
    (13, "MONROE", "ENGINEER", 10, "12-03-2000", 30000, None, 2),
    (14, "ROOSEVELT", "CPA", 9, "10-12-1995", 35000, None, 1),
]

employee_rows = [
    {
        "id": id_, "name": name, "job": job, "mgr_id": mgr,
        "hiredate": datetime.strptime(hiredate, "%m-%d-%Y").date(),
        "salary": salary, "commission": commission, "department_id": department_id,
    }
    for (id_, name, job, mgr, hiredate, salary, commission, department_id) in raw_employees
]

with engine.connect() as conn:
    result = conn.execute(insert(employees), employee_rows)
    print(f"{result.rowcount} employee(s) inserted.")
    conn.commit()

Output:

14 employee(s) inserted.

result.rowcount reports how many rows the statement affected — a quick way to confirm an insert (or later, an update or delete) did what you expected.

Verifying What You Inserted#

A quick select() (covered in full in Lesson 3) confirms the data landed correctly:

from sqlalchemy import select, func

with engine.connect() as conn:
    total = conn.execute(select(func.count()).select_from(employees)).scalar()
    print("Total employees:", total)

    first_three = conn.execute(select(employees.c.name, employees.c.job).limit(3)).fetchall()
    for row in first_three:
        print(row)

Output:

Total employees: 14
('JOHNSON', 'ADMIN')
('HARDING', 'MANAGER')
('TAFT', 'SALES I')

.scalar() is a convenience for queries that return a single value — here, the row count — so you do not have to unwrap a one-column, one-row result yourself.

Practice Exercises#

Continue using the company.db database and the departments / employees tables you just built.

Exercise 1: Add a New Department#

Insert a fifth department named "Marketing" with id=5, then confirm it exists by printing the full list of department names.

# Your code here

Exercise 2: Insert a New Employee#

Insert a new employee named "KENNEDY", job "ANALYST", hired today, salary 31000, mgr_id=10, department_id=2, and no commission. Use datetime.now().date() for the hire date.

# Your code here

Exercise 3: Count Rows Per Table#

Using select(func.count()).select_from(...), print the total number of rows in departments and in employees.

# Your code here

Summary#

You designed two related tables from scratch using Table() and Column(), choosing types like Integer, String, Numeric, and Date, and constraints like primary_key, nullable, and unique. You linked employees to departments with a ForeignKey, and linked employees to itself with a self-referential foreign key for the manager relationship. Finally, you created both tables with metadata.create_all() and inserted a department list and a full employee roster with insert().

Key Concepts#

• Table() / Column() — define a table’s name, columns, types, and constraints as Python objects.
• Numeric(precision, scale) — an exact decimal type, the right choice for currency values.
• ForeignKey("table.column") — links a column to another table’s primary key; can point at the same table for self-referential relationships.
• metadata.create_all(engine) — issues CREATE TABLE for every table registered with that metadata, skipping ones that already exist.
• insert() — builds an insert statement; pass a single dict for one row, or a list of dicts for many at once.
• result.rowcount — the number of rows a statement affected.
• conn.commit() — makes changes permanent; without it, they are rolled back when the connection closes.

Why This Matters#

Designing a schema — picking types, deciding what can be null, and wiring up relationships with foreign keys — is a skill that transfers directly to any real project, whether you are prototyping in SQLite or deploying on PostgreSQL. Doing it in Python with Table() and Column(), instead of hand-written CREATE TABLE SQL, means the exact same code can create your schema on a teammate’s machine, in a test database, or in production, with only the connection string changing.

Continue Building Your Skills#

You now have a real database with related tables and real data in it. In the next lesson you will put SQLAlchemy’s query-building tools to work — filtering, sorting, and aggregating this data with select(), where(), and group_by().