Tkinter inventory management by add edit remove products and managing stock

Tkinter Projects in Tkinter

Features :We can add Product , edit product details, delete product and update stock on purchase.

Tkinter Project to add update delete and manage stock of products by using MySQL or SQLite database

Adding products to table

User can add product name , size , supplier name ( code ), product details , category ( code ) , price and stock to the product table .
There is a separate page to add the products to plus_inv_products table.
After adding the product the unique product id( p_id ) is displayed confirming the addition of the product to table.
While adding the product , two OptionMenus are used, one for selecting product category and other for selecting supplier. Using dictionary the category and suppliers are selected. From the dictionary the values are presented as options to select from the OptionMenu. While storing the data the key of the selected option ( value of the dictionary ) is collected ( integer ) and stored in the product table.

Future addition

Product category and supplier tables can be added and the unique id of category and suppliers can be used. Now the key of the dictionary is used to store the names of the suppliers and product category.
At present while adding the data to table input validation is not done. This part can be added. Database error messages if any is displayed by using try except code blocks.

Database : MySQL or SQLite

The script will work in both MySQL and SQLite databases. The product table name is plus_inv_products.

Creating the table

For MySQL use the SQL dump file ( mysql_dump.txt ) to create the plus_inv_products table with some sample data.
For SQLite database run the file sqlite-create-table.py , before that change the path at 2^nd line. This will create the SQLite database with sample data.

connection string

For changing the database connection string, open my_conn.py kept inside the include directory. Inside my_conn.py file comment or uncomment the relevant line based on which database you want to use. For MySQL database fill the userid, password and database name at relevant place. Based on the line you use ( or comment ) the connection string will be created.

from sqlalchemy import create_engine
def x(): # mysql or sqlite, select one line 
    return  create_engine("mysql+mysqldb://userid:password@localhost/db_name") # mysql
    #return create_engine("sqlite:///D:\\testing\\plus2_inventory_management_v1\\plus2_inventory.db") # sqlite

No other file needs to be changed as all the files call the my_conn.py file and use the my_conn as connection object. Here is an example, these two lines are used in all the files to create the connection object.

from include import my_conn as my_con
my_conn=my_con.x()# for MySQL or SQLite

Display product with Edit and Delete ( product_list.py )

This file will connect to database ( MySQL or SQLite ) and create connection object my_conn. Two dictionaries will be created to store category and supplier name with code.
Using a Treeview all products will be displayed. The data is collected from the table and using a function display_data() the records are inserted to Treeview. Inside the function display_data() before adding the nodes with data all the child nodes are removed.

for i in trv.get_children(): 
      trv.delete(i) # remove all records of treeview

This function display_data() is called to get fresh data from table once any record is updated or deleted. That is the reason the display part of the Treeview is kept inside a function.

Selecting a record for edit or delete

By using Mouse we can select any row of data inside the Treeview and once the row is selected we can trigger the function data_collect().

trv.bind("<<TreeviewSelect>>", data_collect)

This function data_collect() will collect the p_id ( product id ) of the selected row and collect the record details from database.
First the previous inputs ( data ) if any will be removed and then data collected from database table will be populated.

query='SELECT * FROM plus2_inv_products WHERE p_id='+str(p_id)

The OptionMenu used for category and suppliers are populated with values linking to key taken from the record.

User can change the data and on Submit the button will trigger the function data_update().

Inside this function data_update(), the product id or p_id value is collected from the user selection of Treeview. Other data as entered by the user is collected. From the two OptionMenus ( category and suppliers ) the matching key for the selected value is collected and query is prepared with these values to update the record.

query="UPDATE plus2_inv_products SET \
            c_id=" +str(c_id) \
            +",p_name='" + p_name.get("1.0",'end-1c') \
            +"',size='"+ size_str.get() \
            +"',supplier=" +str(supplier) \
            +",details= '"+ details.get("1.0",'end-1c')  \
            +"',stock="+ str(stock_str.get())\
            +",price="+ str(price_str.get()) \
            + " WHERE p_id=" + p_id
        id=my_conn.execute(query)

After getting confirmation from the database about the record updating , the function data_reset() is called to empty and reset all the inputs and keep the update ( and the delete ) button in disabled state. To refresh the display inside the Treeview display_data() function is again called.

Inside the data_reset() function, data from all the inputs are removed or resetted. With the call for the function display_data() a fresh copy of data is taken from the database table to reflect the changes.

Deleting record

Confirmation box before deleting Product

There is a delete button which remains in disabled state initially along with the update button. Once any record is selected by user , the delete and update buttons both state option is changed to normal by using config() method.
Once the user clicks the delete button the function data_delete() is triggered. Inside this function first the user selection is collected ( p_id ) . The user is presented with a message box asking confirmation from the user about the delete operation. Based on the user response to confirmation message the query to delete the record is executed. After deleting the record the reset function data_reset() is called and again the display of data inside the Treeview is refreshed by calling display_data() function.

Selling and updating the stock ( product_stock.py )

This file will display the stock and user can select one product and buy the same. Here user will use one Spinbox to say quantity they want to buy.

Here the upper limit of the Spinbox is set to the stock availability so user can’t select more than the available stock.

If the stock is zero then user can’t buy this item and the Buy button will be disabled.

As the user increase or decrease the quantity by using the Spinbox, the total value will change dynamically reflecting the new value the user has to pay by using the function update_total().

Pay and Update stock

Once the user click the Pay and update stock button , the product id ( p_id ) is passed to the function update_stock(). This function collects the stock value and update the database table by subtracting the sold stock ( spinbox value ) from the main stock for the product.

query='UPDATE plus2_inv_products SET stock=stock-'+sb1_str.get()+ ' WHERE p_id='+str(p_id)

The Treeview will be refreshed to reflect the new stock position.
Here the script can be extended by using multiple product selection option and by using adding and removing products from the shopping cart.

All Files of this project

File	Description
mysql_dump.txt	SQL dump to create table in MySQL database
sqlite-create-table.py	Run this file to create SQLite database with sample data
product_add.py	Adding new Products to table to create table in MySQL database
product_list.py	Display the produts. On Select, product details can be changed or Deleted
product_stock.py	Display the products. On Select, can be purchased and stock will be updated.
include/my_conn.py	MySQL or SQLite database connection details.

OptionMenu Projects in Tkinter

Python Tkinter Button Python- Tutorials

Subscribe to our YouTube Channel here

Tkinter Inventory Management using MySQL or SQLite database