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