Building an SQL-Powered App with Python

SQL in Python - Build an Expense Tracker Desktop Application

Estimated read time: 1:20

Learn to use AI like a Pro

Get the latest AI workflows to boost your productivity and business performance, delivered weekly by expert consultants. Enjoy step-by-step guides, weekly Q&A sessions, and full access to our AI workflow archive.

Summary

This tutorial by Code with Josh walks you through the process of creating an interactive desktop expense tracker application using Python and SQL. Josh demonstrates the integration of SQL with Python through step-by-step coding instructions. The application features include adding, deleting, and displaying expenses using SQL databases. Moreover, the styling of the application is achieved using CSS, allowing users to create visually appealing and functional interfaces. Josh offers a meticulous breakdown of each step, from setting up the initial app framework to implementing database interactions and polishing with CSS for a professional finish.

Highlights

Josh integrates SQL with Python to build an expense tracker application! 🚀
Utilize PyQt6 to create a visually appealing desktop app. 👨‍💻
Interactivity is key, as users can add and delete expenses easily. ✨
Josh shares tips on setting up SQL databases seamlessly. 🔧
Incorporate CSS for a polished, professional look to the app. 🌟

Key Takeaways

Learn to build an interactive desktop application with Python using SQL. 🖥️
Understand how to integrate SQL databases in Python applications. 🐍
Discover how to use PyQt for building user interfaces. 🎨
Explore CSS styling to customize your app’s appearance. 🎨
Grasp how to manage and manipulate data using SQL queries. 📊

Overview

Join Josh as he delves into the world of Python and SQL by building an interactive expense tracker from scratch. This tutorial is perfect for those looking to combine SQL's powerful data management capabilities with Python's versatility in application development.

Through this fun and engaging session, you'll learn how to set up your application using PyQt6 to create visually stunning UI components, while seamlessly integrating SQL to handle complex database interactions. Josh takes his time to explain each part of the process, ensuring even beginners can follow along.

Finally, Josh adds the finishing touches by styling the application with CSS. This episode of Code with Josh is a fantastic resource for anyone looking to advance their skills in Python and database management while creating a real-world application.

Chapters

00:00 - 06:00: Introduction to SQL in Python The chapter introduces the creation of an interactive desktop application using SQL in Python. Josh, the host, guides the audience through the process, focusing on building an interactive expense tracking application via interactive code. The episode is part of the 'Code with Josh' series.
06:00 - 11:00: Creating the Project Structure and Importing Libraries The chapter titled 'Creating the Project Structure and Importing Libraries' introduces the merging of SQL and Python to create a powerful interactive desktop application. The instructor emphasizes the importance of liking and subscribing to their channel. The application will be developed in a fun and engaging manner using pyqt to bring the application to life, while SQL will be used as the backend database.
11:00 - 22:00: Designing the GUI with PyQt6 In this chapter titled 'Designing the GUI with PyQt6,' the narrator discusses the integration of SQL queries within a Python application, focusing on inserting, deleting, and fetching data. Additionally, there is a mention of a newsletter called 'The Nerd Nook,' where one can find a series on writing SQL in Python, along with other trending topics. The narrator encourages readers to subscribe to the newsletter for weekly insights into various Python-related topics.
22:00 - 27:00: Adding Functionality to the App This chapter titled 'Adding Functionality to the App' focuses on integrating SQL with Python. The instructor structures the content in modules to maintain organization and ensures a collaborative build throughout the session. Each step is elaborated on with the rationale behind actions to enhance understanding.
27:00 - 30:00: Implementing SQL Database Operations This chapter introduces the process of creating interactive applications with Python's PQt framework, alongside an overview of SQL database operations in Python. It encourages viewers to check out an ongoing SQL in Python series. The chapter starts with creating three files: one for the application (app.py), a database file for SQL queries, and the third one which gets cut off, likely another essential component of the setup.
30:00 - 43:00: Connecting Database to the GUI The chapter covers setting up the main file responsible for running the application.
43:00 - 56:00: Testing and Debugging The chapter discusses the initial setup of a testing and debugging environment using PyQt6. It begins by instructing to import PyQt6, the latest version required for the task. For those who haven't installed it yet, the chapter suggests firing up the terminal and utilizing the pip3 install pt6 command to have the necessary library on the system. It also mentions the option of creating a virtual environment for the development process, although the narrator proceeds with an existing environment.
56:00 - 70:00: Styling the GUI using CSS In this chapter, we focus on styling the GUI of a project using CSS and PyQt6. The discussion begins by explaining the intention to utilize the QtWidgets module from PyQt6, which is essential for working with the various widgets involved in the project.
70:00 - 86:00: Final Touches and Conclusion In the chapter titled 'Final Touches and Conclusion', the integration of various UI components such as Q Line Edit for user input, Q Combo Box for dropdown selections, Q Date Edit for date selection, and others is discussed. The chapter describes the use of a Q Table Widget resembling a spreadsheet and a QVBox for vertical layout arrangement. This session focuses on tying together the user interface elements to give the application its final form before conclusion.

SQL in Python - Build an Expense Tracker Desktop Application Transcription

00:00 - 00:30 hey guys today we're going to make an interactive desktop application and I'm going to show you guys how you can incorporate SQL into python we're going to do all this through interactive code as I break it all down and we create an interactive expense tracking application using SQL in pqt welcome back to the channel welcome back guys for another episode of Code with Josh for obvious reasons I'm Josh and I'm stoked to have you guys here before I dive into today's
00:30 - 01:00 episode do me a favor hit that like button and subscribe it means the world and it really helps my channel grow now today is all about SQL in Python and I want to show you guys how we can merge these two languages together to create something really powerful and we're going to do this in a fun manner by creating an interactive desktop application I'm going to use pqt to bring the app to life and as our database we're going to incorporate
01:00 - 01:30 and use SQL queries to insert data and delete data and fetch all of our data guys real quick I'm writing a sequel in Python series on my newsletter the nerd Nook that's the first link in the description head on down there come join my weekly python newsletter where I break everything down surrounding topics that are not only trending but topics that you guys request all right so check that down check that out it's the first link in the description and all the other links I have down there are
01:30 - 02:00 dedicated to you guys and helping you grow in the space in Python so check those links out all right enough said let's jump into why you're all here SQL in Python all right I hope you guys are ready for another project now I'm going to do this in modules to keep everything organized and we're going to build this out together I am going to go through each step with you guys explain what I'm doing and why I'm doing it I'm really hoping that you guys are able to see at
02:00 - 02:30 the end of this not only how you can create these interactive applications with pqt but also as like an introduction to SQL in Python um remember guys my current ongoing SQL in Python series is going to be linked in the description so head on down check it out now I'm going to start off by creating three files here one is for my application so app.py I'm going to create a database file for of our SQL queries and then I'm
02:30 - 03:00 going to create a main file which is going to be responsible for running the application so um running the app right I'm going to put that here uh all of our SQL stuff is going to go here so all SQL stuff here and then our app is where we really build out the application okay so uh app design here um and this is where
03:00 - 03:30 I'll actually get started okay and what we need to do first is we need to import Pi qt6 that's the latest version of pqt so if you don't already have this I'll just leave this here fire up your terminal pip 3 install pt6 Okay run that and you'll get that on your system now you can create a virtual environment for this I already have this here through teaching so I'm going to use it like this but you can create a virtual environment to house your
03:30 - 04:00 project cool let's just go through so from PI qt6 uh we specifically want to tap into their module QT widgets which is for working with all the widgets that we want to import here and I'm going to move me down because we're going to have a bunch of imports for now um what we're going to start off with is Q widget this represents the main window the the window the user sees we need Q Lael so Q Lael is all the text we want to see on the screen I'm going to have q push
04:00 - 04:30 button for all the buttons we have q line edit this is the input box which a user can type something in right uh a q combo box is our dropdown selection we are going to have q date edit allowing us to choose a date and we're going to have a few more here there's a lot so I'm just going through them all with you guys Q table widget is like a spreadsheet our table in the app we have QV box for a vertical layout a column
04:30 - 05:00 the opposite of a vertical column is a horizontal layout right so in pi QT everything is held in containers it's either going to be a row horizontal or a column vertical um great I'm going to have a popup box so Q message box for a popup if we want that uh we are going to need here Q table list widget item to add items to that and then finally for
05:00 - 05:30 some styling I think they have yeah Q header view this will allow us to style um the table and I'll actually explain this once we get into that stage um now that is only the QT widgets module another one that's common that I like to use is QT core QT core really just houses all the miscellaneous items from the QT uh framework excuse me and I'm going to use Q date to work with the date and
05:30 - 06:00 then QT is for alignment of the items within the container okay so those are Imports for now okay uh I'll come back to this and expand on this but really when I'm starting a desktop app I just want to get it up and going okay so I want to get the size and I want to get the title and then just load everything to make sure it's in the right place so I'm going to house our app in a class of our own so expense app I'm going to call it this now this is our app but
06:00 - 06:30 everything is built on the window and the window is Q widget right so that's the first one we have here you need to have this okay in order to make a window application this represents the main screen I believe they have one too like Q main window okay they both work very similarly I'm just used to using Q widget we will create our Constructor here so in it right so every time we use this class and it is automatically
06:30 - 07:00 called and we just need to initialize activate our inheritance I want to activate this Q widget so super initialize I'm initializing the super class brilliant now the only one that I'm going to run right now is the settings let's put an S let's just be punctual and anytime I'm referring to my app because we're inheriting Q it I can
07:00 - 07:30 refer to my app as self super easy super nice and I'm going to use here set geometry set geometry takes a XY where do you want the window to appear on your screen so I'm just going to say uh 300 300 and then it needs a width and a height let's say the width is going to be 550 the height is 500 I'm also going to give a window
07:30 - 08:00 title right here I can move me back up right cuz we're done there and this is going to be the name of your app so really anything you want I'll call this expense Tracker app okay this method is done it's just housing the initial settings I'm going to make sure to call it here okay so our settings that's called now I want to run this I want to see if it works uh I'm going to close our database for now not I'm going to head over to our main right
08:00 - 08:30 and this is where I'm going to set everything up initially or I will soon create a database to call here as well but we don't have that yet I'm going to import CIS for our system uh and then we need to import two things from PI qt6 here so QT widgets again uh the other one we need to build an app this is important is Q application we need that to build an application and then I am also going to use a popup box here for
08:30 - 09:00 our database shortly now from our app let's import the class that we built expense app I'm going to mark this for us so we have q application this creates the actual app itself not the window just the app and then we're importing the class from our app file okay so let's keep neat here let's make a main function and I'm going to create an app object if if you've watched any of my other videos okay head
09:00 - 09:30 on over check those out I like Pi QT I have a few out this is going to look familiar if you've worked with flask or D Jango anytime you're making an application an app object is super common and the values just are Q application inside here to make sure it runs smoothly oops we can insert this now if you don't want this you could just use square brackets okay it's going to execute the same way but I going to use the CIS module for something else
09:30 - 10:00 here in a moment so I'm going to actually just use that it'll do the same task um then we need to create a window this is just representing our actual app itself the one we've imported here so anytime I refer to window that refers to our app in order to show the app we can link the show method just like that all right and then uh finally at the end of this we can say system exit and we can
10:00 - 10:30 say app. execute I want to execute I want to run the application okay uh let's drop in our python boiler plate here and we're just about ready to actually run the app let's just call our main function okay let's see if anything needs adjusting run your code so mine appeared a little off to the left it's cut off I'm just going to go and I'm going to tweak that okay so where is that my uh X let's take my X to
10:30 - 11:00 750 and then let me run that again for you guys because every time I I have this load I want it to just appear here for us cool this is perfect so this is your canvas okay you should see just a blank screen if you're on a Windows it's probably going to be white I'm on a Mac the screen's black okay and the title I gave it is up here you can adjust this okay and if that's not the size you want right just refer back I'll actually take you over there now just refer back here
11:00 - 11:30 right this is your width and height of the application all right so that's great we have it all ready to go all right the next thing I like to do is my design all right and typically this can be called anything initialize UI init UI is what I like to call it the first thing we need to do is I need to create an object for everything that I want to see in our application okay so all the buttons the table uh everything else I
11:30 - 12:00 want it to pretty much be here first so I'm just going to label for us create all objects we need these objects before we put them on the app screen so we have a date so I'm going to create one called Date box this is just going to be equal to Q date edit now um in order to get the current date actually I'm going to say I'm going to take the date box we just made and I'm going to say uh which one is it set no is it set date
12:00 - 12:30 it is set date and inside set date we imported something where is it Q date right here I'm just going to drop in Q date and I'm going to say current date all right so whatever date is every time the app loads it's just going to automatically use that date for you now we need a drop- down box this will allow the user to choose um what expenses for
12:30 - 13:00 transportation rent food entertainment you name it that's just a drop- down box uh nothing needs to go inside there for now we will populate it later I'm going to create one called amounts this is going to be our q line edit so I can enter the amount that I spent and then we could give a description for what we spent it on so I'm going to drop that there okay now I have two buttons I have an add button and I have a delete button so let's create button add this is going to be a q push button uh as a keyword
13:00 - 13:30 argument you can just put text here at expense and we will also create a button for deleting from our database and from our application and I will just call this one delete expense pretty cool right so I have a lot of the objects not all of them I'm missing one really important one I'm missing the table so our database we can't can't see the database the SQL is
13:30 - 14:00 done behind the scenes but every time we add something or delete something it's not only going to update our database the changes need to be reflected and shown inside this table of what we can see in our application so here we can use Q table widget um inside here we'll say 0er and five then let's take our table and let's say set horizontal header labels I I think it's labels not
14:00 - 14:30 items labels this is going to take a list so what do you want the column names to be across all right and ideally this is going to match our database as well so I'm going to have ID each task each item each expense will have its own ID we're going to have the date we can have the category uh you can be creative here right this is just an expense app but it's going to work for anything we could have our
14:30 - 15:00 amount and then we can have here our description okay so those are the header titles now um I'll actually spawn this and uh I'm going to hang on I'm going to put something here I'm going to say um um edit table width uh I'm not going to style this yet I want you to see what happens first and then we'll style it okay so this is init UI now we need to also add widgets to a layout and this layout is
15:00 - 15:30 either a column or a row okay so I'm going to do this through a different method trying to keep organized here and I'm just going to call this setup layout okay so first thing I like to do is I like to create all my layouts you can only have one master container okay so no matter if you have three rows and five columns whatever everything needs
15:30 - 16:00 to be held in one final layout my final layout is going to be a vertical layout you could use either or here actually it's just how you structure your containers um I'm going to have three rows Row one row two Row three and these are going to hold all the information we need so all of the above widgets except our table our table doesn't really need it I can just add it to the um Master
16:00 - 16:30 layout the column so before we start the master layout I need to start with Row one what do you want to see in row one so how we do this is we take the row and we say add widget and then you just put the object from above one of these guys of what you want to see in that row so um in the row I want to have a date in my drop-down option so the first thing I'm going to do is I'm going to to create text so Q label and inside here I'm
16:30 - 17:00 going to say date this is a class that we imported above here I could make a widget for this I could make an object but it's just text I'm not changing this text you don't have to create one of these here for Q Lael but you could I'm just putting in here it's going to be shorter in the end so this goes left to right I have the date we can then take and actually drop in the date box right
17:00 - 17:30 I have the text uh we're going to do the same thing here so add widget I'm going to do a q label and I'm going to say category and then we can add in our drop- down box drop down just like that okay so Row one is done right I'm just going to move in here let's do the other two rows so row two uh row two let's take row two add widget what do you want to see here in row two we want to see
17:30 - 18:00 amount so you can enter the amount and I'm just going to copy and paste now actually uh one two three you can see I've actually spelled widget wrong so let me fix that widget uh okay inside here next to amount we can say amount this is going to be not amount description and then what goes here well that's going to be our final description cool uh finally let's just paste two more
18:00 - 18:30 okay and inside here these are going to be not these we need to use our buttons so self do button I'll do add for the top one and then we'll do delete for the bottom one okay cool so everything is added in your rows that's great if you guys are getting value in today's video do me a favor hit that like button and subscribe now I need to add together two layouts and previously I was using add
18:30 - 19:00 widget but if I'm putting together two layouts that doesn't work anymore um so always check what you're trying to add together let's take our Master layout and let's say add layout I'm just going to say Row one we're going to copy and paste this a few times Row one row two and then there's one widget I'm missing which is the table but the table it doesn't need a parent the parent is just going to be the master layout so I'm going to say self table very cool okay
19:00 - 19:30 um really the last thing we need here then is we just need to give the layout to the uh the Q widget so I'm going to say self I want to set the final layout to become my master layout done okay the only thing I'm going to do here to test it okay now this is needed I need to give the master to the Q widget layout is I'm going to go up here inside I'll close this inside
19:30 - 20:00 in it and uh I'm just going to call that method setup layout up inside our super or not our super our in it excuse me after settings I now need to call in it UI all right let's test it let's see how it looks it's going to look really basic but this is a great starting point and we'll style this at the end I'll show you how you can use CSS to create something really cool okay
20:00 - 20:30 look at that here we go so this is roughly how the app is going to look uh there's an issue here these buttons actually should be on the third row so I need to change that uh the date can be changed this has nothing in it yet I need to populate that and then this table right I have all my columns if I make this bigger though look at this I don't like that I need to I need to fix that okay so I need to fix two things uh these buttons need to be on a third
20:30 - 21:00 row and then these headers they need to like stretch okay so let's let's address those now the button issue I forgot to say row two Row three probably so let's open this up yeah I'm going to change row two to row three okay and the other issue I left that done unintentionally or intentionally uh and I actually put here Ed edit table width okay cuz I wanted
21:00 - 21:30 you to see that in action so if I take my table what we need to do to adjust this is I'm going to call a function horizontal header and then I'm going to say set does it give me an option reset section resize mode that's the one I want and then we imported something called Q header view I'm going to say resize mode and I want to stretch okay so that's going to take care of that whole weird thing we had
21:30 - 22:00 going on buttons on the row if I make this bigger you can see that they now match with that that's exactly what we want okay uh next order of business let's get this ready to go um this box needs to have stuff in it uh and I could just do it inside of in it but I'm going to I'm going to come down here let's just make a method for that let's call this pop you drop
22:00 - 22:30 down okay and I yeah I'll make a method for this so how this works is the Q combo box takes a list of categories what options do you want to have in the Box food uh rent bills uh entertainment uh what else are you guys into shopping and maybe you could have like
22:30 - 23:00 other cool we can then take our drop- down box drop down and I'm just going to say add items so you just solve two there's item that's one I'm giving it a list so items and we're going to say here categories uh just up here then I'm just going to call that self inside in it UI populate drop down okay give a quick check to make sure there's no errors
23:00 - 23:30 it's doing what it's supposed to cool there we go food there we go all of our options this is great all right so we are doing really good okay everything is ready to go to keep building now before I go back and I start programming the functionalities of the buttons we're actually going to go into our database cuz I need to create a database and I need to write the queries for this right our buttons are going to
23:30 - 24:00 call these functions so this is really where all of our SQL stuff goes now because we're using pi qt6 um I can actually just tap into QT SQL this just makes life so much easier I'm going to import Q SQL database and then I'm going to import Q SQL query do we see that all right here we go okay uh very nice and and I'm going to create a method called init DB to initialize our
24:00 - 24:30 database every time you open this project you run this app it's going to call this uh if you already have a database created it's not going to create a new one if you're missing one it'll automatically create one for you so let's start off by making a database object so database is equal to Q SQL database add database and inside here you need to specify the type of SQL
24:30 - 25:00 because we're using QT SQL I'm going to say Q SQL light where am I there we go Q SQL light let's then take the database and we can set the database name to whatever we give it later so uh let me do okay let's do this name uh I don't really need self I don't have a class don't know why I did that uh let's just say DB name I'll name this
25:00 - 25:30 later okay so uh this will be created over here your database and we'll name it when we actually run our app in the main file right um if not let's just do a quick check so if not database open if the database is not open let's just return false which is going to throw us an error in the main file right because I can't run this app without out a
25:30 - 26:00 database uh very nice guys okay so what else do we need to initialize well I need to create the table this makes a database but it's it's completely empty there's nothing going on there I'm going to make a query and a query um guys right now my sequel in Python series is going on uh I'm going to link my newsletter below come join in I'm taking you guys through a to z in the SQL series and I EXP explain all this stuff in a lot more detail but I hope I'm
26:00 - 26:30 doing Justice now if you guys are getting value from this hit that like button drop a comment let me know your thoughts a query is like a request to the database so this query object I'm going to use the class we imported qsqlquery and I want to take the query and I want to execute something so um a squl query here is written in Python in a string I have six quotations three before three after and then you can just
26:30 - 27:00 write pretty much standard SQL here I want to when I call this function in it DB I want to create a table if it doesn't exist exists so create table if it doesn't exist what do I want to call this table now this is not the name of your database this is the table I'll call this expenses
27:00 - 27:30 if I can spell okay expenses okay and then I'm going to give a set of parentheses so my table is called expenses now I need to go through and I need to create each column in my table now these columns one's going to be ID and um ID needs to be unique cu no two expenses can have the same ID this is for adding and deleting things we want keep something unique so it's going to have an ID and I'm just going to set
27:30 - 28:00 this to an integer uh I will make this the primary key and I don't want to have to do anything so I'm just going to say Auto increment this is going to automatically increment itself the next one is date uh date is going to be text we are going to have category category is also text we are going to have amount and amount is a number float it's a real
28:00 - 28:30 number okay so real represents that data and then lastly we have is description this is also just text okay pretty cool all right uh so this is going to set up the table with these columns right and uh these columns could be anything for you right but I'm just going to now return true all right looking good let's go over and test this function let's get a database set up there won't be anything in it yet that's
28:30 - 29:00 fine um to do this let's return to main right cuz this is really what's going to be called is Main and inside here after we create an app uh well first let's import so uh from database I would like to import init DB okay so under my app here let's say if not init DB where is
29:00 - 29:30 it now what is in a DB take the name of your database you can call this anything um I will call this expense but it must end with DB for database okay so that this name it does not need to match this table right in fact mine doesn't mine says expenses and then in main it's expense so it doesn't need to match it just needs to end in DB so if this can't be called I want to alert the user that
29:30 - 30:00 there's a problem so a popup box and I'm going to give them a critical error so a popup critical box uh this is just going to appear in the screen so none there's no place where it's going to appear it's going to say error and then it's going to say something like could not load your database okay and then we want the application to exit after 1 second pretty cool I'm going to run this this okay our apps going to appear again
30:00 - 30:30 that's fine uh I want you to see what happens I don't have a database yet now you can see one's created our app loads it's created the database you can't see anything here okay there's nothing here right because we can't see what's happening in that database but it's going to be projected into our table of our application all right really nicely done guys any questions drop a comment let me know if you do I'd be happy to answer I'm going to return back to database here init DB is is done okay
30:30 - 31:00 but we need one to fetch the data from the database I need one to add data and then delete data so I need three SQL methods that we I keep saying methods three SQL functions that we actually need to make in order to interact with this database uh I'm going to close in at DB I'm done with it less distracting whoops let me give some lines first there we go
31:00 - 31:30 all right my first function here is going to be to fetch all the expenses from the database uh this isn't going to take anything but I am going to need to make a new request to the database so another query let's use our qql query and inside there is all your SQL now we're going to say I want to select everything from my table expenses this is not the database name it is the
31:30 - 32:00 name that you made inside here right because this is the name of your table so I want to select everything from expenses and I want to order by the date and you can choose ascending or descending I will say descending order that is my SQL query okay let's create a list of expenses that I can add them to and I'll load it into the Q table widget
32:00 - 32:30 and inside here I'm going to say while there is another row available so while query next right it's going to go through our database boom boom boom boom boom and as long as there's another row of data this Loop is going to continue to repeat let's take our list and let's append okay inside append we are going to put another list cuz it's going to be our uh list of expenses is going to have a bunch of other lists and each of these
32:30 - 33:00 lists represent a row in the database okay so I'm appending another list here for that reason so what is going to be in this list well I'm going to say query uh let's get our value and I'm just going to say I okay and then notice that I don't have I what is i 4 I in range five the reason I'm saying five is because 1 2 3 4 5 so if
33:00 - 33:30 you have more items in your database make sure it's looping enough I'm repeating it five times and I'm populating the data when the loop is done running I'm going to return my list of expenses done every time we load the app every time I make a change to the database this function is going to be called to update our table visually okay um cool let's call one add
33:30 - 34:00 expenses and this needs to take um all the information really so um date category amount description so I'm going to say date category amount and description okay not id id is automatically generated I'm not giving it the ID um cool let's make a query again okay I'm going to say SQL query now this time I want to prepare
34:00 - 34:30 something so um I need to add data and I can't just do that here in my initial query so I need to prepare to add data so I'm going to take my query and we are going to say prepare all right so I'm going to prepare to send data off into our database this is all SQL now so I'm going to do again I have a few lines here six quotation marks now how do we add data using
34:30 - 35:00 SQL are you guys familiar with this right that's in my series if not if you have a SQL background you probably know this in order to add data we are going to say insert into okay what is the name of your table expenses is ours and then what am I going to be adding so I'm just going to actually copy this it's all the same okay so I'm going to be adding that and
35:00 - 35:30 then what are the values to those values okay I have four options date category amount description I need four values currently I don't know so we can substitute that with a question mark okay those will be the values so I'm preparing this query now with date I need to give it the date that I'm sending to it I need to link I need to bind together um the date the category for
35:30 - 36:00 our database so I'm going to say query I'm going to say add bind value and I'm just going to copy this because we're going to use it four times I'm going to give the date right that's coming from here and one two 3 you guessed it we are going to bind our category we are going to bind our amounts and then finally our description Okay so we're binding those to the SQL query now we need to run it
36:00 - 36:30 once we're done so when this is done let's return and let's say query execute I want to run my query I want to execute my query done that's it that's us adding to our database guys if there's any comments or questions let me know drop a comment I'm happy to answer okay I hope you're following along well the last thing we really need is delete expense expenses it's really just one at
36:30 - 37:00 a time now how are we deleting okay remember everything has its own unique ID so I'm going to say expense ID and uh this is what's going to allow us to Target the item in the database and remove that based on the ID so I'm going to make a query qql query uh let's prepare a query so query prepare inside here let's say delete
37:00 - 37:30 from name of your table expenses where the ID is equal to a question mark now what do we need if we use a question mark just like we did here these question marks need a binding value so down here I have one question mark let's take our query let's say add bind value and I'm going to give it my expense ID that's it once we're done let's return let's execute the query
37:30 - 38:00 query execute just like that done right all right so our database the SQL stuff of it is actually done we need to implement the functionality now within our application okay so I'm going to I'm going to close database we're done with it I'm going to return to our app and let's go to the top and let's uh import what we need well actually actually let's go to main to do that so up top
38:00 - 38:30 here all the functions we need from database I would like to import I don't need in it DB here we need fetch expenses add expenses and delete expenses whoa no did I call it delete expenses delete yeah delete expenses right there okay so we need these and I'm going to call these when a button is clicked really okay so okay we've added those let's go down I'm going to close my settings that's not needed design uh
38:30 - 39:00 we're going to come back to that so I'm going to I'm going to leave that and I want to first start off by creating a method to load our data so load table data okay this is this is going to take the information from our database and project it into our application so let's call our method so expenses equals I want to fetch all the expenses remember that that returns a list and
39:00 - 39:30 I'm going to make sure that my table starts at um at zero so I'm going to say set row count giving it a zero okay now I need to go through my list and I need to put it into my table per se so I'm going to say four row let's say index and every expense in enumerate let's go through that and let's say expenses I'm going
39:30 - 40:00 through my list okay I'm going to take my table and I can use their method insert where is it insert row what am I inserting into the row the row index okay then for every column index and our data let's once again do enumerate let's go through and say uh expense right so the first Loop I'm going through my list that my data fetch
40:00 - 40:30 that my function fetched now that I have each row right um I'm using each list here which is each individual Su list and now I'm going through that list okay so I'm going to take my table and we are going to say set item one item at a time I'm going to say row index column index oh I want to use just keep
40:30 - 41:00 the names I don't like that okay and then I need to say Q table widget item we imported this class from pqt we need it for that I want to convert the data to a string right because visually everything can be a string in the back end of the database okay that can be a number an integer you name it right um this is great Okay so every time we load this app now I should
41:00 - 41:30 be calling this so self. load table data just like that okay uh let's Close n UI for now it's fine right so this is how we actually load the data that our fetch expenses function uh returns for us okay awesome so we made a function in database that um allows us to add to the database but we need to give that function let me just take you there to
41:30 - 42:00 show you uh we need to give this function date category amount description this runs the SQL query but I need a function that collects this information and then calls this function here okay so let's create a method I'm going to call this add expense that's one expense uh let me just uh make sure yeah okay we're good add expense uh self now um each of the
42:00 - 42:30 objects we need so I need a date nope I need a date I need a category I need an amount and I need a description so the date how do we get the date that we select from the date box well let's take the date box and let's say uh date that's a function I'm going to convert that to a string and inside here let's just say
42:30 - 43:00 it's going to go in the order of year month day okay the other ones are a bit easier uh for amount let's take our drop- down box did I call it dropped I did I'm going to take drop down and I'm going to say I want to get the current text that I selected from the drop- down box whatever you choose entertainment that's the current text that's presented in the box so category equals entertainment category equals food
43:00 - 43:30 whatever you select for amount we can just say I want to get whatever text was entered in the q line edit because we used a q line edit for this one we used oh I turned it off I didn't want to turn it off I just wanted to mark that for you guys this one we used a q combo box and the last one description is also a q line edit so I can say description text boom so I've collected all the
43:30 - 44:00 information we need um now I'm going to do some checks real quick because I can't leave let's say I can't leave am Mount empty I can't leave description empty right um if not amount I need to make sure that or not uh description okay I don't want those to be empty I'm going to say Q message box let's give the user a
44:00 - 44:30 warning okay uh this is going to appear in our app screen so remember to refer to the app screen I'm going to say self that's our Q widget and I'm just going to call this input error and then what's the message we can say uh amount and description cannot be empty okay so that's my little warning for them and then let's just return okay I don't want the program to shut down I just want to return that um let's
44:30 - 45:00 do another section okay let's now call our function add expenses and I'm going to give it date category all the information we collected is going to be given to description okay um so if that returns true then I'm going to load the table again right so every time I add an expense we should be calling this function CU this is called anytime
45:00 - 45:30 we make a change to the database to update our Q table widget so uh if the query was executed correctly I want to refresh the table and load the new data in my app screen and I'm going to Mark here for now clear inputs because I want to clear all the inputs every time we make an add to the database right because that would stink if you had to do it um cool L I'm going to say here there was an error so Q message box we're going to go
45:30 - 46:00 critical we're going to say self we're going to say err and I'm just going to say like failed to add expense cool that's pretty much done okay um since we're here let's just make clear inputs now so you guys can just see it in action uh let's just make a method called clear inputs so we have
46:00 - 46:30 whoops we have what our four inputs we have date category I don't actually have to even put those let's just start with date self. dat boox and you can see I forgot self so self. uh date box I want to set the date again back to the current date so Q dat. current current date okay let's take our drop- down box so drop-
46:30 - 47:00 down let's say Set current index let's put it back to zero that's the first element in your list amount we can just call clear because it's a text input and description can also be called clear because it's another text input okay so anytime I call this our app is going to be refreshed not the table just the input boxes okay so down here if we add an expense and we update the table let's then reset the input
47:00 - 47:30 boxes so clear inputs all right looking great uh what does that leave me with that really just leaves us with the delete functionality so I'm going to close this oops let's give myself some space first now I can close it okay let's say def delete expense okay so how are we deleting um
47:30 - 48:00 there's going to be no input for it I want to be able to click on a row in the database get the ID from the row I deleted or selected and I want to delete that row okay so wherever I click that's what I want to use and I want to delete that so I'm going to create let's say like selected row is equal to self table uh what do I use for this one current I think they have current row yeah so current row that's whatever I
48:00 - 48:30 clicked on the whole row because I don't want to do column just the row um if I didn't choose one so I need to choose a row in order to call this function so if it's negative one that means you did not choose a row from the table to delete we need to give the user you guessed it a friendly warning uh I'm going to say uhoh uhoh let's say you need to choose a row to
48:30 - 49:00 wow my spelling today row to delete okay so if they didn't choose one I need to remind them that they need to choose one and let's just return that okay so how are we deleting our expense ID expense ID this needs to be an integer now so in the table it's a string doesn't matter okay but if we go
49:00 - 49:30 back to our database we specified when we created it that the ID is an integer this is what allows it to autoincrement it needs to be an integer so I need to take it from the table and I need to convert it back to an integer okay so I'm going to say what do I want to convert to an integer let's say self. table. item from the selected row I need the First
49:30 - 50:00 Column which is at zero because the First Column is our ID and we need to get the text value of that so text cool um let's I guess we could give the user like a message confirm Let's uh do a q message box so I can show you guys how this works I can ask the user a yes or no question which is kind of cool I can say question it's going to appear in the screen and I'll be like uh
50:00 - 50:30 confirm are you sure you want to delete question mark okay um now because I'm asking a question I need to give it two more I need to say Q message box uh standard button is that yeah standard button it's either a yes or a no so I'm just going to copy this again paste in here change the last yes to no just like that okay cool so if
50:30 - 51:00 confirm is equal to yes so I'm going to copy this so if we said yes and delete expenses runs right because that can't give us an error or that wouldn't work so if that's a yes and delete expenses runs I want to reload the table right because that's going to take care of it for us we're calling delete expense that handles the database action this handles
51:00 - 51:30 what we visually see in our app wow guys that's that's pretty much it the functionality done if you run it it's not going to work because we need to create our events really quickly so let's connect this and this to a button uh pqt does events I love the event handling here uh super straightforward so inside init UI let's go just down here and I'm going to take my buttons so
51:30 - 52:00 I'm going to take the ad button when the ad button is clicked I want to connect with my ad expense function I made that's it okay when the delete button is clicked we want to connect with our delete expense function that we made voila it's time to really test this right so let's I'm going to close this
52:00 - 52:30 down get everything neat there we go okay okay save going to my main file I'm going to run the main file let's try to enter some data I already have the database you could delete it but it's empty it doesn't matter in fact I might get an error if I don't delete it let's try it um food uh yeah food sure how much you spend on food $10 uh Burger Burger King is so bad let's go
52:30 - 53:00 five guys I miss a good Five Guys burger at but none okay so I think the issue here let me close this I did some changes so I'm going to try to delete my database first and then rerun that if that didn't handle the air okay there's a bigger problem I need to address but uh generally I've had issues with SQL Alchemy and things to where you know that's happened and uh yeah I want to see if that was my
53:00 - 53:30 air no okay so I'm getting none it's populating if I click so okay so I'm getting a few issues why did I get none well let's start with this eror when I Tred deleting um that is coming from my app file let's go down to delete so we went none there um must be something with the database
53:30 - 54:00 uh I tried to add it but did it tell me oh here we go here we go here we go um I'm appending this this can't be right um I think this needs to be wrapped in another list okay let me try and delete that again so yeah yeah that needs to be one list item I think right cuz that's
54:00 - 54:30 throwing me it's telling me none and that's that's a problem right because it's like I have data let's check if that worked okay so let's do some numbers some letters yes I don't like that it's still a list but we have everything actually working yeah I mean it's working correctly right let's tweak those let me try and delete uh okay so I'm still thrown in air let's fix that for the deletion part of it okay so going back to my app where did I
54:30 - 55:00 have delete here we go okay so we have that uh where's my issue invalid literal for INT with big okay so I said zero but it's a list it's a string it's throwing an issue okay let me let's work through this let me close this down okay let's go to the top of our code here all right uh let's let's head back to database uh where were we
55:00 - 55:30 here so I redid this this still occurring as a list and I can't remove it so this is most likely why there's my extra set of brackets um let's let's try to run that now so I remove those extra set of brackets because I have row equals list that should do it I believe I was just rewrapping it in Brackets which there's no point to that so 17 random stuff okay
55:30 - 56:00 okay so that bracket was the issue I then had an issue with the leeting and I think though the reason I was having a delete issue is because that was a list if I'm not mistaken so yeah you can't convert that to an integer I'm trying to make it an integer um in my app it might actually work now ID delete yes yes yes yes yes let me delete the
56:00 - 56:30 database start from scratch again okay run our app we have a working application which is incredible okay let's say $25 what is this for uh phone bill is your pH is your phone bill $25 probably not there we go look at that okay so the app is functioning looks good well doesn't look good does it we need to fix that let's let's delete that okay um let's let me show you guys
56:30 - 57:00 how you can add CSS styling to this and we can actually Target each widget individually this is really cool so um if you've ever worked with CSS you're off to a good start okay we don't actually have to do a whole lot here let me just close down everything it's really not not needed let's go after set layout I just need space so I open set layout okay so I'm going to close that
57:00 - 57:30 okay let's call one let's call it apply Styles so this is great this is easy um pqt has a builtin method called set style sheet okay and this takes our extensive string and inside here we can Target each individual class that we imported so I can Target Q label I can Target Q push button you could get more specific
57:30 - 58:00 and maybe I'll even show you guys that um for example creating an ID to Target a specific button let's do that let's make a red button for delete and a green button for add um I'm going to start here I'm going to Target my entire screen so remember that that's represented by qet qet class curly brackets for CSS okay and then inside here I can just type all my normal CSS so background color I'm going to give it I already have this color here okay uh we
58:00 - 58:30 can do a font family right so pretty much all your normal standard stuff is going to work here uh and even the hover effects everything which is really cool I'm going to go through here and I'm going to jot out some Styles um color of the text let's say bit darker okay so our Q Widget the windows styled but each element within is still not so all of our Q Lael text let's make the font size
58:30 - 59:00 a bit bigger let's say our color uh I have a color here I'm going to drop in okay A bit of a bluish gray uh font weight we could even make this pop and then let's make sure everything has a little bit of padding I like padding padding is good okay uh cool whoops sorry about that Q label is done um and just to show you guys how this is looking right and how these styles do take effect I'm going to run this to
59:00 - 59:30 show us cool so oh okay well I'm going to run it I'm going to see why it's not working uh this method apply Styles I need to call it inside init UI let's just go down here let's go right here and let's say self. apply Styles okay let's go back trash my terminal rerun it you got to call the method for it to work there we go okay so I mean you can start to see things
59:30 - 60:00 taking form these input boxes need to be changed the buttons need to be changed and so does the table right but my font is taking effect so this is good this is really good um back into our app uh everything where we collect information I'm going to Target them all so I'll say q line edit uh Q combo box and our Q date edit let's target all those let's say a background color of uh
60:00 - 60:30 White let's say a font size of 14 say the text color is that gray color that I like let's give them all a border so a one pixel solid border and uh I'll drop in a color here that's a bit of like a bluish gray again and then I hate these um sharp edges
60:30 - 61:00 right so let's give a border radius let's curve the edges of them all and give them some um padding okay so that's cool okay so all the input fields are done being styled right and be creative here guys for your CSS this is a great way to practice okay um I'm going to do my buttons now or more specifically like a focus so let's say uh q line edit when I focus on it um
61:00 - 61:30 or I guess when I hover on it not really focus right um when I hover on my Q combo box and my Q date edit let's do a border let's give it a border one pixel solid and let's make it a green cool that's cool um okay for the
61:30 - 62:00 other ones I'm actually just going to take this I want to have a focus on it too so let's switch these hovers to say uh focus and uh my colors I'm going to drop in here spent some time getting a good decent color scheme going I that I thought looked really good uh okay Q table widget so background
62:00 - 62:30 color we're going to do just uh standard white we are going to alternate I think this is one right alternate background color uh I'm going to do an off light so can I do like F22 no F2 no no no no no F2 there we go slightly off uh grid line so I want to be able to see between the boxes so grid line color is
62:30 - 63:00 going to be a gray a selection background so when I choose one background color let me just drop in these and I'll talk about them okay so uh oh I think this should be a one okay um yeah the grid I want to see a grid to be able to tell the difference between the items okay um
63:00 - 63:30 very cool we have a lot of other stylings here don't we okay let's take a look at how this looks oky doie oh they curve which I like there we go that gray box too okay so this is this is looking all right going back to our Styles so guys this kind of I'll let you guys experiment with this I'm going to just drop in my other styling cuz I've spent a lot of time styling this the code repo is below for you to check out and then my monthly python project where I broke
63:30 - 64:00 this down this also includes that um let me drop these in and I will talk to you about how to change the colors of the buttons to Target specific ones okay so I've dropped in all the remaining styles that I have let's take a look at the final product I'll teach you how to switch the buttons real quick and then uh your project will be done okay uh yeah this is better so I can click on it and you can see that it's it's hovering green everything looks really good adding expense add
64:00 - 64:30 expense if I click delete expense are you sure yep okay so everything is looking really good let's get this delete expense button to be red actually in the ad expenses green so this is quite a fun one um I'm going to go up here actually after I made the delete button in order to give an ID for a CSS here I'm going to take my delete button and I'm going to say set object name and
64:30 - 65:00 then give it a name this is the ID so I'll say BTN delete okay when we return to our CSS I can use this as an ID now so if I go down here um let's keep the buttons together so here's the buttons okay I'm just going to kind of jump in here where I have the buttons so I'm going to say here hash BTN delete oh did I do underscore or
65:00 - 65:30 hyphen I did underscore so okay so button delete uh there we are so I'm targeting that button let's now I'm just going to honestly I'm going to take all these stylings and I will just apply them to the delete button so let me do this command let's delete those okay and then the color oh jeez what's a what's a red okay I found the
65:30 - 66:00 red let's drop that in uh when it's on Hover let's I'll switch these around so if it's on Hover uh does that change it much no h that'll look really weird pink that's fine I'm just going to do it okay uh so it's taking all the same effects but now now it's going to be red and when I hover it'll be pink okay so let's
66:00 - 66:30 just show you I'm going to run it to show you how it looks here ah why is it not working I don't think the IDS take precedence here so well q q push button takes the ultimate precedence so okay quick fix for that I gave that a name let's give this a name self do button add uh set object name let's just call this uh BTN ad then we'll switch those
66:30 - 67:00 out right CU Q push button it like it's like a in HTML A P tag or an H1 tag those tags take ultimate precedence um so it's kind of backwards here CU an ID should outwrite that um okay so I'm going to go in here let's boom boom boom uh BTN I don't know if I did BT and
67:00 - 67:30 AD these are still taking effect this delete expense what is not triggering with this delete expense ah a little bit of styling issue I guess delete button I am definitely targeting that did I button delete unbelievable that is my spelling issue again okay so all of these are button delete not delete button voila okay
67:30 - 68:00 there we go so I hover and you can see that they're different colors now and that's how you can Target with an ID in pqt within our CSS well there you have it guys our interactive expense Tracker app if you guys got value in today's episode help me out hit that like button and subscribe and drop me a comment let me know your thoughts did you have any struggles implementing SQL and pip remember my SQL and python newsletter it's not even just that it's a python newsletter where I write weekly content
68:00 - 68:30 is the first link in the description head on down there where I do have an ongoing squl and python series and come in and join in on the fun well guys that's all for this week's episode of Code with Josh until next time I'll see you then