pythonScripts/designToVerifLabelDiff_insertToDB.py at master · madhatter215/pythonScripts · GitHub

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
#!/pkg/qct/software/python/3.5.2/bin/python

###############################################################################
# Filename: test6.py
# Author: cskebriti                                                           #
# Description:                                                                #
#                                                                             #
#                                                                             #
#   This program picks up the HTML file generated by                          #
#     'versiondiff_hawkeye_napier.pl' and stuffs it into the HeliumDB         #
#     table based on project (e.g. "LithiumNapier_DesignToVerif_LabelDiff"    #
#     for napier).                                                            #
#                                                                             #
#   The Html file is processed only if the timestamp is recent (<1day).       #
#                                                                             #
# See http://napier_mac/diffDesignToLabel.aspx                                #
#                                                                             #
###############################################################################
import sys, os                                  # Std Lib: used here to print error message
import setup.config as config                   # local app: top level config variables
from datetime import datetime, timedelta        # Std Lib: used to compute age of html file
from pathlib import Path                        # Std Lib: to get to json file
import setup.sql_setup as sql_setup             # local app: keeps db passwd in separate file obfiscated


############################################
## Get global config objects and settings ##
DEBUG           = config.DEBUG
HtmlFile        = config.DesignToVerifDiffLabelHtmlOutputFile   #'Path' object
dbTable         = config.DesignToVerifDiffLabelHtmlOutputDBTable#type string
print ("Html file to read in:\n  '{}'".format(str(HtmlFile)))
print ("Helium DB table to insert to:\n  '{}'".format(dbTable))


################################
## Check that the file exists ##
assert(HtmlFile.exists()),  "Error: Path '{}' does not exist".format(HtmlFile)
assert(HtmlFile.is_file()), "Error: File '{}' does not exist".format(HtmlFile)

HtmlFileAsText = HtmlFile.read_text()
HtmlFileAsText.replace("'", '"') #No single quotes, use only double quotes


######################
## Global Constants ##
ONE_DAY_OLD = datetime.now() - timedelta(days=1)


########################################################################
# Function Definitions:
########################################################################

def insertToDB(HtmlFile):
    ######################
    # Create db connection
    db,cursor = sql_setup.opendb()
    try:
        sql = \
        """
        IF NOT EXISTS (SELECT * FROM [dbo].[{1}]
                       WHERE HTMLfile = '{0}')
        INSERT INTO {1} (HTMLfile) VALUES ('{0}')
        """
        print (sql)
#        import pdb; pdb.set_trace()
        cursor.execute(sql.format(HtmlFileAsText,dbTable))
        if DEBUG:
            print ("Debug option set, execute INSERT to DB but will not commit!")
        else:
            db.commit()
        return (cursor.rowcount)
    ####
    # No matter what exception occurs, close the DB connection
    finally: # Disconnect from server
        print ("\n\n**Closing DB connection**\n")
        sql_setup.closedb(db,cursor)


########################################################################
# Main: Start of Program                                              ##
########################################################################
if 'main' in __name__: print ("\n\n****Start of Program****")

lastModified = datetime.fromtimestamp(HtmlFile.stat().st_mtime)
t = lastModified - timedelta(days=1)
#print ("lastModified = '{}', t = '{}', ONE_DAY_OLD = '{}'".format(lastModified,t,ONE_DAY_OLD))

if t < ONE_DAY_OLD:
    print ("Fresh!")
    if insertToDB(HtmlFile): print ("InsertToDB: Success")
    else: print ("InsertToDB: Already up-to-date")

else:
    print ("File found is not recent, exiting program")