lundi 20 avril 2015

Sqlalchemy ID field isn't populated when relationship with another table is set up

I'm trying to set up Sqlalchemy and am running into problems with setting up relationships between tables. Most likely it's misunderstanding on my part.

A table is set up like so. The important line is the one with two asterisks one either side, setting up the relationship to table "jobs."

class Clocktime(Base):
"""Table for clockin/clockout values

ForeignKeys exist for Job and Employee
many to one -> employee
many to one -> job
"""

__tablename__ = "clocktimes"
id = Column(Integer, primary_key=True)
time_in = Column(DateTime)
time_out = Column(DateTime)
employee_id = Column(Integer, ForeignKey('employees.id'))
**job_id = Column(Integer, ForeignKey('jobs.id'))**
# employee = many to one relationship with Employee
# job = many to one relationship with Job

@property
def timeworked(self):
    return self.time_out - self.time_in

@property
def __str__(self):
    formatter="Employee: {employee.name}, "\
              "Job: {job.abbr}, "\
              "Start: {self.time_in}, "\
              "End: {self.time_out}, "\
              "Hours Worked: {self.timeworked}, "\
              "ID# {self.id}"
    return formatter.format(employee=self.employee, job=self.job, self=self)

Now, the jobs table follows. Check the asterisked line:

class Job(Base):
"""Table for jobs

one to many -> clocktimes
note that rate is cents/hr"""

__tablename__ = "jobs"
id = Column(Integer, primary_key=True)
name = Column(String(50))
abbr = Column(String(16))
rate = Column(Integer)  # cents/hr
**clocktimes = relationship('Clocktime', backref='job', order_by=id)**

def __str__(self):
    formatter = "Name: {name:<50} {abbr:>23}\n" \
                "Rate: ${rate:<7.2f}/hr {id:>62}"
    return formatter.format(name=self.name,
                            abbr="Abbr: " + str(self.abbr),
                            rate=self.rate/100.0,
                            id="ID# " + str(self.id))

When a user starts a new task, the following code is executed in order to write the relevant data to tables jobs and clocktimes:

new_task_job = [Job(abbr=abbrev, name=project_name, rate=p_rate), Clocktime(time_in=datetime.datetime.now())]
    for i in new_task_job:
        session.add(i)
    session.commit()
    start_time = datetime.datetime.now()
    status = 1

Then, when the user takes a break...

new_break = Clocktime(time_out=datetime.datetime.now())
    session.add(new_break)
    session.commit()

If you look in the screenshot, the job_id field isn't being populated. Shouldn't it be populated with the primary key (id) from the jobs table, per

job_id = Column(Integer, ForeignKey('jobs.id'))

or am I missing something? I'm assuming that I'm to write code to do that, but I don't want to break anything that Sqlalchemy is trying to do in the backend. This should be a one job to many clocktimes, since a person can spend several days per task.

Clocktimes Table

Aucun commentaire:

Enregistrer un commentaire