CODEWITHSUNDEEP
pythonpostgresqlduplicates
aviss
aviss

Reputation: 2439

PostgreSQL/Python: insert new row only if doesn't exists already

My table:

class api(Base):
    __tablename__ = "api"

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    gender = Column(String)
    age = Column(String)
    value = Column(Integer)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

When I send an api query with dates which overlaps with previous queries duplicate rows are created. How can I avoid that? I can delete duplicates but is there a more efficient way to avoid inserting them in the first place?

Upvotes: 0

Views: 660

Answers (2)

Philip Tzou
Philip Tzou

Reputation: 6438

It looks like you are using SQLAlchemy ORM. You can add unique=True to a single column to create constraint on your table. For example:

class API(Base):
    ...
    date = Column(DateTime, nullable=False, unique=True)

You can also create unique constraint on multiple columns by passing UniqueConstraint to __table_args__. For example:

class API(Base):
    __table_args__ = (
        UniqueConstraint('date', 'gender'),
    )

It is still recommended that you should check the existence before you inserting the new record into the database, since a violation of database constraint will normally be logged in the database log file.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246308

You could create a database constraint that forbids duplicate entries and handle errors in your code.

Upvotes: 0

Related Questions