Mailing List Archive

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.

According to Chris McDonough
(http://www.zope.org/Documentation/ZWN/ZWN-2001-08-31)
"Zope Corporation has established a mailing list to specifically discuss
issues which arise when integrating Zope and various relational datbases --
zope-db@zope.org. Any posting from "how do I?" to "but..." and the
ever-favorite "help with ...!" is fair game. If you have an interest in
subscribing, please visit http://lists.zope.org/ today, and sign up to the
Zope-DB list."

Thank you for the effort to make sure I'm in the right place, but I think I
am! :-)

Russell
On Friday 31 August 2001 08:45, you wrote:
> > I hope I'm in the right place.
>
> Hardly. This list is for the Zope database, which is an
> object-oriented database, not a relational database. I also don't
> think you are using Python -- the Zope database is written in Python
> and requires the Python to use it. So, unfortunately I think you
> picked the wrong list. Try finding a newsgroup or mailing list with
> "relational database" and "SQL" in its description. :-)
>
> --Guido van Rossum (home page: http://www.python.org/~guido/)

Hi Russell,

I didn't see your original message, but to add rows to your DB you
need to use an 'insert' query:

Here is a query that I've used to add rows to a table called
Persons_Basic:

insert into Persons_Basic values (
<dtml-sqlvar personID type=string>,
<dtml-sqlvar firstName type=string>,
<dtml-sqlvar lastName type=string>,
<dtml-sqlvar birthDate type=float>,
<dtml-sqlvar hairColor type=string>)

personID, firstName, lastName, birthDate, and hairColor are all
parameters of the query.

-steve

On Friday, August 31, 2001, at 07:56 AM, 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.
>
> According to Chris McDonough
> (http://www.zope.org/Documentation/ZWN/ZWN-2001-08-31)
> "Zope Corporation has established a mailing list to specifically discuss
> issues which arise when integrating Zope and various relational
> datbases --
> zope-db@zope.org. Any posting from "how do I?" to "but..." and the
> ever-favorite "help with ...!" is fair game. If you have an interest in
> subscribing, please visit http://lists.zope.org/ today, and sign up to
> the
> Zope-DB list."
>
> Thank you for the effort to make sure I'm in the right place, but I
> think I
> am! :-)
>
> Russell
> On Friday 31 August 2001 08:45, you wrote:
>>> I hope I'm in the right place.
>>
>> Hardly. This list is for the Zope database, which is an
>> object-oriented database, not a relational database. I also don't
>> think you are using Python -- the Zope database is written in Python
>> and requires the Python to use it. So, unfortunately I think you
>> picked the wrong list. Try finding a newsgroup or mailing list with
>> "relational database" and "SQL" in its description. :-)
>>
>> --Guido van Rossum (home page: http://www.python.org/~guido/)
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db

on 8/31/01 7:41 AM, Russell Hires at rhires@earthlink.net wrote:

> Hello everyone,
>
> I hope I'm in the right place. I'm working on a gradebook program for
> teachers, and at this point I want a teacher to be able to add an assignment.
> I've got a basic table that has the student name, id, assignment, and grade,
> with a row for each assignment/grade for each student. I'm at the point now
> where the teacher adds a new assignment and I want <something> to go through
> the table, and create new rows for each student, but with a null value for
> the grade which will be entered later. How do I do that? I'm thinking of just
> going through the table with a SELECT DISTINCT student-id clause (that way I
> only create the rows once for each student assignment), but that seems like
> two steps, one for the SELECT DISTINCT, but then adding new rows based on
> that query is baffling me.
>
> Thanks for the help!


Hi Russell,

*Generally,* I'd say you do something like this with a relation (hence,
releational database) which is a three-table set, e.g.

CREATE TABLE STUDENTS (
NAME VARCHAR2(64),
ID NUMBER PRIMARY KEY,
...
)

CREATE TABLE ASSIGNMENTS (
NAME VARCHAR2(64),
ID NUMBER PRIMARY KEY,
DUE DATE
...
)

CREATE TABLE GRADE (
STUDENTID NUMBER REFERENCES STUDENTS(ID),
ASSIGNMENTID NUMBER REFERENCES ASSIGNMENTS(ID),
SCORE NUMBER,
COMPLETEDON DATE
...
)

and by joining the three tables, you can get your grades. Thus, implicitly
with insertion into the assignments table you create a new assignment which
no-one has completed yet.

So, you can do something like

select students.name student, assignments.name assignment, grade.score
grade
from students, assignments, grade
where
assignments.name = 'My choice asssignment',
and grade.assignmentid = assignment.id,
and grade.studentid = students.id

(that query puts a lot of faith into the query optimizer, but presumably
your tables are pretty small)

By playing around with DISTINCT etc you can cause the NULL entries of a join
to appear as results (I'd have to go look up the exact syntax). I usually
lean heavily on the query plan explanation function of the database (Oracle
for me) to tune queries for speed.

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

Russell,
Sorry about dumping alot of code on you, especially in the grades table.
the foreign key constraints in the grades table will handle deleting
any grades for students or assignments that have been deleted. you may
or may not want that. if not then take out the constraint lines.

just to reiterate:
you don't have to do anything special from zope to get the grades
inserted. just do a normal zsqlmethod and insert into assignments


Tom Jenkins wrote:
> 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