Web Scraping Databases with Mechanical Soup and SQlite
HTML-код
- Опубликовано: 21 июл 2024
- Hi Everyone! In this step by step tutorial, we will extract a huge table of data from the internet and store it inside an SQLite database!
To keep things simple I've chosen a Wikipedia table, but I highly encourage you to apply the same principles on data that updates a bit more frequently (for example weather forecasts) 😃
If you're curious about my IDE - I'm using Wayscript which is now available for the wide public! you no longer need an invitation, you can simply sign up with the following link: app.wayscript.com
⭐clone complete tutorial code⭐
app.wayscript.com/lairs/517c9...
⏰ TIMESTAMPS ⏰
***************************************
00:00 - intro
00:34 - imports and installs
01:42 - web scraping with mechanical soup
02:20 - select HTML table elements
03:47 - extract element attributes
06:11 - find the index value of a list item
07:13 - extract multiple columns of table data
09:44 - organize extracted columns
12:44 - enumerate function
14:02 - dictionary to data frame
14:53 - create SQLite database
15:36 - create SQLite table
16:35 - insert Pandas data frame into SQlite table
17:26 - save data permanently inside database file
18:49 - thanks for watching!
💻 CODE AND IMPORTANT LINKS 💻
***************************************
⭐ URL used in the tutorial:
en.wikipedia.org/wiki/Compari...
⭐ complete code repository on Github:
github.com/MariyaSha/Webscrap...
⭐install SQLite on Linux:
sudo apt install sqlite3
⭐install SQLite on Windows:
Download the Precompiled Binaries for Windows zip file from SQLite docs:
www.sqlite.org/download.html
⭐install SQLite on MAC or Anaconda:
no need to install - you already have it! 😁
⭐ code used in the tutorial:
column_names = ["Founder",
"Maintainer",
"Initial_Release_Year",
"Current_Stable_Version",
"Security_Updates",
"Release_Date",
"System_Distribution_Commitment",
"Forked_From",
"Target_Audience",
"Cost",
"Status"]
📽️ RELATED TUTORIALS📽️
***************************************
🌞 Much Better HTML table Web Scraping with Pandas:
• Much Better Web Scrapi...
🌞 SQLite Databases for Beginners:
• SQLite Backend for Beg...
🌞 Web Scraping Images with Mechanical Soup:
• Better Web Scraping wi...
🌞 Web Scraping Text with Beautiful Soup:
• Web Scraping with Beau... 
Наука
![Rob49 - On Dat Money (with Cardi B) [Official Video]](http://i.ytimg.com/vi/fTFS58mHJh8/mqdefault.jpg)
can mechanical Soup scrap dynamic content "javascript pages" ?
nope! it's meant for HTML/XML scraping only, so it's optimal for websites with very little user interaction! 😊
If you're scraping a JavaScript website - definitely go for Selenium!
I have a bunch of videos explaining how to use it:
⭐ Web Scraping LinkedIn:
ruclips.net/video/7aIb6iQZkDw/видео.html
⭐ Web Scraping Instagram:
ruclips.net/video/iJGvYBH9mcY/видео.html
⭐ Web Scraping Facebook:
ruclips.net/video/SsXcyoevkV0/видео.html
Good luck and I hope it helps! 😀
@@PythonSimplified thank you for your reply and for the good job you are doing ! keep it up
Sometimes you can look at the network activity and call APIs. Every situation is different.
@@PythonSimplified My viewers are asking me if this is legal :D Do you have any answer on such questions? ;-)
@ I've actually filmed a short video on it a while back:
ruclips.net/video/f0B6RdVGcM8/видео.html
It doesn't really count as "legal advice", but as long as you use it within the "fair use" copyrights clause you should be fine 😉
This is probably one of the best and most thorough channels related to practical applications of Python. You have a very unique style, very well done!
Thank you so much for the incredible feedback!! I really enjoyed reading your comment! 😃😃😃
This is perfect for someone wanting to learn you have what a lot of educational videos are missing (simpler way of explaining) they teach like you already know and get it. Educators often forget because they get it don't mean everyone does. Different ways to teaching and people absorb information differently. Thanks for taking time to explain it fully step by step.
New subscriber!! Im a Data Engineer with 10 years of experience. Can't wait to watch all your videos! 👏
I'm hooked on this channel!! Easy to look at and her style resonates with me. She has a real way of helping me understand. Very refreshing!! Thank You!
i love your tutorials! I am in a code camp for web development but im finding the logic driven scripting my true love and following along with your videos makes it really easy to learn as you do an amazing job explaining whats going on! thank you!
You are just too awesome! Simple, powerful, and fluent method to easily remember commands that you wouldn’t otherwise.. bravo, and thank you!
When you said simplied, you are on the money. I needed to get a clear view on OOP, your video literally cleared all my doubts.
Ohhh magnifique!!! Love love love this tutorial!!
your work always improves. The way you explain and how you use the logic of programming is very clear.. thk you
Thank you so much for the lovely comment Martin!! 😁😁😁 I'm super happy you like my explanations! 😊
As a blind programmer i find it very useful when you tell us what You typed. i subscribe Your channel now. Keep it going.
Thank you so much for the lovely comment Chaghlar! 😃
I'm always trying to make these videos as accessible as possible to everyone, and I'm so happy you found them helpful!!
I really admire the fact that you're programming despite the challenge and I'm super excited to have you on board! 😁😁😁
Thank You for te kind words coming from You
One thing i haven't understand in this code. there is value.text.replace inside the brackets. i understand it's function and what it does. but, i got no clue where did You define value variable at first? is it a method of mechanicalsoup library or m i missing something due to i almost asleep.
Thats a list comprehension, ok.
wow - i am very impressed - so much easier than going to coding sites where the logic is harder to translate. Thanks for clearly explaining the logic within each line of code
This is exactly the topic I was looking for, but it was crazy hard to find, so thank you!
Hi,
You have an unique way to explain the subject. I am really impressed the way you explain. I have seen hundreds of videos about web scraping, but none of them as simplified as this. Thank you so much. I heard about Beautifulsoup, Scrapy, Selenium, Puppeteer. But for the first time MechanicalSoup. And I am expecting some more videos about web scraping with the updated versions. You are techings are as sweet as your voice.
Thanks
Great job on presenting this. It was kept basic and very informative.
Thank you! Your tutorials are amazing 🤩
people call Python is easy language but you are makes python so easy and understanding by tutorial videos. Thanks and keep it and make it more and more tutorials videos
It was really a helpful and inspirational experienced session for me to get an overview of different interesting fields in computing like this one .... 😊 Thanks a lot ... ☺
Great video as always! Thank you! I would love to see you make a tutorial on the Curses Module!
Nice walk through - there are many ways to webscrape - I hadn’t come across mechanical soup before (I’ve used beautiful soup, selenium, and scrapy and I would approach this task a bit differently) and I really like this intuitive walk through as the thought process applies to the various programmes
Thank you so much! That's exactly what I was aiming for! 😊
This video is meant to demonstrate a general approach to web scraping and using developer tools to select elements on the DOM!
You can apply the same techniques with any other web scraping library, and I must admit that I'm much more of a Selenium kind of girl... but once in a while it's nice to diversify 😉
@@PythonSimplified I’m not a programmer, but I like to use a little programming to support my work - so this sort of approach to coding is actually better I think for a lot of people who aren’t necessarily doing programming at scale or worried about efficiency - learn the concepts, do something that works and if you want later on you can learn the more efficient ways of doing things. I find some of the more elegant examples are a bit too sophisticated for some people as they can be so efficient it’s hard for a non programmer to actually understand why they work
Thanks for your tutorials...always informative and well-presented, and love your personality. By the way, what monitor is that that you are using?
This is new for me, thanks.
Very good video and I learned a lot from it. Love the way you explain everything.
I have a questions. What are the advantages of using python to scrape the table from the web page instead of using Excel or Power Query?
Great video, thanks for sharing!
A bit offtopic, but what make and model is your monitor? I have been looking around for a 49" ultrawide for office use but it's difficult finding one that ticks all the boxes.
thank you so much, that's exactly what I'm looking for
Yeey! super happy to help! 😉
You have a great tallent to teach us complex programs in the east way. 👏👏👏🌻☘️🐱.
BB - Beautiful and brilliant! :)
It is magic to watch you creating code, very informative and cool.
I just have a question as an Excel-user that would like to learn how to code.
What is the major difference and benefit between the Python code and Importing from a web-page in Excel ?
Thank you !
Amazingly clear explanation - thanks a lot for the example. 100-point :-D !!!
Thank you so much Enrique! 😃
great video, thanks for sharing 👍 I always learn something new.
Thank you so much! glad you liked it Tobs! 😀
Great video. Thanks alot.
Love your video! Content its great.
Thank you so much Andrei! 😃
I Found This Highly Informative and Very Interesting: Thanks Sweetness, Love You And your Channel ~
Thank you so much! I'm glad you enjoyed this tutorial! 😀😀😀
Awsome! Thanks a lot... greetings from Mexico.
if your code included variable assignments of those "print index", then you can still get all your wanted value.text results even if the contents of the tables changes. Also if you are going to re-run this scraper code that writes to the DB, you need to "DROP" the database first (ie delete the table, not just the data itself)
Adorable Mariya….!
Beautiful soup, bs4.
Merci PI Lemost! 😁
thank you, nice tutorial! I have a question, i come across no module of bs4 or mechanical on way scripts, any advices?
Very entertaining. I want to start a Webscrapping project with Python. It was like I spect in many ways
Nice thanks, but if we work with static numbers it brakes as soon as something is added to the table. Would there be a way to only refer to the specific table?
Absolutley! However not with Mechanical Soup but with Pandas! 🐼
Checkout my notebook on Github (I'll film a tutorial about it shortly):
github.com/MariyaSha/WebscrapingDatabases/blob/main/scraper_Pandas.ipynb
The scraper object in the notebook code extracts all the tables from the page. Then you find the index of the table you're looking to scrape and you print it I the next cell.
It's a shortcut that skips the entire scraping process and leaves it to Pandas, which is super handy! 😃
I hope it helps 😊
I wish all female minds worked a little like you. Congratulations from Greece
hahahahaha thank you so much Giorgos! 😁😁😁
Cheers from Canada! 🍁
Definitely the cutest Python Teacher in existence!
I'm officially giving up Ruby!
hahaha thank you so much Zubin! 😀
And yeeeeey! My evil plan to convert everyone to Python is finally working!! muahahahaha!!! 🤣🤣🤣
@@PythonSimplified Found out your in Van! Go Van!
Excelente...me he suscrito a su canal...es impresionante...Excellent, I just suscribed to your channel...It's amazing!!!
Very nice 👌
I love you teacher👍😍😘.
Thanks!
Some pages have links on them that have to be joined to get the url with the info I'm looking for, I want to open then scrape those.. can you explain how to do that?
If a table is shown on multiple web pages (Next page ), could we still use this scraper? Thanks.
Awsome video!
You are great Maria😍😍🥰
And so are you Dipesh!! Thank you so much 😊😊😊
Great video as usual. But why do you use "pip" instead of "pip3". Is pip still supported?
Maam your teachings are awesome 👌👏..can you plss put a video on stadium seat booking system with python as front-end and SQL as backend...
I'm Taking a python course at school right now and am struggling mightily. I have had zero prior experience in programming. Do you have any suggestions on materials i could pick up to help myself?
Subscribed 😊. Nice job. However, if any changes are done to the tables in regards adding/removing rows and/or columns - which in an actively maintained table of Linux distros is quite probable - the scraping algorithm won't work properly anymore. So instead of using hardwired indexes I'd add some more code to analyze the table(s) and compute the indexes dynamically. An other potential issue for certain sites is the EU cookie consent popup () that needs to clicked away before getting to the content. So you'd need to remote control a headless browser using js. Don't know if mechanical soup can handle this. If yes, I'd be interested to learn how.
Your channel is so helpful! Can you do a tutorial on git?
Your videos are great and very informative.
Are you planning to make a video about collecting data from forum sites? And hope you make a video about the how chatgpt is made it.
Now I better understand how Google works. Indeed, it is a simple as a soup. Thank you.
What would be the best for scrapping web sites that have login, so I can download details of my orders? There is one page that has order summaries and for each order I will click to get to the details to download. Which libraries would be best for this type of web scraping?
Hi Felix! 😃
I personally go for Selenium whenever dealing with e-commece sites. It allows you to scrape Javascript (while Mechanical Soup is limited to HTML/XML) and it also gives you a certain degree of protection from security blockers. It opens a browser window from where all the scraping happens and because of this window - the server is convinced you are a legitimate user rather than a bot :)
I have so many tutorials about Selenium (I belive you'll see a bunch of links in the pinned message on this vid) but here's one where I scrape Facebook:
ruclips.net/video/SsXcyoevkV0/видео.html
I hope it helps and good luck! 😊
Can you do a tutorial on how I can connect to the website databases, read data directly from the tables and update table data from the website url and this mechanical soup?
So every time you ran the code to test out the script, it would reload the webpage into mechanical soup. If the website is huge, how do we save the page into a file so that we don’t have to request it from the site again.
Hi Wayland, it's a great question! 😊
One solution could be scraping the entire content of the page with browser.page, converting it into a string (maybe replacing the tags with "," or something similar) and storing it in a csv or text file.
with open('mt_text_file.txt', 'w') as my_file:
my_file.write(my_string)
with open('my_csv_file.csv', 'w') as my_file:
my_file.write(my_string)
If you're selecting specific elements rather than the entire page, try browser.page.find_all(element_name) and you can store them in the exact same manner 😀 This way you are interacting with a file on your computer rather with some kind of a web server.
Good luck scraping and I hope it helps! 😄
how we can do it from a table that has rows organised in multiples pages
Perfect...
whats the differece between mehanical soup and beautiful soup?
Thank you Merry Xmas. Please can you always give li is to environment, editor you are using etc. Us that wayscript? In a month, I will following every video to do stuff. Thanks a ton
and if i already have a table that i wanna insert Pandas data frame into it what should i do ?
What monitor are you using? I need to get one.
Nice
“And boom” haha I always say that when I’m explaining code. Nice
hahaha it's the best way to announce a successful return statement! 😉
I get the error when trying to do pd.DataFrame() "All arrays must be of the same length'. Do you maybe know why?
my first choice would be pandas read_html, but if you wanna do this kinda procedural in a more manual way, using static indexes is the worst way to go.
just extract all tables and iterate over the content of each. ;) so nothing goes wrong if the contentsize changes
If the content size changes it shouldn't really matter as the index is not hard coded... we're performing a search to find in which index the value lives before we slice it so it should adjust accordingly (relevant only if the new distributions won't be added after Zorin OS though) 😊
I definitely agree that read_html is a faster alternative! It's an incredible shortcut, while this video is more of a step by step demonstration of how to approach web scraping in general. I always like shortcuts, but in my opinion knowing the concept behind the shortcut is important as well 😉
Thought the same thing. But for a fast and "dirty" way to scrape data it's fine.
Can you do a video like this but for extracting video links from a website ? Thanks in advance
What do you think is best solution to scrape a website with HTML/XML and recaptcha or a website with javascript that give us a limited list of 10 result and then you must click on every one to popup an html window and see all details?
Do you think is it possible to scrape data from a database when you must send at least some character for some fields?
And what about a website with a database where you must feed a field with EXACT string to query? For example, if in the database there is a many record of names, it's not possible to insert just PAUL and then have all records for PAUL and JEANPAUL.
I am talking about four different websites but all four with a database.
I keep getting an sqlite3.OperationalError with cursor.execute("create table linux (Distribution, " + ",".join(column_names)+ ")") says near "("
How can i fix an incorrect number of bindings supplied ?
time to change my career path
How do you determine what type of website you are scraping from? I.E. Javascript, HTML, XML etc? Awesome tutorial! Hello from Ontario. :D
I don't know if there are strict rules to it, but usually when I need to scrape something, the first thing I consider is how interactive the site is.
If there are many user interactions such as likes, shopping cart or highly scrollable content - I always go for Selenium which is incredibly powerful!
Another consideration is - how likely is it that the website will block my bot? When dealing with e-commence websites you will encounter lots of blockers to prevent scavengers, therefore Selenium will be the best option again as it provides you with a GUI browser (which is considered as a legitimate user/client from the websites perspective. It just doesn't know how to differentiate between a human and an automated process when this browser window exists) and it bypasses these blockers so your ip is not recognized as a bot.
So Selenium is definitely my favorite, but sometimes it's an overkill! 😀
If you're scraping sites with very simple user interaction, for example: Google, Wikipedia, or even the weather forecasts on Environment Canada site (🍁🍁🍁). You'll notice that the majority of user interaction consists of links/anchor elements, which can easily be implemented with a markup language like HTML rather than a scripting one like JS.
Also, these sites do not load more and more content when you scroll down - it has a pre-defined end of the page section and you can't scroll beyond that (unlike in Instagram and Facebook feeds for example).
In these cases the best solution is probably Beautiful/Mechanical Soup or other XML/HTML scrapers with a headless browser (such as the stateful one from this tutorial) 😊
It's so easy to work with and no need to download a special web driver to get it going, just pip install mechanical soup and you can start working! 😁
Good luck with scraping, and I hope it helps!
Hi
Is it possible for you to make a short maybe an 30-1 hour long python crash course
I had actually mentioned this in one of the previous videos too
Hi Zaky! 😀
That's a very specific time frame you got! hahahaha I'll definitely keep posting videos on the Python for beginners series from time to time.
I'm just trying to keep up with all the tutorial ideas in my head and it's not an easy task because I have too many of them!! 😅😅
I've filmed a roadmap video not too long ago, covering some basic stuff for about 8 minutes:
ruclips.net/video/wFEC7VbWBZo/видео.html
If you're looking for a good source of beginners tutorials other than my channel, checkout Rune's channel (and more specifically the 8 hours worth of Python lessons playlist):
ruclips.net/video/ybeeuGXdhrQ/видео.html
Good luck and I hope it helps! 😃
I'm new on your channel thank you for this good and helpful tutorial but we need Django advanced tutorial
Which monitor are you using?
They said it could not be done but you proved them wrong.
Thanks beast
hahahaha thank you habibi! 😁😁😁
@@PythonSimplified 😍😍😍😍😍😍😍😍😍 you welcome habibti .. ❤
What ultrawide monitor are you using?
If beautiful girls like you kept teaching, IT is bound to be more popular
Hi, can we have a tutorial, scraping a search result from a search bar? Thanks
Dear , thanks for the session,it's really helped to understand some of the basic clearly..But while preparing my own tool , I have failed to do it ..is it possible to get some support from you?
Creeper Alert!!!!
Plz make a video to scrape data from flight fare sites like cleartrip and expedia.
Dis very gud stuff right hur.
Hey Mariya , would you tell me name of your large curve monitor, its totally insane like you
linda....simplesmente a melhor...kisses from Brazil
Thanks
You're absolutely welcome! have fun web scraping! 😁
Te amo!!!! :)
I need to scraping instagram , can you have a good tutorial about this issue? Thank you for share.
I love how she says "Attributes"
To be honest i was staring at you whole time 😍
gracias por el video ! de dónde eres ?
Nice channel, You can up information of react native please.
Mariya, like and I have subscribed to your github.
Thank you so much Andrei!! 😃😃😃 it's a great way to find out the topics of the upcoming videos a few hours in advance 😉 (I usually load the lesson code to Github the night before a premiere)
Simply awsome. a topic I wanted to do/research. Presented excellently (best presentation I have seen for a noob like me). By a very pretty lady! But sadly audio volume is not great. :(
Best
can u made a complate playlist in webscraping
Do you have a course building our coding skills?
Hey... Mariya... Love u vdios they are really easy to understand.
I m from Burnaby too. Sent u a connection on LinkedIn. Plz accept. This is Aamir Ahmed.
Wawwwww vous êtes compétente♥.
Dommage, manque sous-titrage en Français