23

I'm looking for the fastest way to create a new SQLAlchemy object only if it doesn't already exist in the database.

The way I'm doing it now is by first getting the count of the query to see if it exists, and if not--then I create it. EG:

if not User.query.filter(email=user.email).count():
    db.session.add(user)
    db.session.commit()

Is this the best way to do it? Would love some feedback. Thanks!

rdegges
  • 32,786
  • 20
  • 85
  • 109
  • 1
    You should probably read this dezpez article to get an idea of how complicated some of these update-or-insert-if-not-exists jobs can be: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ – Craig Ringer Aug 28 '12 at 05:14
  • 1
    As for speed: Everything you do will have to do a round trip to hit the database. I doubt you'll see a big difference between what you're doing and say an `EXISTS` query. The big time costs are the three round trips (one `SELECT`, one `INSERT`, one `COMMIT`), not the queries themselves. If you want speed, you need to batch work into larger queries, do more work within transactions, and use server-side functions where possible. – Craig Ringer Aug 28 '12 at 05:17
  • 1
    How often do records already exist in the table? 50/50? 80/20? If the majority of the time the record does not exist you can just insert it and catch the odd UQ violation. – Tony Gibbs Aug 28 '12 at 16:42

0 Answers0