My approach for avoiding multiple flask-sqlalchemy db entries

Question

My Flask app reads time-based CSV log file of observations where new entries are appended. My approach in aim to avoid duplications is to collect variables and test before adding the record as in:

        sl = db.session.query(DesignObservation).filter_by(worker_id = worker_id).filter_by(design_id = design_id).filter_by(ob_date = ob_date)

        if len(sl.all()):
            pass
        else:
            db.session.add(observation_record)
            db.session.commit()

Not sure why this pattern does not add the record. If I forgo the test the record adds but dups would be added on subsequent runs.

T Burgis · Accepted Answer · 2018-08-10 14:29:36Z

1

You should set UNIQUE constraints on the table you are writing to. The database engine will then manage the uniqueness for you.

You will have to deal with the 'UNIQUE constraint failed' exception that is coming your way ...

answered Aug 10, 2018 at 14:29

T Burgis

1,4359 silver badges9 bronze badges

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

2 Comments

Timothy Lombard Over a year ago

I'm not able to change the data model to set UNIQUE constraints to multiple fields. Needed to come up with an alternate approach.

Timothy Lombard Over a year ago

I found this SO entry helpful- stackoverflow.com/questions/43975349/…

Timothy Lombard · Accepted Answer · 2018-08-11 22:49:36Z

0

What I finally ended up doing is paring the design_id with an ob_date to determine uniqueness then checking using the function below to return a boolean of whether the record to add was unique.

import parser.parse as date_parse

def check_4_obs_dups(design_id, ob_date):
    # returns bool
    r = DesignObservation.query.filter_by(design_id=design_id).all()
    # convert date strings stored in SQLite to datetime 
    s = [date_parse(x.ob_date) for x in r]

    return date_parse(ob_date) in s

Then when iterating thru each line of the CSV file, I check for uniqueness with this loop:

if not check_4_obs_dups(design_id, ob_date):
    db.session.add(observation_record

answered Aug 11, 2018 at 22:49

Timothy Lombard

9671 gold badge13 silver badges32 bronze badges

Collectives™ on Stack Overflow

My approach for avoiding multiple flask-sqlalchemy db entries

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related