Using IF-function to distinguish which sqlite query should be used?

Question

I have the following (updated) code:

from tkinter import ttk
import sqlite3
from tkinter.ttk import *
import math

Quantity = StringVar()


def calc(filter):
    qty = Quantity.get()
    with sqlite3.connect('Test.sql3') as conn:
        cursor = conn.cursor()
        conn.create_function("CEIL", 1, lambda v: int(math.ceil(v)))
        cursor.execute('SELECT CEIL(Upper*?),'
                       'CEIL(Stiffener*?),'
                       'CEIL(Insole*?),CEIL(Sock*?),'
                       'CEIL(Laces*?),'
                       'CEIL(Foil*?),'
                       'CEIL(PBA887*?),'
                       'CEIL(Soles*?),'
                       'CEIL(Eyelets*?),'
                       'CEIL(IA80*?),'
                       'CEIL(GussetElastic*?),'
                       'CEIL(Cartons*?) '
                       'FROM Costing WHERE Type=?',
                       (qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, filter,))
        results = cursor.fetchall()

        item_0_in_result0 = [_[0] for _ in results]

        result0.config(text=item_0_in_result0)

        cursor.close()
        conn.commit()

# Result labels
Label(root, text="Upper Material:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=160)
result0 = Label(root, width=40, background="lightgreen", font=("Arial, bold", 11))
result0.place(x=250, y=160)

line 34, in calc
    (qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, filter,))
sqlite3.OperationalError: near ")": syntax error

Should the change of statement: CEIL(Sock*?) be applied as above code?

The code has been adjusted accordingly to apply to current comments and descriptions.

I am aware that it is not recommended to post the entire code, therefore I have shortened parts that might be repeated in multiple lines to accommodate the Query. Please bear in mind that I am still kind of new to tkinter and only code as a hobby.

Thank you for understanding

I don't understand problem. `command` should run always the same one function which will use `if/else` to execute `calc_pb` or `calc_b` or `calc_m` - you don't have to change `command` when user change `style` — furas, Dec 09 '20 at 10:31
@furas So with that said it would be easier to create more buttons based on the specific condition and rune the desired `def example():`? — ReflexTechR, Dec 09 '20 at 12:38
you can use more buttons or one button with droplist or checkbuttons to select style - and this single button should run function which runs code `if Style is "PB": calc_pb()`, `if Style is "Boys": calc_b()`, etc. — furas, Dec 09 '20 at 12:50
@furas The environment which this is made for does not have the most intelligent user inputs. I would rather keep it simple for end user, although that would be the ideal. I actually have to read up a bit on that as well for future use as i would prefer a button running a `command` that uses a variable. IDEALLY. Thank you — ReflexTechR, Dec 09 '20 at 13:28
The SQL statement has five placeholders `?`, but you provide only four arguments. — acw1668, Dec 10 '20 at 07:31
@acw1668 I apologize for the typo, the full script contains between 12 and 15 arguments depending on the query. I just shortened the `script` to be sufficient in relation to the question and left out the "extra" arguments. I see i also made a typo in the `command=calac_m` The script is running as close as desired at the moment. — ReflexTechR, Dec 12 '20 at 10:37

score 0 · Answer 1 · answered Dec 09 '20 at 10:52

0

If you want to run a query based on a condition then provide a condition for the query.

Example:

            email = request.form['email']
            role = request.form['role']

            # Create Cursor
            cur = mysql.connection.cursor()

            if role == 'admin':
                    # Get user by email
                    result = cur.execute("SELECT * FROM admins WHERE email = %s", [email])
            else:
                    # Get user by email
                    result = cur.execute("SELECT * FROM users WHERE email = %s", [email])

answered Dec 09 '20 at 10:52

izudada

1

All though I can see the approach and will definitely consider this option in the future. The main issue is that I have multiple scripts designed in this way and want to minimize the time required to achieve this, as I am on a deadline. Thank you for the clarity in your answer. I honestly did not think of this option initially. – ReflexTechR Dec 09 '20 at 12:41
The query in your answer gets classified under no-no's for SQL queries as far as documentation goes. Where stated `"SELECT * FROM admins WHERE email = %s", [email]` Syntax *should* `"SELECT * FROM admins WHERE email=?", [email]` OR alternatively if more values: `"SELECT * FROM admins WHERE (email, example) VALUES(?,?)", [email, example]` Just a pointer for future reference. Read up on SQL injection, that is the main issue arising with named syntax. Hope it helps! – ReflexTechR Dec 09 '20 at 13:35

acw1668 · Answer 2 · 2020-12-12T11:57:39.723

You can combine the three calc_x() into one calc() and use an argument for the WHERE clause:

def calc(filter):
    qty = Quantity.get()
    with sqlite3.connect('Test.sql3') as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT Upper*?,'
                       'Stiffener*?,'
                       'Insole*?,'
                       'Sock*?,'
                       'Laces*?,'
                       'Foil*?,'
                       'PBA887*?,'
                       'Soles*?,'
                       'Eyelets*?,'
                       'IA80*?,'
                       'GussetElastic*?,'
                       'Cartons*? '
                       'FROM Costing WHERE Type=?',
                       (qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, qty, filter,))
        results = cursor.fetchall()

        item_0_in_result0 = [_[0] for _ in results]
        item_0_in_result1 = [_[1] for _ in results]
        item_0_in_result2 = [_[2] for _ in results]
        item_0_in_result3 = [_[3] for _ in results]
        item_0_in_result4 = [_[4] for _ in results]
        item_0_in_result5 = [_[5] for _ in results]
        item_0_in_result6 = [_[6] for _ in results]
        item_0_in_result7 = [_[7] for _ in results]
        item_0_in_result8 = [_[8] for _ in results]
        item_0_in_result9 = [_[9] for _ in results]
        item_0_in_result10 = [_[10] for _ in results]
        item_0_in_result11 = [_[11] for _ in results]

        result0.config(text=item_0_in_result0)
        result1.config(text=item_0_in_result1)
        result2.config(text=item_0_in_result2)
        result3.config(text=item_0_in_result3)
        result4.config(text=item_0_in_result4)
        result5.config(text=item_0_in_result5)
        result6.config(text=item_0_in_result6)
        result7.config(text=item_0_in_result7)
        result8.config(text=item_0_in_result8)
        result9.config(text=item_0_in_result9)
        result10.config(text=item_0_in_result10)
        result11.config(text=item_0_in_result11)

        cursor.close()
        conn.commit()

...

# Result labels
Label(root, text="Upper Material:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=160)
result0 = Label(root, width=40, background="lightgreen", font=("Arial, bold", 11))
result0.place(x=250, y=160)

Label(root, text="Stiffener:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=180)
result1 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result1.place(x=250, y=180)

Label(root, text="Insole:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=200)
result2 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result2.place(x=250, y=200)

Label(root, text="PU Lining:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=220)
result3 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result3.place(x=250, y=220)

Label(root, text="Laces:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=240)
result4 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result4.place(x=250, y=240)

Label(root, text="Foil:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=260)
result5 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result5.place(x=250, y=260)

Label(root, text="PBA 887:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=280)
result6 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result6.place(x=250, y=280)

Label(root, text="Soles:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=300)
result7 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result7.place(x=250, y=300)

Label(root, text="Eyelets:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=320)
result8 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result8.place(x=250, y=320)

Label(root, text="IA 80 Solution:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=340)
result9 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result9.place(x=250, y=340)

Label(root, text="Gusset Elastic:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=360)
result10 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result10.place(x=250, y=360)

Label(root, text="Cartons:", width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=80, y=380)
result11 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11))
result11.place(x=250, y=380)

...

Button(root, text='PB', style='S.TButton', width=11, command=lambda: calc('PBSynth')).place(x=20, y=420)
Button(root, text='B', style='S.TButton', width=11, command=lambda: calc('BSynth')).place(x=20, y=460)
Button(root, text='M', style='S.TButton', width=11, command=lambda: calc('MSynth')).place(x=20, y=500)

Updated based on OP update.

Thank you very much for the example. Originally I had different queries retrieving different number of `VALUES`. I currently have the same amount of variables being retrieved from `Test.sql3` so in turn your answer would be more accurate and look much neater. *Thank you* again. — ReflexTechR, Dec 12 '20 at 11:02
I updated the code in the question based on your recommendation. I had three `def example():` running in the script with the above amount of variables for each, so the script was very long and looked messy. I have the same result with much better looking code. — ReflexTechR, Dec 12 '20 at 11:10
@ReflexTechR You created new result labels each time `calc()` is executed which should be avoided. I would recommend to create those result labels outside `calc()` and update their text inside the function. See my updated answer. — acw1668, Dec 12 '20 at 11:58
I can see how that works and looks more definitive. I just want to know if this would be recommended on basics of programming or if both ways could be used? As far as I can understand it's basically assigning the value from the result to another variable `result_0` which then gets placed in the label, where as the script in my updated question places the result directly into the label Can I also compact the answer and change it to `result11 = Label(root, width=20, background="lightgreen", font=("Arial, bold", 11)).place(x=250, y=380)` in a single line for readability? — ReflexTechR, Dec 12 '20 at 12:27
Just a quick question. In the query you can see `Sock*?` as an example. it return a number with a lot of decimals, how can i always round this number up to the nearest whole number. So that even if the result is `60.0001`, it will return `61` — ReflexTechR, Dec 12 '20 at 12:29
If you use Python 3.8+, you can use `(result11 := Label(...)).place(...)`. Since there is no `ceil()` function in sqlite3, but you can use `round()` to simulate it like: `CAST(ROUND(Sock*?+0.5) AS INT)` (but it gives wrong answer if `Sock` is like 60.0). — acw1668, Dec 12 '20 at 12:57
For rounding value up, see this [answer](https://stackoverflow.com/a/14969903/5317403). — acw1668, Dec 12 '20 at 13:03
I can see that there is quite a debate on this query rounding. I will see if the `CAST(ROUND(Sock*?+0.5)AS INT)` will work seeing as it is very rare for these number to end in a exact `0.0` number, it will be `0.0567585` or something like that in most cases, then the `+0.5` might just do the trick.(In my case anyway) — ReflexTechR, Dec 14 '20 at 13:22
You can use python function to do the round up by using `conn.create_function("CEIL", 1, lambda v: int(math.ceil(v)))`. Then you can use the function `CEIL` in your SQL statement like `CEIL(Sock*?)`. — acw1668, Dec 15 '20 at 07:21
Please find the updated code above with some queries. I do apologize for any misinterpretation. Some parts where quite unclear for me (Probably caused by a lack of theory in programming and the collaboration between SQLite and python language "bariers". — ReflexTechR, Dec 15 '20 at 12:45
I got it working. I think i might have mistyped a bracket or something. Same issue with the import. I tend to think it is more complicated. It applies much different form the normal `math.ceil(-0.5) = 1` so it tends to confuse me. **Thank you for all the help** — ReflexTechR, Dec 15 '20 at 13:33

Using IF-function to distinguish which sqlite query should be used?

2 Answers2