Reports_Automatic_Update_System

Update_xl_thread.py is a script to automatically update reports.

• createdb.py - creates database(SQLite);
• db_connect.py - connects to SQLite database (for testing);
• db_connect_sql.py - connects to SQL Server;
• log_error.py - module to log errors;
• send_mail.py - contains functions to work with Outlook;
• sharepoint.py - checks connection to Sharepoint;
• xl.py - contains functions to work with Excel;
• Update_xl_async.py - script for automatic update (asynchronous) - removed;
• Update_xl_thread.py - script for automatic update (synchronous).

---

Update_xl_thread.py

Update_xl_thread.py connects to the table [dbo].[VV_Hermes_Reports_Temp] on 64 Server and gets info about file needed to be updated. After providing an update, writes info about success/failure into server.

db_connect_sql.py

Module to work with database.

class DBConnect() - establishes connection to database.

Uses methods:

error_description() - downloads error description from db.

file_to_update() - returns info about file from db.

group_attachments(groupname) - generator of attachments for email after updating all of groupname reports.

group_mail_check(groupname) - returns 1 if all files from group groupname have been updated.

send_crash_mail(to) - sends mail from server to recipient to using msdb.dbo.sp_send_dbmail. Contains message about crash of main program.

send_emergency_mail(reportName, to) - sends mail from server to recipient to using msdb.dbo.sp_send_dbmail. Contains message about failure with sending mail after reportName has been updated.

successful_update(rID, update_time) - writes update_time into field [LastDateUpdate] for row with [ReportID] = rID into db.

failed_update(rID, update_time, update_error) - writes update_error into field [Error] and update_time into field [LastDateUpdate] for row with [ReportID] = rID to db.

log_error.py

function writelog() - writes info about error into the file log.txt to the same direcotory where the main program is.

sharepoint.py

Moodule to check connection to sharepoint.

function sharepoint_check() - checks existence of the specific folder on sharepoint. If folder is not found, maps drive R to sharepoint.

send_mail.py

Module to send mail using local Outlook account.

function send_mail(to, copy, subject, body, HTMLBody, att, rName) - sends e-mail to to and copy to copy with subject subject. The body of letter is body and optional HTMLBody. Attachment att can be added. rName is used to write name of last updated file into log.txt in case of error.

xl.py

Module to work with Excel.

class ReadOnlyException() - class for catching read-only state of Excel files.

function update_file(root, f) - updates Excel file f. root - path to file.

function cop_file(root, f, dst) - copy file f to destination dst, where dst - either folder or filename.

---

Upd.manifest - added to prevent admin privileges requirements after converting module into executable file. Tested on 32-bit Python with Pyinstaller.

Usage: pyinstaller -m Upd.manifest Update_xl_thread.py

---

Requirements

• pyodbc ⚠️ Works with pyodbc version < 4.0.22. Pyodbc 4.0.22 have an issue with updating/inserting into field with type smalldatetime.

• pythoncom (part of pywin32; when have a problem installing pywin32, check pypiwin32 and postinstall)

• win32com (also part of pywin32)