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