Q:

Foreign key column populating with NULL

I am trying to create a foreign key department_id for employee. But the department_id column value is NULL not 1 as it should be.
Any advise/suggestions are appreciated. Thanks. 
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///db.sqlite3', echo=False)

Base = declarative_base()

Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()

class Department(Base):
    __tablename__ = 'department'

    id = Column(Integer, primary_key=True)
    department_name = Column(String)

class Employee(Base):
    __tablename__ = 'employee'

    id = Column(Integer, primary_key=True)
    employee_name = Column(String)
    department_id = Column(Integer, ForeignKey('department.id'))

Base.metadata.create_all(engine)

d = Department(department_name='electronics')
session.add(d)

e = Employee(employee_name='alice')
session.add(e)

session.commit()

Output:
sqlite> select * from department;
1|electronics

sqlite> select * from employee;
1|alice|

Output should be:
sqlite> select * from department;
1|electronics

sqlite> select * from employee;
1|alice|1

A:

You need to pass a department to the employee object:
e = Employee(employee_name='alice', department = d)

Alternatively, you can introduce introduce a many-to-many relationship between department and employee
import os

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship(
        'Employee',
        secondary='department_employee_link'
    )

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    hired_on = Column(
        DateTime,
        default=func.now())
        departments = relationship(
        Department,
        secondary='department_employee_link'
    )

class DepartmentEmployeeLink(Base):
    __tablename__ = 'department_employee_link'
    department_id = Column(Integer, ForeignKey('department.id'), primary_key=True)
    employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True)

