Python e-mail notifications from MS SQL database and Outlook

Question

I have created python script that chronologically:

• uses sqlalchemy library to select data from MS SQL database

   engine = create_engine("mssql+pyodbc://server\database?driver=SQL Server?Trusted_Connection=yes")
  

• does some modifications to selected data and creates dataframe with employees, e-mail adresses and some additional information

• uses win32com.client library to send e-mails via outlook to employees in created dataframe with some "tailor-made" information

  outlook = win32.Dispatch('outlook.application')

...

for index, row in final_df.iterrows():
    mail = outlook.CreateItem(0)
    mail.To = row["MAIL"]
    mail.SentOnBehalfOfName = "[email protected]"
    mail.cc = row["MANAGER"]
    mail.Subject = ...
    mail.Body = ...
    mail.Send()

1. Is it possible to deploy this kind of script on MS SQL Server? Or possibly on a network drive?

2. Also how con I adjust the code so it won't rely my user profile when it connects to database and when it generates e-mails?

Goal is to schedule it to run automatically on a daily basis.

Answer 1

Generally if you have code or a script that needs to be executed, regardless of the language you will need to publish your code to a server or service that can execute it.

While there are many ways to automate sending emails or reports from SQL Server, your particular script is not one of the usual candidates. As you are using the MS Outlook automation API to open Outlook and send an email, for this to work, you need Outlook to be installed on the executing server AND the script needs to be executed in the context of a user profile that has the mailbox correctly configured. So in short, your specific script requires a user to be logged in, it is merely automating a process that the user could have done themselves.

One solution is to leave your PC on, and schedule the script to be executed using Windows Task Scheduler (or any script/process scheduler of your choice). If this is a critical process you could set your PC to wake up at a time before you need your script executed, so that the schedule will be actioned.

To truly run unattended you will need to change your script logic so that there are no dependencies on local user profile configuration that might not be available. This means you will either need to instantiate an SMTP client manually to transmit the email, (instead of using outlook automation) or find an email service/api that your script can interact with, if you use MS Exchange then you can use the built in Web Services, see Get started with EWS Managed API client applications but there are plenty of other options, like twilio.

There are other options though to running from your PC, clearly your database is hosted on a server somewhere that is online when your PC is off, otherwise there would not be any data change to send an email about, so the next place to look for hosting code would be the database server itself.

1. If you have appropriate permissions, you could use Windows Task Scheduler on the server to execute your script.

   • in its current state that would also require outlook to be installed and configured on the server and that the script executes in the configured user profile.

2. MS Sql Server has an automation process called the SQL Server Agent you can use this to schedule Jobs for execution

   • You script is not a good candidate for this however SQL Server does have a Database Mail component that means you can do this in pure SQL, depending on the complexity of your email content, this is usally a viable option for simple DBA/Admin reporting on a database status.

3. Use a cloud service like Azure or AWS to schedule your script to run. This is way out of scope for this answer, my personal favourite is to make a simple Azure Logic App, but you still need a vastly different script logic to what you have now.

It's probably not what you wanted to hear, but you either need to buy a 3rd party service to do this for your, or develop your script into more of an app or applet. Look into these topics, pick the one that is closest to your code proficiency and post more questions on SO when/if you get stuck.

Answer 2

You cannot run code from nowhere. If you are working at a company, request them to buy a minimal VPS (virtual private server) so it can run all the time. That is very cheap. If its for private needs buy one for yourself.