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

[Wolfgang Meiners]

Wolfgang Meiners schrieb:

[... example of a simple sql-database and relating questions ...]

so after reading the hints of Peter Otten and Bryan i played around a
bit and got the following solution. Of course it would be much simpler
following Bryans idea of natural keys but i think, i will go step by
step. Any useful comments appreacheated.
Wolfgang


================================================================================
# !python
# -*- coding: utf-8 -*-

import sqlite3

schema = """
create table floors (
        fid integer primary key autoincrement,
        floor text not null);

create table rooms (
        rid integer primary key autoincrement,
        number integer,
        fid integer references floors(fid));

create table employees (
        eid integer primary key autoincrement,
        name text not null,
        rid integer references rooms(rid));

create view emplist as select name, number, floor
        from employees natural inner join rooms natural inner join floors;
"""


#con = sqlite3.connect("test.db")
con = sqlite3.connect(":memory:")
cur = con.cursor()
for cmd in schema.split(';'):
    cur.execute(cmd)

def insert_new_value(d):
    sql = """insert or ignore into floors(floor)
    select :floor
    where not exists (select * from floors where floor = :floor)"""
    cur.execute(sql,d)

    sql = """insert or ignore into rooms (number, fid)
    select :number, fid from floors
    where floor = :floor
    and not exists (select * from rooms natural inner join floors
    where number = :number and floor = :floor)"""
    cur.execute(sql,d)

    sql  = """insert or ignore into employees(name,rid)
    select :name, rid from rooms natural inner join floors
    where number = :number and floor = :floor
    and not exists (select * from employees natural inner join rooms
            natural inner join floors where
            name = :name and number = :number and floor = :floor) """
    cur.execute(sql,d)

NewEmployees  =[]
NewEmployees.append({'name': 'Joe', 'number': 21, 'floor': 'first floor'})
NewEmployees.append({'name': 'Nancy', 'number': 22, 'floor': 'second
floor'})
NewEmployees.append({'name': 'George', 'number': 89, 'floor': 'third
floor'})
NewEmployees.append({'name': 'Ellen', 'number': 21, 'floor': 'first floor'})
NewEmployees.append({'name': 'Joe', 'number': 21, 'floor': 'first floor'})

print "Old Values:"
print cur.execute("""select * from emplist order by name """).fetchall()

for d in NewEmployees:
    insert_new_value(d)

print "New Values:"
print cur.execute("""select * from emplist order by name """).fetchall()

con.close()

================================================================================