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

[Wolfgang Meiners]

Bryan schrieb:
> 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

This looks interesting to me. I would have all necessary information in
table employees. But i think the additional tables for rooms and floors
are necessary too, to have a normalized database.

I thougth of a function like try_insert_and_return_key(x,y) for the
tables but i had difficulties to write such a function. In this function
x should be the key and y the depended data that can be compound.
Nevertheless, with a given y there might be more then one x and i think,
this is exactly what you stated above.

Thank you for this hint
Wolfgang