Getting Started with SQLAlchemy — Creating and Dropping Tables (Part 3)

We will start by creating tables using the Core approach because ORM is essentially an extension of Core. By learning the Core approach first, we will build a solid foundation that will make understanding the ORM part easier.

SQL

We know the SQL we want to write. Now, let’s rewrite it using SQLAlchemy. We’ll create two tables: one for employees and one for bonuses. Each employee can have multiple bonuses.

This creates a one-to-many relationship between the employee and bonus tables.

CREATE TABLE IF NOT EXISTS employee (
  employee_id bigserial PRIMARY KEY,
  name TEXT NOT NULL,
  supervisor INT,
  salary numeric NOT NULL
);


CREATE TABLE IF NOT EXISTS bonus (
  bonus_id bigserial PRIMARY KEY,
  employee_id bigint NOT NULL,
  bonus numeric,
  CONSTRAINT fk_employee_id
  FOREIGN KEY(employee_id)
  REFERENCES employee(employee_id)
);

First table

We need to create metadata first, which we will share with the tables. To the Table class, we pass the name of the table, the metadata, and the column definitions.

from sqlalchemy import Table, Column, Integer, Text, Numeric, ForeignKey
from sqlalchemy import MetaData

metadata = MetaData()

with engine.connect() as…