I try to insert in a Postgres database several entities. First a building and then the (several ?) building stairs.
Here is a minimal viable example :
import psycopg
def get_conn():
database = 'XXXX'
username = 'XXXX'
hostname = '127.0.0.1'
return psycopg.connect("dbname='%s' user='%s' host='%s'"%(database, username, hostname))
def prepare_bdd():
conn = get_conn()
cur = conn.cursor()
cur.execute(""" drop table if exists test_building_name """)
cur.execute(""" drop table if exists test_building_stairs """)
cur.execute(""" create table test_building_name (builing_name varchar) """)
cur.execute(""" create table test_building_stairs (name varchar, level int) """)
conn.commit()
cur.close()
conn.close()
class Building:
def __init__(self, building_name=None, stairs_list=[]):
self.building_name = building_name
self.stairs_list = stairs_list
def save(self):
conn = get_conn()
cur = conn.cursor()
# save the Building entity
cur.execute("""INSERT INTO test_building_name(builing_name) VALUES('%s')"""%self.building_name)
# foreach stair, save it too
for stair in self.stairs_list:
stair.save()
conn.commit()
cur.close()
conn.close()
class BuildingStairs:
def __init__(self, building_name=None, floor_level=None):
self.building_name = building_name
self.floor_level = floor_level
def save(self):
conn = get_conn()
cur = conn.cursor()
# save the BuildingStairs entity
cur.execute("""INSERT INTO test_building_stairs(name, level) VALUES('%s', %i)"""%(self.building_name, self.floor_level))
conn.commit()
cur.close()
conn.close()
if __name__ == '__main__':
# Empty & recreate tables
prepare_bdd()
# create test lines
building1 = Building(building_name="A")
building1.stairs_list.append(BuildingStairs(building_name="A", floor_level=0))
building1.save()
building2 = Building(building_name="B")
building2.stairs_list.append(BuildingStairs(building_name="B", floor_level=1))
building2.stairs_list.append(BuildingStairs(building_name="B", floor_level=2))
building2.save()
building3 = Building(building_name="C")
building3.stairs_list.append(BuildingStairs(building_name="C", floor_level=3))
building3.save()
problem : all right for the Building entities but the BuildingStairs are duplicated. Do you see where it comes from ?
| builing_name |
|---|
| A |
| B |
| C |
| name | level |
|---|---|
| A | 0 |
| A | 0 |
| B | 1 |
| B | 2 |
| A | 0 |
| B | 1 |
| B | 2 |
| C | 3 |
It should be
| name | level |
|---|---|
| A | 0 |
| B | 1 |
| B | 2 |
| C | 3 |
