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

Question

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?

Philip Tzou · Accepted Answer · 2016-12-15 09:55:35Z

1

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.

answered Dec 15, 2016 at 9:55

Philip Tzou

6,5582 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aviss Over a year ago

I have multiple entries for one date because of different gender and age. So I guess I need to use your second suggestion but adding age there too... I'll try that and come back to you. Thanks!

e4c5 Over a year ago

Actually the correct approach is the other way round. You have to try to insert and catch the integrity error when the exception fails. Eliminates the need for two queries and also avoids another thread inserting between your select and insert.

Laurenz Albe · Accepted Answer · 2016-12-15 09:50:08Z

0

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

answered Dec 15, 2016 at 9:50

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related