recommended way to insert data into a one to many relationship using python

[Bryan]

Wolfgang Meiners wrote:
> one to many relationships are fairly common, i think. So there should be
> a recommended way to insert data into such a relation using python.
>
> Given the following programm, what is the recommended way to insert the
> list of NewEmployees to the database?
>
> ========================================================================
> # !python
> # -*- coding: utf-8 -*-
>
> import sqlite3
>
> con = sqlite3.connect(":memory:")
> cur = con.cursor()
>
> cur.execute("""create table employees(
>     eid integer primary key autoincrement,
>     name text not null,
>     rid integer references rooms(rid))""")
>
> cur.execute("""create table rooms(
>     rid integer primary key autoincrement,
>     number integer,
>     fid integer references floors(fid))""")
>
> cur.execute("""create table floors(
>     fid integer primary key autoincrement,
>     floor text not null)""")
>
[...]
>
> NewEmployees  =[]
> NewEmployees.append({'name': 'George', 'room': 89, 'floor': 'third floor'})
> NewEmployees.append({'name': 'Ellen', 'room': 21, 'floor': 'first floor'})
>

For that kind of insert to be well-defined, the pair (floor,
room_number) must uniquely identify a room. When natural keys like
that are availabe, they're the obvious choice for primary keys in the
database schema. I suggested getting rid of fid and rid, as in:


schema = """
CREATE TABLE floors (
    floor TEXT PRIMARY KEY
);

CREATE TABLE rooms (
    floor TEXT REFERENCES floors,
    number INTEGER,
    PRIMARY KEY (floor, number)
);

CREATE TABLE employees (
    eid INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    floor TEXT,
    room_number INTEGER,
    FOREIGN KEY (floor, room_number) REFERENCES rooms
)
"""

con = sqlite3.connect(":memory:")
for cmd in schema.split(';'):
    con.execute(cmd)
con.close()



--
--Bryan