[Zope-DB] looping through
Tom Jenkins
tjenkins@devis.com
Fri, 31 Aug 2001 11:13:07 -0400
Russell Hires wrote:
> I got all of that. I want Zope/Python to do the looping part (I think). The
> form for adding the new assignment is in Zope, the result will (through a
> couple of steps) be output in zope on a web page. I just don't know how to
> cause zope/python/postgresql to add new rows to my db, once for each student
> per assignment.
>
Hi Russell,
You don't you don't give any of your table schemas but i think it would
be easiest to do this in postgresql using a function and a trigger.
but this is how i would set it up...
------ start cut ------
create table assignments (assign_id serial, assign_name varchar(100) not
null);
create table students (student_id serial, student_name varchar(100) not
null);
create table grades (grade_id serial, assign_id int4, student_id int4
not null, grade int4, constraint assign_fk forei
gn key (assign_id) references assignments(assign_id) on delete cascade,
constraint student_fk foreign key (student_id)
references students(student_id) on delete cascade);
create function setup_grades() returns opaque as '
declare
student RECORD;
begin
for student in select * from students;
insert into grades (assign_id, student_id) values (NEW.assign_id,
student.student_id);
end loop;
return NEW;
end;
' language 'plpgsql';
create trigger assign_insert_tr
before insert on assignments
for each row execute procedure setup_grades();
------ stop cut ------
Couple of reasons I like this way is:
1) your database server is doing the work, so the application doesn't
have to;
2) if something should happen to your database server during this
process, you won't be stuck with somethings inserted and others not.
now i think zope's zsqlmethods are all wrapped in transactions,but i
think you'd then have to do all the inserts in one zsqlmethod.
(note this code _has_ been tested and verified to work on PostgreSQL 7.1)
--
Tom Jenkins
devIS - Development Infostructure
http://www.devis.com