[Tutor] Fwd: Which databases allow lists as record fields?

[Peter Otten]

DoanVietTrungAtGmail wrote:

>> You don't. You create a second table to hold the list.
>> Then in the second table you include be reference back to the first.
>>
> 
> I thought about that but thought it seemed a roundabout way. But assuming
> I do it that way, how to deal with variable-length list? Most lists have
> 10^3, but some can grow to perhaps 10^7 items. A fixed record structure to
> fit the longest possible record would make the database table sparse and,
> presumably, waste space.

You are misunderstanding Alan, there is nothing "fixed" in his approach.

Instead of one table, e. g. holding student name and a list of courses taken 
by the student

students_with_courses
studendId | name | courses
--------------------------------------------
        1 | Jim  | [maths, biology, english]
        2 | Sue  | [chemistry, spanish]

you have two tables

students
studendId | name
----------------
        1 | Jim
        2 | Sue

courses
studentId | coursename
----------------------
        1 | maths
        1 | biology
        1 | english
        2 | chemistry
        2 | spanish

To find out Sue's courses you'd do two queries:

(1) Find out the studentId

sql = "select studentId from students where name = ?"
studentID = cursor.execute(sql, ["Sue"]).fetchone()[0]

(2) Find the corresponding courses

sql = select coursename from courses where studentId = ?"
for row in cursor.execute(sql, [studentId]).fetchall():
    print row[0]

PS: A realistic use case would introduce a third table to hold course 
information and the table connecting courses and students would hold the 
studentId and the courseId.