Change query in method, depending on if statement

Question

I'm currently working on a Python project, where I need to use a REPLACE into query. The table is for budgets, and is supposed to update the column for either expenses or incomes, depending on the value.

My idea is, to check if it's either an expense or incomes in the initial if statement. Then the query column name 'TYPE' is supposed to change based on the if statement. Atlast as usual, I will format the values into the query to execute.

How can I properly format the query based on my if-statement?

PYTHON:

for key, value in budget.items():
    if value > 0:
        type = "incomes"
    else:
        type = "expenses"
        
    updateQuery = """
        REPLACE into budget (
            username,
            {type},
            categories
        ) VALUES ('{}', '{}', '{}');
    """.format(username, type, key)

    insertTuple = (username, value)
    cursor.execute(updateQuery, insertTuple)

    self.__dbConnector.commit()

M$SQL

CREATE TABLE budget (
    budget_id VARCHAR(255),
    incomes MONEY,
    expense MONEY,
    savings MONEY,
    investments MONEY,
    categories VARCHAR(255),
    FOREIGN KEY(budget_id) REFERENCES user (username)
);

I'm not sure what the question is here, could you be more explicit in what you are asking? — Xinthral, Mar 06 '21 at 18:30
Read title :) Basically, I want to change the REPLACE into query, but the thing is the column depends on the value. Therefore I want to format the updateQuery — onii-sama, Mar 06 '21 at 18:33
Refrain from using name of built-ins as variable names as doing such overrides the name of the built-in within that scope, in this case `type`. https://stackoverflow.com/questions/2417979/can-i-use-the-variable-name-type-as-function-argument-in-python — Sandeep Gusain, Mar 06 '21 at 19:04

score 0 · Answer 1 · answered Mar 06 '21 at 18:46

You can use an f-string to substitute the value of type into the SQL.

Use %s for the placeholders that will be filled in from inserType by cursor.execute(). You don't need to fill them in when formatting the SQL.

for key, value in budget.items():
    if value > 0:
        type = "incomes"
    else:
        type = "expenses"
        
    updateQuery = f"""
        REPLACE into budget (
            username,
            {type},
            categories
        ) VALUES (%s, %s, %s);
    """

    insertTuple = (username, value, key)
    cursor.execute(updateQuery, insertTuple)

    self.__dbConnector.commit()

Change query in method, depending on if statement

1 Answers1