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

[Peter Otten]

Wolfgang Meiners wrote:

> 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

There are probably some opportunities for generalization lurking in the 
following mess, but it would take me more time than I'm willing to invest.

cur.execute("create table new_employees (name, room, floor, fid);")
cur.executemany("""insert into new_employees (name, room, floor) 
                   values (:name, :room, :floor)""", NewEmployees)

c2 = con.cursor()

missing = c2.execute("""
select distinct n.floor from new_employees n
left outer join floors f on n.floor = f.floor
where f.floor is null
""")
cur.executemany("insert into floors (floor) values (?)", missing)
cur.execute("""
update new_employees
set fid = (select fid from floors where floors.floor = 
new_employees.floor)""")


missing = c2.execute("""
select distinct n.fid, n.room from new_employees n
left outer join rooms r on n.fid = r.fid and n.room = r.number
where r.fid is null""")
cur.executemany("insert into rooms (fid, number) values (?, ?)", missing)

new = c2.execute("""
select n.name, r.rid from new_employees n, rooms r
where n.room = r.number and n.fid == r.fid
""")
cur.executemany("insert into employees (name, rid) values (?, ?)", new)

If your data is small enough you may try to do the heavy lifting in Python 
instead of SQL; if not, maybe you'd better ask in a SQL forum.

Peter

Afterthought: Can SQLAlchemy do these kind of things cleanly?