Auto Export Data into Excel from SQL using Python Pyodbc | Python SQL Automation |Task Scheduler #16
Vložit
- čas přidán 4. 09. 2024
- In this video you will learn or explore something new related to Python and SQL Server Automation.
Auto Export Data into Excel from SQL using Python Pyodbc | Python SQL Automation | Task Scheduler
First of all we will establish the connection between Python and SQL Server using pyodbc (To create the SQL connection is very easy as compare to other programming language). After that we will read the data from SQL and store in Pandas DataFrame and very interest part is we will display the notification status on user's screen and that notification you can customize based on your need.
And at the last, we will schedule the script using Task Scheduler to run on the specific date and time. So Let's go!!!
Python SQL Server Script:
------------------------------------------------------------------------------------------------------------------
import pyodbc
import pandas as pd
import os
from datetime import datetime
from plyer import notification
create SQL connection
connection = pyodbc.connect(driver = '{ODBC Driver 17 for SQL Server}',
host = 'DESKTOP-NAKP5E5',
database = "Test",
trusted_connection = 'yes')
SQL Command to read the data
sqlQuery = "select * from dbo.SalesOrder where Region = 'South'"
Getting the data from sql into pandas dataframe
df = pd.read_sql(sql = sqlQuery, con = connection)
Export the data on the Desktop
df.to_csv(os.environ["userprofile"] + "\\Desktop\\PythonScript\\" + "SQL_OrderData_" +
datetime.now().strftime("%d-%b-%Y %H%M%S")
+ ".csv", index = False)
Display Notifiction to User
notification.notify(title="Report Status!!!",
message=f"Sales data has been successfully saved into Excel.\
Total Rows: {df.shape[0]}
Total Columns: {df.shape[1]}",
timeout = 10)
-------------------------------------------------------------------------------------------------------------------
Last Video:
Python Excel Automation: • Excel Automation Using...
Python Teaser: • A Beautiful Python Pro...
Python Pandas Tutorial: • Python Pandas Tutorial...
Python Playlist: • Python Tutorial for Be...
Python Data Structure Playlist: • Python Data Structure
Python OOPs Playlist: • Object Oriented Progra...
This is a very good tutorial for getting pandas and SQL together. The batch file, notifications, and the task scheduling are a great bonus that you've given us!
Thanks Raymond and Glad to hear.
Crystal clear bro, please post many videos 🎉
Sure, tell me what you are looking.
@@ProgrammingIsFunn , Can you also post a video like downloading the files from the sharepoint, data cleansing and uploading the output files into the sharepoint again !!!
I have been searching something like this for a week now and I've finally come across it. Many, many thanks for this tutorial. Your efforts for this knowledge is truly appreciated. Thanks again, I subbed!
I am glad to hear
the wonderful session, Thx friend for making so clear short & explanation for server to excel data
Simple and Quick.
Excellent
thank you so much...you are sharing your great knowledge for free....video was very clear....and helpful too
thank you so much for the great sharing knowledge. It is very useful.
Best video I’ve seen on this subject for us newbies. Thank you ! Now I need to know how to send parameters to call several sql server views and export to 1 excel file different sheets for a particular set of data….eg: export customer info, order info, manufacturing info for a particular order number.
Great..
Really helpful and informative..Thanks a ton...This is the one which i was searched a lots in CZcams....How can i connect with you if i have doubts in Python ?
Very Good my friend
Thanks very very informative!
Great 👍👍👍👍
Thank you! Cheers!
Nice
Thanks a lot bro. 👍👍 Subscribed Ur channel for more solutions
exporting data to XL sheet or SQL and MYSQL to which format it is easy to convert?
@programmingisfun Please make a video on How to count rows of multiple tables in pgadmin server of different database through python code because it takes lot of time to count one by one on daily basis. Is there any shortcut code by using python. Please help me in this.
Great video!!! Are you able to do a tutorial that shows how to link the cron job to a slack channel i.e. schedule runs and post the excel file to a slack bot?
Very useful
Glad you think so!
You are the best. Can you please tell me how to send an email to the user (via outlook) with the excel attachment instead of creating desktop notification
This is very easy using Python to send an email.
Hi Bro.
Do you know the above process.
I am also need it.
🙏
Very good tutorial
Can you please made tutorial in which instead of desktop notification an email is sent to the user.
it's easy.
After reading csv file in jupyter notebook. How to connect with sql server and upload data into database table.
czcams.com/video/eEVG-A4R9WU/video.htmlsi=pJrF2J5bdY5xH_FQ
Nice and helpful, your efforts well appreciated. Is it possible to automate the database to an online storage or folder? I will really appreciate your tips on this. Many thanks 🙏
I would thanks for the so much important video you have created for us . My query is below if you answer this for me is very helpful .
I have jump host server to connect/login database server from my local machine and want to supply run time value like Database server IP and database name. And in sql query need to pass 1 value like account ID while runtime as well ..?
You can integrate excel on runtime to get the input
Hi, you can take input from user at run time, using Python input box or you can integrate excel for input.
@@ProgrammingIsFunn If you have Any sample code or any youtube video link that would be the great
Notification showed 1620 rows when there were 1621. Remember to display df.shape[0] after incrementing by 1 as it starts from 0.
hie @ProgrammingIsFunn I have code which read data from excel and create a table in ms sql , so in that case how to keep check on duplicate entries in ms sql .Please help
Thanks for the video. Can we take real time data from excel to sql using python? Also, can we use Apache druid for near to real time data?
I get "Invalid Port number" error.. would you know how to fix it? I am using Idera Rapid SQL
i was trying to connect using your code , but getting error UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df = pd.read_sql(sql=sqlQuery, con=connection) ?
Ignore that warning. To ignore any warning you can use below script.
import warnings
warnings.filterwarning(‘ignore’)
In future if we will get any new release might be we have to change our approach using sqlAlchemy
Hi, please tell me how to change the encoding to cp1251, when I open a file in excel I have unknown characters.
you can use this iso-8859-1
Please create etl testing automation framework using pytest
can we add more rows into an existing excel file?
Yes we can, using xlwings we can add rows.
Please let me know whether the task will also run when the system is in off or it will run only when system is in on
It will run only when system is on.
How can we write a sql query output to a different sheet in a same excel ?
Very simple 😊.
import pandas as pd
writer = pd.ExcelWriter("YourWorkbookName.xlsx", engine = 'xlsxwriter')
# here, you can store your query's result data
df = pd.read_sql(sql = sqlQuery, con = connection)
df.to_excel(writer, sheet_name = 'HereYourSheetName')
writer.save()
writer.close()
and for more info watch this tutorial.....
czcams.com/video/P8dpG5arKY0/video.html
What is the laptop configuration are you usinh for windows it looks fast .what is the theme for vs code used.Nice session
Karthik, I am using T490 Lenovo thinkpad laptop with I7 and SSD.
I have a sql query which i need to run daily .
Sql Query= SELECT * FROM be_dw where date_key ='1-02-2024.' But i need to take the date_key automatically as per today's date instead of manually entering and changing the date. How can i do that ?
You can take the input at run time from the user, or you can create a mapping file where use can set the input data before executing the script.
@@ProgrammingIsFunn Since i working on automating the process,so input from the user won't help. Mapping the file to get the date, I didn't get that ?
Also,isn't there any SQL function or logic to get the current date?
Edit : I am a beginner. 🙂
@@vishwarajk3 To get the current date and time, you can use getdate() function of sql.
# Get the current date and time
select getdate() -- It will give you date and time
# only for date
select format(getdate(), 'dd/mmm/yyyy')
can we use this in xampp file?
Yeah you can try the same, but we never ever try this in xampp.
To automate through task scheduler, do we need to keep our system and python on all the time ?
Yes.
how to create python file sir......\\desktop\\python..... something
Can we do the inverse of it ?
I mean can we import multiple Excel files from local disc to sql server using python ??
Is it possible?
Yesss ...
U can update, insert, delete,alter the table using python
How to compare two tables
How to do it for Mysql using Python?
Using mysql.connector we can establish the connection between python and mysql
Can't connect
What is userprofile
userprofile means: c:\users\SystemName
try to run the below code for better understanding.
import os
print(os.environ["userprofile"])
Why its not working for MYSQL??
For mysql you have to import mysql.connector library and in this video you will find only for sql server.
And guys
('28000', '[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user \'DESKTOP-76JM819\\endow\'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "Coba" requested by the login. The login failed. (4060); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user \'DESKTOP-76JM819\\endow\'. (18456); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "Coba" requested by the login. The login failed. (4060)') i have problem, can anyone explaine?