title: Inserting into a table of foreign keys
body: <p>I'm a SQLAlchemy noob trying to learn by writing a small room availability web app. I've normalized out my tables so I have a bunch of reference tables (days of the week, room number, course number, etc.) and a room schedule table that has foreign keys to the reference tables. I originally tried using the automap function but when that didn't seem to work I used sqlacodegen to create a model, like so:</p>
  
  <pre><code>from sqlalchemy import Column, ForeignKey, Integer, String
  from sqlalchemy.orm import relationship
  from sqlalchemy.ext.declarative import declarative_base
  
  
  Base = declarative_base()
  metadata = Base.metadata
  
  
  class Course(Base):
      __tablename__ = 'courses'
  
      course_id = Column(Integer, primary_key=True, unique=True)
      course_number = Column(String(8), nullable=False, unique=True)
  
  
  class DayOfWeek(Base):
      __tablename__ = 'day_of_week'
  
      day_id = Column(Integer, primary_key=True)
      weekday = Column(String(10), nullable=False)
  
  
  class EndTime(Base):
      __tablename__ = 'end_time'
  
      end_id = Column(Integer, primary_key=True, unique=True)
      end_time = Column(String(8), nullable=False)
  
  
  class Quarter(Base):
      __tablename__ = 'quarter'
  
      quarter_id = Column(Integer, primary_key=True, unique=True)
      quarter_name = Column(String(10), nullable=False, unique=True)
  
  
  class Room(Base):
      __tablename__ = 'room'
  
      room_id = Column(Integer, primary_key=True, unique=True)
      room_number = Column(Integer, nullable=False, unique=True)
  
  
  class RoomSchedule(Base):
      __tablename__ = 'room_schedule'
  
      schedule_id = Column(Integer, primary_key=True)
      fk_room_id = Column(ForeignKey('room.room_id'), nullable=False, index=True)
      fk_quarter_id = Column(ForeignKey('quarter.quarter_id'), nullable=False, index=True)
      fk_day_id = Column(ForeignKey('day_of_week.day_id'), nullable=False, index=True)
      fk_start_time = Column(ForeignKey('start_time.time_id'), nullable=False, index=True)
      fk_end_time = Column(ForeignKey('end_time.end_id'), index=True)
      fk_course_id = Column(ForeignKey('courses.course_id'), nullable=False, index=True)
  
      fk_course = relationship('Course')
      fk_day = relationship('DayOfWeek')
      end_time = relationship('EndTime')
      fk_quarter = relationship('Quarter')
      fk_room = relationship('Room')
      start_time = relationship('StartTime')
  
  
  class StartTime(Base):
      __tablename__ = 'start_time'
  
      time_id = Column(Integer, primary_key=True, unique=True)
      start = Column(String(6), nullable=False)
  </code></pre>
  
  <p>The problem is when I do an insert into the room_schedule table it tries to back-insert into the reference tables. My current attempt to insert looks like this:</p>
  
  <pre><code>sched = RoomSchedule()
  sched.fk_course = session.query(Course.course_id).filter(Course.course_number =='CS250')
  sched.fk_day = session.query(DayOfWeek.day_id).filter(DayOfWeek.weekday =='Tuesday')
  sched.fk_end_time = session.query(EndTime.end_id).filter(EndTime.end_time == '22:00')
  sched.fk_quarter = session.query(Quarter.quarter_id).filter(Quarter.quarter_name=='1701')
  sched.fk_room = session.query(Room.room_id).filter(Room.room_number=='416')
  sched.fk_start_time = session.query(StartTime.time_id).filter(StartTime.start=='7:30')
  session.add(sched)
  session.commit()
  </code></pre>
  
  <p>I also tried using the individual class constructors to give the foreign keys, as well.</p>
  
  <p>If I was trying to solve this problem in straight Python, I would do this:</p>
  
  <pre><code>def insert_room_busy(con, room_num, quarter_name, day_name, start_time, end_time, course_number):
      insert_data = 'insert into room_schedule (fk_room_id, fk_quarter_id, fk_day_id, fk_start_time, fk_end_time, fk_course_id) ' \
                    'values( (select room_id from room where room_number= %s), ' \
                    '(select quarter_id from quarter where quarter_name= %s), ' \
                    '(select day_id from day_of_week where weekday= %s), ' \
                    '(select time_id from start_time where start= %s), ' \
                    '(select end_id from end_time where end_time= %s), ' \
                    '(select course_id from courses where course_number= %s));'
      cursor = con.cursor()
      cursor.execute(insert_data, (room_num, quarter_name, day_name, start_time, end_time, course_number))
  </code></pre>
  
  <p>And straight SQL would look similar, using subqueries to find the foreign keys.</p>
  
  <p>How do I do this the "right" way?</p>