recommended way to insert data into a one to many relationship using python
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: recommended way to insert data into a one to many relationship using python
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
Re: recommended way to insert data into a one to many relationship using python
Peter Otten wrote: If you create indices for floors (and rooms) cur.execute("""create unique index room_index on rooms (fid, number);""") cur.execute("""create unique index floor_index on floors (floor);""") the addition of missing rows can be simplified to missing = c2.execute("""select distinct floor from new_employees;""") cur.executemany("insert or ignore into floors (floor) values (?)", missing) etc. Peter -- http://mail.python.org/mailman/listinfo/python-list
Re: recommended way to insert data into a one to many relationship using python
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
Re: recommended way to insert data into a one to many relationship using python
Peter Otten schrieb: > Peter Otten wrote: > > If you create indices for floors (and rooms) > > cur.execute("""create unique index room_index on rooms (fid, number);""") > cur.execute("""create unique index floor_index on floors (floor);""") > > the addition of missing rows can be simplified to > > missing = c2.execute("""select distinct floor from new_employees;""") > cur.executemany("insert or ignore into floors (floor) values (?)", missing) > > etc. > > Peter Hi Peter, thank you for your response. What i have got from it, is to have a (temporary) table to do the work inside sql and not from python. I thought of a second method to do it inside sql by an trigger sql = """create trigger insert_new_employee instead of insert on emplist begin # insert floor if not exists floor # insert (room, floor) if not exists (room, floor) # insert (person, rid) end""" but i would have to learn how to write triggers. Your idea gives me a more direct solution. Wolfgang -- http://mail.python.org/mailman/listinfo/python-list
Re: recommended way to insert data into a one to many relationship using python
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
Re: recommended way to insert data into a one to many relationship using python
Wolfgang Meiners schrieb: [... example of a simple sql-database and relating questions ...] so after reading the hints of Peter Otten and Bryan i played around a bit and got the following solution. Of course it would be much simpler following Bryans idea of natural keys but i think, i will go step by step. Any useful comments appreacheated. Wolfgang # !python # -*- coding: utf-8 -*- import sqlite3 schema = """ create table floors ( fid integer primary key autoincrement, floor text not null); create table rooms ( rid integer primary key autoincrement, number integer, fid integer references floors(fid)); create table employees ( eid integer primary key autoincrement, name text not null, rid integer references rooms(rid)); create view emplist as select name, number, floor from employees natural inner join rooms natural inner join floors; """ #con = sqlite3.connect("test.db") con = sqlite3.connect(":memory:") cur = con.cursor() for cmd in schema.split(';'): cur.execute(cmd) def insert_new_value(d): sql = """insert or ignore into floors(floor) select :floor where not exists (select * from floors where floor = :floor)""" cur.execute(sql,d) sql = """insert or ignore into rooms (number, fid) select :number, fid from floors where floor = :floor and not exists (select * from rooms natural inner join floors where number = :number and floor = :floor)""" cur.execute(sql,d) sql = """insert or ignore into employees(name,rid) select :name, rid from rooms natural inner join floors where number = :number and floor = :floor and not exists (select * from employees natural inner join rooms natural inner join floors where name = :name and number = :number and floor = :floor) """ cur.execute(sql,d) NewEmployees =[] NewEmployees.append({'name': 'Joe', 'number': 21, 'floor': 'first floor'}) NewEmployees.append({'name': 'Nancy', 'number': 22, 'floor': 'second floor'}) NewEmployees.append({'name': 'George', 'number': 89, 'floor': 'third floor'}) NewEmployees.append({'name': 'Ellen', 'number': 21, 'floor': 'first floor'}) NewEmployees.append({'name': 'Joe', 'number': 21, 'floor': 'first floor'}) print "Old Values:" print cur.execute("""select * from emplist order by name """).fetchall() for d in NewEmployees: insert_new_value(d) print "New Values:" print cur.execute("""select * from emplist order by name """).fetchall() con.close() -- http://mail.python.org/mailman/listinfo/python-list