-
Notifications
You must be signed in to change notification settings - Fork 7
/
Copy pathpython_script_template.py
104 lines (84 loc) · 3.28 KB
/
python_script_template.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
#!/usr/bin/env python3
"""Create and email a list of new items
Author: Gem Stone-Logan
Contact Info: [email protected] or [email protected]
"""
import psycopg2
import xlsxwriter
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import formatdate
from email import encoders
#Name of Excel File
excelfile = 'OrdersWithoutGrids.xlsx'
# These are variables for the email that will be sent.
# Make sure to use your own library's email server (emaihost)
emailhost = 'smtp.gmail.com'
emailuser = ''
emailpass = ''
emailport = '587'
emailsubject = 'Orders without grids'
emailmessage = '''***This is an automated email***
The weekly new report has been attached. Please take a look and let the Technology Librarian know if there are any questions about it.'''
# Enter your own email information
emailfrom= ''
# emailto can send to multiple addresses by separating emails with commas
emailto = ['','']
#Connecting to Sierra PostgreSQL database
conn = psycopg2.connect("dbname='' user='' host='' port='' password='' sslmode='require'")
#Opening a session and querying the database for weekly new items
cursor = conn.cursor()
cursor.execute("SELECT id2reckey(order_view.record_id)||'a', order_record_cmf.location_code, order_view.accounting_unit_code_num FROM sierra_view.order_view JOIN sierra_view.order_record_cmf ON order_view.record_id=order_record_cmf.order_record_id WHERE order_record_cmf.location_code = 'none';")
#For now, just storing the data in a variable. We'll use it later.
rows = cursor.fetchall()
conn.close()
#Creating the Excel file for staff
workbook = xlsxwriter.Workbook(excelfile)
worksheet = workbook.add_worksheet()
#Formatting our Excel worksheet
worksheet.set_landscape()
worksheet.hide_gridlines(0)
#Formatting Cells
eformat= workbook.add_format({'text_wrap': True, 'valign': 'top'})
eformatlabel= workbook.add_format({'text_wrap': True, 'valign': 'top', 'bold': True})
# Setting the column widths
worksheet.set_column(0,0,14.43)
worksheet.set_column(1,1,7.71)
worksheet.set_column(2,2,9.57)
#Inserting a header
worksheet.set_header('orders without grids')
# Adding column labels
worksheet.write(0,0,'Record_number', eformatlabel)
worksheet.write(0,1,'Location', eformatlabel)
worksheet.write(0,2,'accounting unit', eformatlabel)
# Writing the report for staff to the Excel worksheet
for rownum, row in enumerate(rows):
worksheet.write(rownum+1,0,row[0], eformat)
worksheet.write(rownum+1,1,row[1], eformat)
worksheet.write(rownum+1,2,row[2], eformat)
workbook.close()
#Creating the email message
msg = MIMEMultipart()
msg['From'] = emailfrom
if type(emailto) is list:
msg['To'] = ', '.join(emailto)
else:
msg['To'] = emailto
msg['Date'] = formatdate(localtime = True)
msg['Subject'] = emailsubject
msg.attach (MIMEText(emailmessage))
part = MIMEBase('application', "octet-stream")
part.set_payload(open(excelfile,"rb").read())
encoders.encode_base64(part)
part.add_header('Content-Disposition','attachment; filename=%s' % excelfile)
msg.attach(part)
#Sending the email message
smtp = smtplib.SMTP(emailhost, emailport)
#for Google connection
smtp.ehlo()
smtp.starttls()
smtp.login(emailuser, emailpass)
smtp.sendmail(emailfrom, emailto, msg.as_string())
smtp.quit()