Flagging employees whose spending is close to the budgeted amount I am working with an SQLite database. Is this code written the best way it can be and correct? The code:


Selects from the db adding a column for the percentage difference between two columns
Loops through the values of the columns
If the date is in the past 
If the price is greater than 500 and the percentage difference is less than 1st argument set flag to 1
Else if the price is less than 500 and the percentage difference is less than 2nd argument set flag to 1
Otherwise keep the flag as 0




from datetime import datetime as dt

def calculateEmployeeSpend(read_cursor, flag_higher_amount, flag_lower_budget):

    read_cursor.execute("SELECT distinct b.employee_id, b.amount, "
                "s.spend, b.date, b.amount - s.spend as spend_left,  "
                "100.0*(b.amount - s.spend) / b.amount As PercentDiff FROM employee_budget_upload "
                "As b JOIN employee_budget_spent As s ON  b.employee_id = s.employee_id where b.amount != 0")

    for employee_id, amount, spend, date, spend_left, percent_diff in read_cursor:
        flag=0
        date_of_amount = dt.strptime(date, "%d/%m/%Y")
        if date_of_amount &lt;= dt.now():
            if amount &gt; 500 and percent_diff &lt; int(flag_higher_amount) or amount &lt; 500 and percent_diff &lt; int(flag_lower_budget):
                flag=1

        write_cursor.execute('INSERT OR REPLACE INTO employee_overall_spend VALUES (?,?,?,?,?,?,?,?)',
                         (employee_id, amount, spend, date, spend_left, percent_diff, dt.now().strftime("%d/%m/%Y"), flag))
There's two ways you can go about this. Stick with your current way, or use SQL more.

Current way


I'd change your for loop to assign to one value.
I'd make a function that tells you if the values have the flag.
I'd use *value to tuple unpack into the write_cursor tuple.


And so would get something like:

from datetime import datetime as dt


def get_flag(employee_spend, higher, lower):
    _, amount, _, date, _, percent_diff = employee_spend
    return int(
        dt.strptime(date, "%d/%m/%Y") &lt;= dt.now()
        and (
            amount &gt; 500
            and percent_diff &lt; int(higher)
            or amount &lt; 500
            and percent_diff &lt; int(lower)
        )
    )


def calculateEmployeeSpend(read_cursor, flag_higher_amount, flag_lower_budget):
    higher, lower = int(flag_higher_amount), int(flag_lower_budget)

    read_cursor.execute("SELECT distinct b.employee_id, b.amount, "
                "s.spend, b.date, b.amount - s.spend as spend_left,  "
                "100.0*(b.amount - s.spend) / b.amount As PercentDiff FROM employee_budget_upload "
                "As b JOIN employee_budget_spent As s ON  b.employee_id = s.employee_id where b.amount != 0")

    for employee_spend in read_cursor:
        flag = get_flag(employee_spend, higher, lower)
        write_cursor.execute(
            'INSERT OR REPLACE INTO employee_overall_spend VALUES (?,?,?,?,?,?,?,?)',
            (*employee_spend, dt.now().strftime("%d/%m/%Y"), flag)
        )


Use SQL


If you chose to use SQL I'd change it so that you pass higher and lower to the SQL query.
I'd move the flag logic into the SQL query.
I'd move the current time logic into the SQL query. I found how to get the current time from here.
I'd look into moving the SQL commands into their own files, so that they're easier to maintain.


I'm don't know SQL, but you could use something like:

CALCULATE_EMPLOYEE_SPEND_READ = '''\
DECLARE @curr_date DATETIME;
SET @curr_date = GETDATE();

SELECT
    e.employee_id,
    e.amount,
    e.spend,
    e.date,
    e.spend_left,
    e.percent_diff,
    @curr_date as curr_date,
    case when
        e.date &lt;= @curr_date
        and (
            e.amount &gt; 500
            and e.percent_diff &lt; ?
            or e.amount &lt; 500
            and e.percent_diff &lt; ?
        )
        then 1
        else 0 
    end
    as flag
FROM (
    SELECT distinct
        b.employee_id,
        b.amount,
        s.spend,
        b.date,
        b.amount - s.spend as spend_left,
        100.0 - 100.0*s.spend / b.amount as percent_diff
    FROM employee_budget_upload As b
    JOIN employee_budget_spent As s
    ON  b.employee_id = s.employee_id
    WHERE b.amount != 0
) as e
'''
CALCULATE_EMPLOYEE_SPEND_WRITE = 'INSERT OR REPLACE INTO employee_overall_spend VALUES (?,?,?,?,?,?,?,?)'


def calculateEmployeeSpend(read_cursor, higher, lower):
    read_cursor.execute(CALCULATE_EMPLOYEE_SPEND_READ, (int(higher), int(lower)))
    for employee_spend in read_cursor:
        write_cursor.execute(CALCULATE_EMPLOYEE_SPEND_WRITE, employee_spend)