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() ================================================================================ -- http://mail.python.org/mailman/listinfo/python-list