SQL + Python: Master Data Analysis and Create PDF Reports
ฝัง
- เผยแพร่เมื่อ 1 ส.ค. 2024
- 👉 Explore All My Excel Solutions: pythonandvba.com/solutions
𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
In this tutorial, I'll show you how to use Python and SQL to transform your data into stunning PDF reports. Whether you're an analyst, a business owner, or just looking to create professional-looking reports for your own use, this tutorial will guide you step-by-step through the process.
First, I'll show you how to set up a SQLite database and load sample data into it using Python. Next, I'll demonstrate how to use SQL queries to extract the data you need from the database. Then, I'll use Pandas to manipulate and transform the data, and show you how to create interactive charts with Plotly Express.
Once we have our data prepped and ready, I'll use the FPDF library to generate a beautiful PDF report.
Whether you're a beginner or an experienced Python user, this tutorial will give you the skills you need to create stunning PDF reports with ease. Follow along, and transform your data into stunning reports that will impress your audience!
🌍 𝗟𝗜𝗡𝗞𝗦:
▶ Source Code: github.com/Sven-Bo/PDF-Report...
▶ SQLite Viewer Web App: sqliteviewer.app/
⭐ 𝗧𝗜𝗠𝗘𝗦𝗧𝗔𝗠𝗣𝗦:
0:00 - Introduction
0:33 - Explaining Database Tables
2:05 - Creating Database with Python
3:04 - Inspecting Database
3:53 - Installing & Importing Dependencies
4:23 - Define Paths and Charts
5:24 - Total Sales by Month
8:36 - Total Sales by Product
9:20 - Top Customers by Sales
9:54 - Create PDF Report
11:22 - SQL vs Pandas
12:11 - Close Database Connection
12:48 - Outro
𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): pythonandvba.com/mytoolbelt
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): pythonandvba.com/grafly
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): pythonandvba.com/cuteplots
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): pythonandvba.com/emojify
📑 Excel Templates: pythonandvba.com/go/excel-tem...
🎓 My Courses: pythonandvba.com/go/courses
📚 Books, Tools, and More: pythonandvba.com/resources
𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: / sven-bosau
📸 Instagram: / codingisfun_official
💻 GitHub: github.com/Sven-Bo
💬 Discord: pythonandvba.com/discord
📬 Contact: pythonandvba.com/contact
☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here:
▶ pythonandvba.com/coffee-donation
*Thanks for watching! What's your favourite data analysis tool? Excel, SQL, Python, ... ? Comment below* ⤵
I want to learn them all
Good evening, genius, I am following you from Egypt and I need your help in a very important matter for my work, I have a big event and a lot of visitors have been registered to attend this event, I have a very large number in Excel sheet, and now I have created a QR for each visitor of them to record attendance while entering the event, and because I want to take each QR from the Excel sheet and paste it into the invitation ticket automatically, Can you help me because there is no time to do it manually
Python n R
SQL and Python
All sir 🙌
I think it's only you who is able to make a video with so much information in a short time but to explain in an extremely clear and simple way, all my congratulations for this video. 👍
Thanks for the kind words and for tuning in, Ricardo! I'm glad you found the video useful. Your support means a lot to me! 👍
You're making a huge impact ! You made it easy to understand and you truly care about your subscriber who really need it 😮 Thank you so much 🙏
Thank you for your kind words, Sujung! I'm glad my content is helpful and easy to understand. Your support means a lot to me! 😊
more tutorials like this please. love it . keep it coming
Thanks for your feedback. Glad you liked it! ♥👍
This is one of the best channels you tube has ever recommended me..
Thanks for watching and your kind words! :)
Excellent thanks. The SQL-Python videos are really helpful.
Happy to hear that it was helpful; thank you for taking the time to leave a comment and for watching the video! 👍
Loved the video! Super informative and a hugely helpful resource. Excited to follow through and check out your other videos.
Dankeschön
Happy to hear that it was useful; thank you for taking the time to leave a comment and for watching the video!
Its impressive and amazing. You are really rocking and thanks a ton for your efforts!!!
My pleasure! Appreciate you taking the time to watch and leave a comment. Cheers, Sven ✌️
Just what I needed, thank you!!!
Happy to hear that, Florian. Thanks for watching the video! :)
Thanks a lot for making this available on youtube
My pleasure! Appreciate you taking the time to watch and leave a comment. :)
Thanks that's what I was looking for.
Glad it was helpful. Thanks for watching! :)
This is great! Thanks!
Thanks for the positive feedback! Appreciate you taking the time to leave a comment. 👍
This is an amazing tutorial made simple. Thank you!
This is a full development cycle! Love your videos!
Glad you liked the video and the example I picked. Thanks for watching and taking the time to leave a comment! 👍
Loved it , learnt using python with sql which i need to use in my own projects
Thanks for leaving a comment and for taking the time to watch! Glad you liked it. 👍 Good luck with your projects! 🍀
¡Excelente video! es bastante trabajo pero está impresionante, continúa así, saludos desde Venezuela
Glad you liked this one too. Greetings to Venezuela! 👋
Very well explained.. just wow
Thanks for the kind words! I am glad you enjoyed it. 😃👍
Thanks 😊 🎉
My pleasure! Appreciate you taking the time to watch and leave a comment. 😃😍
You are great 😊
Thank you so much 😀
I love this tutorial, I'm exciting to recreate the same steps, but with Spark Python and using Machine Learning
If You have same tutorials with Spark Python send me the link
Thanks for watching the video and your suggestion. Currently, I do not have any PySpark tutorials.
Great tutorial. I tend to stay away from packages to generate things like pdfs and powerpoint, instead preferring to use Quarto.
Thanks for tuning in! I've actually never heard of Quarto before, but it looks pretty cool. If I've got some free time, I might just give it a try.
This is the video i was searching and waiting --- Writing SQL Queries in Python and -- can you pls make some videos again to Automate Excel Works of Employess do in companies -- like applying filters and to make summary according to sellers and those summary --- need to copy and then paste in new excel files as per sellers sheets
Thank you for your suggestion! I will definitely create more videos on Excel automation in the future. Thanks for watching! 👍
Hey Coding is Fun,
those videos are really great and have pushed my workouput by a lot!
Could you by any chance tell me which tool did you use for the ER-Model?
Continue the great work!
Excellent video as usual.
Is it possible to do a tutorial on a webapp that combines SQL/Pandas and Streamlit ?
Thank you so much for your efforts !
Happy to hear that you liked this video too. Thanks for your video suggestion! :)
Loved that video. I'm just getting started with python and SQLite. What software do you use for your entity relationship model? Thanks
Thanks for watching the video, I used Luna Modeler to create my entity relationship model.
great
Thanks! :)
Lets goooooooo
First comment! BOOM!🚀
@@CodingIsFun I comment the minute I receive your notifications 😂 for better reach
Create a video on topics like
"97% of python programmers don't know these tricks"
Or
"The only 5 pandas commands you will ever need"
These always get the most views, something that the people think they will get immediate value addition from!!
@@VayunEkbote I really appreciate your support - it means the world to me! 🙏 Thanks also for suggesting some video title ideas. I do my best to avoid overly clickbaity titles, but I'll definitely consider your suggestions for future videos. Thanks again! 😅
@@CodingIsFun Grow with clickbaity content, expand with quality content!
How ould you export transformed data and its corresponding visuals in the same workbook at the same time?
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!
How would you export analyzed data in excel and its corresponing visuals in same exported excel workbook?
Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!
I am quite new to plotly, does anyone know how to increase the padding in the 'Top Customers by Sales' chart xaxis title?
Thanks for watching. You could add some padding to avoid the overlapping of the xaxis_title "Customer". Here is the code:
fig.update_layout(
title='Top Customers by Sales',
xaxis_title='Customer',
margin=dict(l=100, r=50, t=75, b=100), # Add padding to entire chart
yaxis_title='Total Sales ($)',
yaxis_tickprefix='$',
)
My issue with this tutorial is that the most difficult part is actually glossed over: what does the business need to know? We can investigate whatever we want but we also need things that are actionable. So how do we decide what to look into further? Or are we just doing EDA? For example, you plot revenue per product. But what does that really mean? And if we don't account for price differences, margin, or co-sales (people buy X because they also buy Y) the insights are meaningless.
Thanks for watching and your feedback!
Hallo, Sven! Alles klar?
What software and apps did you use?
VS Code, Jupyter, Anaconda and SQLite Viewer? Do I need all of them or I can skip any?
Hi. Yes, in the video I used VS Code, Jupyter Notebook and SQLite Viewer (website)
It's possible open a template pdf and insert the charts there?
That sounds possible. Happy Coding! Cheers, Sven ✌️
hello, i have doubt related to download mail attatchment in python
Hello. Ok :/
where I can find Jupyter notebook source code
Thanks for watching. Here it is: github.com/Sven-Bo/PDF-Report-Generator-Using-Python-and-SQL
Happy Coding!
Hello, after inputting df = pd.read_sql_query(query, conn) I recive error ': no such table: sales. I double check and sales.db file is in current_dir. Do you have any advise ? Greetings
Ok I have a solution :D
Great to hear you fixed it! :)
Good evening, genius, I am following you from Egypt and I need your help in a very important matter for my work, I have a big event and a lot of visitors have been registered to attend this event, I have a very large number in Excel sheet, and now I have created a QR for each visitor of them to record attendance while entering the event, and because I want to take each QR from the Excel sheet and paste it into the invitation ticket automatically, Can you help me because there is no time to do it manually
Hey there! So, what's the format of the invitation ticket you've created? Is it in a Word doc, PDF, or something else? By the way, my name is Sven, not "genius" 😬
I am using pycharm and the date option is not available in here.. Please help me
Thanks for watching. Sorry, but I am not sure what you mean. Cheers, Sven ✌️
@@CodingIsFun i have an attribute of data type DATE in my database and i am not able to typecast the entered value from gui into date data type, when i am inserting into the ms sql database
@@itsme9877 Hard to tell from a distance without knowing your code. However, you could try converting it to a date object before inserting.
from datetime import datetime
date_str = "2024-03-17" # Example date string
date_object = datetime.strptime(date_str, "%Y-%m-%d").date()
Gigachad
Thanks for watching the video!
Anyone else thinks that this guy face is actually a deepfake video generated by an indian guy who is the one talking??
🤫 Don't tell anybody!