I'm testing out the classical style of mapping for SQL Alchemy. The problem I'm having is when I insert an object that has multiple database objects as properties. My model is like so:
class File():
def __init__(self, fileName: str, modifiedDate: datetime) -> None:
self.fileName = fileName
self.modifiedDate = modifiedDate
def __eq__(self, other: 'File') -> bool:
return self.fileName == other.fileName
def __hash__(self) -> int:
return hash(self.fileName)
@dataclass(unsafe_hash=True)
class PackageID():
packageRef: str
@dataclass
class LineItem():
customerID: int
file: File
package: PackageID
My mappings are as follows:
Files = Table(
"files",
mapper_registry.metadata,
Column('ID', Integer, primary_key=True, autoincrement=True),
Column('fileName', String(255), nullable=False, unique=True),
Column('modifiedDate', Date, nullable=False))
Packageids = Table(
'packageids',
mapper_registry.metadata,
Column('ID', Integer, primary_key=True, autoincrement=True),
Column('packageRef', String(255), nullable=False, unique=True)
)
Lineitems = Table(
'lineitems',
mapper_registry.metadata,
Column('ID', Integer, primary_key=True),
Column('customerID', Integer),
Column('fileID', ForeignKey('files.ID'), nullable=False),
Column('packageID', ForeignKey('packageids.ID'), nullable=False)
)
def startMappers():
lineMapper = mapper_registry.map_imperatively(models.LineItem, Lineitems)
fileMapper = mapper_registry.map_imperatively(models.File, Files, properties={'lineitem': relationship(models.LineItem, backref='file')})
packageMapper = mapper_registry.map_imperatively(models.PackageID, Packageids, properties={'linteitem': relationship(models.LineItem, backref='package')})
And lastly the code the handles entering the objects to the database:
def testInsert():
engine = startDB()
file = models.File('TestFile12345.xlsx', datetime.now())
package = models.PackageID(packageRef='TestID12345678')
li = models.LineItem(11111, file=file, package=package)
with Session(engine) as session:
fp = FileRepository(session)
pp = PackageRepository(session)
lp = LineItemRepository(session)
file = fp.get(file)
package = pp.get(package)
lp.add(li)
session.commit()
Here the fp.get and pp.get just update the package to an existing model from the database if it exists. The first time I run this it works fine. If I try to enter another entry that has the same file I get a unique error constraint on the file table. I can't figure out the proper way of doing this. Is there a way to just add the line item instead of adding the file & package as well?