One to many relationship in SQLAlchemy

Question

I have the following one-to-many relationship in SQLAlchemy.

class Application(Base):
    __tablename__  = "application"

    id = Column("id", Integer, primary_key = True, autoincrement = True)        
    organization_id = Column(Integer, ForeignKey('organization.id'))
    organization = relationship("Organization", uselist=False, back_populates="applications")

and

class Organization(Base):
    __tablename__  = "organization"

    id = Column("id", Integer, primary_key = True, autoincrement = True)
    name = Column("name", String(128), unique = True, nullable = False)
    applications = relationship("Application", back_populates="organization")

To put the relationship in words, "one organization can have many applications and an application can have a single organization".

The way I infer it is that the Organization class is the parent and the Application class is the child.

I have a .csv file that contains a list of applications. I'm parsing the list to create an instance of Application and Organization from each row. I then set application.organization = organization, and then do session.add(application).

When I the application to the database, if the organization that is added in turn is being added the first time, it's inserted correctly. But when an application is found that has the same organization as one inserted before it complains of an error -

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry 'MICROSOFT' for key 'name'")

I understand that this is because this organization already exists in the DB.

Won't SQLAlchemy take care of this case? If not, how do I handle this on my end?

score 0 · Answer 1 · answered Jul 29 '19 at 20:38

0

You are probably creating a new Organization each time instead of grabbing the already existing one (if it exists).

This code should point you in the right direction

# Organization name from your CSV
org_name = 'MICROSOFT'

# Get Organization if it already exists
application.organization = Organization.query.filter(Organization.name == org_name ).first()

# If the Organization doesn't exist, create a new one
if application.organization is None:
    application.organization = Organization(name=org_name)

answered Jul 29 '19 at 20:38

noslenkwah

1,702
1
17
26

So SQLAlchemy doesn't take care of this automatically? – Kunal Shah Jul 29 '19 at 21:16
@KunalShah - What is "this" exactly? You have told it to not allow organizations to have the same name, and then you try to make two organizations with the same name. It raises an error because you have explicitly asked it to do so under this circumstance (`unique = True`). – noslenkwah Jul 29 '19 at 21:43
Won't SQLAlchemy do the equivalent of create if not exists? – Kunal Shah Jul 29 '19 at 21:44
Not built in, but you can create one. See [this](https://stackoverflow.com/questions/2546207/does-sqlalchemy-have-an-equivalent-of-djangos-get-or-create) question. – noslenkwah Jul 29 '19 at 21:45

One to many relationship in SQLAlchemy

1 Answers1