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