forked from beamzer/ScirtScan
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql2excel.py
executable file
·151 lines (127 loc) · 5.23 KB
/
sql2excel.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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
#!/usr/bin/env python3
import sqlite3
import argparse
import os
import sys
import re
from datetime import date
import openpyxl
from openpyxl.styles import Font, PatternFill
version = "v2.1c, 20230702"
today = date.today()
dir = today.strftime("%Y%m%d")
parser = argparse.ArgumentParser(description='creates an index.html page from the sqlite database websites.db')
parser.add_argument('-d','--debug', action='store_true', help='print debug messages to stderr')
parser.add_argument('-p', '--path', type=str, help=f'The directory path, if not given will assume the directory with todays date: {dir}')
parser.add_argument('-m', '--mail', type=str, help='e-mail address to send the Excel file to')
parser.add_argument('-v','--version', action='store_true', help='show version info and exit')
args = parser.parse_args()
debug = args.debug
if args.version:
sys.exit(f"version: {version}")
# If the path argument is provided, use it as the directory path
if args.path:
directory_path = args.path
else:
# If the path argument is not provided, set the directory name to today's date
directory_path = dir
debug and print("debug output activated")
debug and print(f"Will read from, and store into directory: {directory_path}")
# Check if the directory exists
if not os.path.exists(directory_path):
print(f"The directory {directory_path} does not exist.")
exit()
# Connect to the SQLite database in the directory
try:
database = os.path.join(directory_path, 'websites.db')
conn = sqlite3.connect(database)
debug and print(f"Connected to database {database}")
c = conn.cursor()
except sqlite3.Error as e:
sys.exit(f"Error connecting to database {database}: {e}")
# headers as a list, in the order they should appear in the excel sheet
table_headers = ['website',
'https',
'grade',
'grade check',
'HTTPS redirect',
'certificate validity',
'HSTS',
'headers',
'security.txt',
'robots.txt',
'version',
'error',
'remnants',
'debug',
'check date']
# Query the table structure from the meta table
c.execute("SELECT structure FROM meta")
result = c.fetchone()
if result:
table_structure = result[0]
debug and print(f"Table structure: {table_structure}")
# Extract column names from the table structure
column_pattern = re.compile(r'(\w+)\s+[\w\(\)]+(,)?')
columns = [match.group(1) for match in column_pattern.finditer(table_structure) if match.group(1) != "IF"]
# Build the SQL query
sql_query = "SELECT {} FROM website_checks".format(", ".join(columns))
debug and print(f"sql_query = {sql_query}")
else:
sys.exit(f"unable to read database structure from {database}")
cs = conn.cursor()
try:
cs.execute(sql_query)
rows = cs.fetchall()
except sqlite3.Error as error:
sys.exit(f"Failed to fetch data from {database}", error)
# Create a new workbook and worksheet
workbook = openpyxl.Workbook()
worksheet = workbook.active
# Define cell styles for headers, OK values, and NotOK values
header_style = Font(bold=True)
value_ok_style = PatternFill(start_color='00FF89', end_color='00FF89', fill_type='solid')
value_notok_style = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
for col, header in enumerate(table_headers):
worksheet.cell(row=1, column=col+1, value=header).font = header_style
worksheet.column_dimensions[openpyxl.utils.get_column_letter(col+1)].width = 15
# Write the data rows with appropriate styles
for row, data in enumerate(rows):
for col, value in enumerate(data):
debug and print(f"row = {row}, col = {col}, value = {value}")
cell = worksheet.cell(row=row+2, column=col+1, value=value)
# col 0 = website name
# col 2 = grade
# col 5 = cert. validity (days)
# col 6 = hsts duration
# col 14 = check_date
if col in [1,3,4,7,8,9,10,11,12,13]:
if value == 1:
cell.fill = value_ok_style
cell.value = 'OK'
else:
cell.fill = value_notok_style
cell.value = 'NotOK'
# Save the excel workbook
excelfile = os.path.join(directory_path, 'website_checks.xlsx')
try:
workbook.save(excelfile)
print(f"saving to: {excelfile}")
except OSError as e:
sys.exit(f"Error saving excel file: {e}")
# If the -m switch was specified, e-mail the Excel file to the specified address
# N.B. this has to be modified to your specific situation before it will actually send an e-mail
if args.mail:
msg = MIMEMultipart()
msg['To'] = args.mail
msg['Subject'] = 'Website Checks Report'
with open(excelfile, 'rb') as f:
attach = MIMEApplication(f.read(), _subtype = 'xls')
attach.add_header('Content-Disposition', 'attachment', filename='website_checks.xlsx')
msg.attach(attach)
smtp = smtplib.SMTP('smtp.gmail.com', 587)
smtp.starttls()
smtp.login('[email protected]', 'password') # N.B. be very careful with putting passwords in scripts
smtp.quit()