recommended way to insert data into a one to many relationship usingpython

[Wolfgang Meiners]

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

 

[Peter Otten]

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?

 

[Peter Otten]

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

 

[Bryan]

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

 

[Wolfgang Meiners]

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

 

[Wolfgang Meiners]

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

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

 

[Wolfgang Meiners]

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

================================================================================