6. Build A Streamlit Web App From Scratch For CRUD Operations Using Python and MySQL

hello coders I welcome you all in this video we are going to discuss how to create a web application for crud create read update and delete operations on MySQL using streamlined as you can see here we are going to create web application like this that can perform different operations like create read update and delete on mySQL database using streamlit that you can see over here so let's get started so for that we are going to use pycham and MySQL workbench that you can see over here so let's get started so let me click on this and let me enter my given password so now let's create new database first let me give name current underscore new one you can give any let me execute this query as you can see here our database is successfully created let me press this refresh button as you can see over here let me comment this out and let me use this newly created database let me execute this query as you can see here currently we are using this database also you can click on particular database to use or you can use this use command so now let's create new table in this particular database it name users let me create First Column ID of type integer let me set this as a primary key and also Auto increment our next column name which is where can 50 and third column email again worker 50 let me execute this query as you can see here now our table is successfully created with this name users let me press this refresh button as you can see over here let me click on this icon as you can see here now our table is successfully created that you can see over here having three columns ID name and email now let's jump to pychamp and let me import MySQL dot connector as you can see here no module name MySQL so let me click on this and let me install this package let me click on this as you can see over here now installation process is going as you can see here now our package is installed successfully that you can see over here now let me import streamlit SST as you can see here no module name streamlined so let me click on this and let me press this install package streamlined as you can see here installing package streamlined so we have to wait as you can see over here package is installed successfully as you can see here now our required libraries are successfully installed that you can see over here so now our first step establish a connection to mySQL server so let me write establish connection to mySQL server this one so let me write MySQL Dot connector dot connect and here we have to pass hostname in our case available on localhost now here we have to pass user in my case user is root and also we have to give password that we have given over here on MySQL workbench let me enter my given password in our database name this one crowd underscore new one that you can see over here let me assign it to one variable mydb is equal to this statement and now we have to create one cursor object my DB this one dot cursor and let me assign it to one variable my cursor is equal to this statement let me check connection so for that let me print one message connection established let me run this code as you can see over here now we are successfully connected with our database that you can see over here now let's move to our second step we are going to create streamlined web app so let me create one function with name Main and to call this let me write if underscore underscore name underscore is equal to underscore main then call this function mean so now let's first display title for our web app using St dot title crore operations with my SQL you can give any let me save this and let's run our web app to run this we have to write extremely run our app name current dot Pi this one let me execute this command as you can see over here our title crude operations with my SQL title that we have given so now let's first display options for crud operations and let me write St dot sidebar dot select box so user can select crude operations so let me write select and operation which operations create read update and delete let me save and let me run extremely run crud dot pipe as you can see over here from here user can select different operations create read update and delete that you can see over here so now let's create template to perform selected crore operations like create read update and delete so here we are going to perform selected operations like create read update and delete and first let me assign this to one variable option so if option equal to create so here we will write code to create a record so now let me give sub header create a record let me save this and let me press rerun and let me click on this as you can see over here create a record so let's give four other operations read update and delete as well alif option equal to read then let's give sub header read records option equal to update then sub header is update a record Alice option equal to delete then let's give sub header as delete a record let me save this and let's rerun this web app let me click on this as you can see over here sub header create a record as you can see here read records as you can see update a record and delete a record so now let's write Logic for this for create read update and delete so we can perform this crud operations with mySQL so let's first write code 4 Create a record so let's take user input so St dot text input here user can enter name let me assign it to one variable name and here user can input email let me save this and let me rerun this score let me click on this as you can see here to create a record user will enter name and email address so let me create one button with name create so this record will be created when user press this button with name create so for that we have to write insert query insert into users which is our table name name name and email and values percentage s and percentage s that we are taking from the user this name and email this name and email and let me assign to one variable SQL is equal to this query and another variable well is equal to its name and email let me arrange it properly now here we have to use my cursor dot execute to execute this query let me pass this query and value let's commit changes to the database using mydb dot commit and let me print success message record created successfully let me save this and let me rerun this code let me enter my name an email address let me press on this create Button as you can see here record created successfully let me check as you can see here currently I am using my SQL workbench and let me press this refresh button let me click on this as you can see over here we have successfully created this record using our web app that you can see over here so this way we can create a record using MySQL and streamlit so now let's write code 4 our next operation read this one so let me write my cursor is one dot execute to execute our query let me write select all from our table users and let me write my cursor dot fetch all to fetch all the records and let me assign it to one variable result is equal to this statement to display this we have to use for Loop so four Row in result and we have to write s t dot right to print using streamlit and this row this one let me save this and let me rerun our web app as you can see over here we can able to read our records that you can see over here let's enter new record with name Anil at the rate gmail.com and let me press this create Button as you can see here record created successfully now let me press this read as you can see over here we can able to read all the records so we have successfully completed our second operation read that you can see over here so now our next operation update so let's first take ID from the user so we are going to update a record as per given ID so enter ID and let me assign it to one variable ID and let's take new name from the user enter new name and let me assign it to an variable name at stake new email ID from the user enter new email and let me assign it to one variable email so if user press is button with name update then we have to update a record so let's write MySQL Query update users our table name and we have to set name equal to percentage as email equal to percentage s where where ID is equal to percentage s and we have to update value for name and email as per given ID and let's execute this query having this query and with this value Also let's commit the changes and let's print success message record updated successfully let me save this let me close this connection and let's run our app once again as you can see over here our records let me click on this update Button as you can see here by default ID is 0.00 we will change it shortly let me enter 2 we are going to update second record a new name Rahul and let me give new email address Rahul gmail.com let me press this update Button as you can see here record updated successfully now let's read our records as you can see over here we have successfully updated our record this one that you can see over here so this way we can perform create operation read operation and update operation let's set it to 1 because our record starts with one ID one so let's give minimum value equal to one let me save and let's rerun our web app let me check as you can see over here so this way we can perform create read and update now let's code for delete to delete a record here so we are going to take ID to delete a record enter ID let me assign it to one variable ID if s t dot button delete if user press this button then record will be deleted let's write MySQL Query delete from table users where ID equal to percentage s and well here we have to pass couple with one value so we have to write like this ID and comma and let's execute this query using my cursor dot execute query and value let's commit this transaction and let's print success message using St dot success record deleted successfully let me save this and let's rerun our web app again here we have to set minimum value to 1 so let's read records we are going to delete this first record so let me click on this delete and record one let me press this delete button as you can see here record deleted successfully let me check let's read our records as you can see over here we have successfully deleted our record that you can see over here so this way we can perform crowd operation using MySQL and streamlit that you can see over here let's set minimum value to 1 here let me rerun our web app as you can see over here so this way we can perform crowd operations create read update and delete that you can see over here using MySQL and streamlit that you can see over here hope you like this video please don't forget to subscribe this channel if you like this video smash that like button thank you very much for watching this video take care bye bye see you in the next video

Channel: Data Thinkers

Views: 14,468

Keywords: priyang bhatt, uploads from priyang bhatt

Id: T6lMgciw8o8

Length: 13min 23sec (803 seconds)

Published: Fri Mar 31 2023