John Salerno wrote:
> [EMAIL PROTECTED] wrote:
>
> > [Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
> > 5 1 U of I 1971 1975 BS
> > 6 1 U of I 1975 1976 MS
> > 7 1 U of I 1976 1977 PhD
> >
> > where [Eid] is the primary key of the Education table and
> > [Sid] is the foreign key from the Student table so that the
> > single student record (1) links to three education records
> > (5,6,7).
> >
>
> Hmmm. I think I'm lost. My table will have a key that represents the
> persons ID, in my case it would be salerjo01. This will be what ties
> this table to that person. Can I repeat this key multiple times in the
> Education table?
Yes, because it would be a foreign key.
Perhaps a simple example might help:
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
# in the [person] table, [personid] is the primary key
# but in the [education] table, it's a foreign key
# I deliberately didn't make them AUTOINCREMENT to
# simplify the example
cur.executescript("""
create table person(
personid PRIMARY KEY,
firstname,
lastname
);
create table education(
educationid PRIMARY KEY,
personid,
institution,
yearStart,
yearEnd,
degreeEarned
);
""")
persons = [(1,'Tom','Smith'), \
(2,'Dick','Smith'), \
(3,'Harry','Smith')]
degrees = [(1,1,'University of Illinois',1971,1975,'BS'), \
(2,1,'University of Illinois',1975,1976,'MS'), \
(3,1,'University of Illinois',1976,1977,'PhD'), \
(4,2,'University of Illinois',1971,1974,None), \
(5,2,'DeVry Institute of Technology',1974,1976,'ASEET')]
cur.executemany("""
INSERT INTO person(personid,
firstname,
lastname)
VALUES (?,?,?)"""
, persons)
cur.executemany("""
INSERT INTO education(educationid,
personid,
institution,
yearStart,
yearEnd,
degreeEarned)
VALUES (?,?,?,?,?,?)"""
, degrees)
# Note: since both tables have a field named [personid],
# the table name must be included when referencing
# that field
cur.execute("""
SELECT firstname,
lastname,
institution,
yearStart,
yearEnd,
degreeEarned
FROM education
INNER JOIN person
ON person.personid = education.personid
ORDER BY education.personid;
""")
report = cur.fetchall()
for i in report:
print '%5s %-5s %-30s %d-%d %-6s' % (i)
## Tom Smith University of Illinois 1971-1975 BS
## Tom Smith University of Illinois 1975-1976 MS
## Tom Smith University of Illinois 1976-1977 PhD
## Dick Smith University of Illinois 1971-1974 None
## Dick Smith DeVry Institute of Technology 1974-1976 ASEET
## Note that the third person, Harry Smith, doesn't show up
## in the report.
## That's because no education records were created for him.
## When you do an INNER JOIN between two tables, the linking field,
## [personid] must exist in both tables.
--
http://mail.python.org/mailman/listinfo/python-list
