sqlalchemy.engine.base.Engine COMMIT shows in terminal after running seed program, but data doesn't show when querying table

Question

I have written a seed program to seed data into PostgreSQL, using SQLAlchemy schema. I created a 'courses' table with a column for id, organization, title, authors, start_date, end_date, duration, and schedule_notes.

When I run the program, I see the insert commands and the information that is inserted, followed by a COMMIT statement. However, when I open PostgreSQL and run 'select * from courses', I get nothing. The table is empty.

Here is what I see in the terminal as I run the program:

2015-02-18 16:28:17,353 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2015-02-18 16:28:17,353 INFO sqlalchemy.engine.base.Engine INSERT INTO courses >(organization, title, authors, start_date, end_date, duration, schedule_notes) VALUES (%(organization)s, %(title)s, %(authors)s, %(start_date)s, %(end_date)s, %(duration)s, %(schedule_notes)s) RETURNING courses.id 2015-02-18 16:28:17,353 INFO sqlalchemy.engine.base.Engine {'schedule_notes': 'There are no open sessions.\n', 'start_date': None, 'duration': 'None', 'title': 'Markets with Frictions', 'authors': ['Randall', 'Wright'], 'end_date': None, 'organization': 'University of Wisconsin\xe2\x80\x93Madison'} 2015-02-18 16:28:17,354 INFO sqlalchemy.engine.base.Engine COMMIT

Here is my seed.py: import model

import datetime

def load_courses(session):
    # use u.user
    #reads in file and parses data
    users_table = open("courses.txt", "r")
    for line in users_table:
        aline = line.split('\t')
        organization, title, authors, start_date, duration, schedule_notes = aline

        #creates instance of user
        course = model.Course()
        course.title = title
        course.organization = organization
        author_list = authors.split()
        course.authors = author_list

        starting = start_date.split()

        try:
            month, day, year = starting
            day = day.replace(',', '').replace('st', '').replace('th', '').replace('nd', '').replace('rd', '')
            months = {"Jan":1,"Feb":2, "Mar":3,"Apr":4,"May":5,"Jun":6,"Jul":7, "Aug":8, "Sep":9, "Oct":10,"Nov":11,"Dec":12}
            date = datetime.date(int(year), months[month], int(day))
            course.start_date = date
            converted_duration = duration.replace('st', '').replace(' weeks long', '').replace('th', '').replace('nd', '').replace('rd', '')
            lasting = datetime.timedelta(days=((int(converted_duration))*7))
            course.end_date = lasting + date
        except ValueError:
            course.start_date = None
            course.end_date = None

        try:
            course.duration = duration
        except ValueError:
            course.duration = None

        course.schedule_notes = schedule_notes

        print(type(course.end_date))
        # adds course to session
        session.add(course)

        #commits session changes
        session.commit()


def main(session):
    # You'll call each of the load_* functions with the session as an argument
    load_courses(session)

if __name__ == "__main__":
    s= model.connect()
    main(s)

And this is the content of my model.py:

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.url import URL
# from sqlalchemy.dialects import postgresql
from sqlalchemy.orm import sessionmaker, scoped_session
import settings


DeclarativeBase = declarative_base()

ENGINE = create_engine(URL(**settings.DATABASE), echo=True)

session = scoped_session(sessionmaker(bind=ENGINE,
                                        autocommit = False,
                                        autoflush = False))


def connect():
    global ENGINE
    global Session

    ENGINE = create_engine(('postgresql:///courses'), echo=True)
    Session = sessionmaker(bind=ENGINE)

    return Session()


def create_courses_table(engine):
    """"""
    DeclarativeBase.metadata.create_all(engine)


class Course(DeclarativeBase):
    """Sqlalchemy courses model"""
    __tablename__ = "courses"

    id = Column(Integer, primary_key=True)
    organization = Column('organization', String)
    title = Column('title', String)
    authors = Column('authors', String)
    start_date = Column('start_date', Date(timezone=False), nullable=True)
    end_date = Column('end_date', Date(timezone=False), nullable=True)
    duration = Column('duration', Integer, nullable=True)
    schedule_notes = Column('schedule_notes', Integer, nullable=True)

I've searched through stack overflow, but I can't find any situations where the table exists, and there are no error messages, but the result is still Null.

Any help would be greatly appreciated. Hopefully, I'm making a simple, stupid mistake.

Answer 1

I learned that in order for the data to seed to the table, I needed to insert some values into my table columns manually.

Once I added one row, I was able to use my script to seed the rest of the data.

Silly mistake!