Check if an appointment exists before adding it to the database

Question

I have a python flask restful app that I would like to improve by checking if an appointment exists with a specific doctor at a specifique time before inserting it into the database

I added an if statement at the post method but nothing happens even if I add a new entry. Help me please what did I do wrong

def post(self):
    """creation de rendez-vous avec pat_id et doc_id"""



    appointment = request.get_json(force=True)
    pat_id = appointment['pat_id']
    doc_id = appointment['doc_id']
    appointment_date = appointment['appointment_date']

    a = conn.execute("SELECT count(*) From appointment WHERE doc_id =? AND appointment_date=?",(doc_id,appointment_date,)).fetchone()
    if a == 0:
        appointment['app_id'] = conn.execute('''INSERT INTO appointment(pat_id,doc_id,appointment_date)
            VALUES(?,?,?)''', (pat_id, doc_id,appointment_date)).lastrowid
        conn.commit()
        return appointment

the POST command is succesful yet nothing adds to the database

I am using a sqlite3 database and I'm connecting to it with this:

import sqlite3
import json

with open('config.json') as data_file:
config = json.load(data_file)

conn=sqlite3.connect(config['database'], check_same_thread=False)
conn.execute('pragma foreign_keys=ON')



def dict_factory(cursor, row):
    """rend la base de donn en json"""
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


conn.row_factory = dict_factory

What database are you using and how are you connecting to it? — Luis Orduz, Jun 10 '17 at 01:23
Are you getting anything back from your select query? Is it actually going in to that condition to execute your insert statement? — idjaw, Jun 10 '17 at 01:24
what does `a` return? also you should be doing `count(1)` it's faster — gold_cy, Jun 10 '17 at 02:30
that looks like a `dict` but you're trying to see if its an `int` — gold_cy, Jun 10 '17 at 03:24
@DmitryPolonskiy yeah I just fixed it thanks mate you pointed me to the right direction — Progger, Jun 10 '17 at 03:54

score 0 · Accepted Answer · answered Jun 10 '17 at 01:27

0

fetchone will return a record or None if there isn't any selected. The comparison with 0 will always return False, so the insert will never occur.

Instead you should compare with None:

a = conn.execute(...).fetchone()
if a is None:
    ...

Keep in mind that this is only partially ensuring that no duplicate appointments are created. In reality if two clients try to schedule an appointment at the exact same time this will happen:

Bob sees there is no appointment
Jane sees there is no appointment
Bob adds an appointment
Jane adds an appointment

This may be an acceptably rare edge case for you but if not you should enforce this at the database level. More info here.

answered Jun 10 '17 at 01:27

Eric Pauley

1,709
1
20
30

The initial answer should work, I imagine. The part about atomicity is less relevant because sqlite locks the whole database during a transaction. – Eric Pauley Jun 10 '17 at 01:49
it still doesn't work though even after correcting the fetchone error – Progger Jun 10 '17 at 01:51
Can you add a line to print what a is before the if statement? – Eric Pauley Jun 10 '17 at 02:54
it prints {'count(*)':0} so I changed the if statement to a['count(*)] == 0 and now it works. Thanks man you've been a great help – Progger Jun 10 '17 at 03:20

Check if an appointment exists before adding it to the database

1 Answers1