Assign a variable from a SQLite table entry

Question

I would like to be able to assign a variable from a SQLite table entry I have, I need to be able to get the QTY of the part_no which will be entered by the user. So the user enters a part number, it then searches the database for the part number, once found assigns a variable with the QTY from the qty column for the searched part.

My table set up is below...

db = sqlite3.connect('db.sqlite3')
cursor = db.cursor()
# Table creation
cursor.execute('''
            CREATE TABLE IF NOT EXISTS spares(
                id INTEGER PRIMARY KEY,
                part_no TEXT,
                desc TEXT,
                qty NUM,
                rack TEXT,
                shelf TEXT)
                ''')
db.commit()

I'm unsure on how to write the SQL command to do this or if if can be done at all?

What I have done so far is the below, but this does not grab the qty which I need!

selection = askstring('Please enter your Part Number:)

cursor.execute("SELECT * FROM spares WHERE part_no LIKE '%' || ? || '%'", (selection,))

The above returns the searched part number row, but now how do I set a variable with the QTY entry for that part number?

The reason for been able to do this is so I can do math on the qty to either + or - the entry.

Thanks in advance as always :)

Mujeebur Rahman · Accepted Answer · 2020-11-17 11:28:25Z

1

You can use UPDATE query. Insert this query into your execute function:

UPDATE spares SET qty = 'some qty' WHERE part_no LIKE '%' || ? || '%';

answered Nov 17, 2020 at 11:28

Mujeebur Rahman

1964 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BIGPESH Over a year ago

Thanks for this, this would be the execute query I would use to update the qty, but I first need the qty to do the math. This is why I need the qty to a variable first then do the above execution.

Mujeebur Rahman Over a year ago

In that case, replace * with qty in your original query. This would give just the qty and not the entire table

BIGPESH Over a year ago

Thanks, @mujeebur I think this will work I will try it and comment on the results.

BIGPESH · Accepted Answer · 2020-11-17 14:59:11Z

In the end I did the below...

def remove_data():
    spare_to_remove = askstring('Remove Spare', 'Please enter your part number you wish to remove')
    amount_taken = askstring('Amount Taken', 'Please enter the amount you wish to take')
    amount_taken = int(amount_taken)
    cursor.execute('''SELECT qty FROM spares WHERE part_no LIKE '%' || ? || '%' ''', (spare_to_remove,))
    q_t_y = cursor.fetchone()
    tup_to_int = int(''.join(map(str, q_t_y)))
    sum = (tup_to_int - amount_taken)
    cursor.execute('''UPDATE spares SET (qty) = (?) WHERE part_no = ?''', (sum, spare_to_remove,))
    db.commit()
    messagebox.showinfo('Removed Part', 'Adjusted QTY to ' + str(sum))

hope this helps anyone else with the same issue!

Collectives™ on Stack Overflow

Assign a variable from a SQLite table entry

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related