From: Wolfgang Meiners on
Hi,

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)""")

cur.execute("""insert into floors(floor) values ('first floor')""")
cur.execute("""insert into floors(floor) values ('second floor')""")

cur.execute("""insert into rooms(number,fid) values (21, 1)""")
cur.execute("""insert into rooms(number,fid) values (22, 2)""")

cur.execute("""insert into employees(name,rid) values ('Joe', 1)""")
cur.execute("""insert into employees(name,rid) values ('Nancy', 2)""")

cur.execute("""create view emplist as select name, number, floor
from employees natural inner join rooms natural inner join
floors""")

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

NewEmployees =[]
NewEmployees.append({'name': 'George', 'room': 89, 'floor': 'third floor'})
NewEmployees.append({'name': 'Ellen', 'room': 21, 'floor': 'first floor'})

print NewEmployees
con.close()
===========================================================================

Thank you for any hint
Wolfgang