{"id":714,"date":"2024-11-19T08:37:04","date_gmt":"2024-11-19T08:37:04","guid":{"rendered":"https:\/\/eipsoftware.com\/musings\/?p=714"},"modified":"2025-05-22T15:57:40","modified_gmt":"2025-05-22T15:57:40","slug":"streamlit-crud-app","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/","title":{"rendered":"Streamlit Table Editor App"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\" id=\"main-app\">CRUD (Create, Read, Update, and Delete) it is the cornerstone of working with database applications. Today I will walk through using the <a href=\"https:\/\/streamlit.io\/\">Streamlit<\/a> python framework to build a web based app that you can use to interact with a database. The app will allow you to Read, Update, and Delete tables in your database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The creators of <a href=\"https:\/\/streamlit.io\/\">Streamlit<\/a> framework have taken care of a large amount of the burden of creating a web based app.  However not all of the widgets are feature complete. Streamlit is an asynchronous framework allowing 100s or 1000s of users to use your app.  With CRUD operations that is great for the Create and Read, but for Update and Delete, not so much.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Today&#8217;s tutorial will show you how to overcome those limitations and hopefully inspire you on how you could use Streamlit for your own apps.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">All of the code is available on my <a href=\"https:\/\/github.com\/mmooney512\/musings\/tree\/main\/python%20\/streamlit%20table%20editor\" data-type=\"link\" data-id=\"https:\/\/github.com\/mmooney512\/musings\/tree\/main\/python%20\/streamlit%20table%20editor\">Github page<\/a>, please use responsibly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>NOTE:<\/strong> The code presented is not production ready; i.e. no <a href=\"https:\/\/docs.python.org\/3\/library\/exceptions.html\" data-type=\"link\" data-id=\"https:\/\/docs.python.org\/3\/library\/exceptions.html\">Exception <\/a>blocks but it wouldn&#8217;t take much more effort to adapt the code to be production ready. Instead we will concentrate on the important parts and get you reading and updating your tables quickly. Let&#8217;s start the tutorial below.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"pages\">Pages<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"#main-app\">Main Page<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#files\">Files<\/a>\n<ul class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g3988f71708dd\">Directory Structure<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g51d2bbb785f7\">main.py<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g37c8de04e1f1\">home.py<\/a>\n<ol class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g27ce3bcf0c5a\">home.py &#8211; library import<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g72fdeccc4b44\">main.py &#8211; main()<\/a><\/li>\n<\/ol>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g51ee7095275e\">pages<\/a>\n<ul class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g1c3875d3e2f9\">pages &#8211; add_row.py<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gbf2e49ba488d\">pages &#8211; edit_row.py<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g7c8cfb960e22\">Pages &#8211; delete_row.py<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g76ff6c324690\">Streamlit Handler Class<\/a>\n<ul class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g1fb652c376af\">StreamlitHandler &#8211; Base<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#ge5ab805fe105\">StreamlitAddData<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#streamliteditdata\">StreamlitEditData<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#streamliteditdata\">StreamlitDeleteData<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#data-handler-class\">Data Handler Class<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#pandassqlhandler\">PandasSqlHandler<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#configuration-files\">Configuration Files<\/a>\n<ul class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#sql-config-yaml\">sql_config.yaml<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#streamlit-config-yaml\">streamlit_config.yaml<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#sql-column-config-yaml\">sql_column_config.yaml<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#query\">Query<\/a>\n<ol class=\"wp-block-list\">\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#info-columns\">info.columns<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#sys-row-count\">sys.row_count<\/a><\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#table-query-example\">Table Query &#8211; Example<\/a><\/li>\n<\/ol>\n<\/li>\n\n\n\n<li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=8#github-files\">Github Files<\/a><\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"files\">Files<\/h2>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#files\">Files<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g3988f71708dd\">Directory Structure<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g51d2bbb785f7\">main.py<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g37c8de04e1f1\">home.py<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g27ce3bcf0c5a\">home.py &#8211; library import<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=2#g72fdeccc4b44\">main.py &#8211; main()<\/a><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"g3988f71708dd\">Directory Structure<\/h2>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"392\" height=\"773\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dir_structure.png\" alt=\"\" class=\"wp-image-857\" srcset=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dir_structure.png 392w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dir_structure-152x300.png 152w\" sizes=\"auto, (max-width: 392px) 100vw, 392px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"g51d2bbb785f7\">main.py<\/h2>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><\/div>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">import os\n\nif __name__ == \"__main__\":\n    os.system(\"streamlit run home.py\")<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Is that it? Yes. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We will use main.py to start the Streamlit app, passing in the python file name we want to use for the home page.  In this case, home.py.  Speaking of home.py, let&#8217;s look at it next.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"g37c8de04e1f1\">home.py<\/h2>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># home.py\n\n# -- LIBRARY -----------------------------------------------------------------\nimport streamlit as st\nimport pandas as pd\nimport yaml\nimport os\n\n# -- LOCAL -------------------------------------------------------------------\nfrom library.pandas_sql_handler import PandasSqlHandler\nfrom data_handler.data_handler import DataHandler\nfrom config import sql_column_config\n\n\ndef main():\n    st.set_page_config(layout=\"wide\")\n    st.title(\"Database Table Editor\")\n\n    st.markdown(\n        \"\"\"\n        Select Action and Table to Edit from menu\n        \"\"\"\n        )\n\n\nif __name__ == \"__main__\":\n    main()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Hmmm&#8230; this is not looking too complicated.  And you would be correct.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Many folks will name the home page file, app.py.  I deviated a little bit from convention here, in order to help keep track of what is going. on.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now let&#8217;s go line by line<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"g27ce3bcf0c5a\">home.py &#8211; library import<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># home.py\n\n# -- LIBRARY -----------------------------------------------------------------\nimport streamlit as st\nimport pandas as pd\nimport yaml\nimport os<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">We import some standard libraries that are used in the python world, <a href=\"https:\/\/pandas.pydata.org\/\">pandas<\/a>, <a href=\"https:\/\/pypi.org\/project\/PyYAML\/\">yaml<\/a>, os, and of course <a href=\"https:\/\/streamlit.io\/\">streamlit<\/a>.  The appendix will contain details on how to install the packages.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we will import some local files that we will create below.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># -- LOCAL -------------------------------------------------------------------\nfrom library.pandas_sql_handler import PandasSqlHandler\nfrom data_handler.data_handler import DataHandler\nfrom config import sql_column_config<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"#pandassqlhandler\">PandasSQLHandler<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"#data-handler-class\">DataHandler<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"#sql-column-config-yaml\">sql_column_config<\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"g72fdeccc4b44\">main.py &#8211; main()<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">We aliased the streamlit package as st, which is pretty common alias for the package.  <\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    st.set_page_config(layout=\"wide\")\n    st.title(\"Database Table Editor\")\n\n    st.markdown(\n        \"\"\"\n        Select Action and Table to Edit from menu\n        \"\"\"\n        )\n<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the main function, we will call three streamlit methods.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    st.set_page_config(layout=\"wide\")<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">By default streamlit will limit the width of the content on the webpage. For our tutorial, I will set the layout to wide, to allow for using the complete width of the browser window.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    st.title(\"Database Table Editor\")<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Next we will add a title for the page. By default it is size of H1 html tag.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><\/div>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">   st.markdown(\n        \"\"\"\n        Select Action and Table to Edit from menu\n        \"\"\"\n        )<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The last bit is to add some text, supporting markdown.  You could also use some the st.text method to accomplish the same thing.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you were to run the app you would see the following, without the menu on the sidebar, we will get to that part in a moment.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"987\" height=\"487\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_home.png\" alt=\"\" class=\"wp-image-729\" srcset=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_home.png 987w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_home-300x148.png 300w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_home-768x379.png 768w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_home-900x444.png 900w\" sizes=\"auto, (max-width: 987px) 100vw, 987px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"g51ee7095275e\">pages<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Streamlit has an automatic method of adding pages to the sidebar menu.  From the top menu, we create a directory named, pages.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The pages will be listed sorted by a leading number. In our example we will name the files, 1_add_row.py, 2_edit_row.py, 3_delete_row.py.  Streamlit will remove the number from the file name and convert underscores to spacees.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"178\" height=\"111\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/dte_pages.png\" alt=\"\" class=\"wp-image-730\"\/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g51ee7095275e\">pages<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g1c3875d3e2f9\">Pages &#8211; add_row.py<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gb45116603225\">Pages &#8211; add_row.py &#8211; main()<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gd1872553618f\">pages &#8211; add_row.py &#8211; main() &#8211; session_state<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gdccb25457894\">pages &#8211; add_row.py &#8211; main() &#8211; select table<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g6d46cc4c17b6\">pages &#8211; add_row.py &#8211; main() &#8211; data_editor<\/a><\/li><\/ol><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gbf2e49ba488d\">pages &#8211; edit_row.py<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g1703f2ee0804\">pages &#8211; edit_row.py &#8211; main()<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g19584fa9555b\">pages &#8211; edit_row.py &#8211; main() &#8211; session_state<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g5fc24657cc68\">pages &#8211; edit_row.py &#8211; main() &#8211; sidebar<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gd10215f36ead\">pages &#8211; edit_row.py &#8211; main() &#8211; data_editor<\/a><\/li><\/ol><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g7c8cfb960e22\">Pages &#8211; delete_row.py<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g7d90d7c2ade7\">pages \u2013 delete_row.py \u2013 main()<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g70f51824f452\">pages &#8211; delete_row.py &#8211; main() &#8211; session state<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#g8533b3b5af7d\">pages &#8211; delete_row.py &#8211; main() &#8211; table select box<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#gd6ed28498098\">pages &#8211; delete_row.py &#8211; main() &#8211; sidebar<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=3#geb1797ec2efa\">pages &#8211; delete_row.py &#8211; main() &#8211; data_editor<\/a><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"g1c3875d3e2f9\">Pages &#8211; add_row.py<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"468\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-1024x468.png\" alt=\"\" class=\"wp-image-738\" style=\"width:609px;height:auto\" srcset=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-1024x468.png 1024w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-300x137.png 300w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-768x351.png 768w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-900x411.png 900w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row-1280x585.png 1280w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/add_row.png 1386w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The add_row.py file will allow the user to add a row to the database.  We will use the tables column names and data types when creating the streamlit data editor widget. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"gb45116603225\">Pages &#8211; add_row.py &#8211; main()<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># pages \/ 1_add_row.py\n\n# -- LIBRARY -----------------------------------------------------------------\nimport streamlit as st\n\n# -- LOCAL -------------------------------------------------------------------\nfrom streamlit_handler import StreamlitAddData\n\ndef main():\n    st.set_page_config(layout=\"wide\")\n    # INIT\n    if 'data_editor_add' not in st.session_state:\n        st_handler = StreamlitAddData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler'] \n\n    # layout\n    st.header(\"Add a New Row\")\n    \n    # select box UI for which table\n    selected_table = st_handler.selectbox_table()\n\n    if selected_table is not None:\n        # set the name of the table will be adding rows too\n        st_handler.source_table_name = selected_table\n        # display the data_editor on screen\n        new_data = st_handler.fetch_empty_table()\n\n        if st.button(label=\"Add Row\", \n                     key=\"btn_add_row\"):\n            st_handler.add_source_data(new_data=new_data)\n            if st_handler.query_success():\n                st.success(\"New rows added to the database\")\n\n\nif __name__ == \"__main__\":\n    main()\n<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Details about the code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here we are doing a similar thing as the home page. However I have created a class called streamlit_handler with a subclass of StreamlitAddData that will help with passing of the data from the database to the streamlit widgets.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We will review the streamlit_handler later in the tutorial.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"gd1872553618f\">pages &#8211; add_row.py &#8211; main() &#8211; session_state<\/h5>\n\n\n\n<p class=\"wp-block-paragraph\">One of the main issues with how the streamlit package works is that by default it is stateless, unless you use the streamlit session_state method.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">def main():\n    st.set_page_config(layout=\"wide\")\n    # INIT\n    if 'data_editor_add' not in st.session_state:\n        st_handler = StreamlitAddData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler'] \n<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here we are checking if the session state has been set. If the session is set, we will re-invoking the session state.  The st_handler object contains all of the data we need to add a row to the table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"gdccb25457894\">pages &#8211; add_row.py &#8211; main() &#8211; select table<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    # layout\n    st.header(\"Add a New Row\")\n    \n    # select box UI for which table\n    selected_table = st_handler.selectbox_table()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">We will add a select box so the user can choose which table they would like to add a row too.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g6d46cc4c17b6\">pages &#8211; add_row.py &#8211; main() &#8211; data_editor<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    if selected_table is not None:\n        # set the name of the table will be adding rows too\n        st_handler.source_table_name = selected_table\n        # display the data_editor on screen\n        new_data = st_handler.fetch_empty_table()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here, we will wait until the user has selected a table from the select box. On the change event, the main() function will first fetch the table name from the select box. Second, using the st_handler method fetch_empty_table() will read the meta data about the selected table and populate the data_editor widget.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages &#8211; add_row.py &#8211; main() &#8211; add_row button<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        if st.button(label=\"Add Row\", \n                     key=\"btn_add_row\"):\n            st_handler.add_source_data(new_data=new_data)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Now we will wait until the user presses the button labeled &#8220;Add Row&#8221; and will then invoke the add_source_data method, to write the data to the table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages &#8211; edit_row.py &#8211; main() &#8211; success div<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">            if st_handler.query_success():\n                st.success(\"New rows added to the database\")<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If the add_source_data method is successful, the query_success property will return True; and will display a message to the user.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"gbf2e49ba488d\">pages &#8211; edit_row.py<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"604\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-1024x604.png\" alt=\"\" class=\"wp-image-739\" srcset=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-1024x604.png 1024w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-300x177.png 300w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-768x453.png 768w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-900x530.png 900w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row-1280x754.png 1280w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/edit_row.png 1376w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The edit_row.py page follows a similar pattern as the add_row.py page with a few additions. We will add page navigation buttons and controls for pagination of the table data, let&#8217;s dig in.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"g1703f2ee0804\">pages &#8211; edit_row.py &#8211; main()<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># pages \/ 2_edit_row.py\n\n# -- LIBRARY -----------------------------------------------------------------\nimport streamlit as st\n\n# -- LOCAL -------------------------------------------------------------------\nfrom streamlit_handler import StreamlitEditData\n\ndef main():\n    st.set_page_config(layout=\"wide\",\n                       page_icon=\":anchor:\"\n                       )\n\n    if 'data_editor_update' not in st.session_state:\n        st_handler = StreamlitEditData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler'] \n\n    st.title(\"Edit Rows\") \n\n    # select box UI for which table\n    selected_table = st_handler.selectbox_table()\n    \n    if selected_table is not None:\n        # set the name of the table will be adding rows too\n        st_handler.source_table_name = selected_table\n        st_handler.total_pages = st_handler.fetch_row_count()\n\n \n        with st.sidebar:\n            # Dropdown to select rows per page\n            st_handler.selectbox_rows_per_page()\n            \n            # input box to select page number\n            st_handler.input_page_select()\n\n        # page nav controls\n        st_handler.page_nav_controls()\n\n        with st.container(border=True):\n            edited_data = st_handler.data_editor_formatted()\n\n        st.button(label=\"Update Rows\",\n                  key=\"btn_update_rows\",\n                  on_click=st_handler.update_source_data,\n                  args=(edited_data,)\n                  )\n        if st_handler.query_success():\n            st.success(\"Updateds saved to the database.\")\n\nif __name__ == \"__main__\":\n    main()    <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Details about the code, similar to the add_row.py, we will use check if the session state has been set. If the session is set, we will re-invoking the session state.  The st_handler object contains all of the data we need to edit a row in the table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next there are four parts to the page, checking session_state, the table selectbox, the sidebar, and the data_editor widget.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g19584fa9555b\">pages &#8211; edit_row.py &#8211; main() &#8211; session_state<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">def main():\n    st.set_page_config(layout=\"wide\",\n                       page_icon=\":anchor:\"\n                       )\n\n    if 'data_editor_update' not in st.session_state:\n        st_handler = StreamlitEditData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler'] <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If the session is set, we will re-invoking the session state.  The st_handler object contains all of the data we need to edit a row in the table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g5fc24657cc68\">pages &#8211; edit_row.py &#8211; main() &#8211; sidebar<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        with st.sidebar:\n            # Dropdown to select rows per page\n            st_handler.selectbox_rows_per_page()\n            \n            # input box to select page number\n            st_handler.input_page_select()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Streamlit automatically adds a link for every .py file in the pages directory. In addition, we will add two widgets below the page links in the sidebar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages &#8211; edit_row.py &#8211; main() &#8211; nav controls<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        # page nav controls\n        st_handler.page_nav_controls()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">After the side bar, we will call the st_handler.page_nav_controls() method to print a row with multiple columns showing the page controls.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"gd10215f36ead\">pages &#8211; edit_row.py &#8211; main() &#8211; data_editor<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        with st.container(border=True):\n            edited_data = st_handler.data_editor_formatted()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Below the page nav controls we will display the data_editor widget called by the st_handler.data_editor_formatted() method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages &#8211; edit_row.py &#8211; main() &#8211; update rows button<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        st.button(label=\"Update Rows\",\n                  key=\"btn_update_rows\",\n                  on_click=st_handler.update_source_data,\n                  args=(edited_data,)\n                  )<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Next, we will display a button for when the user has completed their edits. The on_click argument specifies the function to call after the user clicks the button. Some Streamlit widgets support on_click and others on_change.  In our case we will call the st_handler.update_source_data method.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Pages &#8211; edit_row.py &#8211; main() &#8211; success div<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        if st_handler.query_success():\n            st.success(\"Updateds saved to the database.\")<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If the update_source_data method is successful, the query_success property will return True; and will display a message to the user.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"g7c8cfb960e22\">Pages &#8211; delete_row.py<\/h3>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"594\" src=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-1024x594.png\" alt=\"\" class=\"wp-image-755\" srcset=\"https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-1024x594.png 1024w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-300x174.png 300w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-768x445.png 768w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-900x522.png 900w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row-1280x742.png 1280w, https:\/\/eipsoftware.com\/musings\/wp-content\/uploads\/2024\/11\/delete_row.png 1392w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The delete_row.py page follows a similar pattern as the edit_row.py page with a few additions.  We will add a column named Select to the dataset, allowing user to check which rows to delete.  After pressing the Delete Rows button we will show a confirmation button to the user, let\u2019s get started. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"g7d90d7c2ade7\">pages \u2013 delete_row.py \u2013 main()<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \"># pages \/ 3_delete_row.py\n\n# -- LIBRARY -----------------------------------------------------------------\nimport streamlit as st\n\n# -- LOCAL -------------------------------------------------------------------\nfrom streamlit_handler import StreamlitDeleteData\n\ndef main():\n    st.set_page_config(layout=\"wide\")\n    # INIT\n    if 'data_editor_delete' not in st.session_state:\n        st_handler = StreamlitDeleteData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler']     \n\n    # layout\n    st.header(\"Delete Rows\")\n    \n    # select box UI for which table\n    selected_table = st_handler.selectbox_table()\n\n    if selected_table is not None:\n        # Tell user what to do\n        st.text(\"Check box in Select column to mark row for deletion\")\n        st_handler.source_table_name = selected_table\n        st_handler.total_pages = st_handler.fetch_row_count()\n\n        with st.sidebar:\n            # Dropdown to select rows per page\n            st_handler.selectbox_rows_per_page()\n            \n            # input box to select page number\n            st_handler.input_page_select()\n\n        # page nav controls\n        st_handler.page_nav_controls()\n\n        # source table data\n        table_data = st_handler.data_editor_formatted()\n\n        # delete the data from the table\n        st.button(label=\"Delete Rows\",\n                  key=\"btn_delete_rows\",\n                  on_click=st_handler.prompt_for_confirmation,\n                  args=(table_data.query(\"Select == True\").shape[0],)\n                  )\n        \n        #if st.session_state['deletion_requested']:\n        if st_handler.deletion_requested:\n            # delete the data from the table\n            st.button(label=\"Confirm Deletion\",\n                    key=\"btn_confirm_delete\",\n                    on_click=st_handler.delete_source_data,\n                    args=(table_data.query(\"Select == True\"),))\n        \n        if st_handler.query_success():\n            # let user know rows have been deleted\n            st.success(\"Selected rows deleted successfully.\")\n            table_data['Select'] = False\n\n        st.session_state['st_handler'] = st_handler\n\n\nif __name__ == \"__main__\":\n    main()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Details about the code, similar to the edit_row.py, we will use check if the session state has been set. If the session is set, we will re-invoking the session state. The st_handler object contains all of the data we need to select which rows in the table to delete.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next there are five parts to the page, checking session_state, the table selectbox, the sidebar, the data_editor widget, and confirmation from the user.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g70f51824f452\">pages &#8211; delete_row.py &#8211; main() &#8211; session state<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">def main():\n    st.set_page_config(layout=\"wide\")\n    # INIT\n    if 'data_editor_delete' not in st.session_state:\n        st_handler = StreamlitDeleteData()\n        st.session_state['st_handler'] = st_handler\n    else:\n        st_handler = st.session_state['st_handler']     <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If the session is set, we will re-invoking the session state. The st_handler object contains all of the data we need to delete a row in the table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g8533b3b5af7d\">pages &#8211; delete_row.py &#8211; main() &#8211; table select box<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    # layout\n    st.header(\"Delete Rows\")\n    \n    # select box UI for which table\n    selected_table = st_handler.selectbox_table()\n<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Add a header for the page, and then call the st_handler selectbox_table method, same as in addrow and edit_row pages.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"gd6ed28498098\">pages &#8211; delete_row.py &#8211; main() &#8211; sidebar<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        with st.sidebar:\n            # Dropdown to select rows per page\n            st_handler.selectbox_rows_per_page()\n            \n            # input box to select page number\n            st_handler.input_page_select()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Streamlit automatically adds a link for every .py file in the pages directory. In addition, we will add two widgets below the page links in the sidebar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages \u2013 delete_row.py \u2013 main() \u2013 nav controls<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        # page nav controls\n        st_handler.page_nav_controls()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">After the side bar, we will call the st_handler.page_nav_controls() method to print a row with multiple columns showing the page controls.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"geb1797ec2efa\">pages &#8211; delete_row.py &#8211; main() &#8211; data_editor<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        # source table data\n        table_data = st_handler.data_editor_formatted()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Below the page nav controls we will display the data_editor widget called by the st_handler.data_editor_formatted() method.  The method is called from the subclass. The method will add a column named Select which is Boolean data type, and shown as a checkbox in thee data_editor widget.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages \u2013 delete_row.py \u2013 main() \u2013 delete rows button<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        # delete the data from the table\n        st.button(label=\"Delete Rows\",\n                  key=\"btn_delete_rows\",\n                  on_click=st_handler.prompt_for_confirmation,\n                  args=(table_data.query(\"Select == True\").shape[0],)\n                  )<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Next, we will display a button for when the user has completed which rows they want to delete. The on_click argument specifies the function to call after the user clicks the button. Some Streamlit widgets support on_click and others on_change. In our case we will call the st_handler.prompt_for_confirmation method.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"pages-delete-row-main-confirmation\"><strong>pages &#8211; delete_row.py &#8211; main() &#8211; confirmation<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        if st_handler.deletion_requested:\n            # delete the data from the table\n            st.button(label=\"Confirm Deletion\",\n                    key=\"btn_confirm_delete\",\n                    on_click=st_handler.delete_source_data,\n                    args=(table_data.query(\"Select == True\"),))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">When the user selects they want to delete row(s) from the table, a confirmation button will be displayed. After clicking the confirmation button the st_handler.delete_source_data method will be called.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>pages \u2013 delete_row.py \u2013 main() &nbsp;\u2013 success div<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        if st_handler.query_success():\n            # let user know rows have been deleted\n            st.success(\"Selected rows deleted successfully.\")\n            table_data['Select'] = False<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If the delete_source_data method is successful, the query_success property will return True; and will display a message to the user.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now, that we have reviewed the pages, on the next page we will dive into the StreamlitHandler Class and how it communicates with the Streamlit widgets and the database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"g76ff6c324690\">Streamlit Handler Class<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The class is to help with creating the Streamlit widgets and communication between the widgets and the databasee.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Three classes inherit from the StreamlitHandler class<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>StreamlitAddData<\/li>\n\n\n\n<li>StreamlitEditData<\/li>\n\n\n\n<li>StreamlitDeleteData<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The three classes as you can guess relates to the app&#8217;s corresponding page.<\/p>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g76ff6c324690\">Streamlit Handler Class<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g1fb652c376af\">StreamlitHandler &#8211; Base<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g26e160140609\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#slh-load-query\">_load_query<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#slh-set-primary-key-column-name\">__set_primary_key_column_name<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#slh-set-column-order\">__set_column_order<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#fetch-row-count\">fetch_row_count<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#fetch-table-data\">fetch_table_data<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#jump-current-page\">jump_current_page<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#move_current_page\">move_current_page<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#input_page_select\">input_page_select<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#page-nav-controls\">page_nav_controls<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#selectbox-rows-per-page\">selectbox_rows_per_page<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#selectbox-table\">selectbox_table<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#query-success\">query_success<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#update-rows-per-page\">update_rows_per_page<\/a><\/li><\/ol><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#ge5ab805fe105\">StreamlitAddData<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#gf97c6fc85f8f\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#ga875b89b4a99\">fetch_empty_table<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#g6fe09859ca7e\">add_source_data<\/a><\/li><\/ol><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#streamliteditdata\">StreamlitEditData<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#methods\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#data-editor-formatted\">data_editor_formatted<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#update-source-data\">update_source_data<\/a><\/li><\/ol><\/li><\/ol><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#streamliteditdata\">StreamlitDeleteData<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#methods\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#data-editor-formatted\">data_editor_formatted<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=4#prompt-for-confirmation\">prompt_for_confirmation<\/a><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"g1fb652c376af\">StreamlitHandler &#8211; Base<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">I won&#8217;t go through all of the attributes, most are self explaintory.  However, the one thing to mention is that the class uses two other classes, the PandasSQLHandler and the DataHandler class. We will discuss those classes further on in the tutorial. <\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"g26e160140609\">Methods<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"slh-load-query\">_load_query<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def _load_query(self, query_file_name:str = None):\n        \"\"\"\n        Read the query file stored in \/query directory\n        Name of file needs to match the table name which is displayed \n        in the drop down box\n\n        Parameters\n        ----------\n        query_file_name : str, optional\n            name of query file to be loaded in the class if the query name is\n            not specified, it is assumed to use a file name of \n            schema.table_name.sql which is assigned in the source_table_name \n            property.\n        \"\"\"\n        # if user supplied query_file_name else default val from class\n        query_file_name = query_file_name if query_file_name is not None else self._query_file_name\n\n        with open(f\"query\/{query_file_name}\", 'r') as file:\n            self.query = file.read()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Read the query file stored in \/query directory. Name of file needs to match the table name which is displayed in the drop down box.  The names of the tables to be displayed are configured in the config \/ sql_column_config.yaml file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"slh-set-primary-key-column-name\">__set_primary_key_column_name<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __set_primary_key_column_name(self) -&gt; None:\n        \"\"\"\n        sets the primary key column name defined in the\n        config\/sql_column_config.yaml file\n        \"\"\"\n        self.primary_key_column_name = sql_column_config.table[self._base_table_name].PRIMARY_KEY\n<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">sets the primary key column name defined in the config\/sql_column_config.yaml file. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"slh-set-column-order\">__set_column_order<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __set_column_order(self) -&gt; None:\n        \"\"\"\n        sets the column order for the data_editor widgets, while excluding\n        the primary key column.\n        values are defined in the config\/sql_column_config.yaml file\n        \"\"\"\n\n        table_columns = list(self.table_data.columns)\n        if self.primary_key_column_name in table_columns:\n            table_columns.remove(self.primary_key_column_name)\n        self._column_order = tuple(table_columns)    <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">sets the column order for the data_editor widgets, while excluding the primary key column. The values and order are defined in the config\/sql_column_config.yaml file.  The data_editor widgets will display the columns in the same order as defined in the configuration file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"fetch-row-count\">fetch_row_count<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def fetch_row_count(self) -&gt; int:\n        \"\"\"\n        Fetch number of rows in the table, used in pagination\n\n        Returns\n        -------\n        int\n\n        \"\"\"\n        # load the query string\n        self._load_query(sql_column_config.info.count.QUERY)\n        \n        # fetch the row_count data\n        data = self.db_handler.read_table(query=self.query,\n                                          params=(self.source_table_name,)\n                                          )                \n        return data.loc[0, 'row_count']<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Fetch number of rows in the table, used in pagination.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Will run a quick query, The query to use is defined in the config\/sql_column_config.yaml file. In our tutorial we are connecting to Microsoft SQL database.  Instead of doing a row_count query which mayy be expensive, will read from the sys.dm_db_partition_stats table which doesn&#8217;t require elevated privileges.  If the user has access to read \/ write to tables they should be able to read from sys.dm_db_partition_stats. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"fetch-table-data\">fetch_table_data<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def fetch_table_data(self,row_params:tuple=None) -&gt; None:\n        \"\"\"Loads the data from source table\n\n        Returns\n        -------\n        None\n        \"\"\"\n        self.__set_primary_key_column_name()\n        self._load_query()\n        \n        self.table_data = self.db_handler.read_table(self.query, row_params)\n        \n        # set the column order, hiding the primary key column\n        self.__set_column_order()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Method will query the database, using the PandasSQLHandler class. Storing the results to be sent to the data_editor widget.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"jump-current-page\">jump_current_page<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def jump_current_page(self)-&gt; None:\n        \"\"\"\n        When user increments, decrements, enters in page number\n        move to that page\n        \"\"\"    \n        page_input = st.session_state['current_page_input']\n        \n        if page_input &gt;= 1 and page_input &lt;= self._total_pages:\n            self.current_page = page_input\n\n        self.fetch_table_data(row_params=((self.current_page-1)*self.rows_per_page,\n                                    self.rows_per_page,\n                                    ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Will query the source table, inclusive of prior database commits and based on user selected number of rows to display and total rows in the table will jump to the specified page. The requested page is read from Streamlist session_state dictionary.  Conditional logic is in place to prevent from requesting a page that doesn&#8217;t exist. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"move_current_page\">move_current_page<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def move_current_page(self, action:str)-&gt; None:\n        \"\"\"\n        When user increments, decrements, enters in page number\n        move to that page\n        \"\"\"        \n        match action:\n            case 'decrease':\n                if self.current_page &gt; 1:\n                    self.current_page -= 1           \n            case 'increase':\n                if self.current_page &lt; self._total_pages:\n                    self.current_page += 1\n            case _:\n                self.current_page = 1\n\n        self.fetch_table_data(row_params=((self.current_page-1)*self.rows_per_page,\n                                    self.rows_per_page,\n                                    )) <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Will query the source table, inclusive of prior database commits and based on user selected number of rows to display and total rows in the table will move back or forward one page. Using call backs from the nav control buttons, determines the user requested action.  Conditional logic is in place to prevent from requesting a page that doesn&#8217;t exist. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"input_page_select\">input_page_select<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def input_page_select(self):\n        \"\"\"\n        Creates the streamlit input UI element, limiting to number of pages\n\n        Returns\n        -------\n        streamlit.selectbox\n\n        \"\"\"\n        # make sure we don't exceed max number of pages\n        if self.current_page &gt;= self.total_pages:\n            self.current_page = int(self.total_pages)\n\n        return(st.number_input(\n                \"Go to page\",\n                min_value=1,\n                max_value=self.total_pages,\n                value=self.current_page,\n                step=1,\n                key=\"current_page_input\",\n                on_change=self.jump_current_page,\n            ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Returns a Streamlit number_input widget. The widget allows user to input which page number they want to jump too. The widget is used in the page navigation controls show in the sidebar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"page-nav-controls\">page_nav_controls<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def page_nav_controls(self):\n        \"\"\"\n        Creates the streamlit input UI elments for page navigation\n        Returns\n        -------\n        streamlit.selectbox        \n        \"\"\"\n        with st.container(key='page-controller', border=True):\n            col1, col2, col3, col4, col5 = st.columns([1, 2, 1, 1, 4],\n                                                vertical_alignment=\"bottom\"\n                                                )\n        with col1:\n            page_nav_label = st.markdown('&lt;p style=\"font-size:1.3rem;\"&gt;Page&lt;\/p&gt;', unsafe_allow_html=True)\n\n        with col2:\n            page_nav_page_info = st.markdown(f'&lt;p style=\"font-size:1.3rem;\"&gt;{self.current_page} of {self.total_pages}&lt;\/p&gt;', unsafe_allow_html=True)\n            \n        with col3:\n            page_nav_prior_page = st.button(label=\"&amp;lt;&amp;lt;\",\n                 type=\"secondary\", \n                 disabled=(self.current_page &lt;= 1),\n                 on_click=self.move_current_page,\n                 args=('decrease',)\n                 )\n             \n        with col4:\n            page_nav_next_page = st.button(label=\"&amp;gt;&amp;gt;\",\n                 type=\"secondary\", \n                 disabled=(self.current_page &gt;= self.total_pages),\n                 on_click=self.move_current_page,\n                 args=('increase',)\n                 )\n        \n        with col5:\n            # intentionally left the column empty  \n            page_nav_empty = st.empty()  # just a spacer column\n\n        # return the page_nav controls\n        return (page_nav_label, \n                page_nav_page_info, \n                page_nav_prior_page,\n                page_nav_next_page,\n                page_nav_empty\n                )<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The method will return five streamlit widgets that help the user navigate between pages in the data_editor widget.  I used st.markdown widget to pass the label and in the current page information. The other benefit is that you can pass css style properties.  You do have to use the unsafe_allow_html property to True, for the Streamlit to apply the css style properties.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The buttons will navigate to prior page or next page, and use a call back to <a href=\"#move_current_page\" data-type=\"internal\" data-id=\"#move_current_page\">move_current_page<\/a> method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"selectbox-rows-per-page\">selectbox_rows_per_page<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def selectbox_rows_per_page(self):\n        \"\"\"\n        Creates the streamlit drop down UI element, based on the table\n        names stored in the \/config\/sql_column_config.yaml file\n\n        Returns\n        -------\n        streamlit.selectbox\n\n        \"\"\"\n        return (st.selectbox(label=\"Rows per Page:\",\n                             options=streamlit_config.selectbox.rows_per_page.OPTIONS,\n                             index=0,\n                             on_change=self.update_rows_per_page,\n                             key=\"rows_per_page\",\n                             ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Returns a select box that allows the user to determine how many rows to show in the data_editor widgets.  The values are defined in the config \/ streamlit_config.yaml file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"selectbox-table\">selectbox_table<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def selectbox_table(self):\n        \"\"\"\n        Creates the streamlit drop down UI element, based on the table\n        names stored in the \/config\/sql_column_config.yaml file\n\n        Returns\n        -------\n        streamlit.selectbox\n\n        \"\"\"\n        table_options = [f\"{sql_column_config.table[tbl].SCHEMA}.{sql_column_config.table[tbl].NAME}\" for tbl in sql_column_config.table]\n\n        with st.container():\n            col_left, col_mid, col_right = st.columns([4,4,4])\n            with col_left:\n                select_box_table = st.selectbox(label=\"Select a table to edit:\",\n                                                options=table_options,\n                                                index=None,\n                                                placeholder='Select a Table',                             \n                                                )\n            with col_mid:\n                # intentionally left the column empty\n                st.empty()\n            with col_right:\n                # intentionally left the column empty\n                st.empty()\n\n        return (select_box_table)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Returns a streamlit select_box widget, listing the schema + table name to the user.  The schema and table names are listed in the config \/ sql_column_config.yaml file. The select_box widget is wrapped in a container allowing for formatting the width on the page.  Default behavior is for the select_box widget to use the full width of the page. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"query-success\">query_success<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def query_success(self):\n        \"\"\"\n        Returns\n        -------\n        boolean\n            indicates if the last query run was completed successfully\n        \"\"\"\n        return(self._query_success)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Returns a Boolean, indicates if the last query run was completed successfully.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"update-rows-per-page\">update_rows_per_page<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def update_rows_per_page(self) -&gt; None:\n        \"\"\"\n        Update number of rows per page to return\n        \"\"\"\n        self.rows_per_page = st.session_state['rows_per_page']<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Reads from the streamlit sesssion_state dictionary, number of rows per page to return. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"ge5ab805fe105\">StreamlitAddData<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The class inherits from the StreamlitHandler class with additional methods for adding rows to the table.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"gf97c6fc85f8f\">Methods<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"ga875b89b4a99\">fetch_empty_table<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def fetch_empty_table(self, table_name:str = None):\n        \"\"\"\n        Create an empty data_editor widget with appropriate columns for\n        user to input data to be inserted into the source table\n\n        Parameters\n        ----------\n        table_name : str\n            Name of source table\n\n        Returns\n        -------\n        streamlit data_editor\n\n        \"\"\"\n        query_params = (self._source_schema, self._base_table_name,)\n\n        column_info = self.data_handler.get_table_columns(\n            query=self.query,\n            filter_columns=sql_column_config.table[self._base_table_name].COLUMNS,\n            query_params=query_params\n            )\n        \n        data = pd.DataFrame({column_name: pd.Series(dtype=column_type) for column_name, column_type in column_info.items()})\n    \n        return(st.data_editor(data=data,\n                              num_rows=\"dynamic\",\n                              use_container_width=True\n                              ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Create the streamlit data_editor widget.  However we need to know which columns and their corresponding datatypes to add to the data_editor widget.  In order to do so wee will query the meta data of the table using the get_table_columns method from the data_handler class.  The method will also align the data types from Microsoft SQL to pandas dataframe datatypes.  Additionally we will only show to the user, the columns specified in the config file, config \/ sql_column_config.yaml<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"g6fe09859ca7e\">add_source_data<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def add_source_data(self, new_data: pd.DataFrame):\n        \"\"\"\n        Add new rows to the source\n\n        Parameters\n        ----------\n        table_name : pandas DataFrame\n            DataFrame will hold data to be inserted in to the source table\n        \"\"\"\n        for row_ctr in range(0, new_data.shape[0]):\n            self.data_handler.add_row(self.source_table_name,\n                                      new_data.loc[[row_ctr]]\n                                      )\n        self._query_success = True<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Now, we want to add the user input as a row to the table.  The data will be passed from the data_editor widget to thhe database using the data_handler add_row method.  We loop through the data_editor rows because the user may try adding multiple rows at the same time.  If all of the rows are added successfully we set the _query_success flag to True.  The flag is used by the <a href=\"#query-success\">query_success<\/a> method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"streamliteditdata\">StreamlitEditData<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The class inherits from the StreamlitHandler class with additional methods for editing rows in the table.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"methods\">Methods<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"data-editor-formatted\">data_editor_formatted<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def data_editor_formatted(self):\n        \"\"\"\n        Will return a formatted data_editor widget\n\n        Returns\n        -------\n        streamlit data_editor widget\n\n        \"\"\"\n        # offset = (current_page - 1) * rows per page\n        # fetch next = rows per page\n        self.fetch_table_data(row_params=((self.current_page-1)*self.rows_per_page,\n                                          self.rows_per_page,\n                                          ))\n\n        return(st.data_editor(data=self.table_data, \n                              key=\"data_editor_update\",\n                              use_container_width=True,\n                              column_order=self._column_order,\n                              ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Using the <a href=\"#fetch-table-data\">fetch_table_data<\/a> method will fetch the data from the source table. Will pass the offset and number of rows to return to the query to the row_params parameter.  The values have to be passed as a tuple.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Return the streamlit data_editor widget to the user.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"update-source-data\">update_source_data<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def update_source_data(self, edited_data: pd.DataFrame)-&gt; None:\n        \"\"\"\n        Detect changes by comparing the original and edited DataFrames\n\n        Parameters\n        ----------\n        edited_data : pandas DataFrame\n            Will extract out the rows and columns that need to be updated\n        \"\"\"\n\n        if not edited_data.equals(self.table_data):\n            # get what has changed\n            changes = edited_data.compare(self.table_data)\n            for index, row in changes.iterrows():\n                # Update only the changed columns for each modified row\n                row_id = self.table_data.loc[index, self.primary_key_column_name]\n                changed_columns = row.index.get_level_values(0).unique()\n                \n                # Extract only the changed values to update in the database\n                update_data = edited_data.loc[index, changed_columns].to_dict()\n                self.data_handler.update_row(table=self._remote_table_name, \n                                             update_data=update_data,\n                                             primary_key_column=self.primary_key_column_name, \n                                             row_id=row_id)\n            self._query_success = True<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">First we do a comparison to the source data and the data_editor widget data. Both sets are data are stored in a Pandas data frame allowing us to quickly check if they are equal. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If they are not equal, we grab the changes by using the data frame .compare method.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we loop through the rows and columns to determine what has changed. We only want to update the column data that has changed. We store the changes in the update_data in a dictionary variable.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly we call the data_handler update_row method to update the source table.  The method allows for sending multiple row updates. However each row that has changed will create an UPDATE sql statement to be executed. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly if all the updates have been process the we set the _query_success flag to True.  The flag is used by the <a href=\"#query-success\">query_success<\/a> method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"streamliteditdata\">StreamlitDeleteData<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The class inherits from the StreamlitHandler class with additional methods for editing rows in the table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Although in the tutorial I am showing how to delete the rows, I would suggest only deleting table rows when you are keeping table history.  If don&#8217;t have temporal (system-versioned) tables I would suggest the table structure uses a delete column to mark the row as deleted. And you an update query to change the column value.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"methods\">Methods<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"data-editor-formatted\">data_editor_formatted<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">        \"\"\"\n        Will return a formatted data_editor widget\n\n        Returns\n        -------\n        streamlit data_editor widget\n        \"\"\"\n        # get the source table data\n        self.fetch_table_data(row_params=((self.current_page-1)*self.rows_per_page,\n                                          self.rows_per_page,\n                                          ))\n\n        # add column to data set\n        self.table_data[\"Select\"] = False\n        \n        # set the column order\n        self._column_order = ('Select',) + self._column_order\n\n        return(st.data_editor(data=self.table_data, \n                              key=\"data_editor_delete\",\n                              use_container_width=True,\n                              column_order=self._column_order,\n                              ))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Using the <a href=\"#fetch-table-data\">fetch_table_data<\/a> method will fetch the data from the source table. Will pass the offset and number of rows to return to the query to the row_params parameter.  The values have to be passed as a tuple.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once the data is returned will add a column to the data frame named Select, and set its value to False. The column will shown as a checkbox in the data_editor widget.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we reset the column order so the first column is the Select column.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly, return the streamlit data_editor widget to the user.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"prompt-for-confirmation\">prompt_for_confirmation<\/h5>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def delete_source_data(self, delete_data: pd.DataFrame):\n        \"\"\"\n        After user confirms the deletion execute the delete \n        commands against the source database\n\n        Parameters\n        ----------\n        edited_data : pandas DataFrame\n            DataFrame only contains the rows to be deleted.\n\n        \"\"\"\n        self.deletion_requested = False\n\n        # Retrieve selected rows from session state for deletion\n        for row_id in delete_data[self.primary_key_column_name]:\n            self.data_handler.delete_row(self._source_table_name, \n                                         primary_key_column=self.primary_key_column_name,\n                                         row_id=row_id)\n        self._query_success = True<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">First will reset the flag deletion_requested to False, so we don&#8217;t try and delete the same rows twice, if the deletion fails.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next will extract all of the primary key ids that have been selected.  In the <a href=\"#pages-delete-row-main-confirmation\">delete_row.py callback<\/a> to delete_source_data, we filtered the rows being passed to the method through the args.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">args=(table_data.query(\"Select == True\"),))<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The Pandas data frame query method ensures we are only deleting rows that the user selected.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next page will talk about the DataHandler Class<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"data-handler-class\">Data Handler Class<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The class has a few helper methods to aid in sending data between Microsoft SQL and Pandas data frames. The class uses the PandasSqlHandler class.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#data-handler-class\">Data Handler Class<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#methods-1\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#adjust-dataframe-types\">_adjust_dataframe_types<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#sql-to-pandas-dtype\">__sql_to_pandas_dtype<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#get-table-columns\">get_table_columns<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#add-row\">add_row<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#update-row\">update_row<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=5#delete-row\">delete_row<\/a><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"methods-1\">Methods<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">__init__<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __init__(self, db_handler:PandasSqlHandler):\n        self.db_handler = db_handler<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Assign the PanadaSqlHandler class. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"adjust-dataframe-types\">_adjust_dataframe_types<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def _adjust_dataframe_types(self, df):\n        \"\"\" Adjusts the DataFrame's data types to be compatible with SQL Server \"\"\"\n        for column in df.columns:\n            match df[column].dtype:\n                case 'float64':\n                    df[column] = df[column].astype('float32')  # Adjust precision if needed\n                case 'int64':\n                    df[column] = df[column].astype('int32')  # Adjust for SQL Server int handling\n                case 'object':\n                    df[column] = df[column].astype('str')  # Ensure text data type compatibility                \n        return df<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Loop through the columns and set the convert the pandas data type to a data type that Microsoft SQL can support.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"sql-to-pandas-dtype\">__sql_to_pandas_dtype<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __sql_to_pandas_dtype(self, sql_type:str):\n        \"\"\"converts sql data type to appropirate pandas datatype\n        \n        Parameters\n        ----------\n        sql_type : str \n        \"\"\"\n        match sql_type.lower():\n            case 'int':\n                return np.int32\n            case 'varchar':\n                return str\n            case 'nvarchar':\n                return str\n            case 'date' | 'datetime' | 'datetime2':\n                return 'datetime64[ns]'\n            case 'bit':\n                return 'bool'\n            case _ :\n                return object<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Convert Microsoft SQL datatypes to applicable Pandas data frame datatype.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"get-table-columns\">get_table_columns<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def get_table_columns(self, query:str, filter_columns:list, query_params:tuple=None):\n        \"\"\"\n        Get the meta data about the table, including the column name\n        and sql data type.  The sql data type will be converted to a pandas\n        data type.\n\n        Parameters\n        ----------\n        query : str \n            query used to find the list of columns and data types\n        filter_columns : list \n            to filter columns to show to user\n        query_params : tuple, optional\n            parameters to be inserted into the query\n        \n        \"\"\"\n        # convert filter_columns to a string\n        filter_columns = \"','\".join(filter_columns)\n       \n        # get the data frame and apply filter to it\n        metadata_df = self.db_handler.read_table(query=query, params=query_params)\n        metadata_df = self._adjust_dataframe_types(metadata_df).query(f\"COLUMN_NAME in ['{filter_columns}']\")\n        \n        column_info = dict(zip(metadata_df[\"COLUMN_NAME\"],\n                               metadata_df[\"DATA_TYPE\"].apply(self.__sql_to_pandas_dtype)\n                               ))\n        \n        return column_info<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Filter columns are stored in the config \/ sql_column_config.yaml file.  We may only want to show certain columns to the user.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we query the database and read the column meta data.  Then we drop any rows that are not in the filter columns list.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly we return a dictionary with the column name and the converted the data_type from Microsoft SQL data type to a Pandas datatype.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"add-row\">add_row<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def add_row(self, table_name:str, data: pd.DataFrame):\n        \"\"\"\n        Ensure compatible data types for SQL Server before inserting\n\n        Parameters\n        ----------\n        table_name : str\n            The name table to add a row to. Table name should include\n            the schema and table name.  i.e\n            \n            - dbo.my_table_name\n            - log.my_other_table\n\n        data : pandas.DataFrame\n            Data frame holding the data to be inserted into the source table\n        \"\"\"\n        \n        if not isinstance(data, pd.DataFrame) or len(data) != 1:\n            raise ValueError(\"row data should be a pandas DataFrame with a single row\")\n        \n        # Pass the row data to the PandasSQLHandler to handle the database insertion\n        #self.db_handler.insert_row(table_name, row_data)\n        row_dict = data.iloc[0].to_dict()\n        # Create an SQL INSERT statement with placeholders\n        columns = ', '.join(row_dict.keys())\n        placeholders = ', '.join([f\":{col}\" for col in row_dict.keys()])\n        query = f\"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})\"\n        self.db_handler.execute_query(query, row_dict)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">We check that the data is a Pandas data frame and there is only one data frame.  If there is an issue we raise an error. The method does allow to insert multiple rows at the same time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We use the first row to create a dictionary variable called row_dict. It will be used to create the placeholders. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we grab the list of column names and store them in the columns variable.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we create the placeholders, and join them together as a string. The placeholders are the values to insert into the table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next we will format the query string, inserting the table name, columns, and placeholders.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly will execute the query.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"update-row\">update_row<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">def update_row(self, table:str, update_data:dict, primary_key_column:str, row_id):\n        \"\"\"\n        Build an UPDATE query dynamically based on changed column values\n\n        Parameters\n        ----------\n        table_name : str\n            The name table to update row data. Table name should include\n            the schema and table name.  i.e\n            \n            - dbo.my_table_name\n            - log.my_other_table\n\n        update_data : dict\n           Dictionary holding column name value to be updated in the \n           source table\n\n        primary_key : str\n            column name that represents the primary key.\n\n        row_id : any\n            The row to be upated.\n\n        \"\"\"\n        # Convert all values to native Python types (e.g., int, str)\n        update_data = {key: (value.item() if hasattr(value, 'item') else value) for key, value in update_data.items()}\n\n        row_id = int(row_id)\n        set_clause = \", \".join([f\"{col} = :{col}\" for col in update_data.keys()])\n        query = f\"UPDATE {table} SET {set_clause} WHERE {primary_key_column} = :row_id\"\n\n        # Execute the query with the values for each column and row ID\n        params = {**update_data, 'row_id': row_id}\n        \n        self.db_handler.execute_query(query, params)   <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Similiar to the add_row method, we will prepare the query and merge into a string and execute.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First create a dictionary update_data for all the columns and their applicable values to be updated. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next due a conditional conversion on row_id so that it is converted to int 32 if an int 64 value.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next create the string that will hold the column names and the updated value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next merge the update_data dictionary and row_id value into one dictionary that can be passed to the execute_query method. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly execute the query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"delete-row\">delete_row<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def delete_row(self, table_name:str, primary_key_column:str, row_id):\n        \"\"\"\n        Build a delete query dynamically based on changed column values\n\n        Parameters\n        ----------\n        table_name : str\n            The name table to update row data. Table name should include\n            the schema and table name.  i.e\n            \n            - dbo.my_table_name\n            - log.my_other_table\n\n        update_data : dict\n           Dictionary holding column name value to be updated in the \n           source table\n\n        primary_key : str\n            column name that represents the primary key.\n\n        row_id : any\n            The row to be upated.\n\n        \"\"\"\n        query = f\"DELETE FROM {table_name} WHERE {primary_key_column} = {row_id}\"\n        self.db_handler.execute_query(query)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Create a query string to indicate which row to delete from the table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First create the string merging the table_name and the row_id.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Execute the sql query using the execute_query method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next page will talk about the PandasSQLHandler Class<\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"pandassqlhandler\">PandasSqlHandler<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The class has a few helper methods for setting up the database connection, assigning the database environment, reading tables, and executing queries.<\/p>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#pandassqlhandler\">PandasSqlHandler<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#methods-2\">Methods<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#init\">__init__<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#assign-conn-str\">__assign_conn_str<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#read-table\">read_table<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=6#execute-query\">execute_query<\/a><\/li><\/ol><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"methods-2\">Methods<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"init\">__init__<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __init__(self, environment):\n        \"\"\"Class to run SQL queries against MSFT database\n        \n        Parameters\n        ----------\n        None\n\n        Methods\n        ----------\n        run_query(user_query) -&gt; dataframe\n            returns the query results in a list of dictionaries\n        \"\"\"\n    \n        self._environment:str   = environment\n        self._conn_str:str      = self.__assign_conn_str()\n        self._conn_url          = sql.engine.URL.create(\"mssql+pyodbc\", query={\"odbc_connect\": self._conn_str})\n        self.engine             = sql.create_engine(self._conn_url)   <\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">First assign the environment.  Environment configurations are set up in the config\/sql_config.yaml file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next based on the environment, return the SQL_Driver string from the config\/sql_config.yaml file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next combine the connection elements into the URL formatted string<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Lastly invoke the sql alchemy create engine method.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"assign-conn-str\">__assign_conn_str<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def __assign_conn_str(self) -&gt; str:\n        match self._environment.lower():\n            case 'dev':\n                return sql_config.sql.dev.SQL_DRIVER\n            case 'prod':\n                return sql_config.sql.prod.SQL_DRIVER\n            case _ :\n                return sql_config.sql.dev.SQL_DRIVER<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The method will read config\/sql_config.yaml file, and return the applicable SQL_Driver string from the config\/sql_config.yaml file.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"read-table\">read_table<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def read_table(self, query, params=None) -&gt; pd.DataFrame:\n        \"\"\"\n        Run the SQL query and return the results in a Pandas dataframe\n\n        Returns\n        ----------\n        Pandas Dataframe\n        \"\"\"\n        return pd.read_sql(sql=query, con=self.engine, params=params)<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Using Pandas package, call the read_sql method and return a Pandas Dataframe.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"execute-query\">execute_query<\/h4>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">    def execute_query(self, user_query:str, params=None) -&gt; None:\n        \"\"\"\n        Run and commit the sql query\n        \"\"\"\n        with self.engine.connect() as connection:\n            connection.execute(sql.text(user_query), params or {})\n            connection.commit()<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">With the sql database connection defined earlier, execute the sql query and commit the query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">On the next page we will discuss the configuration files.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"configuration-files\">Configuration Files<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We will use .yaml files to help with configuring the app for the local environment.<\/p>\n\n\n\n<nav aria-label=\"Table of Contents\" class=\"wp-block-table-of-contents\"><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#configuration-files\">Configuration Files<\/a><ol><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#sql-config-yaml\">sql_config.yaml<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#streamlit-config-yaml\">streamlit_config.yaml<\/a><\/li><li><a class=\"wp-block-table-of-contents__entry\" href=\"https:\/\/eipsoftware.com\/musings\/streamlit-crud-app\/?page=7#sql-column-config-yaml\">sql_column_config.yaml<\/a><\/li><\/ol><\/li><\/ol><\/nav>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"sql-config-yaml\">sql_config.yaml<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:yaml decode:true \">sql:\n  dev:\n    SERVER_NAME: \"DEV-SERVER\"\n    DATABASE_NAME: \"MY_DATABASE\"\n    SQL_DRIVER: &gt;\n      Driver=SQL Server;\n      Server=DEV-SERVER;\n      Database=MY_DATABASE;\n      Trusted_Connection=yes;\n\n  prod:\n    SERVER_NAME: \"PROD-SERVER\"\n    DATABASE_NAME: \"MY_DATABASE\"\n    # for LINUX server\n    SQL_DRIVER: &gt;\n      Driver={ODBC Driver 18 for SQL Server};\n      Server=PROD-SERVER.azure.com;\n      Database=MY_DATABASE;\n      Trusted_Connection=yes;\n      TrustServerCertificate=yes;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">use sql: for the root.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then add the environment names you would like to define, in this case I defined an environment for dev, and prod.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next define the SQL_Driver connection string. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"streamlit-config-yaml\">streamlit_config.yaml<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Used for configuration options for Streamlit UI elements.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:yaml decode:true \">selectbox:\n  rows_per_page:\n    OPTIONS:\n      - 10\n      - 20\n      - 50\n      - 100<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The select box, allows for showing # of rows per page. It will paginate the data shown in the data_editor widgets.  <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"sql-column-config-yaml\">sql_column_config.yaml<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The configuration file defines what tables and what columns you wish to return to the user.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:yaml decode:true \">table:\n  countryregion:\n    SCHEMA: person\n    NAME: countryregion\n    PRIMARY_KEY: \"ID\"\n    COLUMNS:\n    - CountryRegionCode\n    - Name\n    - ModifiedDate\n  person:\n    SCHEMA: person\n    NAME: person\n    PRIMARY_KEY: \"BusinessEntityID\"\n    COLUMNS:\n    - PersonType\n    - NameStyle\n    - Title\n    - FirstName\n    - MiddleName\n    - LastName\n    - EmailPromotion\n    - Suffix\n  location:\n    SCHEMA: production\n    NAME: location\n    PRIMARY_KEY: \"LocationID\"\n    COLUMNS:\n    - Name\n    - CostRate\n    - Availability    \ninfo:\n  columns:\n    QUERY: info.columns\n  count:\n    QUERY: sys.row_count.sql<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">There are two base groups, table and info.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The table group specifies which tables the user can modify.  The key is that the name of the table, needs to match the file placed in the query directory.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For our example we allow the user to update the production.location table. Therefore there needs to be a corresponding file in the \/ query directory, labeled query \/ production.location.sql<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the second base group there are two informational queries. info.columns, which is used to return the meta data from the database.  And the second query sys.row_count which is used to look at the sys tables and get the row count of files.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">On the next page, we will review the example query files.<\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"query\">Query<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We have two informational queries.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"info-columns\">info.columns<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\t[INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT]\n\t\t, [INFORMATION_SCHEMA].[COLUMNS].[COLLATION_NAME]\nFROM\t[INFORMATION_SCHEMA].[COLUMNS]\nWHERE\t[INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] \t\t= ?\n\t\tAND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME]\t\t= ?\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">File name:  query \/ info.columns.sql<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Return the columns meta data for a specified table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"sys-row-count\">sys.row_count<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">SELECT\tSUM(row_count)\t\t\trow_count\nFROM\tsys.dm_db_partition_stats\nWHERE\tobject_id = OBJECT_ID(?)\n\t\tAND index_id &lt; 2\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">File name: query \/ sys.row_count.sql<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Shows the row counts for the specified table.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"table-query-example\">Table Query &#8211; Example<\/h3>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:python decode:true \">SELECT\t\t  Name\n\t\t, CostRate\n\t\t, Availability\n\t\t, ModifiedDate\nFROM\t\tproduction.location\nORDER BY\tLocationID\nOFFSET\t\t? ROWS FETCH NEXT ? ROWS ONLY\n;<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">File name: query \/ production.location<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Need to use placeholders (?) to allow for the OFFSET statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<h2 class=\"wp-block-heading\" id=\"github-files\">Github Files<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Github files are located here.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/github.com\/mmooney512\/musings\/tree\/main\/python%20\/streamlit%20table%20editor\">Streamlit Table Editor<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I will walk through using the Streamlit python framework to build a web based app that you can use to interact with a database. The app will allow you to Read, Update, and Delete tables in your database.<\/p>\n","protected":false},"author":1,"featured_media":807,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[75,3,8,4],"tags":[14,28,68,69,70,71],"series":[],"class_list":["post-714","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-streamlit","category-python","category-pandas","category-code","tag-sql","tag-python","tag-website","tag-streamlit","tag-crud","tag-tutorial"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/714","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=714"}],"version-history":[{"count":73,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/714\/revisions"}],"predecessor-version":[{"id":866,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/714\/revisions\/866"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media\/807"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=714"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=714"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=714"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=714"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}