{"id":12015,"date":"2021-12-10T07:56:13","date_gmt":"2021-12-10T06:56:13","guid":{"rendered":"https:\/\/flaven.fr\/?p=12015"},"modified":"2026-09-16T10:11:59","modified_gmt":"2026-09-16T08:11:59","slug":"quick-poc-for-a-all-in-one-that-provides-an-seo-dashboard-made-with-streamlit-managing-screaming-frog-automation-storing-results-in-a-database-sqlite-and-create-data-analysis-graphics-for-seo-repo","status":"publish","type":"post","link":"https:\/\/flaven.fr\/2021\/12\/quick-poc-for-a-all-in-one-that-provides-an-seo-dashboard-made-with-streamlit-managing-screaming-frog-automation-storing-results-in-a-database-sqlite-and-create-data-analysis-graphics-for-seo-repo\/","title":{"rendered":"Quick POC for a All-in-one that provides an SEO dashboard made with Streamlit, managing Screaming Frog automation, storing results in a Database (SQLite) and create data-analysis graphics for SEO reports"},"content":{"rendered":"<p>So, my objective was to build a dashboard with Streamlit that automate Screaming Frog SEO Spider for large time saves and fast audits then manage the output (csv reports), save it into a SQLite database and analyse it with creating graphics. <b>You can find all the code on my github account at <a href=\"https:\/\/bit.ly\/3oAPuBP\" target=\"_blank\" rel=\"noopener\">https:\/\/bit.ly\/3oAPuBP<\/a><\/b><\/p>\n<h2>I. SEO POC<\/h2>\n<h3>1. Methodology<\/h3>\n<p>Like always I am heading for a POC (Proof of Concept). So, pragmatism over theory. POC does not have to be perfect or compliant to some coding best practices or even logical at some point! The real value occurs often during the POC in some low noise signals, so be open minded and listen&#8230; As it is said &#8220;Knowledge speaks, Wisdom listens&#8221;. <b>In this exploration phase, the purpose remains to achieve a fixed goal in a limited amount of time, avoiding spending 2 years for instance on a POC! \ud83d\ude42 That&#8217;s it for the methodology.<\/b><\/p>\n<h3>2. Value<\/h3>\n<p>As a PO, sometime for website, I need to have an eye on SEO KPIs. Even so, I am far from being a specialist, I need to grab and quickly overview key indicators (kinda a PO data-science oriented) and henceforth gather SEO best practices to fine-tune the website eventually.<br \/>\n<b>But, as far as possible, it has to be made by a &#8220;tool&#8221; that ease these processes so it should be industrialized and automatized. The background idea is always to chain actions between them in order to improve productivity and facilitate decision-making. That&#8217;s it for the value proposal. Just remember that learning has a cost and experimental work is expensive! So, Timeboxing exploration is a duty!<\/b><\/p>\n<h3>3. Individual<\/h3>\n<p>What about my objectives me as an individual, they are several:<\/p>\n<ul>\n<li>Delegate tedious work to machines in order to give me more free time<\/li>\n<li>Improving my Python&#8217;s practice<\/li>\n<li>Give a more data-science flavor to my resume to become a mixed flavored data-science PO for digital! By the way, for me, Streamlit has solved this paradox between investing on web or data-science. With Streamlit I can do both: (1) IA or Machine learning web tools or (2) handy Web Tools for other dedicated job&#8217;s purposes.<\/li>\n<\/ul>\n<p><b>This POC is definitely in the second category. That&#8217;s it for my personal investment. As a first conclusion, I will say that any POC is a always a good tradeoffs between personal and professional objectives.<\/b><\/p>\n<h3>4. Sources<\/h3>\n<p>This post has been also inspired by these 2 posts but I believe I have taken in a more personal direction the challenge exposed in these 2 posts.<\/p>\n<ul>\n<li>An SEO guide for automating Screaming Frog with Python<br \/><a href=\"https:\/\/www.rocketclicks.com\/client-education\/an-seo-guide-for-automating-screaming-frog-with-python\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.rocketclicks.com\/client-education\/an-seo-guide-for-automating-screaming-frog-with-python\/<\/a>\n    <\/li>\n<li>Badass SEO: Automate Screaming Frog For Large Time Saves and Fast Audits<br \/><a href=\"https:\/\/cometfuel.com\/learn\/automate-screaming-frog\/#using-terminal-mac\" target=\"_blank\" rel=\"noopener\">https:\/\/cometfuel.com\/learn\/automate-screaming-frog\/#using-terminal-mac<\/a><\/li>\n<\/ul>\n<p>I quote this post cause I need to remember it, about the inadequacies sometimes between tech and reality.<\/p>\n<blockquote><p>\nThe first job of an application architect is to &#8220;right size&#8221; the business or enterprise with the most efficient technology for their use case; not just saddle them with your pet preferences because it&#8217;s the latest &#8220;fad-tech&#8221; from Google or it\u2019s what they taught you in school.<\/p><\/blockquote>\n<p>Source: <a href=\"https:\/\/beau-beauchamp.medium.com\/php-is-killing-python-2be459364284\" target=\"_blank\" rel=\"noopener\">https:\/\/beau-beauchamp.medium.com\/php-is-killing-python-2be459364284<\/a><\/p>\n<h2>II. Build up the app (the POC)<\/h2>\n<p>Ok let&#8217;s build our dashboard! Like many softwares, Screaming Frog SEO Spider offers the ability to run without User Interface via Command line. With help of Streamlit, you can build your own webapp (GUI) to industrialize, automate repetitive tasks then transform raw data into useful information. That is my objective.<\/p>\n<h3>1. Guidelines<\/h3>\n<p>So, let&#8217;s build this GUI with these few simple guidelines defined earlier:<\/p>\n<ul>\n<li><b>Streamlit<\/b> library will be the &#8220;wrapper&#8221; for our application. (<a href=\"https:\/\/streamlit.io\/\" target=\"_blank\" rel=\"noopener\">https:\/\/streamlit.io\/<\/a>)<\/li>\n<li><b>SQLite<\/b> will be used as database. I gave up completely MySQL as SQLite is a serverless database. It gives more flexibility to implement a database behind an app, that is the main reason why SQLite is often used in mobile development. (<a href=\"https:\/\/sqlite.org\/index.html\" target=\"_blank\" rel=\"noopener\">https:\/\/sqlite.org<\/a>)<\/li>\n<li><b>SQLAlchemy<\/b> will be use to query the database to offer again flexibility and minimize the written code in Python. Thanks to this abstraction (ORM), it is possible to connect to a large number of databases (Firebird, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, Sybase) and above all to standardize and considerably minimize your code in order to make it readable by other people and finally reusable. That&#8217;s savvy! (<a href=\"https:\/\/www.sqlalchemy.org\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.sqlalchemy.org\/<\/a>)<\/li>\n<li><b>Screaming Frog SEO Spider<\/b> is a website crawler that helps you improve onsite SEO. (<a href=\"https:\/\/www.screamingfrog.co.uk\/seo-spider\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.screamingfrog.co.uk\/seo-spider\/<\/a>)<\/li>\n<\/ul>\n<p><b>Behind all these technical recommendations, there are few key ideas that again should be exposed because these ideas have also prevailed in this POC.<\/b><\/p>\n<h3>2. Few more ideas<\/h3>\n<p><b>2.1 praise for simplicity<\/b><br \/>\nAs a PO, you might suffer from the information and tools dissemination that prevent you from having a big picture of your products. So handling everything in the same screen helps to minimize information&#8217;s confusion. It should be the target of any monitoring application. I will call it praise for simplicity.<br \/>\n<b>For an application, think to centralize and to minify the info, never unnecessarily expand it. That&#8217;s a waste of time for user.<\/b><\/p>\n<p><b>2.2 use cases decision matrix<\/b><br \/>\nAlways think of the most extreme use cases and the non-user use cases for your web application. It will force you to<br \/>\nlook beyond the objective and look for the most attractive use cases. That is also a practical strategy to shorten the exploration phase to look for attractive use cases in order to save time on the primary exploration phase.<br \/>\n<b>What the purpose of building an application that won&#8217;t be use ?<\/b><\/p>\n<p><b>2.3 code prospecting for usages<\/b><br \/>\nCapture the most possible information about similar existing web application like the one you want to create. For<br \/>\ninstance, browsing through Git to grab some code you will prevent you from reinventing the wheel. <b>That&#8217;s a usages benchmark that can be withdrawn from existing codes.<\/b><\/p>\n<p><b>2.4 collect user feedback first then analyse<\/b><br \/>\nLike for any data science project, try to collect the data yourself by mingling with other users than you. One advice if looking user feedback, be sure to record everything of their responses. While you are listening, your bandwidth is very low and it is almost impossible to detect the relevant vs the irrelevant in their feedback. You will make analysis it but later!<\/p>\n<h3>3. The DB: using SQLITE<\/h3>\n<p>Instead of MySQL, I am using SQLITE and like I said before: it is easier to implement but the syntax slightly differs from MySQL.<\/p>\n<p><b>3.1 Advices<\/b><br \/>\nYou can name your database with both extension <code>.sqlite3<\/code> or <code>.db<\/code><br \/>\ne.g. streamlit_sqlalchemy_example.sqlite3 or streamlit_sqlalchemy_example.db<\/p>\n<pre lang=\"python\">\r\n# name of your database\r\nengine = create_engine(\r\n        'sqlite:\/\/\/data\/streamlit_sqlalchemy_example.sqlite3')\r\n<\/pre>\n<p>A good practice is to put the db file in a directory then you find it easily e.g. data is my directory name for db<br \/>\nfiles.<\/p>\n<pre lang=\"python\">\r\n# Valid SQLite URL forms are:\r\nsqlite:\/\/\/:memory: (or, sqlite:\/\/)\r\nsqlite:\/\/\/relative\/path\/to\/file.db\r\nsqlite:\/\/\/\/absolute\/path\/to\/file.db\r\n<\/pre>\n<p>2 important lines to stress how to connect, for the rest, you can check the files available on my github account.<\/p>\n<pre lang=\"python\">\r\n# this line create the empty tables\r\n    Base.metadata.create_all(engine)\r\n\r\n# it imports the table that you need e.g Products\r\nfrom streamlit_sqlalchemy_database import Products\r\n<\/pre>\n<p><b>3.2 SQLite command reminder<\/b><br \/>\nSome useful commands for SQLite<\/p>\n<pre lang=\"sql\">\r\n# to get into SQLITE3, just type the command sqlite3 in the console\r\nsqlite3\r\n\r\n#.open \/your-path\/source.db\r\n# open a connection extension can be .db\r\n.open \/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/data\/screamingfrog_websites_crawls_all.db\r\n\r\n# open a connection extension can be .sqlite3\r\n.open \/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/streamlit_sqlalchemy_example\/data\/screamingfrog_websites_crawls_all_new_1.sqlite3\r\n\r\n# show tables\r\n.tables\r\n\r\n\r\n# make a dump\r\n.output \/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/sqlite_source_dump_2.sql\r\n.dump\r\n\r\n\r\n# read dump\r\n.read \/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/sqlite_source_dump_2.sql\r\n\r\n\r\n- command\r\nSELECT COUNT(*) FROM websites;\r\nSELECT * FROM websites;\r\n\r\nSELECT COUNT(*) FROM crawls;\r\nSELECT * FROM crawls;\r\n\r\n- delete tables\r\nDROP TABLE websites;\r\nDROP TABLE crawls;\r\n\r\n\r\n- delete\r\n# DELETE FROM table_name;\r\nDELETE FROM websites;\r\nVACUUM;\r\n<\/pre>\n<h3>4. Describing Wireframe Screens<\/h3>\n<p><b>My app project is divided in 4 main screens and here is the navigation chosen below:<\/b><\/p>\n<ul>\n<li>screen_1 :: General<\/li>\n<li>screen_2 :: Crawl<\/li>\n<li>screen_3 :: Parse and Insert<\/li>\n<li>screen_4 :: Analyze<\/li>\n<\/ul>\n<p>Making a Wireframe, in addition to being time consuming, also presents the disadvantage that you must define different screen status every time the user is<br \/>\nhitting a button! Honestly, it is obvious that the screens resemblance between the Wireframe and the real application mitigate this step interest. <b>In a way, design screens for your web-application is useless as Streamlit is so quick to build up the screens itself.<\/b> <\/p>\n<p><b>To illustrate this non-interest, I put side by side Wireframe (made quickly with Balsamiq) and the real app (made quickly also with Streamlit). The only thing that may have a real interest is to summarize for each screen, with simple words, the User Experience expected like a User Story. I know Graphic Designers or UX Designers may hate me for this. Below my app User Stories.<\/b><\/p>\n<ul>\n<li>navigation :: As a user, I can select<br \/>\n            destination in a main navigation dropdown menu on the left.<\/li>\n<li>screen_1 :: General :: As a user, I can add into the database a website (title, url) to be crawled later.<\/li>\n<li>screen_2 :: Crawl :: As a user, I can launch the crawl with Screaming Frog by selecting a website in a drop-down menu. By clicking on a button to launch the crawl, it will be print out the Screaming Frog command-line and executed it.<\/li>\n<li>screen_3 :: Parse and Insert :: As a user, I can parse Screaming Frog&#8217;s CVS &#8220;reports&#8221; directory, select a file and launch computation then insert data into the SQLite database.<\/li>\n<li>screen_4 :: Analyze :: As a user, I can load graphics that analyzed Screaming Frog&#8217;s SEO KPIs taken from the SQLite database.<\/li>\n<\/ul>\n<p><b>(Screen_1) General<\/b><\/p>\n<p><i>Wireframe (Balsamiq)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_1_general_balsamiq.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_1_general_balsamiq.png\" alt=\"\" width=\"585\" height=\"330\"><\/p>\n<p><i>Real app (Streamlit)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_1_general_streamlit_screaming_frog.png\"\n    data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_1_general_streamlit_screaming_frog.png\" alt=\"\" width=\"585\" height=\"330\"><\/p>\n<p><b>(Screen_2) Crawl<\/b><\/p>\n<p><i>Wireframe (Balsamiq)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_2_crawl_balsamiq.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_2_crawl_balsamiq.png\" alt=\"\" width=\"585\" height=\"330\"><\/p>\n<p><i>Real app (Streamlit)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_2_crawl_streamlit_screaming_frog.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_2_crawl_streamlit_screaming_frog.png\" alt=\"\"\n    width=\"585\" height=\"330\"><\/p>\n<p><b>(Screen_3) Parse and Insert<\/b><\/p>\n<p><i>Wireframe (Balsamiq)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_3_parse_insert_balsamiq.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_3_parse_insert_balsamiq.png\" alt=\"\" width=\"585\" height=\"330\"><\/p>\n<p><i>Real app (Streamlit)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_3_parse_insert_streamlit_screaming_frog.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_3_parse_insert_streamlit_screaming_frog.png\" alt=\"\"\n    width=\"585\" height=\"330\"><\/p>\n<p><b>(Screen_4) Analyze<\/b><\/p>\n<p><i>Wireframe (Balsamiq)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_4_analyse_balsamiq.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_4_analyse_balsamiq.png\" alt=\"\" width=\"585\" height=\"330\"><\/p>\n<p><i>Real app (Streamlit)<\/i><br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_4_analyze_streamlit_screaming_frog.png\" data-src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/screen_4_analyze_streamlit_screaming_frog.png\" alt=\"\"\n    width=\"585\" height=\"330\"><\/p>\n<h3>5. Create alias for your favorite application in the MAC console<\/h3>\n<p><b>I love todo-list because I always forget how to do things. It is nice to have somewhere a little helping note that shed some light on actions that you seldom do. That is the purpose of the lines below even.<\/b><\/p>\n<p>So, in order to simplify the command-line, I made a alias to be able to call screaming frog application with the alias<br \/>\n<code>froggy<\/code>. Unfortunately it is working in the console but it does not work when you use it into your python scripts. Anyway I bypass this obstacle by using the direct path to the screaming frog application.<\/p>\n<p><b>5.1 Make a shortcut on a MAC for Screaming Frog SEO<\/b><br \/>\nSource : <a href=\"https:\/\/cometfuel.com\/learn\/automate-screaming-frog\/#using-terminal-mac\" target=\"_blank\" rel=\"noopener\">https:\/\/cometfuel.com\/learn\/automate-screaming-frog\/#using-terminal-mac<\/a><\/p>\n<p><b>As a shortcut, you can use whatever you want! Make it simple and meaningful e.g. <code>screamingfrogseospider<\/code>, <code>screaminjayhawkins<\/code> or <code>ranagritando<\/code> or a more basic sf (careful to not confuse with symfony for instance&#8230;). I will gone with <code>froggy<\/code> as a shortcut.<\/b><\/p>\n<p><b>Command only working on the console<\/b><\/p>\n<pre lang=\"bash\">\r\n# full command working in the console\r\nfroggy --crawl https:\/\/flaven.fr --headless --save-crawl --output-folder\r\n\"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/reports\/\" --export-tabs\r\n\"Internal:HTML\" --overwrite --config\r\n\"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/seo_spider_config_3.seospiderconfig\"\r\n<\/pre>\n<p><b>Command working in the python scripts<\/b><\/p>\n<pre lang=\"bash\">\r\n\/Applications\/Screaming\\ Frog\\ SEO\\ Spider.app\/Contents\/MacOS\/ScreamingFrogSEOSpiderLauncher --crawl https:\/\/flaven.fr\r\n--headless --save-crawl --output-folder\r\n\"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/reports\/\" --export-tabs\r\n\"Internal:HTML\" --overwrite --config\r\n\"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/seo_spider_config_3.seospiderconfig\"\r\n<\/pre>\n<p><b>There are probably tons of explanations to find or to give about this issue but I&#8217;d rather move on as there is better to do!<\/b><\/p>\n<p><b>Add a shortcut for Screaming Frog for the mac console<\/b><\/p>\n<pre lang=\"bash\">\r\n# go to \/Users\/[username]\r\ncd ~\r\n\r\n# check if .bash_profile is in this directory\r\nls -la\r\n\r\n\r\n# open with vim or nano\r\nvim .bash_profile\r\nnano .bash_profile\r\n\r\n# using textedit native on mac\r\nopen -e .bash_profile\r\n\r\n# using sublime text\r\nsubl .bash_profile\r\n\r\n\r\n# adding an alias for Screaming Frog, I named it froggy\r\nalias froggy=\"\/Applications\/Screaming\\ Frog\\ SEO\\ Spider.app\/Contents\/MacOS\/ScreamingFrogSEOSpiderLauncher\"\r\nsource ~\/.bash_profile\r\n\r\n# test the froggy shortcut\r\nfroggy --help\r\n<\/pre>\n<pre lang=\"bash\">\r\n# commands inside the console working\r\nfroggy --crawl <url>\r\nfroggy --crawl https:\/\/flaven.fr\/\r\nfroggy --headless --crawl https:\/\/flaven.fr\/\r\nfroggy --headless --save-crawl --output-folder \/Users\/brunoflaven\/Documents\/02_copy\/_streamlit_ideas_for_app\/ --timestamped-output --crawl https:\/\/flaven.fr\/\r\nfroggy --headless --save-crawl --timestamped-output --crawl https:\/\/flaven.fr\/\r\n\r\n\r\n# full command working in the console\r\nfroggy --crawl https:\/\/flaven.fr --headless --save-crawl --output-folder \"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/reports\/\" --export-tabs \"Internal:HTML\" --overwrite --config \"\/Users\/brunoflaven\/Documents\/01_work\/blog_articles\/python-automate-screaming-frog\/seo_spider_config_3.seospiderconfig\"\r\n\r\n\r\n# open the app from the console\r\nopen \"\/Applications\/Screaming Frog SEO Spider.app\"\r\n\r\n# testing the shortcut for Screaming Frog SEO Spider.app\r\nfroggy --help\r\n<\/pre>\n<p><b>5.2 Make a shortcut on a MAC for Sublime Text<\/b><br \/>\nTo use subl, the Sublime Text bin folder needs to be added to the path. For a typical installation of Sublime Text, this will be located at <code>\/Applications\/Sublime Text.app\/Contents\/SharedSupport\/bin<\/code>. If using Bash, the default before macOS 10.15, the following command will add the bin folder to the PATH environment variable:<\/p>\n<pre lang=\"bash\">\r\n# add sublime directly in the console...\r\necho 'export PATH=\"\/Applications\/Sublime Text.app\/Contents\/SharedSupport\/bin:$PATH\"' >> ~\/.bash_profile\r\nsource ~\/.bash_profile\r\n<\/pre>\n<pre lang=\"bash\">\r\n# check\r\nsubl --help\r\n<\/pre>\n<p>Source: <a href=\"https:\/\/www.sublimetext.com\/docs\/command_line.html#mac\" target=\"_blank\" rel=\"noopener\">https:\/\/www.sublimetext.com\/docs\/command_line.html#mac<\/a><\/p>\n<h3>6. Videos<\/h3>\n<p><b>3 additional videos to tackle this post<\/b><\/p>\n<ul>\n<li>Python, Screaming Frog, SEO, Automate, POC Part 1 Manipulating Data with Streamlit &#038; SQLite with the help of SQLAlchemy<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/6R0HYHIVVUQ\" title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/li>\n<li>Python, Screaming Frog, SEO, Automate, POC Part 2 Creating Database in SQLite with Streamlit and SQLAlchemy<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/i_WrW5-i2wY\" title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/li>\n<li>Python, Screaming Frog, SEO, Automate, POC Part 3 Creating Database in SQLite with Streamlit and SQLAlchemy<iframe loading=\"lazy\" width=\"560\" height=\"315\" src=\"https:\/\/www.youtube.com\/embed\/PMC36ZGDWQ8\" title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>Few conclusions can be drawn from this POC:<\/p>\n<ul>\n<li>Python, Pandas, Numpy, Plotly and Streamlit&#8230; are strong enablers to build personal usage dedicated Software or SaaS. It reduces drastically the entrance fee to the Software or SaaS market! I must admit that learning recent web technologies bring me closer than ever to the &#8220;serious&#8221; software industry than I thought. I should say it the other way round that is more the Software Industry that shifts to SaaS, Cloud and web technologies since 20 years from now&#8230;. <b>after all any website is nothing more than a &#8220;software&#8221; running in a browser.<\/b><\/li>\n<li><b>It reinforces me as a user centric PO and shapes me as an &#8220;improver&#8221;, &#8220;tinker&#8221; or &#8220;builder&#8221; for added value tools to improve daily processes and focus on delivering quickly real value! It is F&#8230; Faith Declaration!<\/b><\/li>\n<\/ul>\n<h2>More infos<\/h2>\n<ul>\n<b>Screaming Frog SEO Spider<\/b><\/p>\n<li>Command line interface for screamingfrog<br \/><a href=\"https:\/\/www.screamingfrog.co.uk\/seo-spider\/user-guide\/general\/#command-line\" target=\"_blank\" rel=\"noopener\">https:\/\/www.screamingfrog.co.uk\/seo-spider\/user-guide\/general\/#command-line<\/a><\/li>\n<li>Making the Most of Screaming Frog\u2019s Features in 2020<br \/><a href=\"https:\/\/www.growth-rocket.com\/blog\/making-the-most-of-screaming-frogs-features-in-2020\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.growth-rocket.com\/blog\/making-the-most-of-screaming-frogs-features-in-2020\/<\/a><\/li>\n<li>SEO Spider Configuration<br \/><a href=\"https:\/\/www.screamingfrog.co.uk\/seo-spider\/user-guide\/configuration\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.screamingfrog.co.uk\/seo-spider\/user-guide\/configuration\/<\/a><\/li>\n<li>How To Automate Crawl Reports In Data Studio<br \/><a href=\"https:\/\/www.screamingfrog.co.uk\/how-to-automate-crawl-reports-in-data-studio\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.screamingfrog.co.uk\/how-to-automate-crawl-reports-in-data-studio\/<\/a><\/li>\n<li>Search Solved GitHub Account<br \/><a href=\"https:\/\/github.com\/searchsolved\/search-solved-public-seo\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/searchsolved\/search-solved-public-seo<\/a><\/li>\n<li>Keyword_Clustering_Steamlit by Search Solved<br \/><a href=\"https:\/\/github.com\/searchsolved\/search-solved-public-seo\/tree\/main\/Keyword_Clustering_Steamlit\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/searchsolved\/search-solved-public-seo\/tree\/main\/Keyword_Clustering_Steamlit<\/a><\/li>\n<li>Screaming Frog SEO Spider Demo<br \/><a href=\"https:\/\/www.youtube.com\/watch?v=tkil3pKSFDA\" target=\"_blank\" rel=\"noopener\">https:\/\/www.youtube.com\/watch?v=tkil3pKSFDA<\/a><\/li>\n<p><b>Streamlit<\/b><\/p>\n<li>streamlit-cheat-sheet by daniellewisDL<br \/><a href=\"https:\/\/github.com\/daniellewisDL\/streamlit-cheat-sheet\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/daniellewisDL\/streamlit-cheat-sheet<\/a><\/li>\n<li>Project-for-User-Auth by digipodium<br \/><a href=\"https:\/\/github.com\/digipodium\/Project-for-User-Auth\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/digipodium\/Project-for-User-Auth<\/a><\/li>\n<li>discuss.streamlit.io &#8211; Format_func (function) &#8211; Examples Please<br \/><a href=\"https:\/\/discuss.streamlit.io\/t\/format-func-function-examples-please\/11295\/5\" target=\"_blank\" rel=\"noopener\">https:\/\/discuss.streamlit.io\/t\/format-func-function-examples-please\/11295\/5<\/a><\/li>\n<li>discuss.streamlit.io &#8211; How to use st.cache with sqlalchemy.orm objects<br \/><a href=\"https:\/\/discuss.streamlit.io\/t\/how-to-use-st-cache-with-sqlalchemy-orm-objects\/3329\/5\" target=\"_blank\" rel=\"noopener\">https:\/\/discuss.streamlit.io\/t\/how-to-use-st-cache-with-sqlalchemy-orm-objects\/3329\/5<\/a><\/li>\n<li>How to build interactive dashboards in Python using Streamlit<br \/><a href=\"https:\/\/towardsdatascience.com\/how-to-build-interactive-dashboards-in-python-using-streamlit-1198d4f7061b\" target=\"_blank\" rel=\"noopener\">https:\/\/towardsdatascience.com\/how-to-build-interactive-dashboards-in-python-using-streamlit-1198d4f7061b<\/a><\/li>\n<li>Charly Wargnier github account<br \/><a href=\"https:\/\/github.com\/CharlyWargnier?tab=repositories\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/CharlyWargnier?tab=repositories<\/a><\/li>\n<li>Streamlit with SQL and plotly express to make a dashboard<br \/><a href=\"https:\/\/github.com\/mdsohelmahmood\/mdsohelmahmood.github.io\/blob\/8b6e4d48d23c9ce2d009f1ee36fb5855593ade0f\/_notebooks\/2021-06-29-Streamlit-01.ipynb\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/mdsohelmahmood\/mdsohelmahmood.github.io\/blob\/8b6e4d48d23c9ce2d009f1ee36fb5855593ade0f\/_notebooks\/2021-06-29-Streamlit-01.ipynb<\/a><\/li>\n<li>Customer Segment Profiling App with Streamlit<br \/><a href=\"https:\/\/antonsruberts.github.io\/streamlit-audience\/\" target=\"_blank\" rel=\"noopener\">https:\/\/antonsruberts.github.io\/streamlit-audience\/<\/a><\/li>\n<li>Dropdownmenu in Streamlit without brackets and quotes<br \/><a href=\"https:\/\/python.tutorialink.com\/dropdownmenu-in-streamlit-without-brackets-and-quotes\/\" target=\"_blank\" rel=\"noopener\">https:\/\/python.tutorialink.com\/dropdownmenu-in-streamlit-without-brackets-and-quotes\/<\/a><\/li>\n<li>How to build a multi-language dashboard with Streamlit<br \/><a href=\"https:\/\/blog.devgenius.io\/how-to-build-a-multi-language-dashboard-with-streamlit-9bc087dd4243\" target=\"_blank\" rel=\"noopener\">https:\/\/blog.devgenius.io\/how-to-build-a-multi-language-dashboard-with-streamlit-9bc087dd4243<\/a><\/li>\n<li>streamlit-gettext by fischerbach<br \/><a href=\"https:\/\/github.com\/fischerbach\/streamlit-gettext\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/fischerbach\/streamlit-gettext<\/a><\/li>\n<li>Streamlit by mdsohelmahmood<br \/><a href=\"https:\/\/github.com\/mdsohelmahmood\/Streamlit\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/mdsohelmahmood\/Streamlit<\/a><\/li>\n<li>streamlit-dashboard-template by amrrs<br \/>\n<br \/><a href=\"https:\/\/github.com\/amrrs\/streamlit-dashboard-template\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/amrrs\/streamlit-dashboard-template<\/a><\/li>\n<li>fast.ai<br \/><a href=\"https:\/\/github.com\/fastai\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/fastai<\/a><\/li>\n<li>Dashboard using Streamlit with data from SQL database<br \/><a href=\"https:\/\/towardsdatascience.com\/dashboard-using-streamlit-with-data-from-sql-database-f5c1ee36b51\" target=\"_blank\" rel=\"noopener\">https:\/\/towardsdatascience.com\/dashboard-using-streamlit-with-data-from-sql-database-f5c1ee36b51<\/a>\n<\/li>\n<li>Build a Web App to Group &#038; Plot Excel Files in Python with Streamlit<br \/><a href=\"https:\/\/www.youtube.com\/watch?v=ZDffoP6gjxc\" target=\"_blank\" rel=\"noopener\">https:\/\/www.youtube.com\/watch?v=ZDffoP6gjxc<\/a><\/li>\n<p><b>SQLite<\/b><\/p>\n<li>SQL Exercises: Find salesman commission details where customer grade is 200 or more<br \/><a href=\"https:\/\/www.w3resource.com\/sql-exercises\/sql-quering-on-multiple-table-exercise-7.php\" target=\"_blank\" rel=\"noopener\">https:\/\/www.w3resource.com\/sql-exercises\/sql-quering-on-multiple-table-exercise-7.php<\/a><\/li>\n<li>MySQL Sample Databases<br \/><a href=\"https:\/\/www3.ntu.edu.sg\/home\/ehchua\/programming\/sql\/SampleDatabases.html\" target=\"_blank\" rel=\"noopener\">https:\/\/www3.ntu.edu.sg\/home\/ehchua\/programming\/sql\/SampleDatabases.html<\/a><\/li>\n<li>Data modeling<br \/><a href=\"https:\/\/gquercini.github.io\/courses\/databases\/tutorials\/data-modeling\/\" target=\"_blank\" rel=\"noopener\">https:\/\/gquercini.github.io\/courses\/databases\/tutorials\/data-modeling\/<\/a><\/li>\n<li>MySQL Tutorial<br \/><a href=\"https:\/\/www.mysqltutorial.org\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.mysqltutorial.org\/<\/a><\/li>\n<p><b>SQLAlchemy<\/b><\/p>\n<li>SQLAlchemy \u2014 Python Tutorial<br \/><a href=\"https:\/\/towardsdatascience.com\/sqlalchemy-python-tutorial-79a577141a91\" target=\"_blank\" rel=\"noopener\">https:\/\/towardsdatascience.com\/sqlalchemy-python-tutorial-79a577141a91<\/a><\/li>\n<li>SQLAlchemy. Gu\u00eda de inicio (Spanish)<br \/><a href=\"https:\/\/j2logo.com\/python\/sqlalchemy-tutorial-de-python-sqlalchemy-guia-de-inicio\/\" target=\"_blank\" rel=\"noopener\">https:\/\/j2logo.com\/python\/sqlalchemy-tutorial-de-python-sqlalchemy-guia-de-inicio\/<\/a><\/li>\n<li>SQLAlchemy Tutorial<br \/><a href=\"https:\/\/www.tutorialspoint.com\/sqlalchemy\/index.htm\" target=\"_blank\" rel=\"noopener\">https:\/\/www.tutorialspoint.com\/sqlalchemy\/index.htm<\/a><\/li>\n<li>SQLAlchemy \u2014 Python Tutorial<br \/><a href=\"https:\/\/towardsdatascience.com\/sqlalchemy-python-tutorial-79a577141a91\" target=\"_blank\" rel=\"noopener\">https:\/\/towardsdatascience.com\/sqlalchemy-python-tutorial-79a577141a91<\/a><\/li>\n<li>SQLalchemy + Python Tutorial (using Streamlit)<br \/><a href=\"https:\/\/www.youtube.com\/watch?v=lIHGGcnCmFA\" target=\"_blank\" rel=\"noopener\">https:\/\/www.youtube.com\/watch?v=lIHGGcnCmFA<\/a><\/li>\n<li>streamlit_sqlalchemy_example<br \/><a href=\"https:\/\/github.com\/digipodium\/streamlit_sqlalchemy_example\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/digipodium\/streamlit_sqlalchemy_example<\/a><\/li>\n<li>sqlalchemy.org &#8211; The Python SQL Toolkit and Object Relational Mapper<br \/>\n<br \/><a href=\"https:\/\/www.sqlalchemy.org\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.sqlalchemy.org\/<\/a><\/li>\n<li>analyse slqalchelmy<br \/><a href=\"https:\/\/github.com\/mdsohelmahmood\/mdsohelmahmood.github.io\/blob\/8b6e4d48d23c9ce2d009f1ee36fb5855593ade0f\/_notebooks\/2021-06-29-Streamlit-01.ipynb\" target=\"_blank\" rel=\"noopener\">https:\/\/github.com\/mdsohelmahmood\/mdsohelmahmood.github.io\/blob\/8b6e4d48d23c9ce2d009f1ee36fb5855593ade0f\/_notebooks\/2021-06-29-Streamlit-01.ipynb<\/a>\n<\/li>\n<li>SQLAlchemy ORM Tutorial for Python Developers<br \/><a href=\"https:\/\/auth0.com\/blog\/sqlalchemy-orm-tutorial-for-python-developers\/\" target=\"_blank\" rel=\"noopener\">https:\/\/auth0.com\/blog\/sqlalchemy-orm-tutorial-for-python-developers\/<\/a><\/li>\n<li>Python SQLite \u2013 Travailler avec la date et la date et l\u2019heure (French)<br \/><a href=\"https:\/\/fr.acervolima.com\/python-sqlite-travailler-avec-la-date-et-la-date-et-l-heure\/\" target=\"_blank\" rel=\"noopener\">https:\/\/fr.acervolima.com\/python-sqlite-travailler-avec-la-date-et-la-date-et-l-heure\/<\/a><\/li>\n<p><b>Other<\/b><\/p>\n<li>Creating a Data Science Portfolio<br \/><a href=\"https:\/\/www.maartengrootendorst.com\/blog\/portfolio\/\" target=\"_blank\" rel=\"noopener\">https:\/\/www.maartengrootendorst.com\/blog\/portfolio\/<\/a><\/li>\n<li>Feature Value Analysis: the Supreme Growth Hack<br \/><a href=\"https:\/\/www.priceintelligently.com\/blog\/bid\/191076\/Feature-Value-Analysis-The-Supreme-Growth-Hack\" target=\"_blank\" rel=\"noopener\">https:\/\/www.priceintelligently.com\/blog\/bid\/191076\/Feature-Value-Analysis-The-Supreme-Growth-Hack<\/a><\/li>\n<p><b>Nudge theory<\/b><\/p>\n<li>Nudge theory<br \/><a href=\"https:\/\/en.wikipedia.org\/wiki\/Nudge_theory\" target=\"_blank\" rel=\"noopener\">https:\/\/en.wikipedia.org\/wiki\/Nudge_theory<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>So, my objective was to build a dashboard with Streamlit that automate Screaming Frog SEO Spider for large time saves and fast audits then manage&hellip; <\/p>\n<p class=\"text-center\"><a href=\"https:\/\/flaven.fr\/2021\/12\/quick-poc-for-a-all-in-one-that-provides-an-seo-dashboard-made-with-streamlit-managing-screaming-frog-automation-storing-results-in-a-database-sqlite-and-create-data-analysis-graphics-for-seo-repo\/\" class=\"more-link\">Continue reading &rarr; <span class=\"screen-reader-text\">Quick POC for a All-in-one that provides an SEO dashboard made with Streamlit, managing Screaming Frog automation, storing results in a Database (SQLite) and create data-analysis graphics for SEO reports<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":12018,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"bf_ai_meta_description":"Build an SEO dashboard with Streamlit, automate Screaming Frog, store data in SQLite, and generate SEO reports with data-analysis graphics.","bf_ai_og_title":"SEO Dashboard with Streamlit","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3437,3444,3445,3447,3448,3449,3450,3435],"tags":[2147,3493,2838,2779,2531,2566,2387,3490,2316,2847,44,2810,2793,3508,523],"class_list":["post-12015","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-case-studies","category-programming-databases","category-seo-web-marketing","category-technology-trends","category-tools-productivity","category-tutorials-how-to","category-ux-product-design","category-web-development","tag-automate","tag-clip","tag-deploy","tag-experimentation","tag-p-o","tag-pandas","tag-poc","tag-postgresql","tag-python","tag-screaming-frog","tag-seo","tag-sqlite","tag-streamlit","tag-test-automation","tag-visualization"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3Vuhl-37N","jetpack_featured_media_url":"https:\/\/flaven.fr\/wp-content\/uploads\/2021\/12\/python_automate_screaming_frog_b.jpg","_links":{"self":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/12015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/comments?post=12015"}],"version-history":[{"count":23,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/12015\/revisions"}],"predecessor-version":[{"id":12055,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/12015\/revisions\/12055"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media\/12018"}],"wp:attachment":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media?parent=12015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/categories?post=12015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/tags?post=12015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}