For my next project I wanted to work with a database in Python. Since databases are used in about every major coding situation I wanted to a project that would be close to a real world project. Spent time in January doing basic exercises/classes with Python with databases. Then gave myself a project in early February to come up with and get an MVP done by end of month. An MVP, Minimal Viable Product, is an agile term that basically means, it’s an app that has the most basic features so customers can start using it and give feedback on.
For this project I went with the idea of a consignment shop. The shop would need to have a way to keep track of owners, who owned what, and what was sold. Here’s the requirements I came up with:
- Most be able to add sellers and update their contact information.
- Setup inventory with description, price, seller & be updateable
- Sales screen to enter the item number and have it pull the price from database and save the transaction. Like a very simple cash register.
- Monthly report on the sales and what to pay out after commission (20%). Pulling data from database and running calculations.
To start I used mySQL database, I’m already using that database for some web development testing and had it installed. This is also a very common database used on almost all web hosting sites. I setup the database with 3 tables: sellers, inventory, and sells. The database was put together a bit quickly, but this was for practice and not for a final app. There are things I would do differently with the database, if I were to redo this into a fully functioning application.
After coming up with the project idea I needed to sketch out what the windows would look like. Decided to go with tkinter, for the GUI design, since I’ve been using it for other practice projects. Created sketches for the final app, this helps with knowing what needs to be placed where and also by looking at it I could see what information I might have missed. This saves time then jumping straight into the code and putting stuff on there then to find out later you need to redo a large section because you forgot one little thing.
This project lead itself to one way of building, since certain pieces had to be done before others. Started by creating the sellers screen, then the inventory, the sales screen and finally the report screen. Inventory needs to have the seller assigned to it, the sales need inventory, and the reports have to have sales to show. While I could have put in fake data straight into the database but this way I’m forced to work through it in the right steps, this helps with finding mistakes as working through them.
Seller/Inventory Windows
For the sellers and inventory areas these two sections are basically the same. The client can add information in, the database adds a number as the primary key for those items. By putting in the seller/inventory number, if you know their number, and hitting lookup it will fill in the entry boxes with the information. You can then change any of that info and hit the add/update button to update the database file. I merged this together to keep as few screens as possible and since it was like information it best to stay together. Only had a few conflicts with this, once I had the sellers down, I mostly copied the sellers over to the inventory and changed as needed, these two were too close to the same to recreate from scratch.
Sells/Main Screen
I made the sells screen as the main screen with the idea that most of the time this is where people would spend most of their time. In the menu at the top it points to the other windows: Sellers, Inventory, and Reports. You type in the item numbers for sale with a space between the numbers and hit ‘ENTER’ and it will show a list of the items, a brief description (in case somebody changes tags) and the price, it will also calculate the grand total at the bottom. Then select how it was paid, using the radio buttons, if they forget to select one there’s an error message. This screen also caused an issue with displaying the items, the original way I had this was adding the items straight to the root window. However, removing these after the complete order was done wasn’t really working, added this information into a label frame and was able to remove that when transaction is complete.
Reports
Reports uses the method to display as I first started with the sales screen, putting them straight to the window, didn’t think there would be much need to have it clear and run a different report. The input boxes only take a month ‘02’ two digit and a year ‘2022’ four digit then goes to the database and looks for any sales between the 01 and 31 of that month and year and loops through the results to display everything. This still works for months less than 31 including and tested in February. Added calculations for the commission and total, since these can be calculated there’s no reason to waste database space on storing them. Then at the bottom added the full totals to the columns, using just variables that kept getting added up the numbers while it’s looping.
Some of the items I wanted to add but didn’t get time:
Break the code to separate file(s) spent too much time early on trying to get it to work. This might be more of a tkinter thing than a database thing, which is the primary focus of this project.
To delete a seller or inventory item, this as a small object that might not really needed, they can always override a current one with the way it’s setup.
The night before deadline I tried to add a save to CSV file but it didn’t fully work out so commented out those lines. If I get time I may come back and work on another iteration of this program.
Github link: https://github.com/amacher/consignmentShop/blob/main/consignment.py
