1

I'm not sure if SQL is best to perform the task at hand or maybe a batch or Python script to run the SQL. Just looking for your thoughts and any ideas are much appreciated! Below is what I'm looking to do. Let me know if you need additional info to get me the best response!

We have an existing Database that is continuously being updated. What I need to do is run a Query, daily, and email the results to a particular email address. Each day I want the script to grab only the top 15 results. It would need to sort by Due Date. We have a column for Completed but it doesn't need to show in the report but I would imagine that I need it in the query so the results can be updated each day. Out of all the columns in the DB I only need to Query about 6 and display in the results all 6 besides the Completed column.

If possible, I'd like the email to include the results in HTML format rather than an attachment. My SQL is extremely rusty and I don't remember ever learning how to put into HTML. Below is what I have so far but the last line is not working. Thanks in advance for you help all! 

SELECT [2015 E-Rate Projects].AD, 
[2015 E-Rate Projects].ISR, 
[2015 E-Rate Projects].[Potential Customer], 
[2015 E-Rate Projects].[OppID], 
[2015 E-Rate Projects].[Due Date]
FROM [2015 E-Rate Projects]
WHERE ((([2015 E-Rate Projects].[Response complete])="No"))
ORDER BY [2015 E-Rate Projects].[Due Date]
SELECT TOP 15;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Todd Coplien
  • 85
  • 1
  • 9

1 Answers1

2

If i understood correct you need to use python cron https://pypi.python.org/pypi/python-crontab to execute script every day please refer the documentation. Writing script:

now in order to make connnections you need to install python-mysql package if using mysql

How do I connect to a MySQL Database in Python?

for oracle db use cx_Oracle in python

follow link http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_db/python_db.htm

this link will help you in order to create your desired script..

for email part you can use python-smtplib https://docs.python.org/2/library/smtplib.html

follow for access db What do I need to read Microsoft Access databases using Python?

Community
  • 1
  • 1
kid
  • 153
  • 10